Как добавить новый столбец в таблицу после 2-го или 3-го столбца в таблице с помощью postgres?

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

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

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

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

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

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

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

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

см. также

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

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

пример:

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, '_@eve_', substr(etgr,1,length(etgr)-3));
            END IF;
            etgr = '';
            ctgr = rtgr.nome;
            vtgr = vtgr || 'CREATE TRIGGER ' || ctgr || ' ' || rtgr.cond || ' _@eve_ '
                || 'ON ' || ptable || ' FOR EACH ROW ' || rtgr.act || ';';
        END IF;
        etgr = etgr || rtgr.eve || ' OR ';
    END LOOP;
    IF (vtgr <> '') THEN
        vtgr = replace(vtgr, '_@eve_', 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;

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

да.

например, если вы используете 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

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

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:

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;

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

@ Milen A. Radev

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

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

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