PostgreSQL Visual Query Builder¶
Builds and runs PostgreSQL SELECT queries from a visual-style configuration, including multi-table JOINs, filtering, grouping, ordering, pagination, and multiple export formats. It constructs SQL from inputs, executes it against a configured PostgreSQL data service, and returns text, JSON, and optional HTML/XLSX/PDF outputs.
Usage¶
Use this node when you want to query PostgreSQL data without writing SQL directly. Specify the main table and schema, the columns to select, optional JOINs in JSON, filters (WHERE), grouping (GROUP BY/HAVING), ordering, and pagination limits. Choose the desired output format for reporting or downstream processing. Commonly used in analytics flows, dashboard/report generation, and data exploration pipelines.
Inputs¶
| Field | Required | Type | Description | Example |
|---|---|---|---|---|
| credentials_path | True | STRING | Database connection URI used to authenticate and route requests to the PostgreSQL data service. | postgresql://username:password@db.host:5432/my_database?schema=public |
| timeout | True | INT | Maximum time (seconds) to wait for the service request before failing. | 60 |
| main_table | True | STRING | The primary table to query in the FROM clause. | users |
| main_table_schema | True | STRING | Schema of the primary table. | public |
| selected_columns | True | STRING | Comma-separated column list to select, or * for all. Supports qualified names and expressions. | users.id, users.name, orders.total_amount |
| join_config | True | STRING | JSON array describing JOINs. Each item must include type, table, on, and optional schema. Valid types: INNER, LEFT, RIGHT, FULL, CROSS. | [{"type":"INNER","table":"orders","schema":"public","on":"users.id = orders.user_id"}] |
| where_conditions | True | STRING | WHERE clause conditions without the WHERE keyword. | users.age > 25 AND users.city = 'New York' |
| group_by_columns | True | STRING | Comma-separated columns to group by. | users.city |
| having_conditions | True | STRING | HAVING clause to filter grouped results, without the HAVING keyword. | COUNT(orders.id) > 5 |
| order_by_columns | True | STRING | Comma-separated ORDER BY expressions, with optional ASC/DESC. | users.created_at DESC, users.id ASC |
| limit_count | True | INT | Maximum number of rows to return (1–10000). | 100 |
| offset_count | True | INT | Number of rows to skip from the start (0–10000). | 0 |
| output_format | True | One of: text \| html \| xlsx \| pdf \| all | Controls which outputs are populated. 'text' returns formatted text and JSON; 'html' returns an HTML table; 'xlsx' returns a base64 Excel file; 'pdf' returns a base64 PDF; 'all' returns all formats. | text |
Outputs¶
| Field | Type | Description | Example |
|---|---|---|---|
| result | STRING | Human-readable, formatted text of the query results or an error message. | Visual Query: public.users (100 rows): Row 1: id: 1 name: Alice total_amount: 45.00 ... |
| json_result | STRING | Raw JSON string of the service response, typically containing a 'data' array and metadata. | {"data":[{"id":1,"name":"Alice","total_amount":45.0}],"row_count":100} |
| html_table | STRING | HTML document containing a styled table of results. Populated for 'html' and 'all' formats. | ... |
| xlsx_data | STRING | Base64-encoded XLSX file containing the results. Populated for 'xlsx' and 'all' formats. | |
| pdf_data | STRING | Base64-encoded PDF document containing the results. Populated for 'pdf' and 'all' formats. |
Important Notes¶
- Join configuration: join_config must be valid JSON representing a list of joins. Each join requires 'type', 'table', and 'on'. 'schema' defaults to 'public' if omitted.
- Valid JOIN types: Only INNER, LEFT, RIGHT, FULL, and CROSS are allowed; any other type will cause an error.
- Credentials: credentials_path must be a valid database URI (e.g., postgresql://user:pass@host:port/db). The PostgreSQL data connector endpoint must be configured.
- Output behavior: Output fields are populated based on output_format. For 'text', only result and json_result are returned; other fields are empty strings.
- Empty datasets: HTML/XLSX/PDF exports may be empty if no data rows are returned.
- Limits: limit_count > 0 will apply LIMIT; offset_count > 0 applies OFFSET. Values are constrained to 1–10000 and 0–10000 respectively.
- Security: where_conditions, having_conditions, and order_by_columns are embedded directly into SQL; ensure they are trusted to avoid SQL injection.
- Error handling: On failure, result contains a descriptive message and json_result contains an error object; other outputs are empty.
Troubleshooting¶
- Invalid credentials URI: If you see 'Invalid credentials URI' or 'Service URL not configured', provide a proper postgresql:// URI and ensure the PostgreSQL data connector endpoint is set.
- Invalid join_config JSON: A 'Invalid JSON in join configuration' error means the join_config is not valid JSON or not an array. Provide a JSON array string.
- Missing join fields: Errors like 'Join X missing required field' mean each join object must include 'type', 'table', and 'on'.
- Unsupported JOIN type: If you get 'Invalid join type', change 'type' to one of INNER, LEFT, RIGHT, FULL, or CROSS.
- Empty results: If result is empty, verify selected_columns, filters, and joins. Test a simpler query first (e.g., selected_columns='*', no joins) to confirm connectivity.
- Timeouts: Increase timeout if queries are long-running, or optimize filters/indexes.
- Export files empty: HTML/XLSX/PDF outputs are empty strings when no data rows are returned; confirm your query returns rows.
- Syntax errors from inputs: Ensure where_conditions, group_by_columns, having_conditions, and order_by_columns are valid SQL fragments without leading keywords.