Product Documentation

Appearance

sys.column ​

This view returns information about all of the columns in the user tables in the current database.

Column NameData TypeDescription
table_idbigintThe unique ID for the table. Tables in different schemas within the same database may have the same name, so you can use this ID to uniquely identify a table.
column_idintegerThe ID for the column in the table: a positional number starting with 1 for the first named column.
namenameName of the column.
typetextData type of the column.
nullablebooleanA value of t means the column accepts null values. A value of f means that the column was defined with a NOT NULL constraint.
distribution_keyinteger1 if the column is the distribution key for the table; otherwise null.
sort_keyinteger1 if the column is the sort key for the table; otherwise null.
partition_keyinteger1 or greater if the column is a partitioning key for the table; otherwise null.
cluster_keyinteger1-4 if a column is a cluster key for the table; otherwise null.
encryptedbooleanA value of t means the column is encrypted; f means it is not encrypted.

Examples ​

The following query returns an ordered list of the columns in six user tables in the current database:

premdb=# select name, table_id from sys.column order by 2,1;
       name        | table_id 
--------------------+----------
 numteams           |    46958
 season_name        |    46958
 seasonid           |    46958
 winners            |    46958
 atid               |    46962
 avg_att            |    46962
 capacity           |    46962
 city               |    46962
 htid               |    46962
 name               |    46962
 nickname           |    46962
 stadium            |    46962
 teamid             |    46962
 htid               |    46966
 name               |    46966
 atid               |    46970
 name               |    46970
 atid               |    46974
 ftscore            |    46974
 htid               |    46974
 htscore            |    46974
 matchday           |    46974
 seasonid           |    46974
 avg_mins_per_match |    47071
 cob                |    47071
 dob                |    47071
 firstname          |    47071
 lastname           |    47071
 matches_played     |    47071
 playerid           |    47071
 position           |    47071
 seasonid           |    47071
 teamid             |    47071
 weekly_wages       |    47071
(34 rows)

premdb=# \d
           List of relations
 Schema |   Name   | Type  |    Owner    
--------+----------+-------+-------------
 public | awayteam | table | yellowbrick
 public | hometeam | table | yellowbrick
 public | match    | table | yellowbrick
 public | player   | table | yellowbrick
 public | season   | table | yellowbrick
 public | team     | table | yellowbrick
(6 rows)

The following example joins sys.column to sys.database, sys.schema, and sys.table to get the database name, schema name, and table name (and to make sure that the results are for a single table, schema, and database combination):

premdb=# select d.database_id, d.name database_name, d.owner_id, 
s.name schema_name, t.name table_name, t.table_id,
c.name column_name, c.type, c.distribution_key
from sys.database d 
inner join
sys.table t on (d.database_id = t.database_id)
inner join
sys.column c on (t.table_id = c.table_id)
inner join
sys.schema s on (s.schema_id = t.schema_id) and (s.database_id = d.database_id)
where t.name = 'match' and s.name = 'public'
order by c.column_id;
 database_id | database_name | owner_id | schema_name | table_name | table_id | column_name |            type             | distribution_key 
-------------+---------------+----------+-------------+------------+----------+-------------+-----------------------------+------------------
      16459 | premdb        |    16007 | public      | match      |    16512 | seasonid    | smallint                    |                1
      16459 | premdb        |    16007 | public      | match      |    16512 | matchday    | timestamp without time zone |           [NULL]
      16459 | premdb        |    16007 | public      | match      |    16512 | htid        | smallint                    |           [NULL]
      16459 | premdb        |    16007 | public      | match      |    16512 | atid        | smallint                    |           [NULL]
      16459 | premdb        |    16007 | public      | match      |    16512 | ftscore     | character(3)                |           [NULL]
      16459 | premdb        |    16007 | public      | match      |    16512 | htscore     | character(3)                |           [NULL]
(6 rows)

Parent topic:System Views