PostgreSQL system catalog tables

This page contains information about the PostgreSQL system catalog tables available in Spanner.

PostgreSQL system catalog tables store schema data, such as information about tables and columns, and internal bookkeeping information. These system catalog tables are part of a namespace called pg_catalog.

Limitations and differences

This section lists the columns that are missing and other differences between the Spanner implementation of PostgreSQL system catalog tables and PostgreSQL.

Unsupported columns

Spanner doesn't support every column in its implementation of PostgreSQL system catalog tables. The following columns aren't supported and therefore don't show up in the table.

Catalog table name Missing column names
pg_am amhandler
pg_attribute attacl, attmissingval
pg_class relacl
pg_constraint confdelsetcols
pg_index indnullsnotdistinct
pg_namespace nspacl
pg_proc prosupport, proacl
pg_sequences data_type
pg_type typsubscript, typinput, typoutput, typreceive, typsend, typmodin, typmodout, typanalyze, typacl

Empty columns

Some columns in supported system catalog tables don't contain data. These columns could have content added later so avoid queries that rely on these columns being empty. For each table listed on this page, supported columns are listed, along with information about whether or not the column has content.

Empty tables

Some tables and views have no data in them. This is so that queries can refer to them without encountering errors. This improves compatibility with tools without requiring rewrites to avoid those tables.

Difference from PostgreSQL

In PostgreSQL, you can write to these tables directly to modify the database. In Spanner, system tables are read-only.

The Spanner implementation contains the following changes to PostgreSQL system catalog columns:

  • pg_node_tree columns (most notably pg_attrdef.adbin and pg_proc.prosqlbody) are text columns that contain the SQL expressions.

  • oidvector columns (most notably pg_proc.proargtypes) are oid[] columns.

  • int2vector columns are int8[] columns.

  • pg_class.relnatts column is NULL for sequences because you can't select them in Spanner.

  • pg_collation only contains default and C. General collation support isn't available.

  • pg_index does not contain entries for primary key indexes.

PostgreSQL system catalog tables list

This section contains the PostgreSQL system catalog tables that Spanner supports.

pg_am

The pg_am table has content.

The following table shows whether columns have content.

Column name Type Has content
oid oid Y
amname text Y
amtype char Y

pg_attrdef

The pg_attrdef table has content.

The following table shows whether columns have content.

Column name Type Has content
oid oid Y
adrelid oid Y
adnum int8 Y
adbin text Y

The adbin column should contain a text value that represents the serialized value of the default value.

pg_attribute

The pg_attribute table has content.

The following table shows whether columns have content.

Column name Type Has content
attrelid oid Y
attname text Y
atttypid oid Y
attstattarget int8 N
attlen int8 N
attnum int8 Y
attndims int8 Y
attcacheoff int8 Y
atttypmod int8 N
attbyval bool N
attalign char N
attstorage char N
attcompression char Y
attnotnull bool Y
atthasdef bool Y
atthasmissing bool Y
attidentity char Y
attgenerated char Y
attisdropped bool Y
attislocal bool Y
attinhcount int8 Y
attcollation oid N
attoptions text[] N
attfdwoptions text[] N

pg_class

The pg_class

has content.

The following table shows whether columns have content.

Column name Type Has content
oid oid Y
relname text Y
relnamespace oid Y
reltype oid N
reloftype oid N
relowner oid N
relam oid Y
relfilenode oid N
reltablespace oid N
relpages int8 N
reltuples float8 N
relallvisible int8 N
reltoastrelid oid N
relhasindex bool Y
relisshared bool N
relpersistence char Y
relkind char Y
relnatts int8 Y
relchecks int8 Y
relhasrules bool N
relhastriggers bool N
relhassubclass bool N
relrowsecurity bool N
relforcerowsecurity bool N
relispopulated bool Y
relreplident char N
relispartition bool N
relrewrite oid N
relfrozenxid int8 N
relminmxid int8 N
reloptions text[] N
relpartbound text N

pg_collation

The pg_collation table has content.

The following table shows whether columns have content.

Column name Type Has content
oid oid Y
collname text Y
collnamespace oid Y
collowner oid N
collprovider char Y
collisdeterministic bool Y
collencoding int8 Y
collcollate text N
collctype text N
colliculocale text N
collversion text N

pg_constraint

The pg_constraint table has content.

The following table shows whether columns have content.

Column name Type Has content
oid oid Y
conname text Y
connamespace oid Y
contype char Y
condeferrable bool N
condeferred bool N
convalidated bool Y
conrelid oid Y
contypid oid N
conindid oid N
conparentid oid N
confrelid oid Y
confupdtype char Y
confdeltype char Y
confmatchtype char N
conislocal bool N
coninhcount int8 N
connoinherit bool N
conkey int8[] Y
confkey int8[] Y
conpfeqop oid[] N
conppeqop oid[] N
conffeqop oid[] N
conexclop oid[] N
conbin text N

pg_description

The pg_description table has no content.

The following table shows whether columns have content.

Column name Type Has content
objoid oid N
classoid oid N
objsubid int64 N
description text N

pg_enum

The pg_enum

table has no content.

The following table shows whether columns have content.

Column name Type Has content
oid oid N
enumtypid oid N
collnamespace float8 N
enumlabel text N

pg_index

The pg_index

table has content.

The following table shows whether columns have content.

Column name Type Has content
indexrelid oid Y
indrelid oid Y
indnatts int8 Y
indnkeyatts int8 Y
indisunique bool Y
indisprimary bool Y
indisexclusion bool Y
indimmediate bool N
indisclustered bool Y
indisvalid bool Y
indcheckxmin bool Y
indisready bool Y
indislive bool Y
indisreplident bool Y
indkey int8[] N
indcollation oid[] N
indclass oid[] N
indoption int8[] N
indexprs text N
indpred text N

pg_namespace

The pg_namespace table has content.

The following table shows whether columns have content.

Column name Type Has content
oid oid Y
nspname text Y
nspowner oid N

pg_proc

The pg_proc table has content.

The following table shows whether columns have content.

Column name Type Has content
oid oid Y
proname text Y
pronamespace oid Y
proowner oid N
prolang oid N
procost float8 N
prorows float8 N
provariadic oid Y
prokind char Y
prosecdef bool N
proleakproof bool N
proisstrict bool N
proretset bool Y
provolatile char N
proparallel char N
pronargs int8 Y
pronargdefaults int8 Y
prorettype oid Y
proargtypes oid[] Y
proallargtypes oid[] N
proargmodes char[] N
proargdefaults text N
protrftypes oid[] N
prosrc text[] N
probin text[] N
prosqlbody text Y
proconfig text[] N

pg_sequence

The pg_sequence table has content.

The following table shows whether columns have content.

Column name Type Has content
seqrelid oid Y
seqtypid oid Y
seqstart int8 Y
seqincrement int8 N
seqmax int8 N
seqmin int8 N
seqcache int8 Y
seqcycle bool Y

pg_type

The pg_type table has content.

The following table shows whether columns have content.

Column name Type Has content
oid oid Y
typname text Y
typnamespace oid Y
typowner oid N
typlen int8 Y
typbyval bool Y
typtype char Y
typcategory char Y
typispreferred bool Y
typisdefined bool Y
typdelim char Y
typrelid oid Y
typelem oid Y
typarray oid Y
typalign char N
typstorage char N
typnotnull bool N
typbasetype oid N
typtypmod int8 N
typndims int8 N
typcollation oid N
typdeafultbin text N
typdefault text N

What's next