Where are default values for a particular row stored in amazon redshift?

Question

I want to convert Redshift table to a JSON so that sql queries can be automatically generated from this JSON. For this I need datatypes, column names distkey and sortkey which are available in some or other format in system tables. One thing I couldn't find out is how to extract default values of a redshift column.

Can anybody help me with this?

score 1 · Accepted Answer · edited May 23 '17 at 11:59

This should answer your question: Get the default values of table columns in Postgres?

Summary:

INFORMATION_SCHEMA.COLUMNS works well if you only have one schema. It gets trickier if you have several tables with the same name across schemas.

This works in all cases:

SELECT d.adsrc AS default_value
FROM   pg_catalog.pg_attribute a
LEFT   JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum)
                                     = (d.adrelid,  d.adnum)
WHERE  NOT a.attisdropped   -- no dropped (dead) columns
AND    a.attnum > 0         -- no system columns
AND    a.attrelid = 'myschema.mytable'::regclass
AND    a.attname = 'mycolumn';

I'm using a slightly different version in production to also get the type (and length when appropriate of each column):

SELECT
            pg_namespace.nspname AS schema_name,
            pg_class.relname AS table_name,
            pg_attribute.attname AS column_name,
            pg_type.typname AS type,
            pg_attribute.atttypmod AS column_len,
            pg_attrdef.adsrc AS column_default
FROM
            pg_class
INNER JOIN
            pg_namespace
            ON pg_class.relnamespace = pg_namespace.oid
INNER JOIN
            pg_attribute
            ON pg_class.oid = pg_attribute.attrelid
INNER JOIN
            pg_type
            ON pg_attribute.atttypid = pg_type.oid
LEFT JOIN 
            pg_attrdef  
            ON pg_attribute.attrelid = pg_attrdef.adrelid
            AND pg_attribute.attnum = pg_attrdef.adnum
WHERE
            pg_class.relname = 'table_name'
            AND pg_namespace.nspname = 'schema_name'
            AND pg_type.typname NOT IN ('oid','xid','tid','cid')
            AND pg_attribute.attnum >= 0
ORDER BY
            pg_attribute.attnum ASC
;

score 0 · Answer 2 · answered Dec 08 '15 at 21:25

Redshift is almost same as postgresql. Using it, you may get the column's default from table INFORMATION_SCHEMA.COLUMNS.

I have checked it in postgre:

create table test_tbl (n int DEFAULT 100500);

select table_name, column_name, column_default from INFORMATION_SCHEMA.COLUMNS where table_name = 'test_tbl';

 table_name | column_name | column_default 
------------+-------------+----------------
 test_tbl   | n           | 100500

Where are default values for a particular row stored in amazon redshift?

2 Answers2