COLUMNS

Provides table column information.

Column Name Data Type Description 
TABLE_ID
 INTEGER

Catalog-assigned numeric value that uniquely identifies the table.

 
TABLE_SCHEMA
 VARCHAR

Schema name for which information is listed in the database.

 
TABLE_NAME
 VARCHAR Table name for which information is listed in the database. 
IS_SYSTEM_TABLE
 BOOLEAN

Specifies whether the table is a system table.

 
COLUMN_ID
 VARCHAR

Catalog-assigned VARCHAR value that uniquely identifies a table column.

 
COLUMN_NAME
 VARCHAR

The column name for which information is listed in the database.

 
DATA_TYPE
 VARCHAR

Column's data type, for example VARCHAR(16), INTEGER, or FLOAT.

Arrays of primitive types show the name "Array[type]. Other complex types show the inline name of the type, such as _ct_45035996273833610. These names match the type_name column in the COMPLEX_TYPES table.

 
DATA_TYPE_ID
 INTEGER

Unique numeric ID assigned by the Vertica catalog, which identifies the data type.

 
DATA_TYPE_LENGTH
 INTEGER

Maximum allowable length of the data type.

 
CHARACTER_MAXIMUM_LENGTH
 VARCHAR

Maximum allowable length of the column.

 
NUMERIC_PRECISION
 INTEGER

Number of significant decimal digits.

 
NUMERIC_SCALE
 INTEGER

Number of fractional digits.

 
DATETIME_PRECISION
 INTEGER

For TIMESTAMP data type, returns the declared precision; returns NULL if no precision was declared.

 
INTERVAL_PRECISION
 INTEGER

Number of fractional digits retained in the seconds field.

 
ORDINAL_POSITION
 INTEGER

Column position respective to other columns in the table.

 
IS_NULLABLE
 BOOLEAN

Specifies whether the column can contain NULL values.

 
COLUMN_DEFAULT
 VARCHAR

Expression set on a column with the constraint DEFAULT. 

COLUMN_SET_USING
 VARCHAR Expression set on a column with the constraint SET USING. 
IS_IDENTITY
 BOOLEAN

Specifies whether the column is an IDENTITY column.

Examples

Retrieve table and column information from the COLUMNS table:

=> 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)

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)

DATETIME_PRECISION is 4 because the table definition declares precision as 4:

=> 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 is a sequence available only for numeric column types. To identify what column in a table, if any, is an identity column, search the COLUMNS table to find the identity column in a table testid:

=> CREATE TABLE testid (c1 IDENTITY(1, 1, 1000), c2 INT);
=> \x
Expanded display is on.
=> SELECT * FROM COLUMNS WHERE is_identity='t' AND table_name='testid'; 
-[ RECORD 1 ]------------+--------------------
table_id                 | 45035996273719486
table_schema             | public
table_name               | testid
is_system_table          | f
column_id                | 45035996273719486-1
column_name              | c1
data_type                | int
data_type_id             | 6
data_type_length         | 8
character_maximum_length |
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 1
is_nullable              | f
column_default           |
is_identity              | t

Use the SEQUENCES table to get detailed information about the sequence in testid:

=> SELECT * FROM sequences WHERE identity_table_name='testid';
-[ RECORD 1 ]-------+--------------------
sequence_schema     | public
sequence_name       | testid_c1_seq
owner_name          | dbadmin
identity_table_name | testid
session_cache_count | 1000
allow_cycle         | f
output_ordered      | f
increment_by        | 1
minimum             | 1
maximum             | 9223372036854775807
current_value       | 0
sequence_schema_id  | 45035996273704976
sequence_id         | 45035996273719488
owner_id            | 45035996273704962
identity_table_id   | 45035996273719486

For more information about sequences and identity columns, see Sequences.