Alter column position/ru

Множество людей, только начавших знакомство с PostgreSQL, часто задают вопрос на счет поддержки изменения порядка следования колонок таблицы. На текущий момент поддержка отсутствует. Чтобы изменить порядок колонок, необходимо либо пересоздать таблицу, либо создать новые колонки с последующим переносом данных. Сама по себе идея перемещения колонок разработчикам PostgreSQL не претит, просто шаги к разработке данной функциональности не предпринимались. Назначение этого документа заключается в разъяснении обходных путей и тонких моментов, которые необходимо учитывать, при желании реализовать эту возможность.
Существует две основные причины, по которым может понадобиться изменить порядок колонок в реалиях PostgreSQL:
# размещение колонок фиксированной длины в начале таблицы влияет на оптимизацию физической структуры
# измененный порядок следования колонок в результатах выборок может быть более привлекательным внешне
На текущий момент 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, за которым следует имя колонки.
Category:Administration
Category:Russian