Как добавить новый столбец в таблицу после второго или третьего столбца в таблице, используя postgres?

Ответ 1

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

Если вам действительно нужно это сделать, я могу подумать об одном обходном пути:

• дамп и сохраните описание рассматриваемой таблицы (используя pg_dump --schema-only --table=<schema.table> ...)
• добавьте нужный столбец, где вы хотите, в сохраненное определение
• переименуйте таблицу в сохраненное определение, чтобы не сталкиваться с именем старой таблицы при попытке ее создать.
• создайте новую таблицу, используя это определение
• заполнить новую таблицу данными из старой таблицы, используя "INSERT INTO <new_table> SELECT field1, field2, <default_for_new_field>, field3,... FROM <old_table> ';
• переименовать старую таблицу
• переименуйте новую таблицу в исходное имя
• в конечном итоге отбросить старую, переименованную таблицу после того, как вы убедитесь, что все в порядке

Ответ 2

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

PostgreSQL не поддерживает изменение порядка упорядочения столбцов (см. Изменить положение столбца в wiki PostgreSQL); если таблица относительно изолирована, лучше всего воссоздать таблицу:

CREATE TABLE foobar_new ( ... );
INSERT INTO foobar_new SELECT ... FROM foobar;
DROP TABLE foobar CASCADE;
ALTER TABLE foobar_new RENAME TO foobar;

Если у вас есть много представлений или ограничений, определенных для таблицы, вы можете повторно добавить все столбцы после нового столбца и отбросить исходные столбцы (например, в вики Wiki для PostgreSQL).

Ответ 3

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

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

Все, что было сказано, это хорошая идея полагаться на порядковое позиционирование столбцов, логических или физических? Конечно нет. В производственном коде вы никогда не должны использовать неявный порядок или *. Зачем сделать код более хрупким, чем нужно? Правильность всегда должна быть более приоритетной, чем сохранение нескольких нажатий клавиш.

Как работа вокруг, вы можете на самом деле изменить порядок столбцов, воссоздав таблицу, или через игру "добавить и изменить порядок"

Смотрите также,

• Переупорядочение столбцов tetris, чтобы сделать вещи более экономичными по площади

Ответ 4

Порядок столбцов имеет отношение ко мне, поэтому я создал эту функцию. Посмотрите, помогает ли это. Он работает с индексами, первичным ключом и триггерами. Отсутствуют отсутствующие виды и внешний ключ и другие функции.

Пример:

SELECT xaddcolumn('table', 'col3 int NOT NULL DEFAULT 0', 'col2');

Исходный код:

CREATE OR REPLACE FUNCTION xaddcolumn(ptable text, pcol text, pafter text)  RETURNS void AS $BODY$
DECLARE
    rcol RECORD;
    rkey RECORD;
    ridx RECORD;
    rtgr RECORD;
    vsql text;
    vkey text;
    vidx text;
    cidx text;
    vtgr text;
    ctgr text;
    etgr text;
    vseq text;
    vtype text;
    vcols text;
BEGIN
    EXECUTE 'CREATE TABLE zzz_' || ptable || ' AS SELECT * FROM ' || ptable;
    --colunas
    vseq = '';
    vcols = '';
    vsql = 'CREATE TABLE ' || ptable || '(';
    FOR rcol IN SELECT column_name as col, udt_name as coltype, column_default as coldef,
        is_nullable as is_null, character_maximum_length as len,
        numeric_precision as num_prec, numeric_scale as num_scale
        FROM information_schema.columns
        WHERE table_name = ptable
        ORDER BY ordinal_position
    LOOP
        vtype = rcol.coltype;
        IF (substr(rcol.coldef,1,7) = 'nextval') THEN
            vtype = 'serial';
            vseq = vseq || 'SELECT setval(''' || ptable || '_' || rcol.col || '_seq'''
                || ', max(' || rcol.col || ')) FROM ' || ptable || ';';
        ELSIF (vtype = 'bpchar') THEN
            vtype = 'char';
        END IF;
        vsql = vsql || E'\n' || rcol.col || ' ' || vtype;
        IF (vtype in ('varchar', 'char')) THEN
            vsql = vsql || '(' || rcol.len || ')';
        ELSIF (vtype = 'numeric') THEN
            vsql = vsql || '(' || rcol.num_prec || ',' || rcol.num_scale || ')';
        END IF;
        IF (rcol.is_null = 'NO') THEN
            vsql = vsql || ' NOT NULL';
        END IF;
        IF (rcol.coldef <> '' AND vtype <> 'serial') THEN
            vsql = vsql || ' DEFAULT ' || rcol.coldef;
        END IF;
        vsql = vsql || E',';
        vcols = vcols || rcol.col || ',';
        --
        IF (rcol.col = pafter) THEN
            vsql = vsql || E'\n' || pcol || ',';
        END IF;
    END LOOP;
    vcols = substr(vcols,1,length(vcols)-1);
    --keys
    vkey = '';
    FOR rkey IN SELECT constraint_name as name, column_name as col
        FROM information_schema.key_column_usage
        WHERE table_name = ptable
    LOOP
        IF (vkey = '') THEN
            vkey = E'\nCONSTRAINT ' || rkey.name || ' PRIMARY KEY (';
        END IF;
        vkey = vkey || rkey.col || ',';
    END LOOP;
    IF (vkey <> '') THEN
        vsql = vsql || substr(vkey,1,length(vkey)-1) || ') ';
    END IF;
    vsql = substr(vsql,1,length(vsql)-1) || ') WITHOUT OIDS';
    --index
    vidx = '';
    cidx = '';
    FOR ridx IN SELECT s.indexrelname as nome, a.attname as col
        FROM pg_index i LEFT JOIN pg_class c ON c.oid = i.indrelid
        LEFT JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(i.indkey)
        LEFT JOIN pg_stat_user_indexes s USING (indexrelid)
        WHERE c.relname = ptable AND i.indisunique != 't' AND i.indisprimary != 't'
        ORDER BY s.indexrelname
    LOOP
        IF (ridx.nome <> cidx) THEN
            IF (vidx <> '') THEN
                vidx = substr(vidx,1,length(vidx)-1) || ');';
            END IF;
            cidx = ridx.nome;
            vidx = vidx || E'\nCREATE INDEX ' || cidx || ' ON ' || ptable || ' (';
        END IF;
        vidx = vidx || ridx.col || ',';
    END LOOP;
    IF (vidx <> '') THEN
        vidx = substr(vidx,1,length(vidx)-1) || ')';
    END IF;
    --trigger
    vtgr = '';
    ctgr = '';
    etgr = '';
    FOR rtgr IN SELECT trigger_name as nome, event_manipulation as eve,
        action_statement as act, condition_timing as cond
        FROM information_schema.triggers
        WHERE event_object_table = ptable
    LOOP
        IF (rtgr.nome <> ctgr) THEN
            IF (vtgr <> '') THEN
                vtgr = replace(vtgr, '[email protected]_', substr(etgr,1,length(etgr)-3));
            END IF;
            etgr = '';
            ctgr = rtgr.nome;
            vtgr = vtgr || 'CREATE TRIGGER ' || ctgr || ' ' || rtgr.cond || ' [email protected]_ '
                || 'ON ' || ptable || ' FOR EACH ROW ' || rtgr.act || ';';
        END IF;
        etgr = etgr || rtgr.eve || ' OR ';
    END LOOP;
    IF (vtgr <> '') THEN
        vtgr = replace(vtgr, '[email protected]_', substr(etgr,1,length(etgr)-3));
    END IF;
    --exclui velha e cria nova
    EXECUTE 'DROP TABLE ' || ptable;
    IF (EXISTS (SELECT sequence_name FROM information_schema.sequences
        WHERE sequence_name = ptable||'_id_seq'))
    THEN
        EXECUTE 'DROP SEQUENCE '||ptable||'_id_seq';
    END IF;
    EXECUTE vsql;
    --dados na nova
    EXECUTE 'INSERT INTO ' || ptable || '(' || vcols || ')' ||
        E'\nSELECT ' || vcols || ' FROM zzz_' || ptable;
    EXECUTE vseq;
    EXECUTE vidx;
    EXECUTE vtgr;
    EXECUTE 'DROP TABLE zzz_' || ptable;
END;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;

Ответ 5

Порядок столбцов совершенно не имеет отношения к реляционным базам данных

Да.

Например, если вы используете Python, вы бы сделали:

cursor.execute( "SELECT id, name FROM users" )
for id, name in cursor:
    print id, name

Или вы бы сделали:

cursor.execute( "SELECT * FROM users" )
for row in cursor:
    print row['id'], row['name']

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

cursor.execute( "SELECT * FROM users" )
for id, name in cursor:
   print id, name

Ответ 6

@Jeremy Gustie выше почти работает, но будет делать неправильную вещь, если ординалы отключены (или вообще не сработают, если переупорядоченные ординалы сделают несовместимые типы совпадающими). Попробуйте:

CREATE TABLE test1 (one varchar, two varchar, three varchar);
CREATE TABLE test2 (three varchar, two varchar, one varchar);
INSERT INTO test1 (one, two, three) VALUES ('one', 'two', 'three');
INSERT INTO test2 SELECT * FROM test1;
SELECT * FROM test2;

Результаты показывают проблему:

testdb=> select * from test2;
 three | two |  one
-------+-----+-------
 one   | two | three
(1 row)

Вы можете исправить это, указав имена столбцов в вставке:

INSERT INTO test2 (one, two, three) SELECT * FROM test1;

Это дает вам то, что вы действительно хотите:

testdb=> select * from test2;
 three | two | one
-------+-----+-----
 three | two | one
(1 row)

Проблема возникает, когда у вас есть наследие, которое этого не делает, как я указал выше в своем комментарии к ответу peufeu.

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

INSERT INTO test2 SELECT three, two, one FROM test1;

И вы можете, конечно, сделать так, чтобы они были очень явными:

INSERT INTO test2 (one, two, three) SELECT one, two, three FROM test1;

Это дает вам те же результаты, что и выше, с соответствующими значениями столбцов.

Ответ 7

@Милен А. Радев

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

Простой select * from потребовал бы врожденного знания структуры таблицы и иногда вызывал бы проблемы, если бы порядок столбцов изменился.

Использование pg_fetch_assoc является более надежным методом, так как вы можете ссылаться на имена столбцов и, следовательно, использовать простой select * from.