Postgres SQL

Postgres SQL works with the tables of your PostgreSQL database from a flow: it reads rows, adds new ones, changes or deletes existing ones and runs SQL queries.

When to use it

• Recording in your database the requests that arrive by email or form.
• Giving an agent access to look up customers or invoices.
• Marking as processed the records a flow has already handled.
• Pulling data for a report with your own SQL query.

Configuration

The Operation field decides what the component does and which fields appear:

• Insert (default): adds rows to a table.
• Get: reads rows from a table, with or without conditions.
• Update: changes values in the rows that meet a condition.
• Delete: deletes the rows that meet a condition.
• Custom Query: runs the SQL query you write.
• DatabaseInfo: describes the database’s tables and columns, useful so an agent knows what is there.

Once you pick the credential, Table Name fills with your database’s tables.

You need a Postgres SQL credential with the server, port, user, password and database. See Credentials.

Field	What it does
Credential (required)	The Postgres SQL credential with the connection details.
Operation (required)	What the component does. Default: Insert.
Schema (required)	The database schema that holds the table.
Table Name (required)	The table to work on. With Insert in Custom mode you can type a new name and the table is created if it does not exist.
Mode	With Insert. Dynamic (default): one field per table column appears so you fill its values. Custom: Ingest Data appears to insert the data coming from another component.
Select Filter Type	With Get, Update and Delete, how you write the conditions. String (default): in Filter String, such as age > 25 AND city = 'Madrid'. Build Manually: you say how many conditions you want and a field appears for each one.
Filter String	The conditions rows must meet. Texts go in single quotes and numbers without quotes.
Field To Update	With Update. How many columns you want to change; a field appears for each one’s new value.
Custom Query (required)	With Custom Query. The SQL query to run, for example SELECT * FROM customers WHERE city = 'Seville'.
Limit	With Get, the maximum number of rows returned. Default: 10.

Advanced options

Field	What it does
Return All	With Get, returns every row regardless of Limit. Off by default.

Output

The Data output holds the result: the rows read with Get or Custom Query, or confirmation of what was inserted, changed or deleted. The Database Info output holds the description of the tables with DatabaseInfo.

In tool mode, an agent can use the component to look up or change data by itself. See Tool Mode.

Example

An advisory firm records every enquiry that comes in through its website.

1. Add Postgres SQL with the Postgres SQL credential.
2. Choose Insert, the public schema and the enquiries table.
3. With Mode set to Dynamic, connect the form’s name, email and message to each column’s field.

Good practices

• Use a read-only credential if the component will only look things up.
• Before letting an agent use Update or Delete, test the conditions with Get to see which rows would be affected.

Related components

• MySQL and SQL Server: the same for other databases.
• PGVector: search by meaning in PostgreSQL.