PostgresSQL

PostgresSQL connects your flow to a PostgreSQL database to save into a table the records that come from other components and to run SQL statements. It can also hand the connection details to an agent that queries the database by itself.

More complete version

There is another Postgres SQL component that also reads, updates and deletes rows without writing SQL. For new flows, use it instead of this one.

When to use it

• Saving into a table the requests or contacts the flow has collected.
• Running an SQL query to prepare a report.
• Giving a SQL Agent access to your database so it answers questions about it.

Configuration

You need a Postgres SQL credential with your server’s connection address. See Credentials.

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

• Add (default): saves into a table the records you connect to Ingest Data. If the table does not exist, it creates it with a text column for each field of the records.
• SQL: runs the statement you write in Search Query and returns the rows obtained. Any statement that changes data or tables is saved to the database.
• DatabaseInfo: prepares the connection details and the table to hand them to another component.
• Retriever: offers the table as a search engine by meaning, for components that ask for a retriever. The table must have been filled with Add and an Embedding connected, and the same embeddings model must be connected here. For a full vector store, use PGVector.

Field	What it does
Credential (required)	The Postgres SQL credential with the connection details.
Operation	What the component does. Default: Add.
Table Name (required)	With Add, Retriever and DatabaseInfo. The table where the data is saved, the one searched or the one handed to another component.
Ingest Data	With Add. The records to save, one per row. Connect the data output of another component here.
Search Query	With SQL. The statement to run, for example SELECT name, email FROM customers WHERE city = 'Valencia'.

Connection	What to connect
Embedding	Optional with Add, required with Retriever. An embeddings model, for example OpenAI Embeddings. With Add, if the records have a text field, a version of that text that lets you search it by meaning later is saved alongside each row (the column takes the size of the model’s vectors). With Retriever, it converts the question to search the table.

Advanced options

Field	What it does
Drop Table	With Add. Deletes the table, with all its content, and creates it again before saving the new records. Default: off.
Number of Results	The maximum number of results of the Retriever search and of the search handed over in Database Info. Default: 10.
Search Score Threshold	With Retriever. If not 0, drops results less similar than this value (0 to 1). Default: 0.

Output

• Data: with Add, the records that were saved; with SQL, the rows the statement returns, each with its columns.
• Retriever: with Retriever, the search engine over the table, for components that ask for one.
• Database Info: with DatabaseInfo, the reference to the database and the table. Connect it, for example, to a SQL Agent.

Example

Asesoría Prado saves in PostgreSQL the enquiries that come through its contact form:

1. Add PostgresSQL with the firm’s Postgres SQL credential and leave Operation set to Add.
2. Type enquiries in Table Name.
3. Connect the enquiry data (name, email and message) to Ingest Data, for example from JSON to Data.

Each run adds a row to the enquiries table. The first time, if the table does not exist, it is created with the columns name, email and message.

Good practices

• Turn on Drop Table only if you want to replace the whole content of the table: it deletes the previous data without asking for confirmation.
• With SQL the component can change or delete data and tables. If you will only look things up, use a credential with a read-only user; if it will save data, give it permissions only on the tables it needs.
• Try statements that change data on a copy of the database before running them on the real one.

Related components

• Postgres SQL: the more complete version of this component.
• PGVector: searches by meaning in PostgreSQL.
• SQL Agent: answers questions about your database.
• MySQL: the same for MySQL.