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

Некоторое время назад узнал об одной очень интересной особенности PostgreSQL — можно создать таблицу таблицу БЕЗ атрибутов следующим образом:

postgres@demo=# CREATE TABLE empty();
CREATE TABLE

Команда выполнилась без ошибок. Проверим, что находится в таблице:

postgres@demo=# SELECT * FROM empty;
--
(0 rows)

Ноль строк. Это логично — мы же ничего не добавляли туда. Попробуем вставить строки… Хм, а как это сделать, столбцов ведь в ней нет?

Вот один из вариантов — воспользоваться командой COPY:

postgres@demo=# COPY empty FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> \.
COPY 10

Я ничего не писал, только клавишу Ввод нажимал. Как будто десять строк было добавлено. Снова выведем содержимое таблицы:

postgres@demo=# SELECT * FROM empty;
--
(10 rows)

Строки есть («10 rows»), но их не видно…

А есть и еще один способ добавить строки в таблицу. Он вот так вот банален: 

postgres@demo=# INSERT INTO empty SELECT;
INSERT 0 1

Можно просто SELECT; выполнить — и получите результат, как в данном случае. «Добавим» так несколько строк в таблицу и снова запросим все данные:

postgres@demo=# SELECT * FROM empty;
--
(16 rows)

Но как же так? Зачем тогда такая пустая таблица? А она не пустая! В ней есть системные столбцы, которые скрыты, их нужно прописать в запросе явно:

postgres@demo=# SELECT tableoid, xmin, cmin, xmax, cmax,ctid, * FROM empty;
 tableoid | xmin | cmin | xmax | cmax |  ctid  
----------+------+------+------+------+--------
   616790 | 1114 |    0 |    0 |    0 | (0,1)
   616790 | 1114 |    0 |    0 |    0 | (0,2)
   616790 | 1114 |    0 |    0 |    0 | (0,3)
   616790 | 1114 |    0 |    0 |    0 | (0,4)
   616790 | 1114 |    0 |    0 |    0 | (0,5)
   616790 | 1114 |    0 |    0 |    0 | (0,6)
   616790 | 1114 |    0 |    0 |    0 | (0,7)
   616790 | 1114 |    0 |    0 |    0 | (0,8)
   616790 | 1114 |    0 |    0 |    0 | (0,9)
   616790 | 1114 |    0 |    0 |    0 | (0,10)
   616790 | 1115 |    0 |    0 |    0 | (0,11)
   616790 | 1116 |    0 |    0 |    0 | (0,12)
   616790 | 1117 |    0 |    0 |    0 | (0,13)
   616790 | 1118 |    0 |    0 |    0 | (0,14)
   616790 | 1119 |    0 |    0 |    0 | (0,15)
   616790 | 1120 |    0 |    0 |    0 | (0,16)
(16 rows)

И «пустые» строки можно даже удалять! Напрмер, проведем такой финт:

Запускаем явную транзакцию:

postgres@demo=# BEGIN;
BEGIN

В ней удаляем все строки из пустой таблицы:

postgres@demo=# DELETE FROM empty ;
DELETE 16

Проверяем:

postgres@demo=# SELECT tableoid, xmin, cmin, xmax, cmax,ctid, * FROM 	empty;
 tableoid | xmin | cmin | xmax | cmax | ctid 
----------+------+------+------+------+------
(0 rows)

Вроде ничего нет. А теперь откатываем транзакцию:

postgres@demo=# ROLLBACK;
ROLLBACK

И снова проверяем что у нас в таблице:

postgres@demo=# SELECT tableoid, xmin, cmin, xmax, cmax,ctid, * FROM empty;
 tableoid | xmin | cmin | xmax | cmax |  ctid  
----------+------+------+------+------+--------
   616790 | 1114 |    0 | 1121 |    0 | (0,1)
   616790 | 1114 |    0 | 1121 |    0 | (0,2)
   616790 | 1114 |    0 | 1121 |    0 | (0,3)
   616790 | 1114 |    0 | 1121 |    0 | (0,4)
   616790 | 1114 |    0 | 1121 |    0 | (0,5)
   616790 | 1114 |    0 | 1121 |    0 | (0,6)
   616790 | 1114 |    0 | 1121 |    0 | (0,7)
   616790 | 1114 |    0 | 1121 |    0 | (0,8)
   616790 | 1114 |    0 | 1121 |    0 | (0,9)
   616790 | 1114 |    0 | 1121 |    0 | (0,10)
   616790 | 1115 |    0 | 1121 |    0 | (0,11)
   616790 | 1116 |    0 | 1121 |    0 | (0,12)
   616790 | 1117 |    0 | 1121 |    0 | (0,13)
   616790 | 1118 |    0 | 1121 |    0 | (0,14)
   616790 | 1119 |    0 | 1121 |    0 | (0,15)
   616790 | 1120 |    0 | 1121 |    0 | (0,16)
(16 rows)

В таблице даже кое-что поменялось — подробнее об этом можно прочитать в курсе «DBA2. Настройка и мониторинг» компании «Постгрес Профессиональный», второе занятие.

Еще один из вариантов удаления строк — обратиться к ним по ctid:

postgres@demo=# DELETE FROM empty WHERE ctid = '(0,1)';
DELETE 1

И опять проверим что в таблице:

postgres@demo=# SELECT tableoid, xmin, cmin, xmax, cmax,ctid, * FROM empty;
 tableoid | xmin | cmin | xmax | cmax |  ctid  
----------+------+------+------+------+--------
   616790 | 1114 |    0 | 1121 |    0 | (0,2)
   616790 | 1114 |    0 | 1121 |    0 | (0,3)
   616790 | 1114 |    0 | 1121 |    0 | (0,4)
   616790 | 1114 |    0 | 1121 |    0 | (0,5)
   616790 | 1114 |    0 | 1121 |    0 | (0,6)
   616790 | 1114 |    0 | 1121 |    0 | (0,7)
   616790 | 1114 |    0 | 1121 |    0 | (0,8)
   616790 | 1114 |    0 | 1121 |    0 | (0,9)
   616790 | 1114 |    0 | 1121 |    0 | (0,10)
   616790 | 1115 |    0 | 1121 |    0 | (0,11)
   616790 | 1116 |    0 | 1121 |    0 | (0,12)
   616790 | 1117 |    0 | 1121 |    0 | (0,13)
   616790 | 1118 |    0 | 1121 |    0 | (0,14)
   616790 | 1119 |    0 | 1121 |    0 | (0,15)
   616790 | 1120 |    0 | 1121 |    0 | (0,16)
(15 rows)

Первая строка исчезла.

В общем, помните про системные столбцы и что они увеличивают размер вашей таблицы.

Например так:

Добавим много «пустых» строк в таблицу empty (я делал через create table as select):

postgres@demo=# SELECT count(*) FROM empty;
 count  
--------
 106500
(1 row)

Проверим размер таблицы:

postgres@demo=# \dt+ empty 
							 List of relations
  Schema  | Name  | Type  |  Owner   | Persistence |  Size   | Description 
----------+-------+-------+----------+-------------+---------+-------------
 bookings | empty | table | postgres | permanent   | 2952 kB | 
(1 row)

Проверим размер файла таблицы на диске. Сначала получаю название этого файла:

postgres@demo=# SELECT pg_relation_filepath('empty');
 pg_relation_filepath 
----------------------
 base/16720/616796
(1 row)

Теперь проверяю его размер средствами операционной системы:

postgres@student:~/13/main/base/16720$ ls -lh 616796
-rw------- 1 postgres postgres 2,9M сен 10 20:09 616796

А в таблце по-прежнему ничего «нет»:

postgres@demo=# SELECT * FROM empty;
--
(106500 rows)