PG_TABLE_DEF - Amazon Redshift

PG_TABLE_DEF

Stores information about table columns.

PG_TABLE_DEF only returns information about tables that are visible to the user. If PG_TABLE_DEF does not return the expected results, verify that the search_path parameter is set correctly to include the relevant schemas.

You can use SVV_TABLE_INFO to view more comprehensive information about a table, including data distribution skew, key distribution skew, table size, and statistics.

Table columns

Column name Data type Description
schemaname name Schema name.
tablename name Table name.
column name Column name.
type text Datatype of column.
encoding character(32) Encoding of column.
distkey boolean True if this column is the distribution key for the table.
sortkey integer Order of the column in the sort key. If the table uses a compound sort key, then all columns that are part of the sort key have a positive value that indicates the position of the column in the sort key. If the table uses an interleaved sort key, then each column that is part of the sort key has a value that is alternately positive or negative, where the absolute value indicates the position of the column in the sort key. If 0, the column is not part of a sort key.
notnull boolean True if the column has a NOT NULL constraint.

Example

The following example shows the compound sort key columns for the LINEORDER_COMPOUND table.

select "column", type, encoding, distkey, sortkey, "notnull" from pg_table_def where tablename = 'lineorder_compound' and sortkey <> 0; column | type | encoding | distkey | sortkey | notnull -------------+---------+----------+---------+---------+-------- lo_orderkey | integer | delta32k | false | 1 | true lo_custkey | integer | none | false | 2 | true lo_partkey | integer | none | true | 3 | true lo_suppkey | integer | delta32k | false | 4 | true lo_orderdate | integer | delta | false | 5 | true (5 rows)

The following example shows the interleaved sort key columns for the LINEORDER_INTERLEAVED table.

select "column", type, encoding, distkey, sortkey, "notnull" from pg_table_def where tablename = 'lineorder_interleaved' and sortkey <> 0; column | type | encoding | distkey | sortkey | notnull -------------+---------+----------+---------+---------+-------- lo_orderkey | integer | delta32k | false | -1 | true lo_custkey | integer | none | false | 2 | true lo_partkey | integer | none | true | -3 | true lo_suppkey | integer | delta32k | false | 4 | true lo_orderdate | integer | delta | false | -5 | true (5 rows)

PG_TABLE_DEF will only return information for tables in schemas that are included in the search path. For more information, see search_path.

For example, suppose you create a new schema and a new table, then query PG_TABLE_DEF.

create schema demo; create table demo.demotable (one int); select * from pg_table_def where tablename = 'demotable'; schemaname|tablename|column| type | encoding | distkey | sortkey | notnull ----------+---------+------+------+----------+---------+---------+--------

The query returns no rows for the new table. Examine the setting for search_path.

show search_path; search_path --------------- $user, public (1 row)

Add the demo schema to the search path and run the query again.

set search_path to '$user', 'public', 'demo'; select * from pg_table_def where tablename = 'demotable'; schemaname| tablename |column| type | encoding |distkey|sortkey| notnull ----------+-----------+------+---------+----------+-------+-------+-------- demo | demotable | one | integer | none | f | 0 | f (1 row)