PostgreSQL

1. Overview

PostgreSQL is a powerful, open-source object-relational database system that uses and extends the SQL language. It is known for its proven architecture, reliability, data integrity, robust feature set, and extensibility. PostgreSQL runs on all major operating systems and has been in active development for over 30 years.

Through GoInsight's PostgreSQL node, you can seamlessly integrate database operations into your automated workflows. You can perform comprehensive database management tasks including:

• Query and retrieve data from tables with advanced filtering and pagination
• Insert and update records with support for batch operations and upsert functionality
• Delete and manage table data with multiple deletion modes
• Execute custom SQL statements with parameterized queries for flexibility
• Inspect table structure to retrieve metadata and schema information

2. Prerequisites

Before using this node, you need to have the following:

• A valid PostgreSQL database instance that is accessible from your GoInsight environment.
• Appropriate database credentials (host, port, database name, username, and password) with sufficient permissions to perform the desired operations.
• Network connectivity between GoInsight and your PostgreSQL server (firewall rules configured if necessary).

3. Credentials

For detailed guidance on how to obtain and configure PostgreSQL credentials, please refer to our official documentation: Credentials Configuration Guide.

4. Supported Operations

Summary

The PostgreSQL node provides comprehensive operations for managing database records, executing queries, and inspecting table structures. All operations support parameterized queries to prevent SQL injection and provide debug mode for troubleshooting.

Operation Details

Describe

Retrieves the structure of a PostgreSQL table, including detailed information about columns such as column names, data types, null constraints, and default values. Queries the information_schema.columns system table to obtain table metadata.

Input Parameters:

• Table: The name of the table to describe.

Options:

• Schema: Schema name (namespace). Default: "public".
• ConnectionTimeout: Database connection timeout in seconds. Default: 30.
• Debug: Whether to enable debug mode. When set to true, the response will include the RawSql field. Default: false.

Output:

• Success (bool): Whether the operation was successful
• Result (object-array): Table structure information array, each object contains column details
• RawSql (string): Raw SQL statement executed (only returned when debug=true)
• StatusCode (number): HTTP status code or operation status code (-1 for parameter error, 500 for exceptions)
• ErrorMessage (string): Error message description, returns empty string on success

Execute

Executes one or more PostgreSQL SQL statements with parameterized queries. Supports multiple SQL statements separated by semicolons, with three execution modes: single (process all statements at once), independently (execute each statement separately), and transaction (execute all statements within a transaction with rollback on failure).

Input Parameters:

• Query: SQL query statement(s). Multiple statements can be separated by semicolons. Use $1, $2, $3 placeholders for parameters. Example: "SELECT * FROM users WHERE age > $1;INSERT INTO users(name) VALUES($2)"

Options:

• QueryValues: Array of parameter values for the SQL statement(s). The values will be used to replace $1, $2, $3 placeholders in the query. Example: ["18", "1", "hello@gmail.com", "Melody", "25", "1"]. Default: [].
• ReplaceEmptyStrings: Whether to replace empty strings with NULL. Default: false.
• QueryBatching: Batch processing mode. Valid values: "single" (process all statements at once, fastest), "independently" (execute each statement separately), "transaction" (execute all statements within a transaction with rollback on failure). Default: "single".
• ConnectionTimeout: Database connection timeout in seconds. Default: 30.
• TreatQuotedParamsAsText: Whether to treat query parameters in single quotes as text. When set to true, parameters like '$1' inside single quotes will be treated as literal text and not replaced. Default: false.
• Debug: Whether to enable debug mode. When set to true, the response will include the RawSql field. Default: false.

Output:

• Success (bool): Whether the operation was successful
• Result (object-array): Query result data
• AffectedRows (number): Number of rows affected by the operation
• InsertId (number): Last inserted ID (for INSERT statements)
• RawSql (string): Raw SQL statement executed (only returned when debug=true)
• StatusCode (number): HTTP status code or operation status code (-1 for parameter error, 500 for exceptions)
• ErrorMessage (string): Error message description, returns empty string on success

Insert

Inserts one or more rows into a PostgreSQL table. Supports single or batch insert operations with different batch processing modes (single, independently, transaction). Allows skipping on conflict using ON CONFLICT DO NOTHING and replacing empty strings with NULL. Features RETURNING clause to retrieve inserted data.

Input Parameters:

• Table: The name of the table to insert data into.
• Values: Array of objects containing the data to insert. Example: [{"name":"Melody","age":"25"},{"name":"Alice","age":"30"}].

Options:

• Schema: Schema name (namespace). Default: "public".
• DataMode: Data mapping mode. Valid values: "defineMap" (manually define data mapping), "autoMap" (automatically use previous node data, not yet supported). Default: "defineMap".
• QueryBatching: Batch processing mode. Valid values: "single" (single query mode for best performance), "independently" (each statement executed independently), "transaction" (all statements in a transaction). Default: "single".
• SkipOnConflict: Whether to skip on conflict using ON CONFLICT DO NOTHING. Default: false.
• ReplaceEmptyStrings: Whether to replace empty strings with NULL. Default: false.
• OutputColumns: Array of columns to return using RETURNING clause. Default: ["*"] (all columns). Empty array means no return.
• ConnectionTimeout: Database connection timeout in seconds. Default: 30.
• Debug: Whether to enable debug mode. When set to true, the response will include the RawSql field. Default: false.

Output:

• Success (bool): Whether the operation was successful
• Result (object-array): Query result data
• AffectedRows (number): Number of rows affected by the insert operation
• RawSql (string): Raw SQL statement executed (only returned when debug=true)
• StatusCode (number): HTTP status code or operation status code (-1 for parameter error, 500 for exceptions)
• ErrorMessage (string): Error message description, returns empty string on success

Select

Queries data from a PostgreSQL table with support for conditional filtering, pagination, sorting, and distinct results. Returns paginated query results with metadata including total count, page information, and whether more data is available.

Input Parameters:

• Table: The name of the table to query data from.
• Where: WHERE clause for filtering. Use $1, $2, $3 etc. as placeholders for values. Example: "age > $1 AND (name = $2 OR email = $3)".

Options:

• Schema: Schema name (namespace). Default: "public".
• WhereValues: Array of values for the WHERE clause placeholders. Example: ["18", "Melody", "hello@gmail.com"].
• Page: Page number for pagination. Default: 1.
• PageSize: Number of records per page. Default: 50.
• Sort: Sorting rules as array of objects. Example: [{"id":"asc"},{"updated_at":"desc"}].
• Distinct: Whether to return distinct results. Default: false.
• OutputColumns: Array of columns to return in the result. Example: ["id","name","email"]. Default: [] (all columns).
• ReplaceEmptyStrings: Whether to replace empty strings with NULL in WHERE values. Default: false.
• ConnectionTimeout: Database connection timeout in seconds. Default: 30.
• Debug: Whether to enable debug mode. When set to true, the response will include the RawSql field. Default: false.

Output:

• Success (bool): Whether the operation was successful
• Result (object-array): Query result data rows
• QueryCount (number): Number of rows returned in this query
• TotalCount (number): Total number of rows matching the query condition
• TotalPage (number): Total number of pages based on pageSize
• CurrentPage (number): Current page number
• HasMore (bool): Whether there are more pages available
• RawSql (string): Raw SQL statement executed (only returned when debug=true)
• StatusCode (number): HTTP status code or operation status code (-1 for parameter error, 500 for exceptions)
• ErrorMessage (string): Error message description, returns empty string on success

Upsert

Inserts or updates data in a PostgreSQL table based on unique key matching. Uses INSERT ... ON CONFLICT ... DO UPDATE syntax to perform upsert operations. Supports single or batch data processing with three execution modes: single query, independent queries, and transaction-based processing. Includes RETURNING clause to retrieve inserted or updated records.

Input Parameters:

• Table: The name of the table to perform upsert operation.
• ColumnsToMatchOn: Array of column names used for ON CONFLICT clause. These columns must be unique constraint or primary key columns. Supports composite keys. Example: ["email","status"].
• ValuesToSend: Array of data objects to insert or update. Example: [{"name":"Melody","age":"25","email":"hello@gmail.com","status":"1"}].

Options:

• Schema: Schema name (namespace) where the table is located. Default: "public".
• DataMode: Data mapping mode. Valid values: "defineMap" (manual data mapping), "autoMap" (automatic data mapping, not supported yet). Default: "defineMap".
• QueryBatching: Batch processing mode. Valid values: "single" (single query mode), "independently" (independent query mode), "transaction" (transaction mode). Default: "single".
• ReplaceEmptyStrings: Whether to replace empty strings with NULL. Default: false.
• OutputColumns: Array of columns to return via RETURNING clause. Default: [] (no return). ["*"] returns all columns.
• ConnectionTimeout: Database connection timeout in seconds. Default: 30.
• Debug: Whether to enable debug mode. When set to true, the response will include the RawSql field. Default: false.

Output:

• Success (bool): Whether the operation was successful
• Result (object-array): Query result data
• AffectedRows (number): Number of rows affected by the upsert operation
• RawSql (string): Raw SQL statement executed (only returned when debug=true)
• StatusCode (number): HTTP status code or operation status code (-1 for parameter error, 500 for exceptions)
• ErrorMessage (string): Error message description, returns empty string on success

Update

Updates rows in a PostgreSQL table by matching specific columns and modifying other fields. Supports atomic updates with RETURNING clause to fetch updated records, empty string to NULL conversion, and parameterized queries for SQL injection prevention.

Input Parameters:

• Table: The name of the table to update.
• ColumnsToMatchOn: Array of columns used to identify records for updating via WHERE clause. These columns will NOT be modified. Must be unique columns (primary key or unique key). Example: ["id"] or ["email","status"] for composite keys.
• ValuesToSend: Object containing fields and values to update. Can include match columns' values. Example: {"name":"Melody","age":"25","id":"123"}.

Options:

• Schema: Schema name (namespace). Default: "public".
• DataMode: Data mapping mode. Valid values: "defineMap" (manual data mapping), "autoMap" (auto-use previous node data, not yet supported). Default: "defineMap".
• ReplaceEmptyStrings: Whether to replace empty strings with NULL. Default: false.
• OutputColumns: Array of columns to return using RETURNING clause. Default: [] (no return). ["*"] returns all columns.
• ConnectionTimeout: Database connection timeout in seconds. Default: 30.
• Debug: Whether to enable debug mode. When set to true, the response will include the RawSql field. Default: false.

Output:

• Success (bool): Whether the operation was successful
• Result (object-array): Query result data
• AffectedRows (number): Number of rows affected by the update operation
• RawSql (string): Raw SQL statement executed (only returned when debug=true)
• StatusCode (number): HTTP status code or operation status code (-1 for parameter error, 500 for exceptions)
• ErrorMessage (string): Error message description, returns empty string on success

Delete

Deletes rows from a PostgreSQL table based on specified conditions, or clears/drops the entire table. Supports three deletion modes: delete (conditional row deletion), truncate (clear all data while preserving table structure with optional sequence restart), and drop (remove the entire table with optional cascade).

Input Parameters:

• Table: The name of the table to delete from.
• Where: WHERE condition statement for DELETE command. Supports AND/OR logical operators and $1, $2, $3 placeholders for parameters.

Options:

• Schema: Schema name (namespace). Default: "public".
• Command: Deletion command type. Valid values: "delete" (conditional row deletion), "truncate" (clear all data), "drop" (remove table). Default: "delete".
• WhereValues: Array of parameter values for the WHERE clause. Example: ["18", "Melody", "hello@gmail.com"].
• RestartSequences: Whether to reset sequences (auto-increment IDs) when using TRUNCATE. Default: false.
• Cascade: Whether to cascade delete dependent objects when using TRUNCATE or DROP. Default: false.
• ConnectionTimeout: Database connection timeout in seconds. Default: 30.
• Debug: Whether to enable debug mode. When set to true, the response will include the RawSql field. Default: false.

Output:

• Success (bool): Whether the operation was successful
• AffectedRows (number): Number of rows affected by the delete operation
• Result (object-array): Query result (usually empty array for delete)
• RawSql (string): Raw SQL statement executed (only returned when debug=true)
• StatusCode (number): HTTP status code or operation status code (-1 for parameter error, 500 for exceptions)
• ErrorMessage (string): Error message description, returns empty string on success

5. Example Usage

This section will guide you through creating a simple workflow to query users from a PostgreSQL table. This example demonstrates how to use the Select operation to retrieve filtered data with pagination.

Workflow Overview: The workflow consists of three nodes: Start → PostgreSQL (Select) → Answer

Step-by-Step Guide:

1. Add the PostgreSQL Node:
   • In the workflow canvas, click the "+" button to add a new node.
   • In the panel that appears, select the "Tools" tab.
   • Find and select PostgreSQL from the tools list.
   • In the list of supported operations for PostgreSQL, click and select Select. This will add a Select node to your canvas.
2. Configure the Node:
   • Click on the newly added Select node, and the configuration panel will open on the right side.
   • Credentials Configuration: At the top of the panel, locate the credentials field. Click the dropdown menu and select your pre-configured PostgreSQL credentials. If you haven't configured credentials yet, refer to the Credentials section above.
   • Parameter Configuration:
   • Table: Enter the name of the table you want to query, for example, "users".
   • Where: Enter the WHERE clause for filtering. For example, to find users older than 18, enter "age > $1".
   • WhereValues: Provide the array of values for the WHERE clause placeholders. For our example, enter ["18"].
   • Schema (optional): If your table is not in the default "public" schema, specify the schema name here.
   • Page (optional): Specify which page of results to retrieve. Leave as default (1) to get the first page.
   • PageSize (optional): Specify how many records per page. Leave as default (50) or adjust based on your needs.
   • OutputColumns (optional): To retrieve specific columns only, enter an array like ["id", "name", "email"]. Leave empty to retrieve all columns.
3. Run and Verify:
   • Once all required parameters are correctly filled in, any error indicators in the top-right corner of the workflow canvas will disappear.
   • Click the "Test Run" button in the top-right corner of the canvas to execute the workflow.
   • After successful execution, click the log icon in the top-right corner to view the detailed inputs and outputs of the node.
   • In the output, you'll see the Result array containing the queried user records, along with pagination metadata like TotalCount, CurrentPage, and HasMore.

Final Workflow Summary: After completing the above steps, your workflow is ready. When you click "Test Run", the PostgreSQL node will query users from the specified table based on your filter conditions, and return paginated results that you can use in subsequent workflow steps.

6. FAQs

Q: What should I do if I get a connection timeout error?

A: Connection timeout errors typically occur when GoInsight cannot reach your PostgreSQL server. Please check the following:

• Verify that your PostgreSQL server is running and accessible.
• Check your firewall rules to ensure GoInsight's IP addresses are allowed to connect to your PostgreSQL server.
• Increase the ConnectionTimeout parameter if your database server is slow to respond.
• Verify that your credentials (host, port, username, password) are correct.

Q: How do I handle SQL injection vulnerabilities?

A: All PostgreSQL operations in GoInsight support parameterized queries using placeholders ($1, $2, $3, etc.). Always use these placeholders instead of concatenating user input directly into your SQL statements. For example:

• ✅ Good: WHERE clause = "email = $1", WhereValues = ["user@example.com"]
• ❌ Bad: WHERE clause = "email = 'user@example.com'" (vulnerable to SQL injection)

Q: What's the difference between Insert and Upsert?

A: Insert always creates new records and will fail if there's a conflict with existing unique keys. Upsert (INSERT ... ON CONFLICT ... DO UPDATE) will insert if the record doesn't exist, or update the existing record if there's a conflict on the specified unique columns.

Q: How do I use the Execute operation for custom SQL queries?

A: The Execute operation allows you to run any valid PostgreSQL SQL statement. Use the Query parameter to provide your SQL, and use $1, $2, $3 placeholders for dynamic values. Provide the actual values in the QueryValues array. You can also execute multiple statements separated by semicolons and control their execution mode using the QueryBatching parameter.

Q: What does the Debug parameter do?

A: When Debug is set to true, the operation will include a RawSql field in the response that shows the actual SQL statement executed. This is extremely helpful for troubleshooting query issues, verifying parameter substitution, and understanding exactly what SQL is being sent to your database.

Q: Can I perform transactions across multiple operations?

A: Yes, you can use the Execute operation with QueryBatching set to "transaction". This will execute all your SQL statements within a single database transaction, and if any statement fails, all changes will be rolled back automatically.

7. Official Documentation

For more advanced features and detailed PostgreSQL documentation, please refer to the official PostgreSQL documentation:

PostgreSQL Official Documentation