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_SCHEMA |
VARCHAR | Name of the table's schema. |
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 |
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.