Tables (constraints) v1


This page provides information about both multi-column and single-column constraints on the tables in the database.


Name Type
schema_id int8
schema varchar
table_id int8
table varchar
constraint:type varchar
constraint:name varchar
constraint:column_ordinal int2
constraint:column varchar

Column Descriptions


The schema ID. This column is emitted in CSV exports only.


The schema name.


The table ID. This column is emitted in CSV exports only.

Surprisingly, table IDs turn out to be unique across all databases.


The table name.


Value Constraint Type
c Check Constraint
f Foreign Key
p Primary Key
u Unique


The constraint name. Names are not required to be unique, but usually of course in practise they are.


The column ordinal. This column is emitted in CSV exports only.

Column ordinals are unique to a single table.

In pg_attribute, ordinals for user columns count from 1, system columns are negative.

In stv_blocklist, ordinals for user columns count from 0, and system columns have a rather extraordinary numbering, in that they are the numbers after the final user column, which is to say, the system column numbers vary by table.

(I believe stv_blocklist internally is using unsigned variables, in part from this numbering issue, but also because I’ve been able to make num_values overflow, and when it overflows, it looks like an unsigned value which has wrapped and is then having its bit pattern interpreted as a signed value, where Redshift data types are all signed.)

In pg_attribte, you see quite a number of system columns, but in fact most vestigial, left-overs from Postgres, and do not exist on disk. Only three system columns are actually in use, which have the ordinals -2, -8 and -9, being the row ID, MVCC insert transaction ID and MVCC delete transaction ID, although offhand I cannot remember which is which (although I do recall it’s not the order you’d expect - I have a feeling -8 is the row ID).


The column name.