Можно ли изменить естественный порядок столбцов в Postgres?

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

eg.

# CREATE TABLE test (a int, b int, c int);
# INSERT INTO test VALUES (1,2,3);
# SELECT * FROM test;
 a | b | c 
---+---+---
 1 | 2 | 3
(1 row)

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

# SELECT relname, relfilenode FROM pg_class WHERE relname='test';
 relname | relfilenode 
---------+-------------
 test_t  |       27666
(1 row)

# SELECT attrelid, attname, attnum FROM pg_attribute WHERE attrelid=27666;
 attrelid | attname  | attnum 
----------+----------+--------
    27666 | tableoid |     -7
    27666 | cmax     |     -6
    27666 | xmax     |     -5
    27666 | cmin     |     -4
    27666 | xmin     |     -3
    27666 | ctid     |     -1
    27666 | b        |      1
    27666 | a        |      2
    27666 | c        |      3
(9 rows)

attnum — это уникальный столбец, поэтому вам нужно использовать временное значение, когда вы изменяете номера столбцов как таковые:

# UPDATE pg_attribute SET attnum=4 WHERE attname='a' AND attrelid=27666;
UPDATE 1
# UPDATE pg_attribute SET attnum=1 WHERE attname='b' AND attrelid=27666;
UPDATE 1
# UPDATE pg_attribute SET attnum=2 WHERE attname='a' AND attrelid=27666;
UPDATE 1

# SELECT * FROM test;
 b | a | c 
---+---+---
 1 | 2 | 3
(1 row)

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

Это работает с postgres 8.3, с предыдущими версиями ваш пробег может отличаться.

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

1. Отключите доступ на запись в базу данных
   это необходимо, так как в противном случае любые изменения после запуска следующей точки будут потеряны
2. pg_dump --create --column-inserts databasename > databasename.pgdump.sql
3. Отредактируйте соответствующий оператор CREATE TABLE в файле databasename.pgdump.sql
   Если файл слишком велик для вашего редактора, просто разделите его с помощью команды split, отредактируйте, а затем соберите обратно с помощью cat
4. drop database databasename
   На всякий случай у вас есть свежая резервная копия?
5. psql --single-transaction -f databasename.pgdump.sql
   Если вы не используете --single-transaction, это будет очень медленно

Если вы используете так называемые большие объекты, убедитесь, что они включены в дамп. Я не уверен, есть ли они по умолчанию в 8.1.

Я задавал этот вопрос в pgsql-admin в 2007 году. Сам Том Лейн заявил, что изменить порядок в каталогах практически невозможно.

• https://archives.postgresql.org/pgsql-admin/2007-06/msg00037.php

Уточнение: это относится к пользователям с настоящими инструментами. Не значит, что это не могло быть реализовано. ИМО, так и должно быть.
Все еще верно для Postgres 12.

Вы не можете изменить порядок столбцов на месте с помощью postgres. Однако вы можете решить свою проблему с представлением. Для целей вашего запроса отчета он будет выглядеть как таблица. Что-то вроде:

create view my_view as select * from my_table order by some_col;

Если ваши данные должны оставаться на месте, вам придется жонглировать

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

-- foo is the table you want to re-order columns in
drop table foo;
CREATE TABLE foo ( moo integer, bar character varying(10), baz date ); 
insert into foo (moo, bar, baz) values (34, 'yadz', now()); 
insert into foo (moo, bar, baz) values (12, 'blerp', now()); 
select * from foo; 
    ┌─────┬───────┬────────────┐ 
    │ moo │  bar  │    baz     │ 
    ├─────┼───────┼────────────┤ 
    │  34 │ yadz  │ 2021-04-07 │ 
    │  12 │ blerp │ 2021-04-07 │ 
    └─────┴───────┴────────────┘ 
-- Define your reordered columns here, don't forget one, 
-- or it'll be missing from the replacement.
drop view if exists my_view; 
create view my_view as ( select moo, baz, bar from foo );  
select * from my_view; 
DROP TABLE IF EXISTS foo2; 
--foo2 is your replacement table that has columns ordered correctly
create table foo2 as select * from my_view; 
select * from foo2;
--finally drop the view and the original table and rename
DROP VIEW my_view; 
DROP TABLE foo; 
ALTER TABLE foo2 RENAME TO foo; 
select * from foo;
    ┌─────┬────────────┬───────┐ 
    │ moo │    baz     │  bar  │ 
    ├─────┼────────────┼───────┤ 
    │  34 │ 2021-04-07 │ yadz  │ 
    │  12 │ 2021-04-07 │ blerp │ 
    └─────┴────────────┴───────┘ 

Я хочу того же. Да, порядок не важен для моего варианта использования, но он просто неправильно меня натирает :)

Что я делаю, чтобы решить это следующим образом.

Этот метод гарантирует, что вы СОХРАНИТЕ любые существующие данные,

1. Создайте новую версию таблицы, используя порядок, который я хочу, используя временное имя.
2. Вставьте в эту новую таблицу все данные из существующей.
3. Скиньте старый стол.
4. Переименуйте новую таблицу в «собственное имя» из «временного имени».
5. Повторно добавьте все индексы, которые у вас были ранее.
6. Сбросить последовательность идентификаторов для приращений первичного ключа.

Текущий порядок столов:

id, name, email

<сильный>1. Создайте новую версию таблицы в нужном мне порядке и с временным именем.

В этом примере я хочу, чтобы email было перед name.

CREATE TABLE mytable_tmp
(
  id SERIAL PRIMARY KEY,
  email text,
  name text
);

<сильный>2. Вставьте в эту новую таблицу все данные из существующей.

INSERT INTO mytable_tmp   --- << new tmp table
(
  id
, email
, name
)
SELECT
  id
, email
, name
FROM mytable;  --- << this is the existing table

<сильный>3. Удалите старую таблицу.

DROP TABLE mytable;

<сильный>4. Переименуйте новую таблицу в «имя собственное» вместо «временное имя».

ALTER TABLE mytable_tmp RENAME TO mytable;

<сильный>5. Повторно добавьте все индексы, которые у вас были ранее.

CREATE INDEX ...

<сильный>6. Сбросить последовательность идентификаторов для приращений первичного ключа.

SELECT setval('public.mytable_id_seq', max(id)) FROM mytable;

Указание порядка столбцов в запросе — единственный надежный (и разумный) способ. Тем не менее, вы обычно можете получить другой порядок, изменив таблицу, как показано в примере ниже, поскольку столбцы обычно (не обязательно) возвращаются в том порядке, в котором они были добавлены в таблицу.

postgres=# create table a(a int, b int, c int);
CREATE TABLE
postgres=# insert into a values (1,2,3);
INSERT 0 1
postgres=# select * from a;
 a | b | c
---+---+---
 1 | 2 | 3
(1 row)

postgres=# alter table a add column a2 int;
ALTER TABLE
postgres=# select * from a;
 a | b | c | a2
---+---+---+----
 1 | 2 | 3 |
(1 row)

postgres=# update a set a2 = a;
UPDATE 1
postgres=# alter table a drop column a;
ALTER TABLE
postgres=# alter table a rename column a2 to a;
ALTER TABLE
postgres=# select * from a;
 b | c | a
---+---+---
 2 | 3 | 1
(1 row)

postgres=#

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

CREATE TABLE test_new AS SELECT b, c, a FROM test;
SELECT * from test_new;
 b | c | a 
---+---+---
 2 | 3 | 1
(1 row)

Обратите внимание, что это копирует только данные, а не модификаторы, ограничения, индексы и т. д.

Как только новая таблица будет изменена так, как вы хотите, удалите оригинал и измените имя новой:

BEGIN;
DROP TABLE test;
ALTER TABLE test_new RENAME TO test;
COMMIT;

К сожалению, нет, это не так. Порядок столбцов полностью зависит от Postgres.