</indexterm>

<para>

Recall the <classname>weather</classname> and

<classname>cities</classname> tables from <xref

Recall the <structname>weather</structname> and

<structname>cities</structname> tables from <xref

linkend="tutorial-sql"/>. Consider the following problem: You

want to make sure that no one can insert rows in the

<classname>weather</classname> table that do not have a matching

entry in the <classname>cities</classname> table. This is called

<structname>weather</structname> table that do not have a matching

entry in the <structname>cities</structname> table. This is called

maintaining the <firstterm>referential integrity</firstterm> of

your data. In simplistic database systems this would be

implemented (if at all) by first looking at the

<classname>cities</classname> table to check if a matching record

<structname>cities</structname> table to check if a matching record

exists, and then inserting or rejecting the new

<classname>weather</classname> records. This approach has a

<structname>weather</structname> records. This approach has a

number of problems and is very inconvenient, so

<productname>PostgreSQL</productname> can do this for you.

</para>

</para>

<para>

Let's create two tables: A table <classname>cities</classname>

and a table <classname>capitals</classname>. Naturally, capitals

Let's create two tables: A table <structname>cities</structname>

and a table <structname>capitals</structname>. Naturally, capitals

are also cities, so you want some way to show the capitals

implicitly when you list all cities. If you're really clever you

might invent some scheme like this:

</para>

<para>

In this case, a row of <classname>capitals</classname>

In this case, a row of <structname>capitals</structname>

<firstterm>inherits</firstterm> all columns (<structfield>name</structfield>,

<structfield>population</structfield>, and <structfield>elevation</structfield>) from its

<firstterm>parent</firstterm>, <classname>cities</classname>. The

<firstterm>parent</firstterm>, <structname>cities</structname>. The

type of the column <structfield>name</structfield> is

<type>text</type>, a native <productname>PostgreSQL</productname>

type for variable length character strings. The

<classname>capitals</classname> table has

<structname>capitals</structname> table has

an additional column, <structfield>state</structfield>, which shows its

state abbreviation. In

<productname>PostgreSQL</productname>, a table can inherit from

<para>

Here the <literal>ONLY</literal> before <literal>cities</literal>

indicates that the query should be run over only the

<classname>cities</classname> table, and not tables below

<classname>cities</classname> in the inheritance hierarchy. Many

<structname>cities</structname> table, and not tables below

<structname>cities</structname> in the inheritance hierarchy. Many

of the commands that we have already discussed &mdash;

<command>SELECT</command>, <command>UPDATE</command>, and

<command>DELETE</command> &mdash; support this <literal>ONLY</literal>

);

</programlisting>

Without the specification of the column, the foreign key would also set

the column <literal>tenant_id</literal> to null, but that column is still

the column <structfield>tenant_id</structfield> to null, but that column is still

required as part of the primary key.

</para>

<para>

As an example, suppose that user <literal>miriam</literal> creates

table <literal>mytable</literal> and does:

table <structname>mytable</structname> and does:

<programlisting>

GRANT SELECT ON mytable TO PUBLIC;

GRANT SELECT, UPDATE, INSERT ON mytable TO admin;

<para>

Below is a larger example of how this feature can be used in production

environments. The table <literal>passwd</literal> emulates a Unix password

environments. The table <structname>passwd</structname> emulates a Unix password

file:

</para>

in <xref linkend="xtypes"/>. The following example inserts the

complex type values <literal>(1,1)</literal>

and <literal>(3,3)</literal> into the

columns <literal>a</literal> and <literal>b</literal>, and select

columns <structfield>a</structfield> and <structfield>b</structfield>, and select

them from the table after that.

<programlisting>

<literal>NESTED</literal> paths <literal>$.movies[*]</literal> and

<literal>$.books[*]</literal> and also the usage of

<literal>FOR ORDINALITY</literal> column at <literal>NESTED</literal>

levels (columns <literal>movie_id</literal>, <literal>book_id</literal>,

and <literal>author_id</literal>):

levels (columns <structfield>movie_id</structfield>, <structfield>book_id</structfield>,

and <structfield>author_id</structfield>):

<programlisting>

SELECT * FROM JSON_TABLE (

By default, B-tree indexes store their entries in ascending order

with nulls last (table TID is treated as a tiebreaker column among

otherwise equal entries). This means that a forward scan of an

index on column <literal>x</literal> produces output satisfying <literal>ORDER BY x</literal>

index on column <structfield>x</structfield> produces output satisfying <literal>ORDER BY x</literal>

(or more verbosely, <literal>ORDER BY x ASC NULLS LAST</literal>). The

index can also be scanned backward, producing output satisfying

<literal>ORDER BY x DESC</literal>

indexes are best, but sometimes it's better to create separate indexes

and rely on the index-combination feature. For example, if your

workload includes a mix of queries that sometimes involve only column

<literal>x</literal>, sometimes only column <literal>y</literal>, and sometimes both

<structfield>x</structfield>, sometimes only column <structfield>y</structfield>, and sometimes both

columns, you might choose to create two separate indexes on

<literal>x</literal> and <literal>y</literal>, relying on index combination to

<structfield>x</structfield> and <structfield>y</structfield>, relying on index combination to

process the queries that use both columns. You could also create a

multicolumn index on <literal>(x, y)</literal>. This index would typically be

more efficient than index combination for queries involving both

columns, but as discussed in <xref linkend="indexes-multicolumn"/>, it

would be less useful for queries involving only <literal>y</literal>. Just

how useful will depend on how effective the B-tree index skip scan

optimization is; if <literal>x</literal> has no more than several hundred

optimization is; if <structfield>x</structfield> has no more than several hundred

distinct values, skip scan will make searches for specific

<literal>y</literal> values execute reasonably efficiently. A combination

<structfield>y</structfield> values execute reasonably efficiently. A combination

of a multicolumn index on <literal>(x, y)</literal> and a separate index on

<literal>y</literal> might also serve reasonably well. For

queries involving only <literal>x</literal>, the multicolumn index could be

<structfield>y</structfield> might also serve reasonably well. For

queries involving only <structfield>x</structfield>, the multicolumn index could be

used, though it would be larger and hence slower than an index on

<literal>x</literal> alone. The last alternative is to create all three

<structfield>x</structfield> alone. The last alternative is to create all three

indexes, but this is probably only reasonable if the table is searched

much more often than it is updated and all three types of query are

common. If one of the types of query is much less common than the

<listitem>

<para>

The query must reference only columns stored in the index. For

example, given an index on columns <literal>x</literal>

and <literal>y</literal> of a table that also has a

column <literal>z</literal>, these queries could use index-only scans:

example, given an index on columns <structfield>x</structfield>

and <structfield>y</structfield> of a table that also has a

column <structfield>z</structfield>, these queries could use index-only scans:

<programlisting>

SELECT x, y FROM tab WHERE x = 'key';

SELECT x FROM tab WHERE x = 'key' AND y &lt; 42;

</para>

<para>

Because column <literal>y</literal> is not part of the index's search

Because column <structfield>y</structfield> is not part of the index's search

key, it does not have to be of a data type that the index can handle;

it's merely stored in the index and is not interpreted by the index

machinery. Also, if the index is a unique index, that is

<programlisting>

CREATE UNIQUE INDEX tab_x_y ON tab(x) INCLUDE (y);

</programlisting>

the uniqueness condition applies to just column <literal>x</literal>,

not to the combination of <literal>x</literal> and <literal>y</literal>.

the uniqueness condition applies to just column <structfield>x</structfield>,

not to the combination of <structfield>x</structfield> and <structfield>y</structfield>.

(An <literal>INCLUDE</literal> clause can also be written

in <literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal>

constraints, providing alternative syntax for setting up an index like

<programlisting>

CREATE INDEX tab_x_y ON tab(x, y);

</programlisting>

even though they had no intention of ever using <literal>y</literal> as

even though they had no intention of ever using <structfield>y</structfield> as

part of a <literal>WHERE</literal> clause. This works fine as long as

the extra columns are trailing columns; making them be leading columns is

unwise for the reasons explained in <xref linkend="indexes-multicolumn"/>.

context <literal>f(x)</literal>, but the planner does not notice that and

concludes that an index-only scan is not possible. If an index-only scan

seems sufficiently worthwhile, this can be worked around by

adding <literal>x</literal> as an included column, for example

adding <structfield>x</structfield> as an included column, for example

<programlisting>

CREATE INDEX tab_f_x ON tab (f(x)) INCLUDE (x);

</programlisting>

Since data types can be defined in a variety of ways in SQL, and

<productname>PostgreSQL</productname> contains additional ways to

define data types, their representation in the information schema

can be somewhat difficult. The column <literal>data_type</literal>

can be somewhat difficult. The column <structfield>data_type</structfield>

is supposed to identify the underlying built-in type of the column.

In <productname>PostgreSQL</productname>, this means that the type

is defined in the system catalog schema

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

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

the precision columns). The columns <structfield>udt_name</structfield>,

<structfield>udt_schema</structfield>, and <structfield>udt_catalog</structfield>

always identify the underlying data type of the column, even if the

column is based on a domain. (Since

<productname>PostgreSQL</productname> treats built-in types like

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

<structfield>domain_name</structfield>, <structfield>domain_schema</structfield>,

and <structfield>domain_catalog</structfield>. 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.

the sequence data type (see above). 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 <literal>numeric_precision_radix</literal>.

column <structfield>numeric_precision_radix</structfield>.

</para></entry>

</row>

</para>

<para>

This column indicates in which base the values in the columns

<literal>numeric_precision</literal> and

<literal>numeric_scale</literal> are expressed. The value is

<structfield>numeric_precision</structfield> and

<structfield>numeric_scale</structfield> are expressed. The value is

either 2 or 10.

</para></entry>

</row>

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

<literal>numeric_precision_radix</literal>.

<structfield>numeric_precision_radix</structfield>.

</para></entry>

</row>

</sect1>

<sect1 id="infoschema-sql-features">

<title><literal>sql_features</literal></title>

<title><structname>sql_features</structname></title>

<para>

The table <literal>sql_features</literal> contains information

The table <structname>sql_features</structname> contains information

about which formal features defined in the SQL standard are

supported by <productname>PostgreSQL</productname>. This is the

same information that is presented in <xref linkend="features"/>.

</sect1>

<sect1 id="infoschema-sql-implementation-info">

<title><literal>sql_implementation_info</literal></title>

<title><structname>sql_implementation_info</structname></title>

<para>

The table <literal>sql_implementation_info</literal> contains

The table <structname>sql_implementation_info</structname> contains

information about various aspects that are left

implementation-defined by the SQL standard. This information is

primarily intended for use in the context of the ODBC interface;

</sect1>

<sect1 id="infoschema-sql-parts">

<title><literal>sql_parts</literal></title>

<title><structname>sql_parts</structname></title>

<para>

The table <literal>sql_parts</literal> contains information about

The table <structname>sql_parts</structname> contains information about

which of the several parts of the SQL standard are supported by

<productname>PostgreSQL</productname>.

</para>

</sect1>

<sect1 id="infoschema-sql-sizing">

<title><literal>sql_sizing</literal></title>

<title><structname>sql_sizing</structname></title>

<para>

The table <literal>sql_sizing</literal> contains information about

The table <structname>sql_sizing</structname> contains information about

various size limits and maximum values in

<productname>PostgreSQL</productname>. This information is

primarily intended for use in the context of the ODBC interface;

in <productname>PostgreSQL</productname>) and distinct types (not

implemented in <productname>PostgreSQL</productname>). To be

future-proof, use the

column <literal>user_defined_type_category</literal> to

column <structfield>user_defined_type_category</structfield> to

differentiate between these. Other user-defined types such as base

types and enums, which are <productname>PostgreSQL</productname>

extensions, are not shown here. For domains,

<para>

<literal>InvalidOid</literal> is returned if the column number is out of range,

or if the specified column is not a simple reference to a table column.

You can query the system table <literal>pg_class</literal> to determine

You can query the system table <structname>pg_class</structname> to determine

exactly which table is referenced.

</para>

</para>

<para>

You can query the system table <literal>pg_type</literal> to

You can query the system table <structname>pg_type</structname> to

obtain the names and properties of the various data types. The

<acronym>OID</acronym>s of the built-in data types are defined

in the file <filename>catalog/pg_type_d.h</filename>