Postgres SQL

Learn how to seamlessly connect PostgreSQL to Engini, enabling efficient database integration for your workflows. With the PostgreSQL connector, you can manage data, run queries, and automate database interactions directly in Engini. Enhance productivity by integrating PostgreSQL’s powerful capabilities into your business processes.

Getting Started with Postgres SQL #

Prerequisites #

• A PostgreSQL account: This includes the username and password to access the database.

Connecting Engini to Postgres SQL #

1. Enter your Engini account at https://app.engini.io.
2. Navigate to Connections page by clicking on the Connections on the left side bar or by clicking here.
3. Click on the “Add connection” option located at the top bar.
4. Choose Postgres SQL option from available applications.

5. Enter the following details in the “Add Connection” form:

• Connection Name: Choose a name for easy reference to this PostgreSQL connection.
  
• Server Address: The IP address or domain where the PostgreSQL server is hosted.
  
• Database Name: The name of the specific PostgreSQL database you want to connect to.
  
• Schema: The schema within the database, usually set to public if no other schema is specified.
  
• Username: The PostgreSQL username with sufficient access rights.
  
• Password: The corresponding password for the PostgreSQL username.
  
• Communication Channel: Ensure the communication channel (Cloud or OPA) is accessible for remote connection, depending on your preferred method.

Actions #

Specific tasks you want to perform in your PostgreSQL database through Engini. These actions can automate data management, such as inserting records, running queries, or interacting with specific tables and databases in PostgreSQL.

Create Record #

This activity allows you to create a new record in a specified PostgreSQL table.

1. Table – Choose the table where the new record will be inserted. Make sure to select the correct table from your PostgreSQL database.
2. Keep connection alive until complete – Ensures the connection remains active until the record creation process is fully complete.

Get Records #

This activity retrieves records from a specified PostgreSQL table or view. You can set limits, sorting, and filters to refine the results.

1. Table/View – Specify the name of the table or view from which you want to retrieve records. Ensure the correct PostgreSQL table or view is selected.
2. Top – Define the maximum number of rows to retrieve. The default is 100 if no value is provided.
3. Offset – Specify the number of rows to skip before starting the retrieval. This is useful for pagination. The default is 0 (no rows skipped).
4. Sort by – Choose the field by which you want to sort the retrieved records. You can also specify whether the sorting should be in ascending or descending order.
5. Filter – Add conditions to filter the records you want to retrieve. Choose the field, condition, and value to limit the results to what you need.
6. Keep connection alive until complete – Keeps the connection active while the records are being retrieved, ensuring the process completes without interruptions.

Update Record #

This activity allows you to update an existing record in a specified PostgreSQL table.

1. Table – Specify the name of the table where the record you want to update is located. Ensure the correct PostgreSQL table is selected.
2. Keep connection alive until complete – Keeps the connection active during the update process to ensure it finishes without interruptions, especially for larger or more complex updates.

Update Records #

This activity allows you to update multiple records in a specified PostgreSQL table based on a filter. You can specify the table, and add filtering conditions.

1. Table – Enter the name of the table where the records you want to update are located. Ensure the correct PostgreSQL table is selected.
2. Filter – Add conditions to specify which records should be updated. You can select a field, set a condition, and provide a value to filter the records.
3. Keep connection alive until complete – Ensures that the connection remains open until the update process is fully complete, especially useful for updating multiple records or large datasets.

Delete Records #

This activity allows you to delete records from a specified PostgreSQL table based on a defined filter.

1. Table – Specify the table from which records will be deleted. Make sure to select the correct table from your PostgreSQL database.
2. Filter – Use this option to set conditions or filters to specify which records should be deleted. This ensures you only delete the intended records.
3. Keep connection alive until complete – Keeps the connection active during the entire deletion process, particularly useful when deleting large datasets or performing complex operations.

Create Batch of Records #

This activity allows you to insert a batch of records into a specified PostgreSQL table.

1. Data List – Specifies the list of records you want to insert. The batch can handle up to 1000 items.
2. Table – The name of the table where the records will be inserted. Ensure the correct table from your PostgreSQL database is selected.
3. Keep connection alive until complete – Ensures the connection remains active during the entire batch insert process, especially for large datasets or complex operations.

Get Records Batch #

This activity retrieves a batch of records from a specified PostgreSQL table. You can define the batch size, set sorting and filtering options.

1. Data List – Specifies the list of records to retrieve in a batch. The maximum batch size is 1000 items.
2. Table – Enter the name of the table from which you want to retrieve the batch of records. Ensure the correct PostgreSQL table is selected.
3. Top – Define the maximum number of rows to retrieve. The default value is 100.
4. Sort by – Choose the field to sort the retrieved records by. You can select ascending or descending order.
5. Filter – Add conditions to filter the records before retrieval. You can choose a field, set a condition, and define a value to limit the results.
6. Keep connection alive until complete – Keeps the connection active until the entire batch retrieval process is completed, useful for handling large datasets or complex queries.

Update Batch of Records #

This activity allows you to update a batch of records in a specified PostgreSQL table. You can provide the list of data to update, and set filtering conditions.

1. Data List – Specifies the list of records to be updated. The maximum batch size is 1000 items.
2. Table – Enter the name of the table where the batch of records will be updated. Ensure the correct PostgreSQL table is selected.
3. Filter – Add conditions to specify which records should be updated. You can choose a field, set a condition, and provide a value to filter the batch.
4. Keep connection alive until complete – Ensures that the connection remains active throughout the entire batch update process, which is helpful when working with large datasets or complex updates.

Execute Customized SQL #

This activity allows you to run a custom SQL query directly on a PostgreSQL database. You can write any SQL command, such as SELECT, INSERT, UPDATE, or DELETE, and execute it against the database.

1. SQL – Enter the custom SQL query you want to execute. Ensure the query is correctly formatted for PostgreSQL and targets the correct database objects (e.g., tables, views).
2. Keep connection alive until complete – Keeps the connection active until the SQL query execution is fully completed, especially useful for long-running or complex queries.

Execute Procedure #

This activity allows you to execute a stored procedure in a PostgreSQL database. A stored procedure is a set of SQL statements that can perform a specific task, such as data manipulation or complex operations, in one call.

1. Procedure Name – Enter the name of the stored procedure you want to execute. Ensure that the procedure exists in the PostgreSQL database and that it has the required parameters (if any).
2. Keep connection alive until complete – Keeps the connection open until the stored procedure finishes executing, which is helpful for longer-running processes or procedures that involve multiple steps.