Postgres를 사용하여 테이블의 두 번째 또는 세 번째 열 뒤에 테이블에 새 열을 추가하는 방법은 무엇입니까?

Postgres를 사용하여 테이블의 두 번째 또는 세 번째 열 뒤에 테이블에 새 열을 추가하는 방법은 무엇입니까?

---

postgres를 사용하여 테이블의 두 번째 또는 세 번째 열 뒤에 테이블에 새 열을 추가하는 방법은 무엇입니까?

내 코드는 다음과 같습니다.

ALTER TABLE n_domains ADD COLUMN contract_nr int after owner_id

---

아니요, 직접 할 수있는 방법은 없습니다. 그리고 그에 대한 이유가 있습니다. 모든 쿼리는 필요한 순서 (및 형식 등)로 필요한 모든 필드를 나열해야하므로 한 테이블의 열 순서가 중요하지 않게됩니다.

정말로 그렇게해야한다면 한 가지 해결 방법을 생각할 수 있습니다.

• 문제의 테이블 설명을 덤프하고 저장 (사용 pg_dump --schema-only --table=<schema.table> ...)
• 저장된 정의에서 원하는 위치에 원하는 열을 추가하십시오.
• 테이블을 작성하려고 할 때 이전 테이블의 이름과 충돌하지 않도록 저장된 정의의 테이블 이름을 바꿉니다.
• 이 정의를 사용하여 새 테이블을 만듭니다.
• 'INSERT INTO <new_table>SELECT field1, field2 <default_for_new_field>,, field3, ... FROM <old_table>'을 사용하여 이전 테이블의 데이터로 새 테이블을 채 웁니다 .
• 이전 테이블 이름 변경
• 새 테이블의 이름을 원래 이름으로 바꿉니다.
• 결국 모든 것이 정상인지 확인한 후 이름이 변경된 이전 테이블을 삭제합니다.

---

열의 순서는 중요하지 않습니다. 고정 너비 열을 테이블 앞에 배치하면 데이터의 저장소 레이아웃을 최적화 할 수 있으며 애플리케이션 코드 외부에서 데이터 작업을 더 쉽게 할 수 있습니다.

PostgreSQL은 열 순서 변경을 지원하지 않습니다 ( PostgreSQL 위키의 열 위치 변경 참조 ). 테이블이 비교적 격리 된 경우 가장 좋은 방법은 테이블을 다시 만드는 것입니다.

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

테이블에 대해 정의 된 많은 뷰 또는 제약이있는 경우 새 열 뒤에 모든 열을 다시 추가하고 원래 열을 삭제할 수 있습니다 (예는 PostgreSQL 위키 참조).

---

여기서 진짜 문제는 아직 완료되지 않았다는 것입니다. 현재 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;

---

The order of the columns is totally irrelevant in relational databases

Yes.

For instance if you use Python, you would do :

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

Or you would do :

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

But no sane person would ever use positional results like this :

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

---

@Jeremy Gustie's solution above almost works, but will do the wrong thing if the ordinals are off (or fail altogether if the re-ordered ordinals make incompatible types match). Give it a try:

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;

The results show the problem:

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

You can remedy this by specifying the column names in the insert:

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

That gives you what you really want:

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

The problem comes when you have legacy that doesn't do this, as I indicated above in my comment on peufeu's reply.

Update: It occurred to me that you can do the same thing with the column names in the INSERT clause by specifying the column names in the SELECT clause. You just have to reorder them to match the ordinals in the target table:

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

And you can of course do both to be very explicit:

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

That gives you the same results as above, with the column values properly matched.

---

@ Milen A. Radev

The irrelevant need from having a set order of columns is not always defined by the query that pulls them. In the values from pg_fetch_row does not include the associated column name and therefore would require the columns to be defined by the SQL statement.

A simple select * from would require innate knowledge of the table structure, and would sometimes cause issues if the order of the columns were to change.

pg_fetch_assoc열 이름을 참조 할 수 있으므로을 사용하는 것이 더 안정적인 방법이므로 간단한 select * from.

참조 URL : https://stackoverflow.com/questions/1243547/how-to-add-a-new-column-in-a-table-after-the-2nd-or-3rd-column-in-the-table- 저기