PostgreSQL

PostgreSQL

This skill covers PostgreSQL database operations using the PgQuery command-line tool for executing DDL scripts, managing schemas, and running queries against PostgreSQL databases.

Instructions

When helping users with PostgreSQL operations, follow these guidelines:

1. Always Use PgQuery Tool: Use Y:/CSharpDLLs/PgQuery/PgQuery.exe for all PostgreSQL operations. Never use psql or other PostgreSQL clients directly.

2. Configuration Files: PostgreSQL connection details are stored in JSON configuration files (typically in R:/JsonParams/). Common configs:

   • x3rocs_db.json - x3rocs database connection
   • dw_db.json - Data warehouse connection

3. Command Format: Use --config for database connection and either --file for SQL files or --sql for inline SQL:

   Y:/CSharpDLLs/PgQuery/PgQuery.exe --config "<config-path>" --file "<sql-file-path>"
   

4. DDL Execution Order: When executing multiple DDL scripts that have dependencies:

   • Create new tables before tables that reference them (foreign keys)
   • Drop tables in reverse order (dependent tables first)
   • Handle permission errors by checking if shared functions already exist

5. Error Handling: Common PostgreSQL errors:

   • 42501: must be owner of function - Shared function already exists, skip recreation
   • 42P01: relation does not exist - Table/view doesn't exist, check dependencies
   • 23503: foreign key violation - Referenced table missing or data constraint violated

6. Schema Organization: Follow the project's schema organization:

   • x3 schema - X3 integration data (customers, contacts, etc.)
   • rocs schema - ROCS application data (price discounts, etc.)
   • public schema - Shared utilities and functions

Examples

Example 1: Execute a DDL Script

User: Run the contact.sql DDL to create the new contact table

Claude: I'll execute the contact.sql DDL using PgQuery:
- Use the x3rocs database connection
- Execute the file from the Postgresql directory
- Handle any permission errors for shared functions

[Runs: Y:/CSharpDLLs/PgQuery/PgQuery.exe --config "R:/JsonParams/x3rocs_db.json" --file "c:/Users/matthew.heath/Git/RocsMiddleware/Postgresql/contact.sql"]

Example 2: Execute Multiple Dependent DDL Files

User: We need to update the customer and contact tables - contact has a foreign key to customer

Claude: I'll execute these in the correct order:
1. First run customer.sql (parent table)
2. Then run contact.sql (child table with foreign key)
3. Report any errors

[Executes customer.sql first, then contact.sql]

Example 3: Run Ad-Hoc Query

User: Check if the x3.contact table exists

Claude: I'll query the PostgreSQL information schema:

[Runs: Y:/CSharpDLLs/PgQuery/PgQuery.exe --config "R:/JsonParams/x3rocs_db.json" --sql "SELECT table_name FROM information_schema.tables WHERE table_schema = 'x3' AND table_name = 'contact'"]

Example 4: Export Query Results to Parquet

User: Export the customer table to a parquet file

Claude: I'll export using the --parquet flag:

[Runs: Y:/CSharpDLLs/PgQuery/PgQuery.exe --config "R:/JsonParams/x3rocs_db.json" --sql "SELECT * FROM x3.customer" --parquet "C:/tmp/customers.parquet"]

Reference Implementation Details

PgQuery Tool

Location: Y:/CSharpDLLs/PgQuery/PgQuery.exe or C:/Users/matthew.heath/Git/PgQuery Purpose: Command-line tool for executing PostgreSQL queries and DDL scripts with JSON configuration support

Command Syntax

# Execute SQL file
Y:/CSharpDLLs/PgQuery/PgQuery.exe --config "<config-file>" --file "<sql-file>"

# Execute inline SQL
Y:/CSharpDLLs/PgQuery/PgQuery.exe --config "<config-file>" --sql "<sql-statement>"

Parameters:

• --config / -c (required): Path to PostgreSQL connection config JSON file
• --file / -f: Path to SQL file to execute
• --sql / -s: Inline SQL statement to execute
• --output / -o: Write text output to a file
• --parquet / -p: Write query results to a Parquet file (SELECT queries only)
• Must specify either --file or --sql, not both

Configuration File Format

Location: R:/JsonParams/*.json

{
  "host": "rivsprod01",
  "port": "5432",
  "database": "x3rocs",
  "username": "<from vault: Postgres/x3rocs>",
  "password": "your-password"
}

Common DDL Patterns

Creating Tables with Foreign Keys

Pattern: Always create parent tables before child tables

-- Parent table (customer.sql)
DROP TABLE IF EXISTS x3.customer CASCADE;
CREATE TABLE x3.customer (
    customer_code VARCHAR(30) PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL,
    -- ... other fields
);

-- Child table (contact.sql) - references parent
DROP TABLE IF EXISTS x3.contact CASCADE;
CREATE TABLE x3.contact (
    customer_code VARCHAR(30) NOT NULL,
    contact_code VARCHAR(15) NOT NULL,
    -- ... other fields
    PRIMARY KEY (customer_code, contact_code),
    FOREIGN KEY (customer_code) REFERENCES x3.customer(customer_code) ON DELETE CASCADE
);

Execution Order:

1. Execute customer.sql first
2. Execute contact.sql second

Hash-Based Change Detection

Pattern: Use MD5 hash triggers for detecting data changes

-- Hash column in table
CREATE TABLE x3.customer (
    customer_code VARCHAR(30) PRIMARY KEY,
    -- ... data fields
    x3_hash VARCHAR(32),  -- MD5 hash for change detection
    updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- Hash calculation function
CREATE OR REPLACE FUNCTION x3.update_customer_hash()
RETURNS TRIGGER AS $$
BEGIN
    NEW.x3_hash := md5(
        COALESCE(NEW.customer_code, '') || '|' ||
        COALESCE(NEW.customer_name, '') || '|' ||
        -- ... concatenate all fields for hashing
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to auto-calculate hash
CREATE TRIGGER trg_update_customer_x3hash
    BEFORE INSERT OR UPDATE ON x3.customer
    FOR EACH ROW
    EXECUTE FUNCTION x3.update_customer_hash();

Key Points:

• MD5 is sufficient for change detection (not security)
• 32 characters (VARCHAR(32)) for MD5 hex output
• Exclude timestamp and hash fields from hash calculation
• Use || for string concatenation with pipe delimiter

Upsert Functions

Pattern: Stored procedures for INSERT ... ON CONFLICT DO UPDATE

CREATE OR REPLACE FUNCTION x3.upsert_contact(
    p_customer_code VARCHAR(30),
    p_contact_code VARCHAR(15),
    p_title VARCHAR(20),
    -- ... other parameters
)
RETURNS VOID AS $$
BEGIN
    INSERT INTO x3.contact (
        customer_code, contact_code, title, -- ...
    ) VALUES (
        p_customer_code, p_contact_code, p_title, -- ...
    )
    ON CONFLICT (customer_code, contact_code)
    DO UPDATE SET
        title = EXCLUDED.title,
        -- ... update all fields
END;
$$ LANGUAGE plpgsql;

Troubleshooting

Permission Error: "must be owner of function"

Cause: Shared function already exists and is owned by another user

Solution: Skip recreating the shared function or comment it out in the DDL script

-- Comment out if function already exists
-- CREATE OR REPLACE FUNCTION x3.update_updated_column()
-- RETURNS TRIGGER AS $$
-- BEGIN
--     NEW.updated = CURRENT_TIMESTAMP;
--     RETURN NEW;
-- END;
-- $$ LANGUAGE 'plpgsql';

Foreign Key Violation on Table Creation

Cause: Referenced table doesn't exist yet

Solution: Execute DDL files in dependency order (parent tables first)

Cascade Drop Warning

Cause: DROP TABLE ... CASCADE will drop dependent objects

Solution: This is expected behavior. The CASCADE keyword is intentional for clean rebuilds.

Best Practices

1. Always Use Absolute Paths: PgQuery requires absolute paths for --file parameter
2. Test on Non-Production First: Execute DDL on test databases before production
3. Use CASCADE on DROP: DROP TABLE IF EXISTS x3.customer CASCADE ensures clean drops
4. Hash for Change Detection: Use MD5 hashing to track when data changes between systems
5. Composite Primary Keys: Use format (customer_code, contact_code) for multi-column keys
6. Foreign Key Cascades: Use ON DELETE CASCADE for parent-child relationships
7. Schema Namespacing: Keep tables organized in schemas (x3., rocs., etc.)
8. Timestamp Triggers: Auto-update updated column with BEFORE UPDATE triggers