Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

sys.column ​

This view returns information about all of the columns in all of the tables in the 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 ​

premdb=# select sct.name, scc.* from sys.column scc, sys.table sct 
where scc.table_id=sct.table_id and sct.name='match' 
order by column_id;
 name  | table_id | column_id |   name   |            type             | nullable | distribution_key | sort_key | partition_key | cluster_key | encrypted 
-------+----------+-----------+----------+-----------------------------+----------+------------------+----------+---------------+-------------+-----------
 match |    16414 |         1 | seasonid | smallint                    | t        |                1 |   [NULL] |        [NULL] |      [NULL] | f
 match |    16414 |         2 | matchday | timestamp without time zone | t        |           [NULL] |   [NULL] |        [NULL] |      [NULL] | f
 match |    16414 |         3 | htid     | smallint                    | t        |           [NULL] |   [NULL] |        [NULL] |      [NULL] | f
 match |    16414 |         4 | atid     | smallint                    | t        |           [NULL] |   [NULL] |        [NULL] |      [NULL] | f
 match |    16414 |         5 | ftscore  | character(3)                | t        |           [NULL] |   [NULL] |        [NULL] |      [NULL] | f
 match |    16414 |         6 | htscore  | character(3)                | t        |           [NULL] |   [NULL] |        [NULL] |      [NULL] | f
(6 rows)

Parent topic:System Views