postgre의 기존 테이블에 대해 "create table" sql 문을 생성하는 방법SQL

it-source

postgre의 기존 테이블에 대해 "create table" sql 문을 생성하는 방법SQL

criticalcode 2023. 6. 5. 23:59

postgre의 기존 테이블에 대해 "create table" sql 문을 생성하는 방법SQL

postgreSQL에 테이블을 만들었습니다.테이블 생성에 사용된 SQL 문을 보고 싶은데 확인할 수 없습니다.

어떻게 해야 하나요?create table명령줄 또는 SQL 문을 통해 Postgres의 기존 테이블에 대한 SQL 문을 선택하십시오.

pg_dump -t 'schema-name.table-name' --schema-only database-name

자세한 내용은 설명서를 참조하십시오.

(참고 - 이 솔루션은 Postgre와 함께 작동하지 않습니다.SQL v12+)

해결책은 다음과 같이 -E 옵션을 사용하여 postgres db에 로그인하는 것입니다.

psql -E -U username -d database

가 psql을 할 때 합니다.
설명 표 문:

-- List all tables in the schema (my example schema name is public)
\dt public.*
-- Choose a table name from above
-- For create table of one public.tablename
\d+ public.tablename

명령어를 한 후 으로, 는 sql과 sql을 할 수 .
plpgsql 파일:

CREATE OR REPLACE FUNCTION generate_create_table_statement(p_table_name varchar)
  RETURNS text AS
$BODY$
DECLARE
    v_table_ddl   text;
    column_record record;
BEGIN
    FOR column_record IN 
        SELECT 
            b.nspname as schema_name,
            b.relname as table_name,
            a.attname as column_name,
            pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
            CASE WHEN 
                (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                 FROM pg_catalog.pg_attrdef d
                 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
                'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                              FROM pg_catalog.pg_attrdef d
                              WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
            ELSE
                ''
            END as column_default_value,
            CASE WHEN a.attnotnull = true THEN 
                'NOT NULL'
            ELSE
                'NULL'
            END as column_not_null,
            a.attnum as attnum,
            e.max_attnum as max_attnum
        FROM 
            pg_catalog.pg_attribute a
            INNER JOIN 
             (SELECT c.oid,
                n.nspname,
                c.relname
              FROM pg_catalog.pg_class c
                   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
              WHERE c.relname ~ ('^('||p_table_name||')$')
                AND pg_catalog.pg_table_is_visible(c.oid)
              ORDER BY 2, 3) b
            ON a.attrelid = b.oid
            INNER JOIN 
             (SELECT 
                  a.attrelid,
                  max(a.attnum) as max_attnum
              FROM pg_catalog.pg_attribute a
              WHERE a.attnum > 0 
                AND NOT a.attisdropped
              GROUP BY a.attrelid) e
            ON a.attrelid=e.attrelid
        WHERE a.attnum > 0 
          AND NOT a.attisdropped
        ORDER BY a.attnum
    LOOP
        IF column_record.attnum = 1 THEN
            v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
        ELSE
            v_table_ddl:=v_table_ddl||',';
        END IF;

        IF column_record.attnum <= column_record.max_attnum THEN
            v_table_ddl:=v_table_ddl||chr(10)||
                     '    '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null;
        END IF;
    END LOOP;

    v_table_ddl:=v_table_ddl||');';
    RETURN v_table_ddl;
END;
$BODY$
  LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;

다음은 기능 사용 방법입니다.

SELECT generate_create_table_statement('tablename');

이 기능을 영구적으로 유지하지 않으려면 다음과 같은 drop 문이 있습니다.

DROP FUNCTION generate_create_table_statement(p_table_name varchar);

linux 명령줄에서 postgresql의 테이블에 대한 create table 문을 생성합니다.

데모용 테이블 만들기:

CREATE TABLE your_table(
    thekey   integer NOT NULL,  
    ticker   character varying(10) NOT NULL,
    date_val date,
    open_val numeric(10,4) NOT NULL
);

pg_create psql manual, 테이블 create psql 문을 출력할 수 있습니다.

pg_dump -U your_user your_database -t your_table --schema-only

인쇄 대상:

-- pre-requisite database and table configuration omitted
CREATE TABLE your_table (
    thekey integer NOT NULL, 
    ticker character varying(10) NOT NULL, 
    date_val date,  
    open_val numeric(10,4) NOT NULL 
); 
-- post-requisite database and table configuration omitted

설명:

.pg _dll에 대한 정보를 얻는 데 . -U사용자 이름을 나타냅니다.제 pgadmin 사용자는 비밀번호가 설정되어 있지 않아서 비밀번호를 입력할 필요가 없습니다. 그-t옵션은 한 테이블에 대해 지정하는 것을 의미합니다. --schema-only표의 데이터만 인쇄하고 표의 데이터는 인쇄하지 않음을 의미합니다.

pg_dump진화하는 sql 표준을 잘 처리하기 위해 노력하고 postgresql의 쿼리 언어와 디스크의 표현 사이에 발생하는 수천 가지 세부 사항을 처리하는 엘리트 C 코드입니다.psql 디스크를 롤업하여 문을 만들고 싶다면 드래곤즈: https://doxygen.postgresql.org/pg__dump_8c_source.html

이동할 수 있는 다른 옵션pg_dump테이블을 생성할 때 테이블을 저장하고 SQL 문을 생성합니다.안전한 곳에 보관하고 필요할 때 회수하세요.

또는 SQL을 사용하여 postgresql에서 테이블 이름, 열 이름 및 데이터 유형 정보를 가져옵니다.

CREATE TABLE your_table(  thekey integer NOT NULL,
                          ticker character varying(10) NOT NULL,
                          date_val date,
                          open_val numeric(10,4) NOT NULL
); 

SELECT table_name, column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'your_table';

인쇄 대상:

┌────────────┬─────────────┬───────────────────┐ 
│ table_name │ column_name │     data_type     │ 
├────────────┼─────────────┼───────────────────┤ 
│ your_table │ thekey      │ integer           │ 
│ your_table │ ticker      │ character varying │ 
│ your_table │ date_val    │ date              │ 
│ your_table │ open_val    │ numeric           │ 
└────────────┴─────────────┴───────────────────┘

pg_dump를 사용하지 않고 테이블에 대한 create 문을 찾으려면 이 쿼리가 사용할 수 있습니다(테이블 이름을 테이블 이름으로 변경).

SELECT                                          
  'CREATE TABLE ' || relname || E'\n(\n' ||
  array_to_string(
    array_agg(
      '    ' || column_name || ' ' ||  type || ' '|| not_null
    )
    , E',\n'
  ) || E'\n);\n'
from
(
  SELECT 
    c.relname, a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) as type,
    case 
      when a.attnotnull
    then 'NOT NULL' 
    else 'NULL' 
    END as not_null 
  FROM pg_class c,
   pg_attribute a,
   pg_type t
   WHERE c.relname = 'tablename'
   AND a.attnum > 0
   AND a.attrelid = c.oid
   AND a.atttypid = t.oid
 ORDER BY a.attnum
) as tabledefinition
group by relname;

psql에서 직접 호출할 경우 다음 작업을 수행하는 것이 유용합니다.

\pset linestyle old-ascii

또한 이 스레드의 generate_create_table_statement 함수는 매우 잘 작동합니다.

딘 토더 아주 훌륭해요!테이블에 있는 모든 제약 조건을 표시하고 테이블 이름에 regexp 마스크를 사용할 수 있도록 코드를 조금 수정하겠습니다.

CREATE OR REPLACE FUNCTION public.generate_create_table_statement(p_table_name character varying)
  RETURNS SETOF text AS
$BODY$
DECLARE
    v_table_ddl   text;
    column_record record;
    table_rec record;
    constraint_rec record;
    firstrec boolean;
BEGIN
    FOR table_rec IN
        SELECT c.relname FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                WHERE relkind = 'r'
                AND relname~ ('^('||p_table_name||')$')
                AND n.nspname <> 'pg_catalog'
                AND n.nspname <> 'information_schema'
                AND n.nspname !~ '^pg_toast'
                AND pg_catalog.pg_table_is_visible(c.oid)
          ORDER BY c.relname
    LOOP

        FOR column_record IN 
            SELECT 
                b.nspname as schema_name,
                b.relname as table_name,
                a.attname as column_name,
                pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
                CASE WHEN 
                    (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                     FROM pg_catalog.pg_attrdef d
                     WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
                    'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                                  FROM pg_catalog.pg_attrdef d
                                  WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
                ELSE
                    ''
                END as column_default_value,
                CASE WHEN a.attnotnull = true THEN 
                    'NOT NULL'
                ELSE
                    'NULL'
                END as column_not_null,
                a.attnum as attnum,
                e.max_attnum as max_attnum
            FROM 
                pg_catalog.pg_attribute a
                INNER JOIN 
                 (SELECT c.oid,
                    n.nspname,
                    c.relname
                  FROM pg_catalog.pg_class c
                       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                  WHERE c.relname = table_rec.relname
                    AND pg_catalog.pg_table_is_visible(c.oid)
                  ORDER BY 2, 3) b
                ON a.attrelid = b.oid
                INNER JOIN 
                 (SELECT 
                      a.attrelid,
                      max(a.attnum) as max_attnum
                  FROM pg_catalog.pg_attribute a
                  WHERE a.attnum > 0 
                    AND NOT a.attisdropped
                  GROUP BY a.attrelid) e
                ON a.attrelid=e.attrelid
            WHERE a.attnum > 0 
              AND NOT a.attisdropped
            ORDER BY a.attnum
        LOOP
            IF column_record.attnum = 1 THEN
                v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
            ELSE
                v_table_ddl:=v_table_ddl||',';
            END IF;

            IF column_record.attnum <= column_record.max_attnum THEN
                v_table_ddl:=v_table_ddl||chr(10)||
                         '    '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null;
            END IF;
        END LOOP;

        firstrec := TRUE;
        FOR constraint_rec IN
            SELECT conname, pg_get_constraintdef(c.oid) as constrainddef 
                FROM pg_constraint c 
                    WHERE conrelid=(
                        SELECT attrelid FROM pg_attribute
                        WHERE attrelid = (
                            SELECT oid FROM pg_class WHERE relname = table_rec.relname
                        ) AND attname='tableoid'
                    )
        LOOP
            v_table_ddl:=v_table_ddl||','||chr(10);
            v_table_ddl:=v_table_ddl||'CONSTRAINT '||constraint_rec.conname;
            v_table_ddl:=v_table_ddl||chr(10)||'    '||constraint_rec.constrainddef;
            firstrec := FALSE;
        END LOOP;
        v_table_ddl:=v_table_ddl||');';
        RETURN NEXT v_table_ddl;
    END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.generate_create_table_statement(character varying)
  OWNER TO postgres;

예를 들어, 이제 다음과 같은 쿼리를 만들 수 있습니다.

SELECT * FROM generate_create_table_statement('.*');

그 결과는 다음과 같습니다.

CREATE TABLE public.answer (                                                                        
     id integer DEFAULT nextval('answer_id_seq'::regclass) NOT NULL,                               
     questionid integer  NOT NULL,                                                                  
     title character varying  NOT NULL,                                                             
     defaultvalue character varying  NULL,                                                          
     valuetype integer  NOT NULL,                                                                   
     isdefault boolean  NULL,                                                                       
     minval double precision  NULL,                                                                 
     maxval double precision  NULL,                                                                 
     followminmax integer DEFAULT 0 NOT NULL,                                                       
CONSTRAINT answer_pkey                                                                              
     PRIMARY KEY (id),                                                                              
CONSTRAINT answer_questionid_fkey                                                                  
     FOREIGN KEY (questionid) REFERENCES question(id) ON UPDATE RESTRICT ON DELETE RESTRICT,       
CONSTRAINT answer_valuetype_fkey                                                                   
     FOREIGN KEY (valuetype) REFERENCES answervaluetype(id) ON UPDATE RESTRICT ON DELETE RESTRICT);

각 사용자 테이블에 대해.

제가 생각할 수 있는 가장 쉬운 방법은 pgAdmin 3(여기 참조)을 설치하고 데이터베이스를 보는 데 사용하는 것입니다.자동으로 쿼리를 생성하여 문제의 테이블을 만듭니다.

여기 오래된 질문에 대한 또 다른 해결책이 있습니다.수년간 이 질문에 대한 많은 훌륭한 답변들이 있었고 저의 시도는 그들로부터 많은 것을 빌렸습니다.

Andrey Lebedenko의 솔루션을 시작점으로 사용했습니다. 출력이 이미 제 요구 사항에 매우 근접했기 때문입니다.

특징:

일반적인 관행에 따라 외부 키 제약 조건을 테이블 정의 밖으로 이동했습니다.이제 이들은 하단에 ALTER TABLE 문으로 포함됩니다.외부 키는 동일한 테이블의 열에도 연결할 수 있기 때문입니다.이 경우 테이블 작성이 완료된 후에만 제약 조건을 작성할 수 있습니다.그렇지 않으면 테이블 만들기 문에서 오류가 발생할 수 있습니다.
레이아웃과 들여쓰기가 지금 더 좋아 보입니다(적어도 제 눈에는).
정의 헤더에 명령을 삭제합니다(댓글로 표시됨).
솔루션은 여기서 plpgsql 함수로 제공됩니다.그러나 알고리즘은 절차적 언어를 사용하지 않습니다.함수는 순수한 SQL 컨텍스트에서도 사용할 수 있는 단일 쿼리만 래핑합니다.
중복 하위 쿼리 제거
이제 식별자가 예약된 postgresql 언어 요소와 동일한 경우 따옴표로 묶입니다.
문자열 연결 연산자 ||를 적절한 문자열 함수로 대체하여 코드의 성능, 보안 및 가독성을 향상시켰습니다.참고: || 연산자는 결합된 문자열 중 하나가 NULL인 경우 NULL을 생성합니다.원하는 동작일 때만 사용해야 합니다.(예를 들어 아래 코드의 사용법을 확인하십시오.)

CREATE OR REPLACE FUNCTION public.wmv_get_table_definition (
    p_schema_name character varying,
    p_table_name character varying
)
    RETURNS SETOF TEXT
    AS $BODY$
BEGIN
    RETURN query 
    WITH table_rec AS (
        SELECT
            c.relname, n.nspname, c.oid
        FROM
            pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE
            relkind = 'r'
            AND n.nspname = p_schema_name
            AND c.relname LIKE p_table_name
        ORDER BY
            c.relname
    ),
    col_rec AS (
        SELECT
            a.attname AS colname,
            pg_catalog.format_type(a.atttypid, a.atttypmod) AS coltype,
            a.attrelid AS oid,
            ' DEFAULT ' || (
                SELECT
                    pg_catalog.pg_get_expr(d.adbin, d.adrelid)
                FROM
                    pg_catalog.pg_attrdef d
                WHERE
                    d.adrelid = a.attrelid
                    AND d.adnum = a.attnum
                    AND a.atthasdef) AS column_default_value,
            CASE WHEN a.attnotnull = TRUE THEN
                'NOT NULL'
            ELSE
                'NULL'
            END AS column_not_null,
            a.attnum AS attnum
        FROM
            pg_catalog.pg_attribute a
        WHERE
            a.attnum > 0
            AND NOT a.attisdropped
        ORDER BY
            a.attnum
    ),
    con_rec AS (
        SELECT
            conrelid::regclass::text AS relname,
            n.nspname,
            conname,
            pg_get_constraintdef(c.oid) AS condef,
            contype,
            conrelid AS oid
        FROM
            pg_constraint c
            JOIN pg_namespace n ON n.oid = c.connamespace
    ),
    glue AS (
        SELECT
            format( E'-- %1$I.%2$I definition\n\n-- Drop table\n\n-- DROP TABLE IF EXISTS %1$I.%2$I\n\nCREATE TABLE %1$I.%2$I (\n', table_rec.nspname, table_rec.relname) AS top,
            format( E'\n);\n\n\n-- adempiere.wmv_ghgaudit foreign keys\n\n', table_rec.nspname, table_rec.relname) AS bottom,
            oid
        FROM
            table_rec
    ),
    cols AS (
        SELECT
            string_agg(format('    %I %s%s %s', colname, coltype, column_default_value, column_not_null), E',\n') AS lines,
            oid
        FROM
            col_rec
        GROUP BY
            oid
    ),
    constrnt AS (
        SELECT
            string_agg(format('    CONSTRAINT %s %s', con_rec.conname, con_rec.condef), E',\n') AS lines,
            oid
        FROM
            con_rec
        WHERE
            contype <> 'f'
        GROUP BY
            oid
    ),
    frnkey AS (
        SELECT
            string_agg(format('ALTER TABLE %I.%I ADD CONSTRAINT %s %s', nspname, relname, conname, condef), E';\n') AS lines,
            oid
        FROM
            con_rec
        WHERE
            contype = 'f'
        GROUP BY
            oid
    )
    SELECT
        concat(glue.top, cols.lines, E',\n', constrnt.lines, glue.bottom, frnkey.lines, ';')
    FROM
        glue
        JOIN cols ON cols.oid = glue.oid
        LEFT JOIN constrnt ON constrnt.oid = glue.oid
        LEFT JOIN frnkey ON frnkey.oid = glue.oid;
END;
$BODY$
LANGUAGE plpgsql;

여러 테이블에 대해 동시에 이 작업을 수행하려면 -t 스위치를 여러 번 사용해야 합니다(콤마 구분 목록이 작동하지 않는 이유를 파악하는 데 시간이 걸렸습니다).또한 결과를 다른 컴퓨터의 postgres 서버로 아웃파일 또는 파이프로 보내는 데 유용할 수 있습니다.

pg_dump -t table1 -t table2 database_name --schema-only > dump.sql

pg_dump -t table1 -t table2 database_name --schema-only | psql -h server_name database_name

@vkkeeper의 응답에 기반한 훨씬 더 많은 수정.특정 스키마에서 테이블을 쿼리할 수 있습니다.

CREATE OR REPLACE FUNCTION public.describe_table(p_schema_name character varying, p_table_name character varying)
  RETURNS SETOF text AS
$BODY$
DECLARE
    v_table_ddl   text;
    column_record record;
    table_rec record;
    constraint_rec record;
    firstrec boolean;
BEGIN
    FOR table_rec IN
        SELECT c.relname, c.oid FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                WHERE relkind = 'r'
                AND n.nspname = p_schema_name
                AND relname~ ('^('||p_table_name||')$')
          ORDER BY c.relname
    LOOP
        FOR column_record IN
            SELECT
                b.nspname as schema_name,
                b.relname as table_name,
                a.attname as column_name,
                pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
                CASE WHEN
                    (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                     FROM pg_catalog.pg_attrdef d
                     WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
                    'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                                  FROM pg_catalog.pg_attrdef d
                                  WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
                ELSE
                    ''
                END as column_default_value,
                CASE WHEN a.attnotnull = true THEN
                    'NOT NULL'
                ELSE
                    'NULL'
                END as column_not_null,
                a.attnum as attnum,
                e.max_attnum as max_attnum
            FROM
                pg_catalog.pg_attribute a
                INNER JOIN
                 (SELECT c.oid,
                    n.nspname,
                    c.relname
                  FROM pg_catalog.pg_class c
                       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                  WHERE c.oid = table_rec.oid
                  ORDER BY 2, 3) b
                ON a.attrelid = b.oid
                INNER JOIN
                 (SELECT
                      a.attrelid,
                      max(a.attnum) as max_attnum
                  FROM pg_catalog.pg_attribute a
                  WHERE a.attnum > 0
                    AND NOT a.attisdropped
                  GROUP BY a.attrelid) e
                ON a.attrelid=e.attrelid
            WHERE a.attnum > 0
              AND NOT a.attisdropped
            ORDER BY a.attnum
        LOOP
            IF column_record.attnum = 1 THEN
                v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
            ELSE
                v_table_ddl:=v_table_ddl||',';
            END IF;

            IF column_record.attnum <= column_record.max_attnum THEN
                v_table_ddl:=v_table_ddl||chr(10)||
                         '    '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null;
            END IF;
        END LOOP;

        firstrec := TRUE;
        FOR constraint_rec IN
            SELECT conname, pg_get_constraintdef(c.oid) as constrainddef
                FROM pg_constraint c
                    WHERE conrelid=(
                        SELECT attrelid FROM pg_attribute
                        WHERE attrelid = (
                            SELECT oid FROM pg_class WHERE relname = table_rec.relname
                                AND relnamespace = (SELECT ns.oid FROM pg_namespace ns WHERE ns.nspname = p_schema_name)
                        ) AND attname='tableoid'
                    )
        LOOP
            v_table_ddl:=v_table_ddl||','||chr(10);
            v_table_ddl:=v_table_ddl||'CONSTRAINT '||constraint_rec.conname;
            v_table_ddl:=v_table_ddl||chr(10)||'    '||constraint_rec.constrainddef;
            firstrec := FALSE;
        END LOOP;
        v_table_ddl:=v_table_ddl||');';
        RETURN NEXT v_table_ddl;
    END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

다음은 제약 조건을 포함하여 지정된 스키마의 단일 테이블에 대한 DDL을 생성하는 단일 문입니다.

SELECT 'CREATE TABLE ' || pn.nspname || '.' || pc.relname || E'(\n' ||
   string_agg(pa.attname || ' ' || pg_catalog.format_type(pa.atttypid, pa.atttypmod) || coalesce(' DEFAULT ' || (
                                                                                                               SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
                                                                                                               FROM pg_catalog.pg_attrdef d
                                                                                                               WHERE d.adrelid = pa.attrelid
                                                                                                                 AND d.adnum = pa.attnum
                                                                                                                 AND pa.atthasdef
                                                                                                               ),
                                                                                                 '') || ' ' ||
              CASE pa.attnotnull
                  WHEN TRUE THEN 'NOT NULL'
                  ELSE 'NULL'
              END, E',\n') ||
   coalesce((SELECT E',\n' || string_agg('CONSTRAINT ' || pc1.conname || ' ' || pg_get_constraintdef(pc1.oid), E',\n' ORDER BY pc1.conindid)
            FROM pg_constraint pc1
            WHERE pc1.conrelid = pa.attrelid), '') ||
   E');'
FROM pg_catalog.pg_attribute pa
JOIN pg_catalog.pg_class pc
    ON pc.oid = pa.attrelid
    AND pc.relname = 'table_name'
JOIN pg_catalog.pg_namespace pn
    ON pn.oid = pc.relnamespace
    AND pn.nspname = 'schema_name'
WHERE pa.attnum > 0
    AND NOT pa.attisdropped
GROUP BY pn.nspname, pc.relname, pa.attrelid;

PgAdmin4가 있으면 엽니다.데이터베이스로 이동합니다--> 스키마---> 테이블--> 원하는 스크립트를 만들 테이블 이름을 마우스 오른쪽 단추로 클릭합니다---> 스크립트---> 스크립트 생성

여기 셰키 쿼리의 약간 개선된 버전이 있습니다.
기본 키 제약 조건을 생성하고 임시 테이블을 처리할 수 있습니다.

with pkey as
(
    select cc.conrelid, format(E',
    constraint %I primary key(%s)', cc.conname,
        string_agg(a.attname, ', ' 
            order by array_position(cc.conkey, a.attnum))) pkey
    from pg_catalog.pg_constraint cc
        join pg_catalog.pg_class c on c.oid = cc.conrelid
        join pg_catalog.pg_attribute a on a.attrelid = cc.conrelid 
            and a.attnum = any(cc.conkey)
    where cc.contype = 'p'
    group by cc.conrelid, cc.conname
)
select format(E'create %stable %s%I\n(\n%s%s\n);\n',
    case c.relpersistence when 't' then 'temporary ' else '' end,
    case c.relpersistence when 't' then '' else n.nspname || '.' end,
    c.relname,
    string_agg(
        format(E'\t%I %s%s',
            a.attname,
            pg_catalog.format_type(a.atttypid, a.atttypmod),
            case when a.attnotnull then ' not null' else '' end
        ), E',\n'
        order by a.attnum
    ),
    (select pkey from pkey where pkey.conrelid = c.oid)) as sql
from pg_catalog.pg_class c
    join pg_catalog.pg_namespace n on n.oid = c.relnamespace
    join pg_catalog.pg_attribute a on a.attrelid = c.oid and a.attnum > 0
    join pg_catalog.pg_type t on a.atttypid = t.oid
where c.relname = :table_name
group by c.oid, c.relname, c.relpersistence, n.nspname;

사용하다table_name테이블 이름을 지정하는 매개 변수입니다.

앞서 언급한 다른 답변과 마찬가지로 이를 수행하는 내장 함수는 없습니다.

테이블을 복제하는 데 필요한 모든 정보를 가져오거나 배포 및 체크인된 ddl을 비교하는 기능이 있습니다.

이 기능은 다음을 출력합니다.

열(정밀도, null/not-precision, 기본값 포함)
제약
색인


CREATE OR REPLACE FUNCTION public.show_create_table(
  in_schema_name varchar,
  in_table_name varchar
)
RETURNS text
LANGUAGE plpgsql VOLATILE
AS
$$
  DECLARE
    -- the ddl we're building
    v_table_ddl text;

    -- data about the target table
    v_table_oid int;

    -- records for looping
    v_column_record record;
    v_constraint_record record;
    v_index_record record;
  BEGIN
    -- grab the oid of the table; https://www.postgresql.org/docs/8.3/catalog-pg-class.html
    SELECT c.oid INTO v_table_oid
    FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE 1=1
      AND c.relkind = 'r' -- r = ordinary table; https://www.postgresql.org/docs/9.3/catalog-pg-class.html
      AND c.relname = in_table_name -- the table name
      AND n.nspname = in_schema_name; -- the schema

    -- throw an error if table was not found
    IF (v_table_oid IS NULL) THEN
      RAISE EXCEPTION 'table does not exist';
    END IF;

    -- start the create definition
    v_table_ddl := 'CREATE TABLE ' || in_schema_name || '.' || in_table_name || ' (' || E'\n';

    -- define all of the columns in the table; https://stackoverflow.com/a/8153081/3068233
    FOR v_column_record IN
      SELECT
        c.column_name,
        c.data_type,
        c.character_maximum_length,
        c.is_nullable,
        c.column_default
      FROM information_schema.columns c
      WHERE (table_schema, table_name) = (in_schema_name, in_table_name)
      ORDER BY ordinal_position
    LOOP
      v_table_ddl := v_table_ddl || '  ' -- note: two char spacer to start, to indent the column
        || v_column_record.column_name || ' '
        || v_column_record.data_type || CASE WHEN v_column_record.character_maximum_length IS NOT NULL THEN ('(' || v_column_record.character_maximum_length || ')') ELSE '' END || ' '
        || CASE WHEN v_column_record.is_nullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
        || CASE WHEN v_column_record.column_default IS NOT null THEN (' DEFAULT ' || v_column_record.column_default) ELSE '' END
        || ',' || E'\n';
    END LOOP;

    -- define all the constraints in the; https://www.postgresql.org/docs/9.1/catalog-pg-constraint.html && https://dba.stackexchange.com/a/214877/75296
    FOR v_constraint_record IN
      SELECT
        con.conname as constraint_name,
        con.contype as constraint_type,
        CASE
          WHEN con.contype = 'p' THEN 1 -- primary key constraint
          WHEN con.contype = 'u' THEN 2 -- unique constraint
          WHEN con.contype = 'f' THEN 3 -- foreign key constraint
          WHEN con.contype = 'c' THEN 4
          ELSE 5
        END as type_rank,
        pg_get_constraintdef(con.oid) as constraint_definition
      FROM pg_catalog.pg_constraint con
      JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
      JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
      WHERE nsp.nspname = in_schema_name
      AND rel.relname = in_table_name
      ORDER BY type_rank
    LOOP
      v_table_ddl := v_table_ddl || '  ' -- note: two char spacer to start, to indent the column
        || 'CONSTRAINT' || ' '
        || v_constraint_record.constraint_name || ' '
        || v_constraint_record.constraint_definition
        || ',' || E'\n';
    END LOOP;

    -- drop the last comma before ending the create statement
    v_table_ddl = substr(v_table_ddl, 0, length(v_table_ddl) - 1) || E'\n';

    -- end the create definition
    v_table_ddl := v_table_ddl || ');' || E'\n';

    -- suffix create statement with all of the indexes on the table
    FOR v_index_record IN
      SELECT indexdef
      FROM pg_indexes
      WHERE (schemaname, tablename) = (in_schema_name, in_table_name)
    LOOP
      v_table_ddl := v_table_ddl
        || v_index_record.indexdef
        || ';' || E'\n';
    END LOOP;

    -- return the ddl
    RETURN v_table_ddl;
  END;
$$;

예

SELECT * FROM public.show_create_table('public', 'example_table');

생산물

CREATE TABLE public.example_table (
  id bigint NOT NULL DEFAULT nextval('test_tb_for_show_create_on_id_seq'::regclass),
  name character varying(150) NULL,
  level character varying(50) NULL,
  description text NOT NULL DEFAULT 'hello there!'::text,
  CONSTRAINT test_tb_for_show_create_on_pkey PRIMARY KEY (id),
  CONSTRAINT test_tb_for_show_create_on_level_check CHECK (((level)::text = ANY ((ARRAY['info'::character varying, 'warn'::character varying, 'error'::character varying])::text[])))
);
CREATE UNIQUE INDEX test_tb_for_show_create_on_pkey ON public.test_tb_for_show_create_on USING btree (id);

이것이 저에게 효과적인 변형입니다.

pg_dump -U user_viktor -h localhost unit_test_database -t floorplanpreferences_table --schema-only

또한 스키마를 사용하는 경우에는 당연히 다음 사항도 지정해야 합니다.

pg_dump -U user_viktor -h localhost unit_test_database -t "949766e0-e81e-11e3-b325-1cc1de32fcb6".floorplanpreferences_table --schema-only

테이블을 다시 만드는 데 사용할 수 있는 출력을 얻을 수 있습니다. 해당 출력을 psql로 실행하십시오.

pg_dump -h XXXXXXXXXXX.us-west-1.rds.amazonaws.com -U anyuser -t tablename -s

테이블의 DDL을 볼 수 있는 DBeaver와 같은 무료 DB 관리 도구를 사용할 수도 있습니다. 예는 다음과 같습니다.

DataGrip의 기능은 pgAdmin과 동일합니다.테이블을 마우스 오른쪽 버튼으로 클릭하면 테이블 만들기 문을 자동으로 생성하는 옵션이 나타납니다.

단순한 단일 SQL 솔루션입니다.아이디어를 얻으면 보여주고 싶은 더 많은 속성으로 확장할 수 있습니다.

with c as (
SELECT table_name, ordinal_position, 
 column_name|| ' ' || data_type col
, row_number() over (partition by table_name order by ordinal_position asc) rn
, count(*) over (partition by table_name) cnt
FROM information_schema.columns
WHERE table_name   in ('pg_index', 'pg_tables')
order by table_name, ordinal_position
)
select case when rn = 1 then 'create table ' || table_name || '(' else '' end
 || col 
 || case when rn < cnt then ',' else '); ' end
from c 
order by table_name, rn asc;

출력:

create table pg_index(indexrelid oid,
 indrelid oid,
 indnatts smallint,
 indisunique boolean,
 indisprimary boolean,
 indisexclusion boolean,
 indimmediate boolean,
 indisclustered boolean,
 indisvalid boolean,
 indcheckxmin boolean,
 indisready boolean,
 indislive boolean,
 indisreplident boolean,
 indkey ARRAY,
 indcollation ARRAY,
 indclass ARRAY,
 indoption ARRAY,
 indexprs pg_node_tree,
 indpred pg_node_tree);

 create table pg_tables(schemaname name,
 tablename name,
 tableowner name,
 tablespace name,
 hasindexes boolean,
 hasrules boolean,
 hastriggers boolean,
 rowsecurity boolean);

이를 사용하여 ddl.out 파일로 출력을 가져옵니다.

~/bin/pg_dump -p 30000 -d <db_name> -U <db_user> --schema=<schema_name> -t <table_name> --schema-only >> /tmp/ddl.out

따라서 경로: /tmp/ddl.out에서 DDL이 생성됩니다.

함수를 만들지 않고 쿼리가 기본 테이블 구조를 생성하도록 하려면 다음과 같은 해결책이 있습니다.

select 'CREATE TABLE ' || table_name ||'(' ||STRING_AGG (
    column_name || ' ' || data_type ,
        ','
       ORDER BY
        table_name,
        ordinal_position
    ) ||');'
    from 
information_schema.columns 
where table_schema = 'public'
group by 
table_name

은 또다쉬옵은션운을 사용하는 이었습니다.[HeidiSQL client][1] for PostgreSQL데이터베이스

모든 데이터베이스 및 테이블이 나열된 데이터베이스 탭으로 이동하는 방법.

DDL을 보려는 테이블/보기를 클릭하거나 특정 테이블의 문을 만듭니다.

이제 이 클라이언트는 오른쪽 창에서 해당 테이블에 대해 다음 작업을 수행합니다.

첫 번째 창은 테이블의 데이터에 대한 창입니다.

두 번째 SQL 호스트 정보

테이블 및 크기와 같은 데이터베이스 수준 정보의 경우 세 번째

표/보기 정보 탭이 더 중요한 경우에는 표 작성 명세서를 즉시 사용할 수 있습니다.

스냅샷에는 기밀 데이터를 사용하는 것으로 표시할 수 없습니다. 직접 사용해 보고 문제가 발견되면 알려 주십시오.

InpgadminIII 데이터베이스>> 스키마>>표>>'내 테이블'>>스크립트>>'중 하나를 선택하십시오(생성,삽입, 업데이트, 삭제...)'

다음은 몇 가지 수정 사항이 있는 쿼리입니다.

select 'CREATE TABLE ' || a.attrelid::regclass::text || '(' ||
string_agg(a.attname || ' ' || pg_catalog.format_type(a.atttypid, 
a.atttypmod)||
        CASE WHEN 
            (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
             FROM pg_catalog.pg_attrdef d
             WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
            ' DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                          FROM pg_catalog.pg_attrdef d
                          WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
        ELSE
            '' END
||
        CASE WHEN a.attnotnull = true THEN 
            ' NOT NULL'
        ELSE
            '' END,E'\n,') || ');' 
FROM pg_catalog.pg_attribute a join pg_class on a.attrelid=pg_class.oid
WHERE a.attrelid::regclass::varchar =  
'TABLENAME_with_or_without_schema'
AND a.attnum > 0 AND NOT a.attisdropped  and pg_class.relkind='r'
group by a.attrelid;

특정 테이블 생성 뒤에 SQL(DDL)을 생성합니다.우리는 간단히 이 SQL 쿼리를 사용할 수 있습니다.

SHOW TABLE your_schema_name.your_table_name

언급URL : https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-existing-table-in-postgr

'it-source' 카테고리의 다른 글

루비에서 중복 키를 덮어쓰지 않고 두 개의 해시를 병합하려면 어떻게 해야 합니까? (0)	2023.06.05
"꺼짐" 상태에서 UIS 스위치 색상 변경 (0)	2023.06.05
"PageAdapter:: notifyDataSetChanged"가 보기를 업데이트하지 않는 이유는 무엇입니까? (0)	2023.06.05
R 스크립트에서 전체적으로 경고를 억제하는 방법 (0)	2023.06.05
iPhone/Safari 입력 요소 반올림 끄기 (0)	2023.05.31

현재글postgre의 기존 테이블에 대해 "create table" sql 문을 생성하는 방법SQL

각종 프로그래밍 정보를 다루는 블로그입니다.

Android, oracle, PHP, C, Python, mysql, spring-boot, Powershell, Wordpress, reactjs, AngularJS, JQuery, mariaDB, javascript, sql-server, Excel, JSON, java, AJAX, git,

Today :
Yesterday :

criticalcode

postgre의 기존 테이블에 대해 "create table" sql 문을 생성하는 방법SQL

postgre의 기존 테이블에 대해 "create table" sql 문을 생성하는 방법SQL

linux 명령줄에서 postgresql의 테이블에 대한 create table 문을 생성합니다.

또는 SQL을 사용하여 postgresql에서 테이블 이름, 열 이름 및 데이터 유형 정보를 가져옵니다.

예

'it-source' 카테고리의 다른 글

'it-source'의 다른글

티스토리툴바

« 2025/03 »
일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

postgre의 기존 테이블에 대해 "create table" sql 문을 생성하는 방법SQL

postgre의 기존 테이블에 대해 "create table" sql 문을 생성하는 방법SQL

linux 명령줄에서 postgresql의 테이블에 대한 create table 문을 생성합니다.

또는 SQL을 사용하여 postgresql에서 테이블 이름, 열 이름 및 데이터 유형 정보를 가져옵니다.

예

'it-source' 카테고리의 다른 글

'it-source'의 다른글

관련글

티스토리툴바