The view columns contains information
about all table columns (or view columns) in the database. System
columns (oid, etc.) are not included.
Only those columns are shown that the current user has access to
(by way of being the owner or having some privilege).

Table 33-10. columns
Columns

Name

Data Type

Description

table_catalog

sql_identifier

Name of the database containing the table (always the
current database)

table_schema

sql_identifier

Name of the schema containing the table

table_name

sql_identifier

Name of the table

column_name

sql_identifier

Name of the column

ordinal_position

cardinal_number

Ordinal position of the column within the table
(count starts at 1)

column_default

character_data

Default expression of the column

is_nullable

character_data

YES if the column is
possibly nullable, NO if it is
known not nullable. A not-null constraint is one way a
column can be known not nullable, but there can be
others.

data_type

character_data

Data type of the column, if it is a built-in type, or
ARRAY if it is some array (in
that case, see the view element_types), else USER-DEFINED (in that case, the type is
identified in udt_name and
associated columns). If the column is based on a domain,
this column refers to the type underlying the domain (and
the domain is identified in domain_name and associated columns).

character_maximum_length

cardinal_number

If data_type identifies a
character or bit string type, the declared maximum
length; null for all other data types or if no maximum
length was declared.

character_octet_length

cardinal_number

If data_type identifies a
character type, the maximum possible length in octets
(bytes) of a datum (this should not be of concern to
PostgreSQL users); null
for all other data types.

numeric_precision

cardinal_number

If data_type identifies a
numeric type, this column contains the (declared or
implicit) precision of the type for this column. The
precision indicates the number of significant digits. It
can be expressed in decimal (base 10) or binary (base 2)
terms, as specified in the column numeric_precision_radix. For all other
data types, this column is null.

numeric_precision_radix

cardinal_number

If data_type identifies a
numeric type, this column indicates in which base the
values in the columns numeric_precision and numeric_scale are expressed. The value is
either 2 or 10. For all other data types, this column is
null.

numeric_scale

cardinal_number

If data_type identifies an
exact numeric type, this column contains the (declared or
implicit) scale of the type for this column. The scale
indicates the number of significant digits to the right
of the decimal point. It can be expressed in decimal
(base 10) or binary (base 2) terms, as specified in the
column numeric_precision_radix.
For all other data types, this column is null.

datetime_precision

cardinal_number

If data_type identifies a
date, time, timestamp, or interval type, this column
contains the (declared or implicit) fractional seconds
precision of the type for this column, that is, the
number of decimal digits maintained following the decimal
point in the seconds value. For all other data types,
this column is null.

interval_type

character_data

Not yet implemented

interval_precision

character_data

Not yet implemented

character_set_catalog

sql_identifier

Applies to a feature not available in PostgreSQL

character_set_schema

sql_identifier

Applies to a feature not available in PostgreSQL

character_set_name

sql_identifier

Applies to a feature not available in PostgreSQL

collation_catalog

sql_identifier

Applies to a feature not available in PostgreSQL

collation_schema

sql_identifier

Applies to a feature not available in PostgreSQL

collation_name

sql_identifier

Applies to a feature not available in PostgreSQL

domain_catalog

sql_identifier

If the column has a domain type, the name of the
database that the domain is defined in (always the
current database), else null.

domain_schema

sql_identifier

If the column has a domain type, the name of the
schema that the domain is defined in, else null.

domain_name

sql_identifier

If the column has a domain type, the name of the
domain, else null.

udt_catalog

sql_identifier

Name of the database that the column data type (the
underlying type of the domain, if applicable) is defined
in (always the current database)

udt_schema

sql_identifier

Name of the schema that the column data type (the
underlying type of the domain, if applicable) is defined
in

udt_name

sql_identifier

Name of the column data type (the underlying type of
the domain, if applicable)

An identifier of the data type descriptor of the
column, unique among the data type descriptors pertaining
to the table. This is mainly useful for joining with
other instances of such identifiers. (The specific format
of the identifier is not defined and not guaranteed to
remain the same in future versions.)

is_self_referencing

character_data

Applies to a feature not available in PostgreSQL

is_identity

character_data

Applies to a feature not available in PostgreSQL

identity_generation

character_data

Applies to a feature not available in PostgreSQL

identity_start

character_data

Applies to a feature not available in PostgreSQL

identity_increment

character_data

Applies to a feature not available in PostgreSQL

identity_maximum

character_data

Applies to a feature not available in PostgreSQL

identity_minimum

character_data

Applies to a feature not available in PostgreSQL

identity_cycle

character_data

Applies to a feature not available in PostgreSQL

is_generated

character_data

Applies to a feature not available in PostgreSQL

generation_expression

character_data

Applies to a feature not available in PostgreSQL

is_updatable

character_data

YES if the column is
updatable, NO if not (Columns in
base tables are always updatable, columns in views not
necessarily)

Since data types can be defined in a variety of ways in SQL,
and PostgreSQL contains
additional ways to define data types, their representation in the
information schema can be somewhat difficult. The column
data_type is supposed to identify the
underlying built-in type of the column. In PostgreSQL, this means that the type is
defined in the system catalog schema pg_catalog. This column might be useful if the
application can handle the well-known built-in types specially
(for example, format the numeric types differently or use the
data in the precision columns). The columns udt_name, udt_schema, and
udt_catalog always identify the
underlying data type of the column, even if the column is based
on a domain. (Since PostgreSQL
treats built-in types like user-defined types, built-in types
appear here as well. This is an extension of the SQL standard.)
These columns should be used if an application wants to process
data differently according to the type, because in that case it
wouldn't matter if the column is really based on a domain. If the
column is based on a domain, the identity of the domain is stored
in the columns domain_name, domain_schema, and domain_catalog. If you want to pair up columns
with their associated data types and treat domains as separate
types, you could write coalesce(domain_name,
udt_name), etc.