Tables

Daria Barysheva

Contents

Create a new table
View the list of tables
View the specified table
System catalog tables
Useful queries

Table is a main data object in ADB. ADB tables are similar to tables in any relational database, except distribution of table rows across the different cluster segments. When creating a table, you specify the table distribution policy.

Also, ADB provides several table storage types and compression options that can be used to store data in the most efficient way.

Create a new table

Syntax

To create an initially empty table in the current database and define its structure, use the CREATE TABLE command. The user who issues the command owns the table. The basic syntax of this command is described below.

NOTE

For more details, see CREATE TABLE in the Greenplum documentation.

CREATE [ { TEMPORARY | TEMP } | UNLOGGED] TABLE [IF NOT EXISTS]
  <table_name> (
  [ { <column_name> <data_type> [ COLLATE <collation> ] [<column_constraint> [ ... ] ] [ ENCODING ( <storage_column_directive> [, ...] ) ]
    | <table_constraint>
    | LIKE <source_table> [ <like_option> ... ] }
    [, ... ]
  ])
[ INHERITS ( <parent_table> [, ... ] ) ]
[ WITH ( <storage_table_directive> [, ... ] ) ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <tablespace_name> ]
[ DISTRIBUTED BY (<column_name> [<opclass>], [ ... ] )
       | DISTRIBUTED RANDOMLY | DISTRIBUTED REPLICATED ]
[ PARTITION BY <partition_type> (<column_name>) <partition_specification>]

Basic clauses

TEMPORARY | TEMP

Specifies a temporary table, which is automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below)

UNLOGGED

Specifies an unlogged table, which data is not written to the write-ahead log (WAL). This makes such tables faster than ordinary tables, yet not crash-safe. The contents of unlogged tables are not replicated to mirror segment instances

COLLATE

Defines the column collation. If not specified, the column data type’s default collation is used

ENCODING

Defines the column storage options: type of compression, compression level, and block size. This clause is valid only for append-optimized column-oriented tables

LIKE

Specifies a table from which the new table automatically copies all column names, their data types, NOT NULL-constraints. Other column definition parts can be included or excluded via the INCLUDING/EXCLUDING statements (see the <like_option> argument below). Unlike INHERITS, the new table and original table are completely decoupled after creation is complete. It means that changing the original table structure (via ALTER TABLE) has no impact on the new table. Pay attention that storage properties specified in the WITH clause and partition structure are not copied

INHERITS

Specifies a list of tables from which the new table automatically inherits all columns and their constraints. Compare to the LIKE clause, use of INHERITS creates a persistent relationship between the new child table and its parent table. All modifications applied to the parent table structure normally propagate to the child one as well

WITH

Defines storage parameters for the table

ON COMMIT

Specifies the behavior of temporary tables at the end of a transaction block. Possible values:

PRESERVE ROWS — no special action is taken. This is the default behavior.
DELETE ROWS — all rows in the temporary table should be deleted at the end of the current transaction. Essentially, an automatic TRUNCATE is done.
DROP — the temporary table should be dropped at the end of the current transaction.

TABLESPACE

Defines the name of the tablespace in which the new table is to be created. If not specified, the database’s default tablespace is used, or the server configuration parameter temp_tablespaces (in case of a temporary table)

DISTRIBUTED BY

Sets the table distribution policy. Possible values:

DISTRIBUTED BY (<column_name> [<opclass>], [ … ] ) — hash distribution.
DISTRIBUTED RANDOMLY — random distribution that is based on the round-robin algorithm.
DISTRIBUTED REPLICATED — full distribution (the table copy is stored on every cluster segment).

For more details, see Distribution

PARTITION BY

Specifies a partitioned table. For more details, see Partitioning

Arguments

<table_name>

A table name

<column_name>

A column name

<data_type>

A column data type. See Recommendations on choosing column data types below

<collation>

A column collation

<column_constraint>

A constraint defined at the column level. The name <constraint_name> is given optionally. Possible constraints:

NOT NULL — specifies that the column is not allowed to contain null values.
NULL — specifies that the column is allowed to contain null values. It is the default behavior (unless NOT NULL is specified).
CHECK — specifies the boolean expression <expression> which new or updated table rows should satisfy for an INSERT or UPDATE operation to succeed. A constraint marked as NO INHERIT will not propagate to child tables (created via INHERITS).
DEFAULT — sets the default column value equal to <default_expr>.
UNIQUE — specifies that the column should contain only unique values. Null values are not considered equal. Only for heap tables.
PRIMARY KEY — specifies that the column should contain unique (non-duplicate) non-null values. Only one primary key can be specified for a table (it can include several columns). Only for heap tables.

For heap tables, referential integrity constraints (FOREIGN KEY, REFERENCES) are also allowed, but not enforced.

There are two groups of constraints:

DEFERRABLE | NOT DEFERRABLE. A constraint that is not deferrable will be checked immediately after every command. Checking of deferrable constraints can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE and PRIMARY KEY constraints can be deferrable. NOT NULL and CHECK constraints are not deferrable.
INITIALLY DEFERRED | INITIALLY IMMEDIATE. If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default behavior. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check time can be altered with the SET CONSTRAINTS command.

For indexes associated with a UNIQUE or PRIMARY KEY constraint you can define a tablespace and storage options as well as for ordinary tables (see <index_parameters> below).

Syntax:

[ CONSTRAINT <constraint_name>]
{ NOT NULL
  | NULL
  | CHECK  ( <expression> ) [ NO INHERIT ]
  | DEFAULT <default_expr>
  | UNIQUE <index_parameters>
  | PRIMARY KEY <index_parameters>
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Where <index_parameters> is:

[ WITH ( <storage_table_directive> [, ... ] ) ]
[ USING INDEX TABLESPACE <tablespace_name> ]

<storage_column_directive>

Specifies the column storage settings for append-optimized column-oriented tables:

compresstype — compression type.
compresslevel — compression level.
blocksize — block size (in bytes).

Syntax:

compresstype={ZLIB|ZSTD|RLE_TYPE|NONE}
[compresslevel={0-9}]
[blocksize={8192-2097152} ]

For more details on compression options, see Data compression

<table_constraint>

A constraint defined at the table level. The name <constraint_name> is given optionally. Unlike <column_constraint>, does not allow to set constraints NULL, NOT NULL, and DEFAULT. The rest of the syntax is the same (see <column_constraint> above).

Syntax:

[ CONSTRAINT <constraint_name> ]
{ CHECK ( <expression> ) [ NO INHERIT ]
  | UNIQUE ( <column_name> [, ... ] ) <index_parameters>
  | PRIMARY KEY ( <column_name> [, ... ] ) <index_parameters>
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Where <index_parameters> is:

[ WITH ( <storage_table_directive> [, ... ] ) ]
[ USING INDEX TABLESPACE <tablespace_name> ]

You can use this form of constraints to define primary keys based on several columns

<source_table>

An original table, which structure is used when a new table is being created via the LIKE statement

<like_option>

Defines whether to include or exclude the following parts of column definitions (when copying the original table via the LIKE statement):

DEFAULTS — default expressions. By default, they are excluded, which results in the copied columns in the new table not having defaults.
CONSTRAINTS — column constraints CHECK. NOT NULL-constraints are always copied to the new table.
INDEXES — indexes, PRIMARY KEY and UNIQUE constraints.
STORAGE — column storage settings. By default, they are excluded, which results in the copied columns in the new table having type-specific default settings.
COMMENTS — comments for the copied columns, constraints, and indexes.
ALL — all parts mentioned above.

Syntax:

{INCLUDING|EXCLUDING} {DEFAULTS|CONSTRAINTS|INDEXES|STORAGE|COMMENTS|ALL}

<parent_table>

A parent table, which structure is used when a new table is being created via the INHERITS statement

<storage_table_directive>

Specifies the table storage settings:

appendoptimized — whether or not the table should be created as append-optimized. If FALSE or not declared, the table will be created as a heap table.
blocksize — block size (in bytes). Only for append-optimized tables.
orientation — data orientation. If COLUMN or not declared, the table will be created as a column-oriented table, and as a row-oriented — otherwise. Only for append-optimized tables.
checksum — if TRUE or not declared, CRC checksum validation is enabled. The checksum is calculated during block creation and is stored on disk. Checksum validation is performed during block reads. If the checksum calculated during the read does not match the stored checksum, the transaction is cancelled. Only for append-optimized tables.
compresstype — compression type. Only for append-optimized tables.
compresslevel — compression level. Only for append-optimized tables.
fillfactor — a percentage of filling of a standard eight-byte block (between 10 and 100). 100 (complete packing) is the default value. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. For a table which entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate.
oids — if FALSE or not declared, object identifiers (OID) are not assigned to the table rows. It is not recommended to use OIDs for user-created tables, as using OIDs for large table can cause wrap-around of the 32-bit OID counter.

Syntax:

appendoptimized={TRUE|FALSE}
blocksize={8192-2097152}
orientation={COLUMN|ROW}
checksum={TRUE|FALSE}
compresstype={ZLIB|ZSTD|RLE_TYPE|NONE}
compresslevel={0-9}
fillfactor={10-100}
[oids=FALSE]

For more details on compression options, see Data compression.

The default values for some storage options listed above can be changed via the server configuration parameter gp_default_storage_options

<tablespace_name>

A name of the tablespace, which tha table should be stored in

<opclass>

An operator class name. Can be specified optionally to use a non-default hash function for distribution

<partition_type>

A partitioning type. Possible values:

LIST — list of values.
RANGE — numeric or date range.

<partition_description>

A partitioning specification including: partitions, subpartitions, default partition. For more details, see Partitioning

There are some recommendations on how to choose data types for columns in the most efficient way:

Choose data types according to the stored data. For example, use numeric data types for numbers, character data types for strings, date or timestamp data types for dates, and so on.
The data types you select should use the least possible space but can still accommodate your data. For example, use INT instead of BIGINT, TEXT instead of CHAR(<n>), etc.
When using the smallest numeric data types, try to forecast their possible expansion in the future. Pay attention that changing from a smaller datatype to a larger one after loading large amounts of data can be costly. Therefore, if your current data values fit, for example, in the SMALLINT type properly but it is likely that the values will expand later, INT is the better long-term choice.
If possible, use specialized data types: INET, CIDR, JSON, JSONB, MACADDR, etc.
Use the same data types in the columns for which JOIN operations will be performed. When data types are different, the database should convert one of them in order to compare data values correctly, which adds unnecessary overhead.
Though Greenplum has a rich set of native data types, you can create your own ones. Usage of ENUM can give a huge performance boost.

Examples

Create a heap table with REPLICATED distribution type and column constraints

CREATE TABLE book_type(id SERIAL PRIMARY KEY, name TEXT UNIQUE NOT NULL)
DISTRIBUTED REPLICATED;

Create a heap table with REPLICATED distribution type and table constraints

CREATE TABLE book_cover(id SERIAL, name TEXT NOT NULL, PRIMARY KEY(id), UNIQUE(name))
DISTRIBUTED REPLICATED;

Create an append-optimized column-oriented table (distributed by the column value)

CREATE TABLE author(id SERIAL, name TEXT NOT NULL)
WITH (appendoptimized=true, orientation=column)
DISTRIBUTED BY(id);

Create an append-optimized row-oriented table with ZLIB compression (at the table level)

CREATE TABLE book(id SERIAL, title TEXT, author_id INT NOT NULL, public_year SMALLINT NULL, type_id INT NOT NULL, cover_id INT NOT NULL)
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id);

Create an append-optimized column-oriented table with ZSTD compression (at the column level)

CREATE TABLE book_free_fragment(id SERIAL, book_id INT, shop_id INT, fragment TEXT ENCODING(compresstype=ZSTD, compresslevel=5))
WITH (appendoptimized=true, orientation=column)
DISTRIBUTED BY(id);

Create a table via the LIKE statement

Create the book_type table if it does not exist yet:

CREATE TABLE IF NOT EXISTS book_type(id SERIAL PRIMARY KEY, name TEXT UNIQUE NOT NULL)
DISTRIBUTED REPLICATED;

The possible result is:

NOTICE:  relation "book_type" already exists, skipping
CREATE TABLE

Check the structure of the created table via the psql command \d+:

\d+ book_type

The result is:

                                              Table "public.book_type"
 Column |  Type   |                       Modifiers                        | Storage  | Stats target | Description
--------+---------+--------------------------------------------------------+----------+--------------+-------------
 id     | integer | not null default nextval('book_type_id_seq'::regclass) | plain    |              |
 name   | text    | not null                                               | extended |              |
Indexes:
    "book_type_pkey" PRIMARY KEY, btree (id)
    "book_type_name_key" UNIQUE CONSTRAINT, btree (name)
Distributed Replicated

Add another table book_type_arch with the same structure and constraints via the LIKE statement:
```
CREATE TABLE book_type_arch(LIKE book_type INCLUDING ALL)
DISTRIBUTED REPLICATED;
```
The result is:
```
CREATE TABLE
```

Check the structure of the created table via the psql command \d+:

\d+ book_type_arch

You can see that it is similar to the book_type table:

                                            Table "public.book_type_arch"
 Column |  Type   |                       Modifiers                        | Storage  | Stats target | Description
--------+---------+--------------------------------------------------------+----------+--------------+-------------
 id     | integer | not null default nextval('book_type_id_seq'::regclass) | plain    |              |
 name   | text    | not null                                               | extended |              |
Indexes:
    "book_type_arch_pkey" PRIMARY KEY, btree (id)
    "book_type_arch_name_key" UNIQUE CONSTRAINT, btree (name)
Distributed Replicated

CAUTION

Pay attention that both tables now use the same sequence book_type_id_seq for the first column id. It can lead to unexpected consequences.

In order to check that two tables are not related anymore, alter the first table book_type:
```
ALTER TABLE book_type
DROP COLUMN name;
```
The result is:
```
ALTER TABLE
```

Check the structure of the book_type_arch table again. It has not been changed.

adb=# \d+ book_type_arch
                                           Table "public.book_type_arch"
 Column |  Type   |                       Modifiers                        | Storage  | Stats target | Description
--------+---------+--------------------------------------------------------+----------+--------------+-------------
 id     | integer | not null default nextval('book_type_id_seq'::regclass) | plain    |              |
 name   | text    | not null                                               | extended |              |
Indexes:
    "book_type_arch_pkey" PRIMARY KEY, btree (id)
    "book_type_arch_name_key" UNIQUE CONSTRAINT, btree (name)
Distributed Replicated

Create a table via the INHERITS statement

Create the book table if it does not exist yet:

CREATE TABLE IF NOT EXISTS book(id SERIAL, title TEXT, author_id INT NOT NULL, public_year SMALLINT NULL, type_id INT NOT NULL, cover_id INT NOT NULL)
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id);

The possible result is:

NOTICE:  relation "book" already exists, skipping
CREATE TABLE

Check the structure of the created table via the psql command \d+:

\d+ book

The result is:

                                          Append-Only Table "public.book"
   Column    |   Type   |                     Modifiers                     | Storage  | Stats target | Description
-------------+----------+---------------------------------------------------+----------+--------------+-------------
 id          | integer  | not null default nextval('book_id_seq'::regclass) | plain    |              |
 title       | text     |                                                   | extended |              |
 author_id   | integer  | not null                                          | plain    |              |
 public_year | smallint |                                                   | plain    |              |
 type_id     | integer  | not null                                          | plain    |              |
 cover_id    | integer  | not null                                          | plain    |              |
Compression Type: zlib
Compression Level: 5
Block Size: 32768
Checksum: t
Distributed by: (id)
Options: appendonly=true, orientation=row, compresstype=zlib, compresslevel=5

Add another table book_arch with the same structure and constraints via the INHERITS statement:

CREATE TABLE book_arch()
INHERITS(book)
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id);

The result is:

CREATE TABLE

Check the structure of the created table via the psql command \d+:

\d+ book_arch

You can see that it is similar to the book table:

                                        Append-Only Table "public.book_arch"
   Column    |   Type   |                     Modifiers                     | Storage  | Stats target | Description
-------------+----------+---------------------------------------------------+----------+--------------+-------------
 id          | integer  | not null default nextval('book_id_seq'::regclass) | plain    |              |
 title       | text     |                                                   | extended |              |
 author_id   | integer  | not null                                          | plain    |              |
 public_year | smallint |                                                   | plain    |              |
 type_id     | integer  | not null                                          | plain    |              |
 cover_id    | integer  | not null                                          | plain    |              |
Compression Type: zlib
Compression Level: 5
Block Size: 32768
Checksum: t
Inherits: book
Distributed by: (id)
Options: appendonly=true, orientation=row, compresstype=zlib, compresslevel=5

In order to check that two tables stay related after creation is complete, alter the first table book:
```
ALTER TABLE book
DROP COLUMN type_id;
```
The result is:
```
ALTER TABLE
```

Check the structure of the book_arch table again. The type_id column does not exist anymore, as well as in the parent table:

adb=# \d+ book_arch
                                        Append-Only Table "public.book_arch"
   Column    |   Type   |                     Modifiers                     | Storage  | Stats target | Description
-------------+----------+---------------------------------------------------+----------+--------------+-------------
 id          | integer  | not null default nextval('book_id_seq'::regclass) | plain    |              |
 title       | text     |                                                   | extended |              |
 author_id   | integer  | not null                                          | plain    |              |
 public_year | smallint |                                                   | plain    |              |
 cover_id    | integer  | not null                                          | plain    |              |
Compression Type: zlib
Compression Level: 5
Block Size: 32768
Checksum: t
Inherits: book
Distributed by: (id)
Options: appendonly=true, orientation=row, compresstype=zlib, compresslevel=5

Create a temporary table

Start a new transaction:
```
START TRANSACTION;
```
The result is:
```
START TRANSACTION
```
Create a temporary table. In the ON COMMIT clause define that the table should be dropped after the current transaction is committed:
```
CREATE TEMP TABLE book_temp(id INT, name TEXT)
ON COMMIT DROP
DISTRIBUTED BY(id);
```
The result is:
```
CREATE TABLE
```

Check the table existence via the psql command \dt+:

\dt+

The result is:

                                         List of relations
    Schema    |        Name        | Type  |  Owner  |       Storage        |  Size   | Description
--------------+--------------------+-------+---------+----------------------+---------+-------------
 pg_temp_1229 | book_temp          | table | gpadmin | heap                 | 544 kB  |
 public       | author             | table | gpadmin | append only columnar | 1088 kB |
 public       | book               | table | gpadmin | append only          | 1088 kB |
 public       | book_arch          | table | gpadmin | append only          | 1088 kB |
 public       | book_cover         | table | gpadmin | heap                 | 544 kB  |
 public       | book_free_fragment | table | gpadmin | append only columnar | 1088 kB |
 public       | book_type          | table | gpadmin | heap                 | 544 kB  |
 public       | book_type_arch     | table | gpadmin | heap                 | 544 kB  |
 public       | spatial_ref_sys    | table | gpadmin | heap                 | 7424 kB |
(9 rows)

Commit the current transaction:
```
COMMIT;
```
The result is:
```
COMMIT
```

Check the temporary table existence again. You can see that the book_temp table is not available anymore:

adb=# \dt+
                                      List of relations
 Schema |        Name        | Type  |  Owner  |       Storage        |  Size   | Description
--------+--------------------+-------+---------+----------------------+---------+-------------
 public | author             | table | gpadmin | append only columnar | 1088 kB |
 public | book               | table | gpadmin | append only          | 1088 kB |
 public | book_arch          | table | gpadmin | append only          | 1088 kB |
 public | book_cover         | table | gpadmin | heap                 | 544 kB  |
 public | book_free_fragment | table | gpadmin | append only columnar | 1088 kB |
 public | book_type          | table | gpadmin | heap                 | 544 kB  |
 public | book_type_arch     | table | gpadmin | heap                 | 544 kB  |
 public | spatial_ref_sys    | table | gpadmin | heap                 | 7424 kB |
(8 rows)

Create a table with the CHECK constraint

Create a table with the following restriction: there should be no rows with the value less than 1 in the book_count column:

CREATE TABLE book_order(id INT, book_id INT, client_id INT, book_count SMALLINT, order_date TIMESTAMP, CHECK(book_count >= 1))
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id);

The following query uses the same CHECK constraint, but at the column level:

CREATE TABLE book_order(id INT, book_id INT, client_id INT, book_count SMALLINT CHECK(book_count >= 1), order_date TIMESTAMP)
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id);

Regardless of the selected syntax form, you can check the created constraint. Try to insert rows into the book_order table with 0 in the book_count column:

INSERT INTO book_order VALUES(1, 1, 1, 0, now());

The query returns the error:

ERROR:  new row for relation "book_order" violates check constraint "book_order_book_count_check"  (seg4 10.92.16.100:10004 pid=4039)
DETAIL:  Failing row contains (1, 1, 1, 0, 2022-11-14 19:01:19.179355).

Change the INSERT query as described below:
```
INSERT INTO book_order VALUES(1, 1, 1, 1, now());
```
Now the result is successful:
```
INSERT 0 1
```

Create a partitioned table

CREATE TABLE client (id INT, name TEXT, gender CHAR(1))
DISTRIBUTED BY (id)
PARTITION BY LIST (gender)
( PARTITION girls VALUES ('F'),
  PARTITION boys VALUES ('M'));

The result is:

NOTICE:  CREATE TABLE will create partition "client_1_prt_girls" for table "client"
NOTICE:  CREATE TABLE will create partition "client_1_prt_boys" for table "client"
CREATE TABLE

NOTE

For more examples, see Partitioning.

View the list of tables

To view the list of available tables in the current database, use one of the following ways:

Run the SQL query against the information_schema.tables catalog table. Use table_type = 'BASE TABLE' in the WHERE predicate if you want to list only tables. Otherwise, the result will also contain views (having table_type = 'VIEW').

SELECT table_catalog, table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

This command returns the following columns:

table_catalog — database name;
table_schema — schema name;
table_name — table name;

table_type — table type (BASE TABLE for tables, VIEW for views).

Query result

  table_catalog |    table_schema    |        table_name        | table_type
---------------+--------------------+--------------------------+------------
 adb           | arenadata_toolkit  | daily_operation          | BASE TABLE
 adb           | arenadata_toolkit  | db_files_current         | BASE TABLE
 adb           | arenadata_toolkit  | db_files_history         | BASE TABLE
 adb           | arenadata_toolkit  | operation_exclude        | BASE TABLE
 adb           | diskquota          | quota_config             | BASE TABLE
 adb           | diskquota          | state                    | BASE TABLE
 adb           | diskquota          | table_size               | BASE TABLE
 adb           | gp_toolkit         | gp_disk_free             | BASE TABLE
 adb           | gp_toolkit         | __gp_log_master_ext      | BASE TABLE
 adb           | gp_toolkit         | __gp_log_segment_ext     | BASE TABLE
 adb           | information_schema | sql_features             | BASE TABLE
 adb           | information_schema | sql_implementation_info  | BASE TABLE
 adb           | information_schema | sql_languages            | BASE TABLE
 adb           | information_schema | sql_packages             | BASE TABLE
 adb           | information_schema | sql_parts                | BASE TABLE
 adb           | information_schema | sql_sizing               | BASE TABLE
 adb           | information_schema | sql_sizing_profiles      | BASE TABLE
 adb           | kadb               | offsets                  | BASE TABLE
 adb           | madlib             | migrationhistory         | BASE TABLE
 adb           | pg_catalog         | gp_configuration_history | BASE TABLE
 adb           | pg_catalog         | gp_distribution_policy   | BASE TABLE
 adb           | pg_catalog         | gp_fastsequence          | BASE TABLE
 adb           | pg_catalog         | gp_id                    | BASE TABLE
 adb           | pg_catalog         | gp_segment_configuration | BASE TABLE
 adb           | pg_catalog         | gp_version_at_initdb     | BASE TABLE
 adb           | pg_catalog         | pg_aggregate             | BASE TABLE
 adb           | pg_catalog         | pg_am                    | BASE TABLE
 adb           | pg_catalog         | pg_amop                  | BASE TABLE
 adb           | pg_catalog         | pg_amproc                | BASE TABLE
 adb           | pg_catalog         | pg_appendonly            | BASE TABLE
 adb           | pg_catalog         | pg_attrdef               | BASE TABLE
 adb           | pg_catalog         | pg_attribute             | BASE TABLE
 adb           | pg_catalog         | pg_attribute_encoding    | BASE TABLE
 adb           | pg_catalog         | pg_authid                | BASE TABLE
 adb           | pg_catalog         | pg_auth_members          | BASE TABLE
 adb           | pg_catalog         | pg_auth_time_constraint  | BASE TABLE
 adb           | pg_catalog         | pg_cast                  | BASE TABLE
 adb           | pg_catalog         | pg_class                 | BASE TABLE
 adb           | pg_catalog         | pg_collation             | BASE TABLE
 adb           | pg_catalog         | pg_compression           | BASE TABLE
 adb           | pg_catalog         | pg_constraint            | BASE TABLE
 adb           | pg_catalog         | pg_conversion            | BASE TABLE
 adb           | pg_catalog         | pg_database              | BASE TABLE
 adb           | pg_catalog         | pg_db_role_setting       | BASE TABLE
 adb           | pg_catalog         | pg_default_acl           | BASE TABLE
 adb           | pg_catalog         | pg_depend                | BASE TABLE
 adb           | pg_catalog         | pg_description           | BASE TABLE
 adb           | pg_catalog         | pg_enum                  | BASE TABLE
 adb           | pg_catalog         | pg_event_trigger         | BASE TABLE
 adb           | pg_catalog         | pg_extension             | BASE TABLE
 adb           | pg_catalog         | pg_extprotocol           | BASE TABLE
 adb           | pg_catalog         | pg_exttable              | BASE TABLE
 adb           | pg_catalog         | pg_foreign_data_wrapper  | BASE TABLE
 adb           | pg_catalog         | pg_foreign_server        | BASE TABLE
 adb           | pg_catalog         | pg_foreign_table         | BASE TABLE
 adb           | pg_catalog         | pg_index                 | BASE TABLE
 adb           | pg_catalog         | pg_inherits              | BASE TABLE
 adb           | pg_catalog         | pg_language              | BASE TABLE
 adb           | pg_catalog         | pg_largeobject           | BASE TABLE
 adb           | pg_catalog         | pg_largeobject_metadata  | BASE TABLE
 adb           | pg_catalog         | pg_namespace             | BASE TABLE
 adb           | pg_catalog         | pg_opclass               | BASE TABLE
 adb           | pg_catalog         | pg_operator              | BASE TABLE
 adb           | pg_catalog         | pg_opfamily              | BASE TABLE
 adb           | pg_catalog         | pg_partition             | BASE TABLE
 adb           | pg_catalog         | pg_partition_encoding    | BASE TABLE
 adb           | pg_catalog         | pg_partition_rule        | BASE TABLE
 adb           | pg_catalog         | pg_pltemplate            | BASE TABLE
 adb           | pg_catalog         | pg_proc                  | BASE TABLE
 adb           | pg_catalog         | pg_proc_callback         | BASE TABLE
 adb           | pg_catalog         | pg_range                 | BASE TABLE
 adb           | pg_catalog         | pg_resgroup              | BASE TABLE
 adb           | pg_catalog         | pg_resgroupcapability    | BASE TABLE
 adb           | pg_catalog         | pg_resourcetype          | BASE TABLE
 adb           | pg_catalog         | pg_resqueue              | BASE TABLE
 adb           | pg_catalog         | pg_resqueuecapability    | BASE TABLE
 adb           | pg_catalog         | pg_rewrite               | BASE TABLE
 adb           | pg_catalog         | pg_seclabel              | BASE TABLE
 adb           | pg_catalog         | pg_shdepend              | BASE TABLE
 adb           | pg_catalog         | pg_shdescription         | BASE TABLE
 adb           | pg_catalog         | pg_shseclabel            | BASE TABLE
 adb           | pg_catalog         | pg_statistic             | BASE TABLE
 adb           | pg_catalog         | pg_stat_last_operation   | BASE TABLE
 adb           | pg_catalog         | pg_stat_last_shoperation | BASE TABLE
 adb           | pg_catalog         | pg_tablespace            | BASE TABLE
 adb           | pg_catalog         | pg_trigger               | BASE TABLE
 adb           | pg_catalog         | pg_ts_config             | BASE TABLE
 adb           | pg_catalog         | pg_ts_config_map         | BASE TABLE
 adb           | pg_catalog         | pg_ts_dict               | BASE TABLE
 adb           | pg_catalog         | pg_ts_parser             | BASE TABLE
 adb           | pg_catalog         | pg_ts_template           | BASE TABLE
 adb           | pg_catalog         | pg_type                  | BASE TABLE
 adb           | pg_catalog         | pg_type_encoding         | BASE TABLE
 adb           | pg_catalog         | pg_user_mapping          | BASE TABLE
 adb           | public             | author                   | BASE TABLE
 adb           | public             | book                     | BASE TABLE
 adb           | public             | book_arch                | BASE TABLE
 adb           | public             | book_cover               | BASE TABLE
 adb           | public             | book_free_fragment       | BASE TABLE
 adb           | public             | book_order               | BASE TABLE
 adb           | public             | book_type                | BASE TABLE
 adb           | public             | book_type_arch           | BASE TABLE
 adb           | public             | client                   | BASE TABLE
 adb           | public             | client_1_prt_boys        | BASE TABLE
 adb           | public             | client_1_prt_girls       | BASE TABLE
 adb           | public             | spatial_ref_sys          | BASE TABLE
(106 rows)

Run the SQL query against the pg_catalog.pg_tables catalog view.

SELECT * FROM pg_catalog.pg_tables
ORDER BY schemaname, tablename;

This command returns the following columns:

schemaname — schema name;
tablename — table name;
tableowner — table owner;
tablespace — tablespace name;
hasindexes — boolean value that defines whether the table has indexes;
hasrules — boolean value that defines whether the table has rules;

hastriggers — boolean value that defines whether the table has triggers.

Query result

     schemaname     |        tablename         | tableowner | tablespace | hasindexes | hasrules | hastriggers
--------------------+--------------------------+------------+------------+------------+----------+-------------
 arenadata_toolkit  | daily_operation          | gpadmin    |            | f          | f        | f
 arenadata_toolkit  | db_files_current         | gpadmin    |            | f          | f        | f
 arenadata_toolkit  | db_files_history         | gpadmin    |            | f          | f        | f
 arenadata_toolkit  | operation_exclude        | gpadmin    |            | f          | f        | f
 diskquota          | quota_config             | gpadmin    |            | t          | f        | f
 diskquota          | state                    | gpadmin    |            | t          | f        | f
 diskquota          | table_size               | gpadmin    |            | t          | f        | f
 gp_toolkit         | __gp_log_master_ext      | gpadmin    |            | f          | f        | f
 gp_toolkit         | __gp_log_segment_ext     | gpadmin    |            | f          | f        | f
 gp_toolkit         | gp_disk_free             | gpadmin    |            | f          | f        | f
 information_schema | sql_features             | gpadmin    |            | f          | f        | f
 information_schema | sql_implementation_info  | gpadmin    |            | f          | f        | f
 information_schema | sql_languages            | gpadmin    |            | f          | f        | f
 information_schema | sql_packages             | gpadmin    |            | f          | f        | f
 information_schema | sql_parts                | gpadmin    |            | f          | f        | f
 information_schema | sql_sizing               | gpadmin    |            | f          | f        | f
 information_schema | sql_sizing_profiles      | gpadmin    |            | f          | f        | f
 kadb               | offsets                  | gpadmin    |            | t          | f        | f
 madlib             | migrationhistory         | gpadmin    |            | f          | f        | f
 pg_catalog         | gp_configuration_history | gpadmin    | pg_global  | f          | f        | f
 pg_catalog         | gp_distribution_policy   | gpadmin    |            | t          | f        | f
 pg_catalog         | gp_fastsequence          | gpadmin    |            | t          | f        | f
 pg_catalog         | gp_id                    | gpadmin    | pg_global  | f          | f        | f
 pg_catalog         | gp_segment_configuration | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | gp_version_at_initdb     | gpadmin    | pg_global  | f          | f        | f
 pg_catalog         | pg_aggregate             | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_am                    | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_amop                  | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_amproc                | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_appendonly            | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_attrdef               | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_attribute             | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_attribute_encoding    | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_auth_members          | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | pg_auth_time_constraint  | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | pg_authid                | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | pg_cast                  | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_class                 | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_collation             | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_compression           | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_constraint            | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_conversion            | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_database              | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | pg_db_role_setting       | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | pg_default_acl           | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_depend                | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_description           | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_enum                  | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_event_trigger         | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_extension             | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_extprotocol           | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_exttable              | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_foreign_data_wrapper  | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_foreign_server        | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_foreign_table         | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_index                 | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_inherits              | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_language              | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_largeobject           | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_largeobject_metadata  | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_namespace             | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_opclass               | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_operator              | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_opfamily              | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_partition             | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_partition_encoding    | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_partition_rule        | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_pltemplate            | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | pg_proc                  | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_proc_callback         | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_range                 | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_resgroup              | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | pg_resgroupcapability    | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | pg_resourcetype          | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | pg_resqueue              | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | pg_resqueuecapability    | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | pg_rewrite               | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_seclabel              | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_shdepend              | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | pg_shdescription         | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | pg_shseclabel            | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | pg_stat_last_operation   | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_stat_last_shoperation | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | pg_statistic             | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_tablespace            | gpadmin    | pg_global  | t          | f        | f
 pg_catalog         | pg_trigger               | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_ts_config             | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_ts_config_map         | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_ts_dict               | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_ts_parser             | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_ts_template           | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_type                  | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_type_encoding         | gpadmin    |            | t          | f        | f
 pg_catalog         | pg_user_mapping          | gpadmin    |            | t          | f        | f
 public             | author                   | gpadmin    |            | f          | f        | f
 public             | book                     | gpadmin    |            | f          | f        | f
 public             | book_arch                | gpadmin    |            | f          | f        | f
 public             | book_cover               | gpadmin    |            | t          | f        | f
 public             | book_free_fragment       | gpadmin    |            | f          | f        | f
 public             | book_order               | gpadmin    |            | f          | f        | f
 public             | book_type                | gpadmin    |            | t          | f        | f
 public             | book_type_arch           | gpadmin    |            | t          | f        | f
 public             | client                   | gpadmin    |            | f          | f        | f
 public             | client_1_prt_boys        | gpadmin    |            | f          | f        | f
 public             | client_1_prt_girls       | gpadmin    |            | f          | f        | f
 public             | spatial_ref_sys          | gpadmin    |            | t          | f        | f
(106 rows)

If you work in the psql client program, you can use meta-commands to show the list of tables:

\dt — this command returns the following information:

Schema — schema name;
Name — table name;
Type — relation type (table for tables, view for views);
Owner — table owner;

Storage — table type.

                          List of relations
 Schema |        Name        | Type  |  Owner  |       Storage
--------+--------------------+-------+---------+----------------------
 public | author             | table | gpadmin | append only columnar
 public | book               | table | gpadmin | append only
 public | book_arch          | table | gpadmin | append only
 public | book_cover         | table | gpadmin | heap
 public | book_free_fragment | table | gpadmin | append only columnar
 public | book_order         | table | gpadmin | append only
 public | book_type          | table | gpadmin | heap
 public | book_type_arch     | table | gpadmin | heap
 public | client             | table | gpadmin | heap
 public | client_1_prt_boys  | table | gpadmin | heap
 public | client_1_prt_girls | table | gpadmin | heap
 public | spatial_ref_sys    | table | gpadmin | heap
(12 rows)

\dt+ — in comparison with \dt, this command returns additional fields:

Size — table size;

Description — table description.

                                      List of relations
 Schema |        Name        | Type  |  Owner  |       Storage        |  Size   | Description
--------+--------------------+-------+---------+----------------------+---------+-------------
 public | author             | table | gpadmin | append only columnar | 1088 kB |
 public | book               | table | gpadmin | append only          | 1088 kB |
 public | book_arch          | table | gpadmin | append only          | 1088 kB |
 public | book_cover         | table | gpadmin | heap                 | 544 kB  |
 public | book_free_fragment | table | gpadmin | append only columnar | 1088 kB |
 public | book_order         | table | gpadmin | append only          | 608 kB  |
 public | book_type          | table | gpadmin | heap                 | 544 kB  |
 public | book_type_arch     | table | gpadmin | heap                 | 544 kB  |
 public | client             | table | gpadmin | heap                 | 544 kB  |
 public | client_1_prt_boys  | table | gpadmin | heap                 | 544 kB  |
 public | client_1_prt_girls | table | gpadmin | heap                 | 544 kB  |
 public | spatial_ref_sys    | table | gpadmin | heap                 | 7424 kB |
(12 rows)

TIP

In order to add system tables to the output of psql commands listed above, use the S postfix after their names: \dtS, \dt+S.
To list tables with the specified schema name, you can use templates with regular expressions. For example, the \dt kadb.* command returns tables with the schema name kadb. By default, \dt and \dt+ commands show tables for the current schema. For more information, see Schema search path.

View the specified table

To view the structure of the specified table, you can use one of the following psql commands:

\d — this command returns the following information:

A list of table columns with their names, data types, and modifiers (i.e. constraints, sequences, etc.).
Table storage options including: compression type, compression level, block size. Specified only for append-optimized tables.
Whether or not the checksum is used.
A number of child tables (created via the INHERITS clause).

Distribution policy.

adb=# \d author
               Append-Only Columnar Table "public.author"
 Column |  Type   |                      Modifiers
--------+---------+-----------------------------------------------------
 id     | integer | not null default nextval('author_id_seq'::regclass)
 name   | text    | not null
Checksum: t
Distributed by: (id)

adb=# \d book
                      Append-Only Table "public.book"
   Column    |   Type   |                     Modifiers
-------------+----------+---------------------------------------------------
 id          | integer  | not null default nextval('book_id_seq'::regclass)
 title       | text     |
 author_id   | integer  | not null
 public_year | smallint |
 cover_id    | integer  | not null
Compression Type: zlib
Compression Level: 5
Block Size: 32768
Checksum: t
Number of child tables: 1 (Use \d+ to list them.)
Distributed by: (id)

\d+ — in comparison with \d, this command returns some additional information:

Column storage options including: compression type, compression level, block size. Specified only for append-optimized column-oriented tables.
Some additional column data including: statistic target, description.
A list of child tables (created via the INHERITS clause).

The options that were used to create the table (in the WITH clause).

adb=# \d+ author
                                                             Append-Only Columnar Table "public.author"
 Column |  Type   |                      Modifiers                      | Storage  | Stats target | Compression Type | Compression Level | Block Size | Description
--------+---------+-----------------------------------------------------+----------+--------------+------------------+-------------------+------------+-------------
 id     | integer | not null default nextval('author_id_seq'::regclass) | plain    |              | none             | 0                 | 32768      |
 name   | text    | not null                                            | extended |              | none             | 0                 | 32768      |
Checksum: t
Distributed by: (id)
Options: appendonly=true, orientation=column

adb=# \d+ book
                                          Append-Only Table "public.book"
   Column    |   Type   |                     Modifiers                     | Storage  | Stats target | Description
-------------+----------+---------------------------------------------------+----------+--------------+-------------
 id          | integer  | not null default nextval('book_id_seq'::regclass) | plain    |              |
 title       | text     |                                                   | extended |              |
 author_id   | integer  | not null                                          | plain    |              |
 public_year | smallint |                                                   | plain    |              |
 cover_id    | integer  | not null                                          | plain    |              |
Compression Type: zlib
Compression Level: 5
Block Size: 32768
Checksum: t
Child tables: book_arch
Distributed by: (id)
Options: appendonly=true, orientation=row, compresstype=zlib, compresslevel=5

System catalog tables

ADB (as well as Greenplum) supports several system catalog tables within the pg_catalog schema. Some of them can be useful for monitoring of your cluster and other DBA activity. Examples of such tables are listed below.

NOTE

For more information on all system catalog tables, see System Tables in Greenplum documentation.

gp_configuration_history

Contains information about system changes related to fault detection and recovery operations.

Basic columns
Column name	Purpose
time	A timestamp for the event occurred
dbid	A unique identifier of a segment (or master) instance. Refers to `gp_segment_configuration.dbid`
desc	The event description

gp_segment_configuration

Contains information about mirroring and segment instance configuration.

Basic columns
Column name	Purpose
dbid	A unique identifier of a segment (or master) instance
content	A content identifier for a segment (or master) instance. A primary segment instance and its corresponding mirror always have the same `content` value. Possible values: For a segment the value is from `0` to `N-1`, where `N` is the number of primary segments in the system. For a master the value is always `-1`.
role	The role that a segment is currently running as. Possible values are `p` (primary) or `m` (mirror)
preferred_role	The role that a segment was originally assigned at initialization time. Possible values are `p` (primary) or `m` (mirror)
mode	The synchronization status of a segment instance with its mirror copy. Possible values are `s` (Synchronized) or `n` (Not In Sync). This column always shows `n` for Master and `s` for Standby Master. Use the gp_stat_replication table to determine the synchronization state between Master and Standby Master
status	The fault status of a segment instance. Possible values are `u` (up) or `d` (down)
port	The TCP port the database server listener process is using
hostname	A hostname of a segment host
address	The hostname used to access a particular segment instance on a segment host. This value may be the same as `hostname` on systems that do not have per-interface hostnames configured
datadir	A segment instance data directory

pg_database

Stores information about databases.

Basic columns
Column name	Purpose
datname	A database name
datdba	A database owner, usually the user who created it. Refers to pg_authid.oid
dattablespace	A database tablespace. This tablespace is the default one for all system catalogs of that database and for all tables and indexes created within the database (unless the `TABLESPACE` clause is given for them when creating)

pg_class

Stores information about all database relations. In addition to tables, it includes indexes, sequences, views, composite types, and TOAST tables (which store large "out of line" attributes of the source table in the secondary table). Not all columns are meaningful for all relation types.

Basic columns
Column name	Purpose
relname	A relation name
relnamespace	OID of the schema that contains this relation. Refers to `pg_namespace.oid`
relowner	A relation owner, usually the user who created it. Refers to pg_authid.oid
relam	An access method: B-tree, Bitmap, hash, etc. It is defined only for indexes. Refers to pg_am.oid
reltablespace	The tablespace in which the relation is stored. If zero, the database’s default tablespace is used. Refers to `pg_tablespace.oid`
relpages	Size of the on-disk relation representation in pages (of 32 KB each). This is only an estimate used by the planner. It is updated by `VACUUM`, `ANALYZE`, and a few DDL commands
reltuples	A number of rows in the relation. This is only an estimate used by the planner. It is updated by `VACUUM`, `ANALYZE`, and a few DDL commands
reltoastrelid	OID of the TOAST table associated with the current relation, `0` if none. Refers to `pg_class.oid`
relpersistence	A relation persistence type: `p` — heap or append-optimized table; `u` — unlogged temporary table; `t` — temporary table.
relkind	A relation type: `r` — heap or append-optimized table; `i` — index; `S` — sequence; `t` — TOAST table; `v` — view; `c` — composite type; `f` — foreign table; `u` — uncatalogued temporary heap table; `o` — internal append-optimized segment files and EOFs; `b` — append-only block directory; `M` — append-only visibility map.
relstorage	A relation storage mode: `a` — append-optimized; `c` — column-oriented; `h` — heap; `v` — virtual; `x` — external table.

pg_namespace

Stores information about schemas (namespaces).

Basic columns
Column name	Purpose
nspname	A schema name
nspowner	A schema owner, usually the user who created it. Refers to pg_authid.oid

pg_tablespace

Stores information about tablespaces.

Basic columns
Column name	Purpose
spcname	A tablespace name
spcowner	A tablespace owner, usually the user who created it. Refers to pg_authid.oid

Useful queries

Examples of queries to the system catalog tables are listed below.

List all databases sorted by their size

SELECT oid, datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

The result is:

  oid  |  datname  | pg_size_pretty
-------+-----------+----------------
 16384 | adb       | 430 MB
 19885 | diskquota | 283 MB
 12812 | postgres  | 280 MB
     1 | template1 | 280 MB
 12809 | template0 | 276 MB
(5 rows)

List all schemas

SELECT oid, nspname FROM pg_catalog.pg_namespace ORDER BY nspname;

The result is:

  oid  |      nspname
-------+--------------------
 16385 | arenadata_toolkit
 19895 | diskquota
 12350 | gp_toolkit
 12090 | information_schema
 19968 | kadb
 16472 | madlib
  6104 | pg_aoseg
  7012 | pg_bitmapindex
    11 | pg_catalog
    99 | pg_toast
  2200 | public
(11 rows)

List all tablespaces

SELECT oid, spcname FROM pg_catalog.pg_tablespace ORDER BY spcname;

The result is:

 oid  |  spcname
------+------------
 1663 | pg_default
 1664 | pg_global
(2 rows)

List external tables

SELECT oid, relname FROM pg_catalog.pg_class WHERE relstorage = 'x';

The result is:

  oid  |       relname
-------+----------------------
 12374 | __gp_log_segment_ext
 12376 | __gp_log_master_ext
 12420 | gp_disk_free
(3 rows)

Find a number of database relations grouped by their type

SELECT relkind, COUNT(*) FROM pg_class GROUP BY(relkind) ORDER BY COUNT(*);

The result is:

 relkind | count
---------+-------
 S       |     6
 o       |     8
 M       |     8
 t       |    37
 c       |    84
 r       |   106
 v       |   173
 i       |   193
(8 rows)

List all TOAST tables with the main tables they are associated with

SELECT t1.oid AS "TOAST OID", t1.relname AS "TOAST name", t2.oid AS "Main OID", t2.relname AS "Main name"
FROM pg_catalog.pg_class t1
LEFT JOIN pg_catalog.pg_class t2 ON t1.oid = t2.reltoastrelid
WHERE t1.relkind = 't'
ORDER BY t2.relname;

The result is:

 TOAST OID |   TOAST name   | Main OID |        Main name
-----------+----------------+----------+--------------------------
     53318 | pg_toast_53315 |    53315 | author
     53331 | pg_toast_53328 |    53328 | book
     53366 | pg_toast_53363 |    53363 | book_arch
     53306 | pg_toast_53302 |    53302 | book_cover
     53344 | pg_toast_53341 |    53341 | book_free_fragment
     53293 | pg_toast_53289 |    53289 | book_type
     53356 | pg_toast_53352 |    53352 | book_type_arch
     53409 | pg_toast_53406 |    53406 | client
     53423 | pg_toast_53420 |    53420 | client_1_prt_boys
     53415 | pg_toast_53412 |    53412 | client_1_prt_girls
     16398 | pg_toast_16396 |    16396 | daily_operation
     45090 | pg_toast_45087 |    45087 | db_files_current
     16388 | pg_toast_16386 |    16386 | db_files_history
      6092 | pg_toast_5036  |     5036 | gp_segment_configuration
     16408 | pg_toast_16406 |    16406 | operation_exclude
      2830 | pg_toast_2604  |     2604 | pg_attrdef
      6233 | pg_toast_6231  |     6231 | pg_attribute_encoding
      2832 | pg_toast_2606  |     2606 | pg_constraint
      2966 | pg_toast_2964  |     2964 | pg_db_role_setting
      2834 | pg_toast_2609  |     2609 | pg_description
      5510 | pg_toast_3079  |     3079 | pg_extension
      9905 | pg_toast_9903  |     9903 | pg_partition_encoding
      2836 | pg_toast_1255  |     1255 | pg_proc
      2838 | pg_toast_2618  |     2618 | pg_rewrite
      3598 | pg_toast_3596  |     3596 | pg_seclabel
      2846 | pg_toast_2396  |     2396 | pg_shdescription
      2840 | pg_toast_2619  |     2619 | pg_statistic
      2336 | pg_toast_2620  |     2620 | pg_trigger
      6222 | pg_toast_6220  |     6220 | pg_type_encoding
     18759 | pg_toast_18755 |    18755 | spatial_ref_sys
     12202 | pg_toast_12200 |    12200 | sql_features
     12207 | pg_toast_12205 |    12205 | sql_implementation_info
     12212 | pg_toast_12210 |    12210 | sql_languages
     12217 | pg_toast_12215 |    12215 | sql_packages
     12222 | pg_toast_12220 |    12220 | sql_parts
     12227 | pg_toast_12225 |    12225 | sql_sizing
     12232 | pg_toast_12230 |    12230 | sql_sizing_profiles
(37 rows)

Get information about a relation by its name

SELECT rel.oid,
       rel.relname,
       schema.nspname,
       ts.spcname,
       rel.relstorage,
       rel.relpersistence,
       rel.relkind,
       rel.relpages,
       rel.reltuples
FROM pg_catalog.pg_class rel
LEFT JOIN pg_catalog.pg_namespace schema ON schema.oid = rel.relnamespace
LEFT JOIN pg_catalog.pg_tablespace ts ON ts.oid = rel.reltablespace
WHERE rel.relname = 'pg_class';

Where 'pg_class' can be replaced by any other relation name.

The result is:

 oid  | relname  |  nspname   | spcname | relstorage | relpersistence | relkind | relpages | reltuples
------+----------+------------+---------+------------+----------------+---------+----------+-----------
 1259 | pg_class | pg_catalog |         | h          | p              | r       |        7 |       615
(1 row)

Get information on all segments in the current ADB cluster

SELECT * FROM gp_segment_configuration ORDER BY content, role;

The result is:

 dbid | content | role | preferred_role | mode | status | port  | hostname | address  |        datadir
------+---------+------+----------------+------+--------+-------+----------+----------+------------------------
   34 |      -1 | m    | m              | s    | u      |  5432 | bds-smdw | bds-smdw | /data1/master/gpseg-1
    1 |      -1 | p    | p              | n    | u      |  5432 | bds-mdw  | bds-mdw  | /data1/master/gpseg-1
   18 |       0 | m    | m              | s    | u      | 10500 | bds-sdw2 | bds-sdw2 | /data1/mirror/gpseg0
    2 |       0 | p    | p              | s    | u      | 10000 | bds-sdw1 | bds-sdw1 | /data1/primary/gpseg0
   19 |       1 | m    | m              | s    | u      | 10501 | bds-sdw2 | bds-sdw2 | /data1/mirror/gpseg1
    3 |       1 | p    | p              | s    | u      | 10001 | bds-sdw1 | bds-sdw1 | /data1/primary/gpseg1
   20 |       2 | m    | m              | s    | u      | 10502 | bds-sdw2 | bds-sdw2 | /data1/mirror/gpseg2
    4 |       2 | p    | p              | s    | u      | 10002 | bds-sdw1 | bds-sdw1 | /data1/primary/gpseg2
   21 |       3 | m    | m              | s    | u      | 10503 | bds-sdw2 | bds-sdw2 | /data1/mirror/gpseg3
    5 |       3 | p    | p              | s    | u      | 10003 | bds-sdw1 | bds-sdw1 | /data1/primary/gpseg3
   22 |       4 | m    | m              | s    | u      | 10504 | bds-sdw2 | bds-sdw2 | /data1/mirror/gpseg4
    6 |       4 | p    | p              | s    | u      | 10004 | bds-sdw1 | bds-sdw1 | /data1/primary/gpseg4
   23 |       5 | m    | m              | s    | u      | 10505 | bds-sdw2 | bds-sdw2 | /data1/mirror/gpseg5
    7 |       5 | p    | p              | s    | u      | 10005 | bds-sdw1 | bds-sdw1 | /data1/primary/gpseg5
   24 |       6 | m    | m              | s    | u      | 10506 | bds-sdw2 | bds-sdw2 | /data1/mirror/gpseg6
    8 |       6 | p    | p              | s    | u      | 10006 | bds-sdw1 | bds-sdw1 | /data1/primary/gpseg6
   25 |       7 | m    | m              | s    | u      | 10507 | bds-sdw2 | bds-sdw2 | /data1/mirror/gpseg7
    9 |       7 | p    | p              | s    | u      | 10007 | bds-sdw1 | bds-sdw1 | /data1/primary/gpseg7
   26 |       8 | m    | m              | s    | u      | 10500 | bds-sdw1 | bds-sdw1 | /data1/mirror/gpseg8
   10 |       8 | p    | p              | s    | u      | 10000 | bds-sdw2 | bds-sdw2 | /data1/primary/gpseg8
   27 |       9 | m    | m              | s    | u      | 10501 | bds-sdw1 | bds-sdw1 | /data1/mirror/gpseg9
   11 |       9 | p    | p              | s    | u      | 10001 | bds-sdw2 | bds-sdw2 | /data1/primary/gpseg9
   28 |      10 | m    | m              | s    | u      | 10502 | bds-sdw1 | bds-sdw1 | /data1/mirror/gpseg10
   12 |      10 | p    | p              | s    | u      | 10002 | bds-sdw2 | bds-sdw2 | /data1/primary/gpseg10
   29 |      11 | m    | m              | s    | u      | 10503 | bds-sdw1 | bds-sdw1 | /data1/mirror/gpseg11
   13 |      11 | p    | p              | s    | u      | 10003 | bds-sdw2 | bds-sdw2 | /data1/primary/gpseg11
   30 |      12 | m    | m              | s    | u      | 10504 | bds-sdw1 | bds-sdw1 | /data1/mirror/gpseg12
   14 |      12 | p    | p              | s    | u      | 10004 | bds-sdw2 | bds-sdw2 | /data1/primary/gpseg12
   31 |      13 | m    | m              | s    | u      | 10505 | bds-sdw1 | bds-sdw1 | /data1/mirror/gpseg13
   15 |      13 | p    | p              | s    | u      | 10005 | bds-sdw2 | bds-sdw2 | /data1/primary/gpseg13
   32 |      14 | m    | m              | s    | u      | 10506 | bds-sdw1 | bds-sdw1 | /data1/mirror/gpseg14
   16 |      14 | p    | p              | s    | u      | 10006 | bds-sdw2 | bds-sdw2 | /data1/primary/gpseg14
   33 |      15 | m    | m              | s    | u      | 10507 | bds-sdw1 | bds-sdw1 | /data1/mirror/gpseg15
   17 |      15 | p    | p              | s    | u      | 10007 | bds-sdw2 | bds-sdw2 | /data1/primary/gpseg15
(34 rows)

Find which host a mirror of the specified segment is located on

SELECT t2.hostname
FROM gp_segment_configuration t1
LEFT JOIN gp_segment_configuration t2 ON t1.content = t2.content
WHERE t1.dbid = 2
AND t2.preferred_role = 'm';

Where 2 can be replaced by the specified segment dbid.

The result is:

 hostname
----------
 bds-sdw2
(1 row)

List last 10 events related to segment configuration changes

SELECT * FROM gp_configuration_history ORDER BY time desc LIMIT 10;

The result is:

             time              | dbid |                                      desc
-------------------------------+------+--------------------------------------------------------------------------------
 2022-11-14 20:05:55.03926+00  |   24 | FTS: update role, status, and mode for dbid 24 with contentid 6 to m, u, and s
 2022-11-14 20:05:55.039219+00 |    8 | FTS: update role, status, and mode for dbid 8 with contentid 6 to p, u, and s
 2022-11-14 20:05:55.035604+00 |   19 | FTS: update role, status, and mode for dbid 19 with contentid 1 to m, u, and s
 2022-11-14 20:05:55.035532+00 |    3 | FTS: update role, status, and mode for dbid 3 with contentid 1 to p, u, and s
 2022-11-14 20:05:00.018624+00 |   24 | FTS: update role, status, and mode for dbid 24 with contentid 6 to m, u, and n
 2022-11-14 20:05:00.018573+00 |    8 | FTS: update role, status, and mode for dbid 8 with contentid 6 to p, u, and n
 2022-11-14 20:05:00.010778+00 |   19 | FTS: update role, status, and mode for dbid 19 with contentid 1 to m, u, and n
 2022-11-14 20:05:00.010712+00 |    3 | FTS: update role, status, and mode for dbid 3 with contentid 1 to p, u, and n
 2022-11-14 20:04:55.43101+00  |   22 | FTS: update role, status, and mode for dbid 22 with contentid 4 to m, u, and s
 2022-11-14 20:04:55.430979+00 |    6 | FTS: update role, status, and mode for dbid 6 with contentid 4 to p, u, and s
(10 rows)

Found a mistake? Seleсt text and press Ctrl+Enter to report it