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)