[sql] PostgreSQL에서 테이블의 목록 열 이름과 데이터 유형을 얻는 방법은 무엇입니까?

Question 1

다음 쿼리를 사용하여 PostgreSQL에서 테이블의 열 이름 및 데이터 유형 목록을 가져올 수 있습니다.

Question 2

psql명령 줄을 열고 다음을 입력합니다.

\d+ table_name

Question 3

select column_name,data_type
from information_schema.columns
where table_name = 'table_name';

위의 쿼리를 사용하면 열과 데이터 유형을

Question 4

SELECT
        a.attname as "Column",
        pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
    FROM
        pg_catalog.pg_attribute a
    WHERE
        a.attnum > 0
        AND NOT a.attisdropped
        AND a.attrelid = (
            SELECT c.oid
            FROM pg_catalog.pg_class c
                LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relname ~ '^(hello world)$'
                AND pg_catalog.pg_table_is_visible(c.oid)
        );

테이블 이름으로 Hello World 변경

더 많은 정보 : http://www.postgresql.org/docs/9.3/static/catalog-pg-attribute.html

Question 5

동일한 테이블 이름을 가진 여러 스키마가있는 경우 스키마 이름을 추가하는 것을 잊지 마십시오.

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'your_table_name' AND table_schema = 'your_schema_name';

또는 psql 사용 :

\d+ your_schema_name.your_table_name

Question 6

더 많은 스키마와 nullable을 지원하기 위해 업데이트 된 Pratik 답변 :

SELECT
    "pg_attribute".attname                                                    as "Column",
    pg_catalog.format_type("pg_attribute".atttypid, "pg_attribute".atttypmod) as "Datatype",

    not("pg_attribute".attnotnull) AS "Nullable"
FROM
    pg_catalog.pg_attribute "pg_attribute"
WHERE
    "pg_attribute".attnum > 0
    AND NOT "pg_attribute".attisdropped
    AND "pg_attribute".attrelid = (
        SELECT "pg_class".oid
        FROM pg_catalog.pg_class "pg_class"
            LEFT JOIN pg_catalog.pg_namespace "pg_namespace" ON "pg_namespace".oid = "pg_class".relnamespace
        WHERE
            "pg_namespace".nspname = 'schema'
            AND "pg_class".relname = 'table'
    );

Question 7

특정 스키마에서 테이블의 열 이름 및 유형 찾기를 지원하고 하위 쿼리없이 JOIN을 사용하는 버전

SELECT
    pg_attribute.attname AS column_name,
    pg_catalog.format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS data_type
FROM
    pg_catalog.pg_attribute
INNER JOIN
    pg_catalog.pg_class ON pg_class.oid = pg_attribute.attrelid
INNER JOIN
    pg_catalog.pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE
    pg_attribute.attnum > 0
    AND NOT pg_attribute.attisdropped
    AND pg_namespace.nspname = 'my_schema'
    AND pg_class.relname = 'my_table'
ORDER BY
    attnum ASC;

Question 8

    SELECT DISTINCT
        ROW_NUMBER () OVER (ORDER BY pgc.relname , a.attnum) as rowid ,
        pgc.relname as table_name ,
        a.attnum as attr,
        a.attname as name,
        format_type(a.atttypid, a.atttypmod) as typ,
        a.attnotnull as notnull,
        com.description as comment,
        coalesce(i.indisprimary,false) as primary_key,
        def.adsrc as default
    FROM pg_attribute a
    JOIN pg_class pgc ON pgc.oid = a.attrelid
    LEFT JOIN pg_index i ON
        (pgc.oid = i.indrelid AND i.indkey[0] = a.attnum)
    LEFT JOIN pg_description com on
        (pgc.oid = com.objoid AND a.attnum = com.objsubid)
    LEFT JOIN pg_attrdef def ON
        (a.attrelid = def.adrelid AND a.attnum = def.adnum)
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = pgc.relnamespace

    WHERE 1=1
        AND pgc.relkind IN ('r','')
        AND n.nspname <> 'pg_catalog'
        AND n.nspname <> 'information_schema'
        AND n.nspname !~ '^pg_toast'

    AND a.attnum > 0 AND pgc.oid = a.attrelid
    AND pg_table_is_visible(pgc.oid)
    AND NOT a.attisdropped
    ORDER BY rowid
    ;