Alter column position/ru

Множество людей, только начавших знакомство с PostgreSQL, часто задают вопрос на счет поддержки изменения порядка следования колонок таблицы. На текущий момент поддержка отсутствует. Чтобы изменить порядок колонок, необходимо либо пересоздать таблицу, либо создать новые колонки с последующим переносом данных. Сама по себе идея перемещения колонок разработчикам PostgreSQL не претит, просто шаги к разработке данной функциональности не предпринимались. Назначение этого документа заключается в разъяснении обходных путей и тонких моментов, которые необходимо учитывать, при желании реализовать эту возможность.

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

1. размещение колонок фиксированной длины в начале таблицы влияет на оптимизацию физической структуры
2. измененный порядок следования колонок в результатах выборок может быть более привлекательным внешне

На текущий момент PostgreSQL хранит порядок следования колонок таблиц к колонке attnum системной таблицы pg_attribute. Единственный путь изменить порядок колонки - пересоздать таблицу, или добавить колонки, переливая данные между старыми и новыми до тех пор, пока не будет достигнут нужный порядок.

Обходные пути

Воссоздай таблицу

В первом примере приведена таблица film_actor, содержащая три колонки - actor_id, film_id и last_update. Со временем мы заметили, что разработчики часто путаются, какой id относится к фильмам, а какой - к актерам. Поэтому было принято решение изменить порядок следования колонок таблицы.

pagila=# \d film_actor
                     Table "public.film_actor"
   Column    |            Type             |       Modifiers        
-------------+-----------------------------+------------------------
 actor_id    | smallint                    | not null
 film_id     | smallint                    | not null
 last_update | timestamp without time zone | not null default now()
Indexes:
    "film_actor_pkey" PRIMARY KEY, btree (actor_id, film_id)
    "idx_fk_film_id" btree (film_id)
Foreign-key constraints:
    "film_actor_actor_id_fkey" FOREIGN KEY (actor_id) REFERENCES actor(actor_id) ON UPDATE CASCADE ON DELETE RESTRICT
    "film_actor_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
    last_updated BEFORE UPDATE ON film_actor FOR EACH ROW EXECUTE PROCEDURE last_updated()

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

pagila=# begin; 
BEGIN
pagila=# create table film_actor_new ( 
pagila-# film_id smallint references film, 
pagila-# actor_id smallint references actor, 
pagila-# last_update timestamp not null default now(), 
pagila-# primary key (actor_id,film_id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit 
index "film_actor_new_pkey" for table "film_actor_new"
CREATE TABLE
pagila=# insert into film_actor_new select film_id, actor_id, last_update from film_actor; 
INSERT 0 5462
pagila=# drop table film_actor cascade; 
NOTICE:  drop cascades to rule _RETURN on view nicer_but_slower_film_list
NOTICE:  drop cascades to view nicer_but_slower_film_list
NOTICE:  drop cascades to rule _RETURN on view film_list
NOTICE:  drop cascades to view film_list
NOTICE:  drop cascades to rule _RETURN on view actor_info
NOTICE:  drop cascades to view actor_info
DROP TABLE
pagila=# alter table film_actor_new RENAME to film_actor;
ALTER TABLE
pagila=# \d film_actor
                     Table "public.film_actor"
   Column    |            Type             |       Modifiers        
-------------+-----------------------------+------------------------
 film_id     | smallint                    | not null
 actor_id    | smallint                    | not null
 last_update | timestamp without time zone | not null default now()
Indexes:
    "film_actor_new_pkey" PRIMARY KEY, btree (actor_id, film_id)
Foreign-key constraints:
    "film_actor_new_actor_id_fkey" FOREIGN KEY (actor_id) REFERENCES actor(actor_id)
    "film_actor_new_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id)

Для полноты процесса необходимо воссоздать представления, ссылающиеся на новую таблицу. Также нужно помнить о необходимости воссоздания необходимых индексов, триггеров и иных объектов базы, чтобы конечная схема соответствовала первоначальной. Все это лучше делать в транзакции, однако, стоит помнить о возможном большом количестве зависимостей и возможных последствиях.

Добавь колонки и перелей данные

Иногда неоправданно идти путем воссоздания таблицы. Тогда может помочь другой - добавление колонок с одновременным перемещением данных. В примере у нас есть актер с идентификатором, его имя и фамилия, дата последнего обновления. Далее нам потребовалось добавить отчество в нашу таблицу, при этом мы хотим добавить новую колонку между именем и фамилией. Поехали!

pagila=# \d actor
                                         Table "public.actor"
   Column    |            Type             |                        Modifiers                         
-------------+-----------------------------+----------------------------------------------------------
 actor_id    | integer                     | not null default nextval('actor_actor_id_seq'::regclass)
 first_name  | character varying(45)       | not null
 last_name   | character varying(45)       | not null
 last_update | timestamp without time zone | not null default now()
Indexes:
    "actor_pkey" PRIMARY KEY, btree (actor_id)
    "idx_actor_last_name" btree (last_name)
Triggers:
    last_updated BEFORE UPDATE ON actor FOR EACH ROW EXECUTE PROCEDURE last_updated()

Сначала мы добавим колонку middle_name, вначале она будет последней по счету. Затем добавим новые колонки last_name и last_update, обновим данными соответствующих им исходных колонок, удалим исходные колонки, переименуем новые в исходные.

pagila=# begin;
BEGIN
pagila=# alter table actor add column middle_name text, add column lname text, add column lupdate text;
ALTER TABLE
pagila=# update actor set lname=last_name, lupdate=last_update;
UPDATE 200
pagila=# alter table actor drop column last_name cascade, drop column last_update cascade; 
NOTICE:  drop cascades to rule _RETURN on view nicer_but_slower_film_list
NOTICE:  drop cascades to view nicer_but_slower_film_list
NOTICE:  drop cascades to rule _RETURN on view film_list
NOTICE:  drop cascades to view film_list
NOTICE:  drop cascades to rule _RETURN on view actor_info
NOTICE:  drop cascades to view actor_info
ALTER TABLE
pagila=# alter table actor rename column lname to last_name;
ALTER TABLE
pagila=# alter table actor  rename column lupdate to last_update;
ALTER TABLE
pagila=# \d actor
                                      Table "public.actor"
   Column    |         Type          |                        Modifiers                         
-------------+-----------------------+----------------------------------------------------------
 actor_id    | integer               | not null default nextval('actor_actor_id_seq'::regclass)
 first_name  | character varying(45) | not null
 middle_name | text                  | 
 last_name   | text                  | 
 last_update | text                  | 
Indexes:
    "actor_pkey" PRIMARY KEY, btree (actor_id)
Triggers:
    last_updated BEFORE UPDATE ON actor FOR EACH ROW EXECUTE PROCEDURE last_updated()

И снова для полного завершения необходимо воссоздать зависимые представления, разрешить вопросы с индексами, триггерами и иными объектами при наличии.

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

Спрячь различия с помощью представления

Также существует метод изменения порядка колонок таблицы с помощью представления. В основе лежат следующие идеи: создание новых колонок в таблице, переименование таблицы, воссоздание представления с нужным порядком следования колонок и именем старой таблицы. Начиная с PostgreSQL 9.3 представления ведут себя аналогично обычным таблицам, включая операции записи. Это решает лишь логическую организацию, однако, если необходимо провести оптимизацию физической структуры, необходимо прибегнуть к методам, описанным выше.

Введение нового синтаксиса изменения колонок в PostgreSQL

Приведенные методы имеют ряд недостатков, на этом фоне неоднократно делались заявления о желании введения поддержки этой функциональности из коробки. С другой стороны, автоматическое изменение порядка в целях физической оптимизации структуры с независимым логическим представлением - весомый аргумент в пользу разработки такой возможности PostgreSQL. Камнем преткновения сейчас является факт одновременного использования идентификаторов логической и физической структур. В ближайших планах разнести ссылки на три идентификатора: постоянный идентификатор, физической и логической структур. Это позволит в запросах вида select * ссылаться на логический номер колонок, в то время, как в задачах, где требуется взаимодействие с дисковой системой - ссылаться на физический номер, а иных - просто обращаться к колонке по ее постоянному идентификатору.

• Сведения о предстоящих планах
• Список TODO, который забавно почитать и узнать предысторию

С другой стороны необходимо собрать обратную связь по синтаксису. В основном используется ключевое слово BEFORE/AFTER, за которым следует имя колонки.