<!-- $Header: /cvsroot/pgsql/doc/src/sgml/information_schema.sgml,v 1.2 2003/05/25 09:36:09 petere Exp $ -->

owned by the current user. (The owner of the table or domain is

the owner of the constraint.)

(<literal>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).

<entry>

Data type of the column, if it is a built-in type, else

<literal>USER-DEFINED</literal> (in that case, the type is

identified in <literal>udt_name</literal> 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 <literal>domain_name</literal> and associated

columns).

</entry>

If <literal>data_type</literal> identifies a character or bit

string type, the declared maximum length; null for all other

data types or if no maximum length was declared.

If <literal>data_type</literal> 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.

If <literal>data_type</literal> 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 may be expressed in decimal (base 10)

or binary (base 2) terms, as specified in the column

If <literal>data_type</literal> identifies a numeric type, this

column indicates in which base the values in the columns

If <literal>data_type</literal> 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 may

be expressed in decimal (base 10) or binary (base 2) terms, as

specified in the column

If <literal>data_type</literal> identifies a date, time, or

interval type, the declared precision; null for all other data

types or if no precision was declared.

Name of the database that the column data type (the underlying

type of the domain, if applicable) is defined in (always the

current database)

Name of the schema that the column data type (the underlying

type of the domain, if applicable) is defined in

<entry>

Name of the column data type (the underlying type of the

domain, if applicable)

</entry>

<entry>

A unique identifier of the data type of the column (The

specific format of the identifier is not defined and not

guaranteed to remain the same in future versions.)

</entry>

<para>

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 <literal>data_type</literal> 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

<literal>pg_catalog</literal>. This column may 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 <literal>udt_name</literal>,

<literal>udt_schema</literal>, and <literal>udt_catalog</literal>

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

<literal>domain_name</literal>, <literal>domain_schema</literal>,

and <literal>domain_catalog</literal>. If you want to pair up

columns with their associated data types and treat domains as

separate types, you could write <literal>coalesce(domain_name,

udt_name)</literal>, etc. Finally, if you want to check whether

two columns have the same type, use

<literal>dtd_identifier</literal>.

</para>

</sect1>

<sect1 id="infoschema-constraint-table-usage">

<title><literal>constraint_table_usage</literal></title>

<para>

The view <literal>constraint_table_usage</literal> identifies all

tables in the current database that are used by some constraint and

are owned by the current user. (This is different from the view

<literal>table_constraints</literal>, which identifies all table

constraints along with the table they are defined on.) For a

foreign key constraint, this view identifies the table that the

foreign key references. Unique and primary key constraints simply

identify the table they belong to. Check constraints and not-null

constraints are not included in this view.

</para>

<table>

<title><literal>constraint_table_usage</literal> Columns</title>

<tgroup cols="3">

<thead>

<row>

<entry>Name</entry>

<entry>Data Type</entry>

<entry>Description</entry>

</row>

</thead>

<tbody>

<row>

<entry><literal>table_catalog</literal></entry>

<entry><type>sql_identifier</type></entry>

<entry>

Name of the database that contains the table that is used by

some constraint (always the current database)

</entry>

</row>

<row>

<entry><literal>table_schema</literal</entry>

<entry><type>sql_identifier</type></entry>

<entry>

Name of the schema that contains the table that is used by some

constraint

</entry>

</row>

<row>

<entry><literal>table_name</literal</entry>

<entry><type>sql_identifier</type></entry>

<entry>Name of the table that is used by some constraint</entry>

</row>

<row>

<entry><literal>constraint_catalog</literal></entry>

<entry><type>sql_identifier</type></entry>

<entry>Name of the database that contains the constraint (always the current database)</entry>

</row>

<row>

<entry><literal>constraint_schema</literal</entry>

<entry><type>sql_identifier</type></entry>

<entry>Name of the schema that contains the constraint</entry>

</row>

<row>

<entry><literal>constraint_name</literal</entry>

<entry><type>sql_identifier</type></entry>

<entry>Name of the constraint</entry>

</row>

</tbody>

</tgroup>

</table>

constraints belonging to domains owned by the current user.

<entry>

Name of the database that contains the unique or primary key

constraint that the foreign key constraint references (always

the current database)

</entry>

<entry>

Name of the schema that contains the unique or primary key

constraint that the foreign key constraint references

</entry>

<entry>

Name of the unique or primary key constraint that the foreign

key constraint references

</entry>

Match option of the foreign key constraint:

Update rule of the foreign key constraint:

Delete rule of the foreign key constraint:

current database that are owned by the current user.

constraints belonging to tables owned by the current user.

defined in the current database. Only those tables and views are

shown that the current user has access to (by way of being the

owner or having some privilege).

current database. Only those views are shown that the current user

has access to (by way of being the owner or having some privilege).