PostgreSQL Integration Block

The PostgreSQL block provides seamless integration with PostgreSQL databases, enabling robust CRUD operations and custom SQL queries for advanced data management within your workflows.

Note: Prepare your PostgreSQL connection string and credentials in advance to ensure secure and efficient setup.

Features

CRUD Operations: Execute SELECT, INSERT, UPDATE, and DELETE operations through an intuitive visual interface.
Custom SQL Queries: Run any valid PostgreSQL statement for tailored data interactions.
Response Mapping: Map query results to workflow variables for dynamic use.
Secure Credentials: Utilize encrypted storage for sensitive connection details.
Transaction Support: Ensure data consistency with ACID-compliant transactions.
Error Handling: Benefit from comprehensive error detection and resolution.

Configuration

Database Connection

Credentials Setup:
- Specify the host and port (default: 5432).
- Provide the database name.
- Enter username and password.
- Optionally configure SSL for secure connections.

Connection String Format:

postgresql://username:password@host:port/database

Note: For production environments, include SSL parameters (e.g., ?sslmode=require) in the connection string to encrypt data transmission.

Flow Editor Setup

Drag the PostgreSQL block from the Integrations category into your flow.
Select an operation (e.g., SELECT, INSERT, UPDATE, DELETE, Custom Query).
Input the connection string, database name, table, and operation-specific parameters.
Test in the Indite editor’s preview mode to verify connectivity and query execution.

Warning: Validate the connection string and query syntax in preview mode to catch errors before deployment.

CRUD Operations

SELECT Operation

Query PostgreSQL tables or views with flexible filtering, grouping, and sorting capabilities.

Configuration Options:

Table/View: Select the target table or view.
Columns: Choose specific columns or use SELECT *.
WHERE Conditions: Apply filters to refine results.
GROUP BY: Aggregate data by columns.
HAVING: Filter grouped results.
ORDER BY: Sort results by columns.
LIMIT/OFFSET: Enable pagination for large datasets.

Example:

-- Retrieve active users with recent activity
SELECT id, name, email, created_at 
FROM users 
WHERE status = 'active' 
AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC 
LIMIT 10;

INSERT Operation

Add records to PostgreSQL tables with support for bulk inserts and conflict handling.

Configuration Options:

Table: Specify the target table.
Column Mapping: Map bot variables to table columns.
Bulk Insert: Insert multiple rows in one query.
Duplicate Handling: Use ON CONFLICT for key violations.
Auto-increment: Handle SERIAL or IDENTITY columns automatically.

Example:

-- Insert a single order with customer details
INSERT INTO orders (customer_id, product_id, quantity, order_date, status) 
VALUES (123, 456, 2, NOW(), 'pending') 
RETURNING id, customer_id, status;

UPDATE Operation

Modify records with conditional updates and multi-table joins.

Configuration Options:

Table: Target table for updates.
SET Clauses: Define new column values.
WHERE Conditions: Specify rows to update.
JOIN Operations: Update with data from related tables.
LIMIT: Restrict the number of updated rows.

Example:

-- Deactivate users with no recent login
UPDATE users 
SET status = 'inactive', updated_at = NOW() 
WHERE last_login < NOW() - INTERVAL '90 days' 
RETURNING id, name, status;

DELETE Operation

Remove records with precise conditions and referential integrity.

Configuration Options:

Table: Target table for deletion.
WHERE Conditions: Define rows to delete.
JOIN Deletes: Delete from multiple tables.
LIMIT: Restrict deletion count.
Foreign Key Handling: Manage cascading deletes.

Example:

-- Delete expired user sessions
DELETE FROM user_sessions 
WHERE expires_at < NOW() 
RETURNING session_id, user_id;

Custom SQL Queries

Execute advanced PostgreSQL statements for specialized operations.

Supported Query Types

Data Definition Language (DDL): CREATE, ALTER, DROP statements for schema management.
Data Manipulation Language (DML): Complex SELECT, INSERT, UPDATE, DELETE queries.
Data Control Language (DCL): GRANT and REVOKE for permissions.
Stored Procedures: CALL statements for procedures.
Functions: Query custom functions for computed results.
Common Table Expressions (CTEs): Use WITH clauses for modular queries.
Window Functions: Perform advanced analytics with OVER clauses.

Custom Query Examples

Complex Analytics Query

-- Analyze monthly sales with growth metrics
WITH monthly_sales AS (
  SELECT 
    DATE_TRUNC('month', order_date) as month,
    SUM(total_amount) as revenue,
    COUNT(*) as order_count
  FROM orders 
  WHERE order_date >= NOW() - INTERVAL '12 months'
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT 
  month,
  revenue,
  order_count,
  LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
  ROUND(((revenue - LAG(revenue) OVER (ORDER BY month)) / 
         LAG(revenue) OVER (ORDER BY month) * 100), 2) as growth_percentage
FROM monthly_sales
ORDER BY month;

Data Migration Query

-- Migrate user data to a JSONB profile
INSERT INTO user_profiles (user_id, profile_data, migrated_at)
SELECT 
  u.id,
  jsonb_build_object(
    'preferences', u.preferences,
    'settings', u.settings,
    'metadata', u.metadata
  ),
  NOW()
FROM users u
LEFT JOIN user_profiles up ON u.id = up.user_id
WHERE up.user_id IS NULL;

Maintenance Query

-- Clean up expired tokens and update statistics
WITH deleted_tokens AS (
  DELETE FROM auth_tokens 
  WHERE expires_at < NOW() 
  RETURNING user_id
)
UPDATE user_stats 
SET last_cleanup = NOW(), 
    expired_tokens_removed = (SELECT COUNT(*) FROM deleted_tokens)
WHERE user_id IN (SELECT user_id FROM deleted_tokens);

Advanced Features

Parameter Binding

Safely incorporate workflow variables to prevent SQL injection.

-- Query orders with parameterized inputs
SELECT * FROM orders 
WHERE user_id = $1 
AND order_date >= $2 
AND status = $3;

Parameters:

$1: User ID from workflow variable
$2: Start date from workflow variable
$3: Status filter from workflow variable

Transaction Management

Group multiple queries into a single transaction for atomicity.

-- Process an order with stats and audit logging
BEGIN;
  INSERT INTO orders (user_id, total_amount) VALUES ($1, $2);
  UPDATE user_stats SET total_orders = total_orders + 1 WHERE user_id = $1;
  INSERT INTO audit_log (action, user_id, timestamp) VALUES ('order_created', $1, NOW());
COMMIT;

JSON Operations

Leverage PostgreSQL’s robust JSON support for flexible data handling.

-- Extract user details from JSONB data
SELECT 
  user_id,
  profile_data->>'name' as name,
  profile_data->'preferences'->>'theme' as theme
FROM user_profiles 
WHERE profile_data ? 'active' 
AND profile_data->>'active' = 'true';
 
-- Update JSONB fields for user activity
UPDATE user_profiles 
SET profile_data = profile_data || '{"last_seen": "2025-10-09"}'::jsonb
WHERE user_id = $1;

Troubleshooting

Common Issues

Connection Errors: Verify host, port, credentials, and network access. Ensure the PostgreSQL server is running and reachable.
Syntax Errors: Check SQL syntax and parameter bindings for accuracy.
Constraint Violations: Address unique key, foreign key, or check constraint failures.
Permission Errors: Confirm the database user has sufficient privileges.
Timeout Errors: Optimize long-running queries or adjust timeout settings.
WhatsApp Compatibility: Use text or .mp4 formats for WhatsApp integration—see WhatsApp Guide.

Warning: Test all queries in the Indite editor’s preview mode to identify and resolve issues before deployment.

Response Mapping

Transform query results into workflow variables:

Single Row: Extract specific column values.
Multiple Rows: Process results as arrays or iterate over them.
JSON Results: Parse JSONB fields into structured data.
Aggregated Data: Utilize computed values in workflow logic.

Error Handling

The PostgreSQL block manages various error scenarios:

Connection Errors: Handles unreachable servers or invalid credentials.
Syntax Errors: Detects malformed SQL or logic errors.
Constraint Violations: Addresses unique, foreign key, or check constraint issues.
Permission Errors: Flags insufficient database privileges.
Timeout Errors: Manages queries exceeding execution limits.

Best Practices

Performance

Index Strategy: Create indexes for frequent query patterns.
Limit Results: Use LIMIT to control data volume.
Optimize JOINs: Structure joins for efficiency.
Query Analysis: Use EXPLAIN or EXPLAIN ANALYZE to optimize performance.

Security

Parameter Binding: Always use parameterized queries for user inputs.
Least Privilege: Assign minimal permissions to database users.
SSL Connections: Enforce SSL for production databases.
Audit Logging: Track database access and modifications.

Data Integrity

Transactions: Wrap multi-step operations in transactions.
Validation: Validate data before database operations.
Backup Strategy: Implement regular, automated backups.
Error Recovery: Design rollback mechanisms for failed operations.

Node Display

CRUD Operations: Displays operation type (SELECT/INSERT/UPDATE/DELETE) and target table.
Custom Queries: Shows "Custom SQL" with a query preview.
Results: Indicates row count and execution status.
Errors: Provides actionable error messages and troubleshooting tips.

Google Sheets MongoDB