Tables (columns) v1

Description

This page provides information about the tables in the database, on a per-column basis, so it is here you will find column encodings and the like.

Any single-column constraints are also shown. Multi-column constraints (and also single column constraints) are shown on the Tables (constraints) page.

Note that all I/O in Redshift, in the system tables, is recorded on a per-slice, per-query and/or per-table basis; there’s no information about columns. As such, there are no I/O columns in this page.

Columns

Name	Type
schema_id	int8
schema	varchar
table_id	int8
table	varchar
column_ordinal	int2
column	varchar
data_type_id	int8
base_data_type_name	varchar
octet_length	int4
precision	int2
scale	int2
data type	varchar
encoding	varchar
sk	int2
dk	varchar
constraints:nn	varchar
constraints:pk	varchar
constraints:u	varchar
blocks:sorted	int8
blocks:unsorted	int8
blocks:total	int8
foreign key:flag	bool
foreign key:schema_id	int8
foreign key:schema	varchar
foreign key:table_id	int8
foreign key:table	varchar
foreign key:column_ordinal	int2
foreign key:column	varchar
foreign key	varchar
default:type	varchar
default:value	varchar
default	varchar

Column Descriptions

schema_id

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

schema

The schema name.

table_id

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

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

table

The table name.

column_ordinal

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

column

The column name.

data_type_id

The data type ID. This column is emitted in CSV exports only.

base_data_type_name

This is the base data type name, which is to say, the data type name but sans adornment; no length, or precision, or scale. Just the name.

octet_length

The length of any data type which has a length (which is to say, char, varchar, and varbyte), NULL otherwise. This column is emitted in CSV exports only.

precision

If the data type for the column is numeric, this is the precision, otherwise NULL. This column is emitted in CSV exports only.

Remember that precision 19 or less gives an 8 byte numeric, 20 or more gives a 16 byte numeric, which has to be handled with a maths library rather than processor native instructions, and has unexpected sorting behaviour, to boot.

scale

If the data type for the column is numeric, this is the scale, otherwise NULL. This column is emitted in CSV exports only.

data type

This is the full data type name, which is to say, the data type name with adornment; showing length, or precision and scale.

encoding

Value	Encoding Type	Encoding ID
raw	raw	0, 128
bytedict	Byte Dictionary	1
delta	Delta (one byte)	2
lzo	LZO	3, 131
runlength	Runlength	4
delta32k	Delta (two byte)	5
text255	Text255 (255 byte limit)	7
mostly8	Mostly (one byte)	15
mostly16	Mostly (two byte)	16
mostly32	Mostly (four byte)	17
text32k	Text32k (no limit)	18
zstd	ZSTD	19, 147
az64	AZ64	20, 148
unknown	Unknown	n/a

I’ve included the encoding IDs, because they’re a little intriguing. First, we can see some encoding have more than one ID, which is unexpected. Then we can see what looks like a missing encoding, number six. What was that? what happened to it?

sk

The sortkey ordinal of the column, NULL if the column is not part of the sortkey.

dk

If this column is the distribution key, then DK, otherwise NULL.

constraints:nn

Set to the string NN if the NOT NULL constraint is set, NULL otherwise.

constraints:pk

Set to the string PK if the primary key constraint is set, NULL otherwise.

constraints:u

Set to the string U if the unique constraint is set, NULL otherwise.

blocks:sorted

The number of sorted blocks.

blocks:unsorted

The number of unsorted blocks.

blocks:total

The total number of blocks.

foreign key:flag

True if a foreign key is specified for the column, false otherwise. This column is emitted in CSV exports only.

foreign key:schema_id

If a foreign key is specified for the column, this it the schema ID of the foreign table. This column is emitted in CSV exports only.

foreign key:schema

If a foreign key is specified for the column, this it the schema name of the foreign table. This column is emitted in CSV exports only.

foreign key:table_id

If a foreign key is specified for the column, this it the table ID of the foreign table. This column is emitted in CSV exports only.

foreign key:table

If a foreign key is specified for the column, this it the table name of the foreign table. This column is emitted in CSV exports only.

foreign key:column_ordinal

If a foreign key is specified for the column, this it the column ordinal of the column in the foreign table. This column is emitted in CSV exports only.

foreign key:column

If a foreign key is specified for the column, this it the column name of the column in the foreign table. This column is emitted in CSV exports only.

foreign key

If a foreign key is specified for the column, this is the full text of the foreign key - the schema name, table name and column name; otherwise, NULL.

default:type

If a default is specified for the column, this is the type of default, otherwise NULL. This column is emitted in CSV exports only.

There are three types of default;

. default . identity . default_identity (which is my shorthand for generated by default as identity)

This column shows only the type of default, not the value of the default.

default:value

If a default is specified for the column, this is the value of default, otherwise NULL. This column is emitted in CSV exports only.

There are three types of default, and their values are presented as strings, as follows;

Type	Value
default	the default value
identity	seed, step
default_identity	seed, step

This column shows only the value of the default, not the type of the default.

default

If a default is specified for the column, this is the type and value of default, otherwise NULL.

The columns, when not NULL, is one of the following three strings;

default( default value )
identity( seed, step )
default_identity( seed, step )