COLUMNS

Provides table column information.

Provides table column information. For columns of Iceberg external tables, see ICEBERG_COLUMNS.

Column Name Data Type Description
TABLE_ID INTEGER Catalog-assigned numeric value that uniquely identifies the table.
TABLE_NAMESPACE VARCHAR For Eon Mode databases, name of the namespace that contains the table.
TABLE_SCHEMA VARCHAR

Name of the table's schema.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

TABLE_NAME VARCHAR Name of the table containing the column.
IS_SYSTEM_TABLE BOOLEAN Whether the table is a system table.
COLUMN_ID VARCHAR Catalog-assigned VARCHAR value that uniquely identifies a table column.
COLUMN_NAME VARCHAR Name of the column.
DATA_TYPE VARCHAR

Column data type.

Arrays of primitive types show the name ARRAY[type]. Other complex types show the inline name of the type, which matches the type_name value in the COMPLEX_TYPES table. For example: _ct_45035996273833610.

DATA_TYPE_ID INTEGER Catalog-assigned unique numeric ID of the data type.
DATA_TYPE_LENGTH INTEGER Maximum allowable length of the data type.
CHARACTER_MAXIMUM_LENGTH VARCHAR Maximum allowable length of a VARCHAR column.
NUMERIC_PRECISION INTEGER Number of significant decimal digits for a NUMERIC column.
NUMERIC_SCALE INTEGER Number of fractional digits for a NUMERIC column.
DATETIME_PRECISION INTEGER Declared precision for a TIMESTAMP column, or NULL if no precision was declared.
INTERVAL_PRECISION INTEGER Number of fractional digits retained in the seconds field of an INTERVAL column.
ORDINAL_POSITION INTEGER Column position respective to other columns in the table.
IS_NULLABLE BOOLEAN Whether the column can contain NULL values.
COLUMN_DEFAULT VARCHAR Expression set on a column with the DEFAULT constraint.
COLUMN_SET_USING VARCHAR Expression set on a column with the SET USING constraint.
IS_IDENTITY BOOLEAN Whether the column is an IDENTITY column.

Examples

=> SELECT table_schema, table_name, column_name, data_type, is_nullable
   FROM columns WHERE table_schema = 'store'
   AND data_type = 'Date';
 table_schema |    table_name     |      column_name       | data_type | is_nullable
--------------+-------------------+------------------------+-----------+-------------
 store        | store_dimension   | first_open_date        | Date      | f
 store        | store_dimension   | last_remodel_date      | Date      | f
 store        | store_orders_fact | date_ordered           | Date      | f
 store        | store_orders_fact | date_shipped           | Date      | f
 store        | store_orders_fact | expected_delivery_date | Date      | f
 store        | store_orders_fact | date_delivered         | Date      | f
6 rows)

In the following query, datetime_precision is NULL because the table definition declares no precision:

=> CREATE TABLE c (c TIMESTAMP);
CREATE TABLE
=> SELECT table_name, column_name, datetime_precision FROM columns
   WHERE table_name = 'c';
 table_name | column_name | datetime_precision
------------+-------------+--------------------
 c          | c           |
(1 row)

In the following example, timestamp precision is set:

=> DROP TABLE c;
=> CREATE TABLE c (c TIMESTAMP(4));
CREATE TABLE
=> SELECT table_name, column_name, datetime_precision FROM columns
   WHERE table_name = 'c';
 table_name | column_name | datetime_precision
------------+-------------+--------------------
 c          | c           |                  4

An IDENTITY column sequence is defined in a table's DDL. Column values automatically increment as new rows are added. The following query returns identity columns:

=> CREATE TABLE employees (employeeID IDENTITY, fname varchar(36), lname varchar(36));
CREATE TABLE
=> SELECT table_name, column_name, is_identity FROM columns WHERE is_identity = 't';
 table_name | column_name | is_identity
------------+-------------+-------------
 employees  | employeeID  | t
(1 row)

You can query the SEQUENCES table to get detailed information about an IDENTITY column sequence:

=> SELECT sequence_schema, sequence_name, identity_table_name, sequence_id FROM sequences WHERE identity_table_name ='employees';
 sequence_schema |      sequence_name       | identity_table_name |    sequence_id
-----------------+--------------------------+---------------------+-------------------
 public          | employees_employeeID_seq | employees           | 45035996273848816
(1 row)

For details about sequences and IDENTITY columns, see Sequences.