Got it, let's fix that incomplete query for your Redshift venue_ident table. Here's the complete, working version to retrieve the column details you're targeting, plus breakdowns to help you understand how it fits together:

Complete Query Using pg_attribute + information_schema.columns #

SELECT DISTINCT 
    c.column_name,
    a.attencodingtype,
    a.attnotnull,
    a.atttypid::regtype AS data_type,
    a.attnum AS column_position
FROM 
    pg_attribute a
INNER JOIN 
    information_schema.columns c 
    ON c.ordinal_position = a.attnum 
    AND c.column_name = a.attname
    AND c.table_name = 'venue_ident'
    AND c.table_schema = 'public' -- Update this if your table lives in a different schema
INNER JOIN 
    pg_class t ON a.attrelid = t.oid
WHERE 
    a.attnum > 0 
    AND NOT a.attisdropped
    AND t.relname = 'venue_ident'
    AND t.relkind = 'r' -- Ensure we only target regular tables (not views/sequences)
ORDER BY 
    a.attnum;

Key Fixes & Explanations #

• Missing Join Condition: Added c.column_name = a.attname to link the column name from information_schema.columns to the internal column name stored in pg_attribute.attname—this was the critical gap in your original query.
• Table Specificity: Joined pg_class to explicitly target the venue_ident table (via t.relname) and filtered for regular tables (t.relkind = 'r') to avoid pulling data from unintended objects like views or sequences.
• Schema Filter: Included c.table_schema = 'public' to narrow results to your table's schema (adjust this if your table isn't in the default public schema).
• Ordered Results: Added ORDER BY a.attnum to return columns in their original table order, making the output easier to read.

Simplified Alternative Using Redshift's PG_TABLE_DEF #

Redshift's built-in PG_TABLE_DEF view already includes core column metadata, so you can shorten the query while still getting the extra details from pg_attribute:

SELECT 
    pd.column AS column_name,
    a.attencodingtype,
    a.attnotnull,
    pd.type AS data_type,
    a.attnum AS column_position
FROM 
    pg_table_def pd
INNER JOIN 
    pg_attribute a 
    ON pd.tablename = relname(a.attrelid)
    AND pd.column = a.attname
WHERE 
    pd.schemaname = 'public'
    AND pd.tablename = 'venue_ident'
    AND a.attnum > 0
    AND NOT a.attisdropped
ORDER BY 
    a.attnum;

Quick Notes on Fields #

• attencodingtype: Values map to Redshift compression types: 0 = uncompressed, 1 = zlib, 2 = lzo, 3 = zstd (for newer Redshift versions).
• atttypid::regtype: Converts the internal data type OID to a human-readable name (similar to information_schema.columns.data_type, but more precise for complex types like arrays).

内容的提问来源于stack exchange，提问作者Arora