Redshift column names

Question

I'm new to redshift. I have some tables in 'abc' schema whose column names and primary key information needs to be extracted. Can someone guide. Assume schema name is 'abc' and table name is 'xyz' whose columns are required to be listed in a single row.

Possible duplicate of [Show tables, describe tables equivalent in redshift](https://stackoverflow.com/questions/18733385/show-tables-describe-tables-equivalent-in-redshift) — Shailesh, Aug 13 '18 at 08:07
in Redshift - you know primary key is not enforced? and you also have distribution and sortkey which may be of interest. — Jon Scott, Aug 13 '18 at 16:16
correct answers are not marked in the link @Yankee. I had visited that link but it didnt answer my question. When i run the query id doesnt return my columns even after entering correct table name and schema name — Kapil Lehar, Aug 13 '18 at 17:43

score 0 · Answer 1 · answered Aug 13 '18 at 22:13

0

Use v_generate_tbl_ddl.sql provided by AWS Labs, you can use table level or schema level filters.

answered Aug 13 '18 at 22:13

demircioglu

3,069
1
15
22

score 0 · Answer 2 · answered Aug 14 '18 at 07:02

0

You can query the SVV_COLUMNS table. It includes:

Scheme Name
Table Name
Column Name

answered Aug 14 '18 at 07:02

John Rotenstein

241,921
22
380
470

score 0 · Answer 3 · answered Aug 19 '21 at 19:19

I used this query to get a list of primary keys. A lot of additional info can be added. Postres official documentation to pg_ tables helps a lot.

SELECT
    f.attname AS column_name
FROM
    pg_catalog.pg_namespace n
JOIN pg_catalog.pg_class c ON
    n.oid = c.relnamespace
JOIN pg_catalog.pg_attribute f ON
    c.oid = f.attrelid
JOIN pg_catalog.pg_constraint p ON
    p.conrelid = c.oid
    AND f.attnum = ANY (p.conkey)
WHERE
    n.nspname = 'schema_name'
    AND c.relkind = 'r'
    AND c.relname = 'table_name'
    AND p.contype = 'p'
    AND f.attnum > 0
ORDER BY
    f.attnum;

Redshift column names

3 Answers3