The PostgreSQL Query component allows you to execute query statements against PostgreSQL to retrieve data.
It supports PostgreSQL 11 and above.
Output payload
| Output Payload | Description |
|---|---|
cv.Payload |
Outputs a record in the output format specified by the property for each record that matches the condition. |
Example of output in array format
When table1 table has column1 (integer type) and column2 (string type), specifying `SELECT * FROM table1` in the SQL property and “array” in the output format property, the output payload is generated as follows.
Output Example
[1, "abc"]
[2, "def"]
[3, "hij"].
Example of output in object format
When table1 table has column1 (integer type) and column2 (string type), specifying `SELECT * FROM table1` in the SQL property and “object format” in the output format property, the output payload is generated as follows.
Output example
{"column1": 1, "column2": "abc"}
{"column1": 2, "column2": "def"}
{"column1": 3, "column2": "hij"}
Component properties
| Property Name | Description |
|---|---|
| SQL | Specify SQL statement to execute |
| Output Format | Select array or object format |
| Host | Specify PostgreSQL host. |
| Port | Specify the PostgreSQL port. |
| Username | Specify a PostgreSQL username. |
| Password | Specify the PostgreSQL password. |
| Database | Specifies a database. |
| Timezone | Specify the timezone for the database |
| SSLMode | The encryption of the communication can be disable (no SSL/TLS connection), allow (first try a connection without SSL/TLS connection and if the connection fails, then try a SSL/TLS connection), prefer (first try a connection with SSL/TLS connection and if the connection fails, then try a connection without SSL/TLS connection), or require (try a connection with SSL/TLS connection). connection), prefer (first try an SSL/TLS connection and if it fails to connect, then try a non-SSL/TLS connection), require (make an SSL/TLS connection. (makes an SSL/TLS connection and verifies the server certificate if specified, but not if not specified), verify-ca (makes an SSL/TLS connection and verifies the server certificate), verify-full (verifies the SSL/TLS connection and the server certificate, and also verifies the server name) |
| Root Certificate | Specify the root certificate to use; if you are using AWS Aurora or RDS, you must use a server certificate issued by AWS. This will be the root certificate for that server certificate. |
Placeholder
You can use a placeholder in the SQL statement to be executed to use the value of cv.Playload in the SQL statement.
SELECT * FROM table1 WHERE id=?
? The value of cv.Payload is used for the placeholder specified in cv. When cv.Payload contains 1, the record with id 1 can be retrieved.
More than one placeholder can be specified.
SELECT * FROM table1 WHERE name=? AND age=?
To specify values for more than one placeholder, specify the values in an array in cv.
["First Last", 20]
Text2SQL
Text2SQL is a function that allows you to generate SQL statements from the Japanese you enter in the prompt for the data you wish to retrieve using OpenAI’s ChatGPT functionality.
Please refer to here for how to use it.
Need more help with this?
Join our slack community for help