PostgreSQL에 없는 경우 열을 추가하는 방법은 무엇입니까?

PostgreSQL에 없는 경우 열을 추가하는 방법은 무엇입니까?

질문은 간단합니다. 방법가x를 y그러나 경우에만x열이 존재하지 않습니까?열이 존재하는지 확인하는 방법만 여기서 찾았습니다.

SELECT column_name 
FROM information_schema.columns 
WHERE table_name='x' and column_name='y';

Postgres 9.6에서는 옵션을 사용하여 이 작업을 수행할 수 있습니다.if not exists

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name INTEGER;

다음은 "DO" 문을 사용한 짧은 버전입니다.

DO $$ 
    BEGIN
        BEGIN
            ALTER TABLE <table_name> ADD COLUMN <column_name> <column_type>;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column <column_name> already exists in <table_name>.';
        END;
    END;
$$

이를 매개 변수로 전달할 수 없습니다. 클라이언트 측 문자열에서 변수 대체를 수행해야 하지만, 이 쿼리는 열이 이미 존재하는 경우에만 메시지를 내보내고, 그렇지 않은 경우 메시지를 추가하며(예: 잘못된 데이터 유형) 다른 오류에서 계속 실패합니다.

외부 소스에서 오는 임의 문자열인 경우에는 이러한 방법을 수행하지 않는 것이 좋습니다.사용하는 방법(클라이언트 측 또는 서버 측 동적 문자열을 쿼리로 실행)에 관계없이 SQL 주입 공격을 받을 수 있기 때문에 재해가 발생할 수 있습니다.

Postgres 9.6 추가 ALTER TABLE tbl ADD COLUMN IF NOT EXISTS column_name.
그래서 이것은 대부분 구식입니다.이전 버전이나 변형 버전에서 사용하여 열 이름 이상을 확인할 수 있습니다.

---

CREATE OR REPLACE function f_add_col(_tbl regclass, _col  text, _type regtype)
  RETURNS bool
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF EXISTS (SELECT FROM pg_attribute
              WHERE  attrelid = _tbl
              AND    attname  = _col
              AND    NOT attisdropped) THEN
      RETURN false;
   ELSE
      EXECUTE format('ALTER TABLE %s ADD COLUMN %I %s', _tbl, _col, _type);
      RETURN true;
   END IF;
END
$func$;

통화:

SELECT f_add_col('public.kat', 'pfad1', 'int');

온다아를 반환합니다.true성공하면, 그렇지 않으면false(열이 이미 있음).
잘못된 테이블 또는 형식 이름에 대한 예외를 발생시킵니다.

왜 다른 버전입니까?

• 이 작업은 다음과 같이 수행할 수 있습니다.DO 진술, 그나러DO문은 아무것도 반환할 수 없습니다.그리고 만약 반복적으로 사용하기 위해서라면, 저는 기능을 만들 것입니다.

• 개체 식별자 유형을 사용합니다. regclass그리고.regtype위해서_tbl그리고._typea) SQL 주입을 방지하고 b) 두 가지 모두의 유효성을 즉시 확인합니다(가장 저렴한 방법). 이름 열름_col에 대해 여전히 소독해야 합니다.EXECUTE와 함께quote_ident() 항목

  • Postgre로 테이블 이름 지정SQL 함수 매개 변수

• format() Postgres 9.1+가 필요합니다.이전 버전의 경우 수동으로 연결:

    EXECUTE 'ALTER TABLE ' || _tbl || ' ADD COLUMN ' || quote_ident(_col) || ' ' || _type;
  

• 테이블 이름을 스키마로 한정할 수 있지만 그럴 필요는 없습니다.
  함수 호출에서 식별자를 이중 따옴표로 묶어서 낙타 대소문자와 예약된 단어를 보존할 수 있습니다(그러나 이 중 어떤 것도 사용해서는 안 됩니다).

• 질문합니다pg_catalog information_schema자세한 설명:

  • 지정된 스키마에 테이블이 있는지 확인하는 방법

• 가 EXCEPTION절이 상당히 느립니다.
  이것이 더 간단하고 빠릅니다.설명서:

팁.

가 들어 있는 EXCEPTIONclause는 블록이 없는 경우보다 들어가고 나가는 데 훨씬 더 많은 비용이 듭니다.그러므로 사용하지 마십시오.EXCEPTION불필요하게

선택한 후 쿼리가 반환됩니다.true/false함수를 사용합니다.

존재():
EXESS의 인수는 임의의 SELECT 문 또는 하위 쿼리입니다.하위 쿼리는 행을 반환하는지 여부를 결정하기 위해 평가됩니다.하나 이상의 행을 반환하는 경우 EXISTES의 결과는 "true"이고, 하위 쿼리가 행을 반환하지 않는 경우 EXISTES의 결과는 "false"입니다.

SELECT EXISTS(SELECT  column_name 
                FROM  information_schema.columns 
               WHERE  table_schema = 'public' 
                 AND  table_name = 'x' 
                 AND  column_name = 'y'); 

다음 동적 SQL 문을 사용하여 테이블을 변경합니다.

DO
$$
BEGIN
IF NOT EXISTS (SELECT column_name 
                 FROM  information_schema.columns 
                WHERE  table_schema = 'public' 
                  AND  table_name = 'x' 
                  AND  column_name = 'y') THEN
ALTER TABLE x ADD COLUMN y int DEFAULT NULL;
ELSE
RAISE NOTICE 'Already exists';
END IF;
END
$$

Postgre 9.5+를 사용하는 사람들을 위해 (대부분 그렇게 생각합니다) 꽤 간단하고 깨끗한 솔루션이 있습니다.

ALTER TABLE if exists <tablename> add if not exists <columnname> <columntype>

아래 함수는 열이 존재하면 확인하고 적절한 메시지를 반환하지 않으면 열을 테이블에 추가합니다.

create or replace function addcol(schemaname varchar, tablename varchar, colname varchar, coltype varchar)
returns varchar 
language 'plpgsql'
as 
$$
declare 
    col_name varchar ;
begin 
      execute 'select column_name from information_schema.columns  where  table_schema = ' ||
      quote_literal(schemaname)||' and table_name='|| quote_literal(tablename) || '   and    column_name= '|| quote_literal(colname)    
      into   col_name ;   

      raise info  ' the val : % ', col_name;
      if(col_name is null ) then 
          col_name := colname;
          execute 'alter table ' ||schemaname|| '.'|| tablename || ' add column '|| colname || '  ' || coltype; 
      else
           col_name := colname ||' Already exist';
      end if;
return col_name;
end;
$$

이것은 기본적으로 Sola의 해결책이지만, 조금 정리했습니다.제가 단지 그의 해결책을 "개선"하고 싶지 않았다는 것은 충분히 다릅니다(게다가, 저는 그것이 다소 무례하다고 생각합니다).

주요 차이점은 EXECUTE 형식을 사용한다는 것입니다.제 생각에는 좀 더 깨끗하지만, 그 말은 당신이 반드시 Postgres에 있어야 한다는 것을 의미한다고 생각합니다.SQL 9.1 이상.

이것은 9.1에서 테스트되었으며 작동합니다.참고: 스키마/table_name/또는 data_type이 올바르지 않으면 오류가 발생합니다.이는 "수정"될 수 있지만 대부분의 경우 올바른 동작일 수 있습니다.

CREATE OR REPLACE FUNCTION add_column(schema_name TEXT, table_name TEXT, 
column_name TEXT, data_type TEXT)
RETURNS BOOLEAN
AS
$BODY$
DECLARE
  _tmp text;
BEGIN

  EXECUTE format('SELECT COLUMN_NAME FROM information_schema.columns WHERE 
    table_schema=%L
    AND table_name=%L
    AND column_name=%L', schema_name, table_name, column_name)
  INTO _tmp;

  IF _tmp IS NOT NULL THEN
    RAISE NOTICE 'Column % already exists in %.%', column_name, schema_name, table_name;
    RETURN FALSE;
  END IF;

  EXECUTE format('ALTER TABLE %I.%I ADD COLUMN %I %s;', schema_name, table_name, column_name, data_type);

  RAISE NOTICE 'Column % added to %.%', column_name, schema_name, table_name;

  RETURN TRUE;
END;
$BODY$
LANGUAGE 'plpgsql';

용도:

select add_column('public', 'foo', 'bar', 'varchar(30)');

마이그레이션 스크립트에 추가할 수 있습니다. 실행되면 함수를 호출하고 삭제합니다.

create or replace function patch_column() returns void as
$$
begin
    if exists (
        select * from information_schema.columns
            where table_name='my_table'
            and column_name='missing_col'
     )
    then
        raise notice 'missing_col already exists';
    else
        alter table my_table
            add column missing_col varchar;
    end if;
end;
$$ language plpgsql;

select patch_column();

drop function if exists patch_column();

저의 경우 어떻게 생성되었는지에 대한 이유로 마이그레이션 스크립트가 서로 다른 스키마를 분할하는 것이 조금 어렵습니다.

이 문제를 해결하기 위해 오류를 감지하고 무시하는 예외를 사용했습니다.이것은 또한 보기에 훨씬 더 쉬워지는 좋은 부작용을 가지고 있었습니다.

그러나 다른 솔루션에는 이 솔루션을 능가하는 자체적인 이점이 있습니다.

DO $$
BEGIN
  BEGIN
    ALTER TABLE IF EXISTS bobby_tables RENAME COLUMN "dckx" TO "xkcd";
  EXCEPTION
    WHEN undefined_column THEN RAISE NOTICE 'Column was already renamed';
  END;
END $$;

당신은 다음 방법으로 그것을 할 수 있습니다.

ALTER TABLE tableName drop column if exists columnName; 
ALTER TABLE tableName ADD COLUMN columnName character varying(8);

따라서 열이 이미 있는 경우 열을 삭제합니다.그런 다음 특정 테이블에 열을 추가합니다.

쿼리에서 columnn_name을 반환했는지 확인하기만 하면 됩니다.

그렇지 않은 경우 다음과 같은 작업을 실행합니다.

ALTER TABLE x ADD COLUMN y int;

당신이 'x'와 'y'에 유용한 것을 넣는 곳, 그리고 물론 내가 사용했던 곳에 적합한 데이터 유형.

언급URL : https://stackoverflow.com/questions/12597465/how-to-add-column-if-not-exists-on-postgresql