# PostGres PostgreSQL, often referred to as Postgres, is a robust, open-source relational database management system (RDBMS). It has gained prominence for its extensibility, reliability, and support for advanced data types and features. PostgreSQL is widely used in various applications, from small-scale projects to enterprise-level solutions. The documentation will cover the configuration process, usage of the PostgreSQL integration, querying the database via datasets, and troubleshooting common issues. By the end of this guide, you will have a comprehensive understanding of integrating PostgreSQL with Nected and leveraging its capabilities for your applications. ### **Setting Up PostgreSQL Integration** To connect Nected to a PostgreSQL database and enable seamless data interaction, you need to set up a PostgreSQL integration. This section outlines the step-by-step process for configuring the integration effectively.
#### **1. Adding Integration** Select **Postgres** from the integrations page.. This choice initiates the process of setting up an integration for PostgreSQL. #### **2. Configuring Connection Settings** The configuration of connection settings is a critical step in establishing the connection between Nected and your PostgreSQL database. The following parameters need to be defined: * **Environment Type:** Choose whether you are configuring the integration for a "Staging" or "Production" environment. The selection of the environment type determines the context in which the integration will operate. By default, the environment type is “Staging”. * **Integration Name:** Assign a unique and meaningful name for the integration. It is essential that this name is distinct throughout the Nected platform and does not contain any spaces. * **Host (Database IP Address):** Specify the IP address or hostname of the PostgreSQL database to which you want to connect. * **Port Number:** Enter the port number on which Nected should communicate with the PostgreSQL database. The default port for PostgreSQL is 5432. * **Username:** Provide the username of the authorized user who has the necessary privileges to connect to the database and perform read and write operations. * **Password (optional, encrypted):** You may enter the password for the authorized user. Passwords are encrypted to ensure maximum security for your database. If the database user does not have a password, you can leave this field blank. * **Database Name:** Specify the name of the PostgreSQL database from which Nected will access and manipulate data. * **IP Addresses for Allow-list:** To enhance security, it's recommended to configure an allow-list of IP addresses that can access your PostgreSQL database. Ensure that Nected service IP addresses, such as "43.205.43.45," are included in this allow-list to secure the connection. #### **3. Testing the Connection** After configuring the connection settings, it's crucial to test the connection to ensure that Nected can successfully communicate with the PostgreSQL database. This test verifies that the provided information is accurate and the connection is established without issues. #### **4. Publishing in Staging** If the connection test is successful, you can proceed to publish the integration in the "Staging" environment. In this context, "Staging" refers to a development or testing environment. For a "Production" database, the option will be "Publish in Production." Publishing the connector in a specific environment enables you to set up rules and operations within that environment. #### **5. Integration Status** After configuring and publishing the integration, it's essential to check the integration status. Close the connection information form and check the integration status. For a "Staging" environment, the status will be "staging," and for a "Production" environment, it will be "Production." Monitoring the status ensures that the connector is active and properly integrated into the selected environment. {% hint style="info" %} **Note:** If using Nected cloud, Nected IP address (**43.205.43.45**) must be added to the allow-list on your database server to secure your connections. Not required in [on-premise setup](https://docs.nected.ai/nected-docs/integrations/integrations-libraries/broken-reference) {% endhint %} {% hint style="warning" %} You need to publish staging and production integrations in their respective environments. If a integration is published only in staging, then it cannot be used in the production environment, and any call to rule in production env will fail giving *"connector not published in production"* error {% endhint %} ### Querying Postgres Database via DataSet
Nected provides a powerful feature that allows you to query and interact with your PostgreSQL database through datasets. This section will guide you through the process of adding a new dataset, writing SQL code for querying the PostgreSQL database, and performing various operations, including creating rows and executing join operations. #### **Adding a New DataSet:**
Before you can query the PostgreSQL database, you need to add a new dataset to your Nected environment. Follow these steps: 1. **Navigate to Datasets**: Click on "Datasets" in the left navigation panel to access the Datasets page. 2. **Create a Dataset**: Click the "+ Create Dataset" button on the Datasets page. Choose the database integration associated with the dataset you wish to create. Note that you can create multiple datasets for one integration. 3. **Dataset Information**: Complete the dataset information form, including details such as the dataset name, dataset type (staging or production), the source for dataset parameters. 4. Finally you need to write the specific database query to retrieve data from your connected database. For example, to connect the complete databse within your dataset, write: ```sql SELECT * FROM table_name; //Change the table_name with your database table_name ``` #### **Query Operations:** In addition to basic querying, you can perform various operations on the data retrieved from the PostgreSQL database. Here are some common tasks you can carry out:
#### **1. Aggregations:** You can use aggregations to summarize and analyze your financial data. For instance, to calculate the total deposit amount for a specific day: ```sql SELECT transaction_date, SUM(amount) AS total_deposit FROM financial_transactions WHERE transaction_type = 'Transfer' GROUP BY transaction_date; ``` This query aggregates the total deposit amount for each distinct transaction date. #### **2. Filtering:** Filtering is essential for retrieving specific subsets of data. To find all transactions above a certain amount, you can use: ```sql SELECT transaction_date, amount, description FROM financial_transactions WHERE amount > 1000; ``` This query filters and selects transactions with amounts greater than $1000. #### **3. Sorting:** Sorting data can be useful for visualizing trends or identifying the most significant transactions. To sort transactions by amount in descending order: ```sql SELECT transaction_date, amount, description FROM financial_transactions ORDER BY amount DESC; ``` This query returns transactions sorted by the amount in descending order. #### **4. Grouping and Aggregating:** Combining grouping and aggregation helps summarize data based on certain criteria. To find the total transaction amount by transaction type: ```sql SELECT transaction_type, SUM(amount) AS total_amount FROM financial_transactions GROUP BY transaction_type; ``` This query groups transactions by type and calculates the total amount for each type. #### **5. Joins:** If your database has multiple related tables, joins can be used to combine data from different sources. For instance, to link transactions with account details: ```sql SELECT t.transaction_id, t.transaction_date, t.amount, a.account_name FROM financial_transactions t JOIN accounts a ON t.account_id = a.account_id; ``` This query joins the `financial_transactions` table with an `accounts` table to retrieve transactions along with their corresponding account names. These SQL operations and queries can help you extract valuable insights and generate reports from your financial data stored in the `financial_transactions` table. The principles and techniques demonstrated here can be adapted and expanded upon to suit specific analysis and reporting needs in the fintech industry or any database scenario. > **Tip:** Always ensure that your SQL code is well-tested in the "Staging" environment before deploying it to the "Production" environment to prevent potential data integrity issues and unexpected consequences. ### **Supported Queries for Creating a Dataset:** When creating a dataset in Nected, you have the flexibility to retrieve and filter data using a variety of PostgreSQL queries. These queries help you shape your dataset to meet your analytical needs. Below are the supported queries for creating a dataset: | Query Type | Description | Example | | -------------------- | -------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Select Queries | Retrieve data from one or more tables, with various filtering and sorting options. | SELECT \* FROM products WHERE price > 50; | | | | SELECT customer\_name, order\_date FROM customers JOIN orders ON customers.customer\_id = orders.customer\_id; | | Aggregation Queries | Perform data analysis with aggregation functions like SUM, COUNT, GROUP BY, etc. | SELECT product\_id, SUM(sale\_amount) AS total\_sales FROM sales GROUP BY product\_id; | | Join Queries | Combine data from multiple tables using JOIN operations for more complex queries. | SELECT customers.customer\_name, orders.order\_date FROM customers JOIN orders ON customers.customer\_id = orders.customer\_id; | | Subqueries | Use subqueries to nest one query inside another, often in the WHERE or FROM clauses. | SELECT product\_name FROM products WHERE product\_id IN (SELECT product\_id FROM sales WHERE sale\_amount > 1000); | | Union Queries | Merge the results of two or more SELECT queries into a single result set. | SELECT customer\_name FROM customers UNION SELECT supplier\_name FROM suppliers; | | Intersection Queries | Retrieve common records from two or more SELECT queries. | SELECT product\_id FROM products WHERE price > 50 INTERSECT SELECT product\_id FROM sales; | | Difference Queries | Find records that exist in one SELECT query but not in another. | SELECT product\_id FROM products WHERE price > 50 EXCEPT SELECT product\_id FROM sales; | | Conditional Queries | Use conditional expressions (CASE WHEN) to perform conditional logic within queries. | SELECT product\_name, CASE WHEN price > 100 THEN 'Expensive' ELSE 'Affordable' END AS price\_category FROM products; | | Window Functions | Analyze data across a set of table rows related to the current row with functions like ROW\_NUMBER(), RANK(), LEAD(), and LAG(). | SELECT product\_name, price, ROW\_NUMBER() OVER (ORDER BY price) AS row\_num FROM products; | | Recursive Queries | Create queries that refer to themselves, commonly used for hierarchical data. | WITH RECURSIVE employee\_hierarchy AS ( SELECT employee\_id, manager\_id FROM employees WHERE manager\_id IS NULL UNION ALL SELECT e.employee\_id, e.manager\_id FROM employees e INNER JOIN employee\_hierarchy eh ON e.manager\_id = eh.employee\_id ) SELECT \* FROM employee\_hierarchy; | | Dynamic SQL | Construct and execute SQL queries dynamically based on runtime conditions or user inputs. | Example of constructing a dynamic SQL query based on user input. | ### **Non-Supported Queries for Creating a Dataset** While Nected offers extensive support for data retrieval and shaping, some certain queries and operations are not supported when creating a dataset: | Query Type | Description | Example | | ---------------------------- | ----------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------ | | Delete Queries | Removing records from a table is not supported when creating a dataset. | DELETE FROM products WHERE price < 10; | | Truncate Table | Truncating a table to remove all records is not supported. | TRUNCATE TABLE orders; | | Create, Alter, Drop Queries | Queries for creating, altering, or dropping tables and database schema are not supported. | CREATE TABLE new\_table (column1 INT, column2 VARCHAR); | | Indexing Queries | Creating or managing indexes on tables is not supported. | CREATE INDEX idx\_customer\_name ON customers (customer\_name); | | Stored Procedures | Creating stored procedures and functions within the database is not supported. | CREATE PROCEDURE calculate\_total\_sales() BEGIN SELECT SUM(sale\_amount) FROM sales; END; | | Grant and Revoke Permissions | Managing user privileges and permissions on database objects is not supported. | GRANT SELECT ON products TO user1; | ## Trigger Postgres operations as Rule Actions
In Nected, rule actions provide a way to automate PostgreSQL operations when specific conditions are met. While datasets are typically used for read-only queries, rule actions allow you to modify the dataset and trigger specific operations within your PostgreSQL database. Below, we'll explore some SQL queries that can be used as rule actions to write or edit data within the `financial_transactions` table: #### **1. Inserting New Financial Transactions:** 1. **Static Query** To insert new financial transactions into the PostgreSQL database as a rule action, you can use the `INSERT INTO` statement with static values. For example, if a rule is triggered to log a new deposit: ```sql INSERT INTO financial_transactions (transaction_date, transaction_time, account_id, transaction_type, amount, currency_code, description, status) VALUES ('2023-10-21', '09:30:00', 106, 'Deposit', 1500.00, 'USD', 'Client Payment', 'Completed'); ``` This query adds a new deposit transaction to the database with the specified details. 2. **Tokenized Query Using Output Data, Custom Input and/or Data from Dataset** To make the query dynamic and utilize data from the rule's output or custom input or dataset, you can use tokenized queries with token attributes. For example, if you want to insert a new transaction with attributes from either of those: ```sql INSERT INTO financial_transactions (transaction_date, transaction_time, account_id, transaction_type, amount, currency_code, description, status) VALUES ('{{.dataSet.transaction_date}}', '{{.dataSet.transaction_time}}', {{.customInput.account_id}}, '{{.dataSet.transaction_type}}', {{.outputData.amount}}, '{{.outputData.currency_code}}', '{{.outputData.description}}', 'Completed'); ``` This query fetches the transaction details from the rule's output data, custom input and details from dataset attributes. #### **2. Updating Transaction Status:** 1. **Static Query** You can use the `UPDATE` statement with static values as a rule action to modify the status of specific transactions. For instance, if a rule action is set to change the status of a transaction: ```sql UPDATE financial_transactions SET status = 'Completed' WHERE transaction_id = 21; ``` This query updates the status of a transaction with a specific `transaction_id` to 'Completed'. 2. **Tokenized Query Using Output Data, Custom Input and/or Data from Dataset** To make the query dynamic and utilize data from the rule's output or custom input or dataset, you can use tokenized queries with token attributes. For example, if you want to update the status of a transaction based on either of these: ```sql UPDATE financial_transactions SET status = '{{.outputData.new_status}}' WHERE transaction_id = {{.customInput.transaction_id}}; ``` This query adjusts the status of a transaction using data from the rule's output, custom inputs. If needed, you can also use token to extract data from dataset attributes. These SQL queries demonstrate how you can use rule actions with both static and tokenized queries to perform data modification operations within the PostgreSQL database in response to specific triggers or conditions. Rule actions offer flexibility in automating data updates, ensuring data accuracy, and enhancing the functionality of your fintech application or database. ### **Supported Queries for Writing an Action** Nected allows you to perform various actions on your data, such as inserting, updating, and merging records, all while ensuring data integrity. Here are the supported queries for writing an action: | Query Type | Description | Example | | --------------------- | ----------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Insert Queries | Add new records to a table. | INSERT INTO products (product\_name, price) VALUES ('New Product', 50); | | Update Queries | Modify existing records in a table. | UPDATE customers SET email = '' WHERE customer\_id = 123; | | Merge Queries | Combine data from source table(s) into a target table, typically used for data synchronization. | MERGE INTO target\_table USING source\_table ON target\_table.id = source\_table.id WHEN MATCHED THEN UPDATE SET target\_table.value = source\_table.value WHEN NOT MATCHED THEN INSERT (id, value) VALUES (source\_table.id, source\_table.value); | | Transactional Queries | Execute a sequence of queries within a transaction to ensure consistency. | BEGIN; INSERT INTO orders (order\_id, customer\_id, order\_date) VALUES (789, 123, '2023-11-15'); UPDATE customers SET total\_orders = total\_orders + 1 WHERE customer\_id = 123; COMMIT; | ### **Non-Supported Queries for Writing an Action** When it comes to writing actions in Nected, there are limitations on the types of queries and operations that can be performed to ensure data consistency and security. Here are the queries that are not supported for writing an action: | Query Type | Description | Example | | ---------------------------- | ---------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------ | | Truncate Table | Truncating a table to remove all records is not supported as actions. | TRUNCATE TABLE orders; | | Create, Alter, Drop Queries | Queries for creating, altering, or dropping tables and database schema are not supported as actions. | CREATE TABLE new\_table (column1 INT, column2 VARCHAR); | | Indexing Queries | Creating or managing indexes on tables is not supported as actions. | CREATE INDEX idx\_customer\_name ON customers (customer\_name); | | Stored Procedures | Creating stored procedures and functions within the database is not supported as actions. | CREATE PROCEDURE calculate\_total\_sales() BEGIN SELECT SUM(sale\_amount) FROM sales; END; | | Grant and Revoke Permissions | Managing user privileges and permissions on database objects is not supported as actions. | GRANT SELECT ON products TO user1; | ### Add PostgreSQL as Workflow Node Adding nodes in the workflow editor of Nected is essential because they represent the individual tasks, decisions, or actions that comprise the overall automated process. By adding and configuring nodes, users can tailor workflows to fit their specific business processes, data handling requirements, and decision logic, ensuring that tasks are executed efficiently and consistently. To add PostgreSQL as a workflow node, follow the following steps: 1. Within the workflow canvas, you would typically click the '+' or 'Add Node' button to reveal the list of nodes. 2. Choose PostgreSQL from the node list. These are the most basic steps, for a more detailed overview of how to configure it, read the [DB Node](https://docs.nected.ai/nected-docs/workflow/add-node/action-nodes/database-node) page. #### Supported & Non-supported Queries as Workflow Node: 1. While used as a Workflow Node, all types of `SELECT` and `INSERT or UPDATE` queries are supported. 2. The list of Non-Supported query list is just the same as '[Non-Supported Queries for Writing an Action](#non-supported-queries-for-writing-an-action)'. ### **Next Steps** After you successfully publish the database connector, you can use it via [Dataset](https://docs.nected.ai/nected-docs/datasets/overview) to unify data & attach it as input to the [Rules](https://docs.nected.ai/nected-docs/rules/overview) as well as to the [Workflows](https://docs.nected.ai/nected-docs/workflow) to invoke any database operations as part of [Rule Actions](https://docs.nected.ai/nected-docs/rules/rule-actions) or [Workflow Nodes](https://docs.nected.ai/nected-docs/workflow/add-node). --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.nected.ai/nected-docs/integrations/integrations-libraries/postgres.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.