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

postgresql

69918 просмотра

7 ответа

13370 Репутация автора

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

Мой код выглядит следующим образом

ALTER TABLE n_domains ADD COLUMN contract_nr int after owner_id
Автор: Roland Источник Размещён: 07.08.2009 08:14

Ответы (7)


58 плюса

47413 Репутация автора

Решение

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

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

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

-1 плюса

8379 Репутация автора

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

Да.

Например, если вы используете 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
Автор: peufeu Размещён: 11.08.2009 12:59

27 плюса

293 Репутация автора

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

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

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

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

Автор: Jeremy Gustie Размещён: 08.02.2013 02:47

0 плюса

729 Репутация автора

Приведенное выше решение @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;

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

Автор: Spanky Quigman Размещён: 27.08.2013 10:59

-2 плюса

1 Репутация автора

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

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

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

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

Автор: Raithier Размещён: 17.02.2014 06:37

6 плюса

40355 Репутация автора

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

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

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

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

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

Автор: Evan Carroll Размещён: 29.04.2018 08:22

2 плюса

21 Репутация автора

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

Пример:

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;
Автор: Samuel Cunha Размещён: 10.05.2018 01:10
Вопросы из категории :
32x32