Таблицы

Дарья Барышева

Содержание

Создание новой таблицы
Просмотр списка таблиц
Просмотр информации о таблице
Таблицы системного каталога
Полезные запросы к системным таблицам

Таблица — это основной объект данных в ADB. В целом, таблицы в ADB идентичны таблицам в любой реляционной СУБД, за исключением распределения табличных строк по различным сегментам кластера. При создании таблицы необходимо выбрать политику распределения данных.

Наряду с этим, ADB поддерживает несколько типов таблиц и вариантов сжатия данных, которые могут быть использованы для наиболее оптимального хранения данных.

Создание новой таблицы

Обзор синтаксиса

Для создания новой таблицы в текущей БД необходимо использовать команду CREATE TABLE. Пользователь, выполняющий команду, назначается владельцем таблицы. Базовый синтаксис команды приведен ниже.

ПРИМЕЧАНИЕ

Полную информацию по команде CREATE TABLE можно получить в документации Greenplum.

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>]

Основные выражения

TEMPORARY | TEMP

Определяет временную (temporary) таблицу, которая автоматически удаляется в конце сессии или — опционально — в конце текущей транзакции (см. ON COMMIT ниже)

UNLOGGED

Определяет "нелогируемую" (unlogged) таблицу, данные которой не будут писаться в write-ahead log (WAL). Это делает unlogged-таблицы намного быстрее обычных, но вместе с тем понижает их отказоустойчивость. Содержимое unlogged-таблиц не реплицируется на зеркала сегментов

COLLATE

Определяет сортировку (collation) столбца. Если параметр не определен, используется дефолтная сортировка типа данных столбца

ENCODING

Определяет опции хранения на уровне столбца: тип сжатия, уровень сжатия, размер блока. Выражение применимо только к таблицам append-optimized с колоночной ориентацией (column-oriented)

LIKE

Определяет таблицу, из которой в новую копируются имена столбцов, их типы данных и NOT NULL-ограничения. Прочие части определения столбцов могут быть включены или исключены при копировании посредством использования выражений INCLUDING/EXCLUDING (см. аргумент <like_option> ниже). По сравнению с использованием INHERITS, новая и исходная таблица никак не связаны после завершения создания таблицы. Это означает, что изменение исходной таблицы (через команду ALTER TABLE) не оказывает влияния на структуру новой таблицы. Обратите внимание, что опции хранения, указанные в выражении WITH, и структура партиционирования не копируются

INHERITS

Определяет список таблиц, от которых новая таблица автоматически наследует все столбцы и их ограничения. В отличие от выражения LIKE, INHERITS создает постоянную связь между новой таблицей и всеми родительскими таблицами. Изменения структуры родительских таблиц автоматически применяются к дочерней таблице

WITH

Определяет опции хранения на уровне таблицы

ON COMMIT

Определяет поведение временных (temporary) таблиц после завершения транзакции. Возможные значения:

PRESERVE ROWS — дополнительных действий не требуется. Поведение по умолчанию.
DELETE ROWS — все строки таблицы должны быть удалены. Равнозначно выполнению команды TRUNCATE.
DROP — временная таблица должна быть полностью удалена.

TABLESPACE

Определяет название табличного пространства (tablespace), в котором будет создана таблица. Если параметр не задан, будет использован tablespace БД по умолчанию либо — для временной таблицы — конфигурационный параметр temp_tablespaces

DISTRIBUTED BY

Устанавливает политику распределения данных. Возможные значения:

DISTRIBUTED BY (<column_name> [<opclass>], [ … ] ) — хеш-распределение.
DISTRIBUTED RANDOMLY — случайное распределение с использованием алгоритма round-robin.
DISTRIBUTED REPLICATED — полное распределение, при котором копия таблицы сохраняется на каждом сегменте кластера.

Дополнительные сведения можно получить в статье Распределение данных

PARTITION BY

Определяет партиционирование для таблицы. Дополнительную информацию можно получить в статье Партиционирование

Аргументы

<table_name>

Имя таблицы

<column_name>

Имя столбца

<data_type>

Тип данных столбца. Рекомендации по выбору типов данных приведены ниже

<collation>

Используемая для столбца сортировка (collation)

<column_constraint>

Ограничение (constraint), определенное на уровне столбца. Имя ограничения <constraint_name> указывается опционально. Возможные ограничения:

NOT NULL — указывает, что столбец не может содержать null-значений.
NULL — указывает, что столбец может содержать null-значения. Это ограничение используется по умолчанию (если не указано NOT NULL).
CHECK — определяет логическое выражение <expression>, которому должны удовлетворять новые или обновляемые строки, чтобы команды INSERT или UPDATE завершились успешно. Ограничение, отмеченное как NO INHERIT, не будет применяться к дочерним таблицам (создаваемым через выражение INHERITS).
DEFAULT — определяет для столбца значение по умолчанию <default_expr>.
UNIQUE — указывает, что столбец должен содержать только уникальные значения. Null-значения не считаются дубликатами. Применимо только к heap-таблицам.
PRIMARY KEY — указывает, что столбец должен содержать уникальные значения, отличные от null. Только один первичный ключ может быть определен для таблицы (он может включать несколько столбцов). Применимо только к heap-таблицам.

Для heap-таблиц также допускается использовать ограничения ссылочной целостности (FOREIGN KEY, REFERENCES), однако фактически они никак не обрабатываются.

Существует две группы ограничений:

DEFERRABLE | NOT DEFERRABLE. "Неотложное" (not deferrable) ограничение проверяется непосредственно после завершения команды. Проверка "отложенных" (deferrable) может быть перенесена на момент завершения транзакции (с использованием команды SET CONSTRAINTS). Тип NOT DEFERRABLE применяется по умолчанию. В настоящий момент только ограничения UNIQUE и PRIMARY KEY могут быть deferrable. Ограничения NOT NULL и CHECK — не могут.
INITIALLY DEFERRED | INITIALLY IMMEDIATE. Если ограничение отмечено как deferrable, это выражение определяет время для проверки ограничения. Выражение INITIALLY IMMEDIATE указывает на проверку после каждой команды. Это поведение по умолчанию. Выражение INITIALLY DEFERRED означает проверку в конце транзакции. Время проверки ограничения может быть изменено с использованием команды SET CONSTRAINTS.

Для ограничений UNIQUE и PRIMARY KEY можно дополнительно указать табличное пространство и опции хранения, как для обычных таблиц (через параметр <index_parameters>).

Синтаксис:

[ 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 ]

Где <index_parameters>:

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

<storage_column_directive>

Определяет опции хранения данных на уровне столбца для таблиц append-optimized с колоночной ориентацией (column-oriented):

compresstype — тип сжатия данных.
compresslevel — уровень сжатия данных.
blocksize — размер блока (в байтах).

Синтаксис:

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

Дополнительную информацию по опциям сжатия можно получить в статье Сжатие данных

<table_constraint>

Ограничение (constraint), определенное на уровне таблицы. Имя ограничения <constraint_name> указывается опционально. В отличие от <column_constraint>, не позволяет указывать ограничения NULL, NOT NULL и DEFAULT. В остальном синтакис конструкции идентичен (см. <column_constraint> выше).

Синтаксис:

[ 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 ]

Где <index_parameters>:

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

Эта форма ограничений может быть использована, например, для определения первичного ключа, основанного на нескольких столбцах

<source_table>

Исходная таблица, структура которой копируется при создании новой таблицы с использованием выражения LIKE

<like_option>

Определяет, включать (INCLUDING) или исключать (EXCLUDING) следующие части определений столбцов (при копировании исходной таблицы на базе выражения LIKE):

DEFAULTS — дефолтные значения столбцов. По умолчанию они исключаются, что приводит к созданию столбцов в новой таблице с незаполненными дефолтными значениями.
CONSTRAINTS — ограничения CHECK. NOT NULL-ограничения переносятся по умолчанию.
INDEXES — индексы, ограничения PRIMARY KEY и UNIQUE.
STORAGE — опции хранения данных в столбцах. По умолчанию они исключаются, что приводит к созданию столбцов в новой таблице с опциями хранениями, соответствующими типам данных в столбцах.
COMMENTS — комментарии к копируемым столбцам, их ограничениям и индексам.
ALL — все, указанное выше.

Синтаксис:

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

<parent_table>

Родительская таблица, структура которой копируется при создании новой таблицы с использованием выражения INHERITS

<storage_table_directive>

Определяет опции хранения данных на уровне таблицы:

appendoptimized — является ли таблица append-optimized. Если параметр не заполнен или равен FALSE, создается таблица с типом heap.
blocksize — размер блока (в байтах). Применимо только к таблицам append-optimized.
orientation — ориентация данных в таблице. Если параметр не заполнен или равен COLUMN, создается таблица с колоночной ориентацией (column-oriented), в противном случае — со строковой (row-oriented). Применимо только к таблицам append-optimized.
checksum — если параметр равен TRUE или не заполнен, для таблицы будет использоваться проверка контрольной суммы (Cyclic Redundancy Check, CRC). Контрольная сумма рассчитывается при создании блока, после чего сохраняется на диске. Проверка контрольной суммы выполняется при чтении блоков. Если контрольная сумма, вычисленная при чтении блока, не совпадает с хранимой на диске — транзакция не выполняется. Применимо только к таблицам append-optimized.
compresstype — тип сжатия данных. Применимо только к таблицам append-optimized.
compresslevel — уровень сжатия данных. Применимо только к таблицам append-optimized.
fillfactor — степень заполнения стандартного восьмикилобайтного блока. Измеряется в процентах от 10 до 100. Значение 100 (соответствующее "полной упаковке") используется по умолчанию. При использовании меньших значений fillfactor команды INSERT заполняют страницы таблиц лишь до заданного значения, резервируя оставшееся пространство для команд UPDATE. Для таблиц, обновляемых редко либо никогда, значение 100 является оптимальным выбором. Однако для часто обновляемых таблиц предпочтительны меньшие значения fillfactor.
oids — если параметр равен FALSE или не установлен, идентификаторы объектов (Object Identifier, OID) не будут присваиваться строкам таблицы. Использование OID не рекоменовано для пользовательских таблиц, поскольку в больших таблицах оно может вызвать "зацикливание" (wrap-around) 32-битного счетчика OID.

Синтаксис:

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]

Дополнительную информацию по опциям сжатия можно получить в статье Сжатие данных.

Для некоторых из перечисленных выше опций можно определить значения по умолчанию — в серверном конфигурационном параметре gp_default_storage_options

<tablespace_name>

Название табличного пространства (tablespace), в котором будет создана таблица

<opclass>

Название оператора (operator). Может быть заполнено опционально для использования пользовательской хеш-функции распределения данных

<partition_type>

Тип партиционирования. Возможные значения:

LIST — список значений.
RANGE — диапазон чисел или дат.

<partition_description>

Спецификация партиционирования, включая: партиции, субпартиции, описание дефолтной партиции. Дополнительные сведения можно получить в статье Партиционирование

Существует несколько рекомендаций, как выбрать наиболее оптимальный тип данных для столбцов таблиц:

Всегда выбирайте тип данных в соответствии с хранимой в каждом столбце информацией.
Выбирайте типы данных, которые будут иметь минимальный возможный размер, но при этом смогут вмещать все ваши данные. Например, используйте INT вместо BIGINT, TEXT вместо CHAR(<n>) и так далее.
При выборе числовых типов данных старайтесь спрогнозировать возможное увеличение данных в будущем. Имейте в виду, что переход от меньшего типа данных к большему в таблицах со значительным числом строк может быть дорогостоящей операцией. Поэтому, если текущие данные помещаются в тип SMALLINT, но в будущем вероятно их увеличение — тип INT является лучшим выбором на долгосрочную перспективу.
Если возможно, используйте специализированные типы данных: INET, CIDR, JSON, JSONB, MACADDR и так далее.
Используйте одинаковые типы данных в столбцах, по которым выполняются операции JOIN. Когда типы данных отличаются, СУБД вынуждена их конвертировать для корректного сопоставления, что может негативно сказаться на производительности.
Хотя Greenplum поддерживает широкий выбор встроенных типов данных, вы можете создавать свои собственные. Например, использование ENUM может обеспечить значительный прирост производительности.

Примеры

Создание heap-таблицы с политикой распределения REPLICATED и ограничениями на уровне столбцов

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

Создание heap-таблицы с политикой распределения REPLICATED и ограничениями на уровне таблицы

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

Создание таблицы append-optimized с колоночной ориентацией (и хеш-распределением)

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

Создание таблицы append-optimized со строковой ориентацией и ZLIB-сжатием данных (на уровне таблицы)

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

Создание таблицы append-optimized с колоночной ориентацией и ZSTD-сжатием данных (на уровне столбца)

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

Создание таблицы с использованием выражения LIKE

Создайте таблицу book_type, если она еще не существует:

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

Результат:

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

Проверьте структуру таблицы с использованием psql-команды \d+:

\d+ book_type

Результат:

                                              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

Добавьте другую таблицу book_type_arch с той же структурой и ограничениями столбцов с использованием выражения LIKE:
```
CREATE TABLE book_type_arch(LIKE book_type INCLUDING ALL)
DISTRIBUTED REPLICATED;
```
Результат:
```
CREATE TABLE
```

Проверьте структуру новой таблицы с помощью psql-команды \d+:

\d+ book_type_arch

Можно увидеть, что она полностью идентична таблице book_type:

                                            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

ВНИМАНИЕ

Обе таблицы используют одну последовательность book_type_id_seq для заполнения первого столбца id. Это может привести к нежелательному поведению в дальнейшем.

Чтобы убедиться в отсутствии связи между таблицами, измените первую таблицу book_type:
```
ALTER TABLE book_type
DROP COLUMN name;
```
Результат:
```
ALTER TABLE
```

Проверьте структуру второй таблицы book_type_arch еще раз. Она осталась прежней.

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

Создание таблицы с использованием выражения INHERITS

Создайте таблицу book, если она еще не существует:

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

Результат:

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

Проверьте структуру созданной таблицы с использованием psql-команды \d+:

\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    |              |
 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

Добавьте новую таблицу book_arch с той же структурой и ограничениями столбцов с использованием выражения INHERITS:
```
CREATE TABLE book_arch()
INHERITS(book)
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id);
```
Результат:
```
CREATE TABLE
```

Проверьте структуру новой таблицы с помощью psql-команды \d+:

\d+ book_arch

Можно увидеть, что она полностью идентична таблице book:

                                        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

Чтобы убедиться в связи таблиц после создания, измените структуру первой таблицы book:
```
ALTER TABLE book
DROP COLUMN type_id;
```
Результат:
```
ALTER TABLE
```

Проверьте структуру второй таблицы book_arch еще раз. Столбец type_id удален, как и в родительской таблице:

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

Создание временной таблицы

Запустите новую транзакцию:
```
START TRANSACTION;
```
Результат:
```
START TRANSACTION
```
Создайте временную таблицу. В выражении ON COMMIT укажите, что таблица должна быть удалена после завершения транзакции:
```
CREATE TEMP TABLE book_temp(id INT, name TEXT)
ON COMMIT DROP
DISTRIBUTED BY(id);
```
Результат:
```
CREATE TABLE
```

Проверьте существование таблицы с помощью psql-команды \dt+:

\dt+

Результат:

                                         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;
```
Результат:
```
COMMIT
```

Проверьте существование таблицы еще раз. Таблица book_temp более не существует:

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)

Создание таблицы с CHECK-ограничением

Создайте таблицу со следующим ограничением: в ней не должно быть строк со значением менее 1 в столбце book_count:

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

Следующий запрос использует то же ограничение CHECK, но на уровне столбца:

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

Независимо от выбранной синтаксической формы можно проверить работу ограничения следующим образом. Попробуйте добавить в таблицу book_order строку со значением 0 в столбце book_count:
```
INSERT INTO book_order VALUES(1, 1, 1, 0, now());
```
Запрос приведет к ошибке:
```
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).
```
Измените запрос INSERT, как показано ниже:
```
INSERT INTO book_order VALUES(1, 1, 1, 1, now());
```
Теперь результат успешен:
```
INSERT 0 1
```

Создание партиционированной таблицы

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'));

Результат:

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

ПРИМЕЧАНИЕ

Дополнительные примеры можно найти в статье Партиционирование.

Просмотр списка таблиц

Для просмотра существующих таблиц в текущей БД можно использовать следующие способы:

Сделать выборку из системной таблицы information_schema.tables. Для вывода только таблиц требуется указать table_type = 'BASE TABLE' в выражении WHERE. В противном случае результат будет также содержать представления (для которых 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;

Эта команда возвращает следующие столбцы:

table_catalog — имя базы данных;
table_schema — имя схемы;
table_name — имя таблицы;

table_type — тип таблицы (BASE TABLE для обычных таблиц, VIEW для представлений).

Результат запроса

  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)

Сделать выборку из системного представления pg_catalog.pg_tables.

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

Эта команда возвращает следующие столбцы:

schemaname — имя схемы;
tablename — имя таблицы;
tableowner — владелец таблицы;
tablespace — имя табличного пространства (tablespace);
hasindexes — логическое значение, указывающее имеет ли таблица индексы;
hasrules — логическое значение, указывающее имеет ли таблица правила;

hastriggers — логическое значение, указывающее имеет ли таблица триггеры.

Результат запроса

     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)

При работе с терминальным клиентом psql для вывода таблиц можно воспользоваться мета-командами:

\dt — возвращает следующую информацию:

Schema — имя схемы;
Name — имя таблицы;
Type — тип таблицы (table для обычных таблиц, view для представлений);
Owner — владелец таблицы;

Storage — тип таблицы.

                          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+ — по сравнению с \dt, эта команда возвращает дополнительные данные:

Size — размер таблицы;

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)

РЕКОМЕНДАЦИЯ

Для добавления системных таблиц в вывод описанных выше psql-команд можно указать постфикс S после названий команд: \dtS, \dt+S.
Для вывода таблиц, принадлежащих определенной схеме, можно использовать регулярные выражения. Например, команда \dt kadb.* выведет таблицы с именем схемы kadb. По умолчанию команды \dt и \dt+ показывают таблицы для текущей схемы. Дополнительную информацию можно получить в разделе Schema search path.

Просмотр информации о таблице

Чтобы увидеть структуру определенной таблицы, можно воспользоваться одной из следующих psql-команд:

\d — эта команда выводит следующую информацию:

Список столбцов таблицы с их именами, типами данных и модификаторами (constraints, sequences и так далее).
Опции хранения, определенные на уровне таблицы: тип сжатия данных (compression type), уровень сжатия (compression level), размер блока данных (block size). Доступно только для таблиц append-optimized.
Указание того, использует ли таблица контрольную сумму (checksum).
Количество дочерних таблиц (созданных с использованием выражения INHERITS).

Политика распределения данных.

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+ — по сравнению с \d, эта команда возвращает ряд дополнительных данных:

Опции хранения, определенные на уровне столбцов: тип сжатия данных (compression type), уровень сжатия (compression level), размер блока данных (block size). Доступно только для таблиц append-optimized с колоночной ориентацией (column-oriented).
Дополнительные поля по столбцамм: statistic target, описание.
Список дочерних таблиц (созданных с использованием выражения INHERITS).

Опции, использованные при создании таблицы (в выражении WITH).

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

Таблицы системного каталога

ADB (как и Greenplum) поддерживает несколько системных таблиц в рамках схемы pg_catalog. Некоторые из них могут быть полезны для мониторинга кластера и прочих целей. Примеры таких таблиц описаны ниже.

ПРИМЕЧАНИЕ

Для получения дополнительной информации по системным таблицам можно обратиться к статье System Tables в документации Greenplum.

gp_configuration_history

Содержит информацию об изменениях в системе, связанных с обнаружением сбоев и восстановлением после них.

Основные столбцы
Название	Назначение
time	Временная метка зафиксированного события
dbid	Уникальный идентификатор инстанса (сегмента или мастера). Ссылается на `gp_segment_configuration.dbid`
desc	Описание события

gp_segment_configuration

Содержит информацию о конфигурации кластера: статусе мастеров, сегментов и их зеркал.

Основные столбцы
Название	Назначение
dbid	Уникальный идентификатор инстанса (сегмента или мастера)
content	Уникальный идентификатор шарда данных. Primary-сегмент и его зеркало всегда имеют одинаковое значение столбца `content`. Возможные значения: Для сегмента от `0` до `N-1`, где `N` — количество primary-сегментов в кластере. Для мастера всегда равно `-1`.
role	Роль, которую выполняет сегмент в настоящий момент. Возможные значения: `p` (primary) и `m` (mirror)
preferred_role	Роль, назначенная сегменту при инициализации системы. Возможные значения: `p` (primary) и `m` (mirror)
mode	Статус синхронизации сегмента со своим зеркалом. Возможные значения: `s` (Synchronized) и `n` (Not In Sync). Этот столбец всегда показывает значение `n` для Master и `s` для Standby Master. Используйте системную таблицу gp_stat_replication для просмотра синхронизации Master и Standby Master
status	Статус работы сегмента. Возможные значения: `u` (up) и `d` (down)
port	TCP-порт, прослушиваемый сервером БД
hostname	Имя хоста
address	Адрес хоста. Может совпадать со значением `hostname` в системах, не использующих настройку имен хостов для каждого интерфейса (per-interface)
datadir	Физическое расположение рабочей директории на сегменте (или мастере)

pg_database

Хранит информацию о базах данных (database).

Основные столбцы
Название	Назначение
datname	Имя базы данных
datdba	Владелец базы данных. Как правило, пользователь, создавший ее. Ссылается на pg_authid.oid
dattablespace	Табличное пространство, назначенное базе данных. Используется по умолчанию для всех системных таблиц базы данных, а также всех пользовательских таблиц и индексов, создаваемых внутри нее (за исключением явного указания выражения `TABLESPACE` при их создании)

pg_class

Хранит информацию о всех отношениях (relation) базы данных. Наряду с таблицами, они включают в себя индексы, последовательности (sequence), представления (view), составные типы (composite type), и TOAST-таблицы (которые хранят "сверхбольшие" атрибуты исходной таблицы, выходящие за пределы фиксированного размера страницы). Столбцы содержат данные не для всех отношений.

Основные столбцы
Название	Назначение
relname	Название отношения
relnamespace	Уникальный идентификатор (OID) схемы, содержащей отношение. Ссылается на `pg_namespace.oid`
relowner	Владелец отношения. Как правило, пользователь, создавший его. Ссылается на pg_authid.oid
relam	Способ доступа: B-tree, Bitmap, hash и т.д. Заполняется только для индексов. Ссылается на pg_am.oid
reltablespace	Табличное пространство (tablespace), в котором хранится отношение. Если не заполнено, используется табличное пространство БД по умолчанию. Ссылается на `pg_tablespace.oid`
relpages	Размер, занимаемый отношением на диске (в страницах по 32 КБ каждая). Это приблизительная оценка, используемая планировщиком выполнения запросов. Значение обновляется командами `VACUUM`, `ANALYZE` и рядом DDL-команд
reltuples	Число строк в отношении. Это приблизительная оценка, используемая планировщиком выполнения запросов. Значение обновляется командами `VACUUM`, `ANALYZE` и рядом DDL-команд
reltoastrelid	Уникальный идентификатор (OID) TOAST-таблицы, связанной с отношением; `0` — если таких таблиц нет. Ссылается на `pg_class.oid`
relpersistence	Persistence-тип отношения: `p` — таблица heap или append-optimized; `u` — временная таблица unlogged temporary; `t` — временная таблица temporary.
relkind	Тип отношения: `r` — таблица heap или append-optimized; `i` — индекс; `S` — последовательность (sequence); `t` — TOAST-таблица; `v` — представление (view); `c` — составной тип (composite type); `f` — foreign-таблица; `u` — uncatalogued temporary heap-таблица; `o` — internal append-optimized segment files и EOFs; `b` — append-only block directory; `M` — append-only visibility map.
relstorage	Способ хранения данных отношения: `a` — append-optimized; `c` — column-oriented; `h` — heap; `v` — virtual; `x` — external table.

pg_namespace

Хранит информацию о схемах (schema или namespace).

Основные столбцы
Название	Назначение
nspname	Название схемы
nspowner	Владелец схемы. Как правило, пользователь, создавший ее. Ссылается на pg_authid.oid

pg_tablespace

Хранит информацию о табличных пространствах (tablespace).

Основные столбцы
Название	Назначение
spcname	Название tablespace
spcowner	Владелец tablespace. Как правило, пользователь, создавший его. Ссылается на pg_authid.oid

Полезные запросы к системным таблицам

Примеры запросов к таблицам системного каталога приведены ниже.

Вывести все базы данных с сортировкой по их размеру

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

Результат:

  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)

Вывести все схемы

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

Результат:

  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)

Вывести все табличные пространства (tablespace)

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

Результат:

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

Вывести внешние (external) таблицы

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

Результат:

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

Найти число отношений в БД (relation) с группировкой по их типу

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

Результат:

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

Вывести все TOAST-таблицы с указанием соответствующих им основных таблиц

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;

Результат:

 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)

Получить информацию об отношении (relation) по его имени

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';

Где значение 'pg_class' может быть заменено на имя конкретного отношения.

Результат:

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

Получить информацию по всем сегментам кластера ADB

SELECT * FROM gp_segment_configuration ORDER BY content, role;

Результат:

 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)

Определить хост, на котором расположено зеркало определенного сегмента

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';

Где значение 2 может быть заменено на идентификатор нужного сегмента dbid.

Результат:

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

Вывести последние 10 событий, связанных с изменением конфигурации сегментов

SELECT * FROM gp_configuration_history ORDER BY time desc LIMIT 10;

Результат:

             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)

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней