[POSTGRE] 시스템 테이블로 정보 조회하기

• PK 설정 안된 테이블 조회하기

SELECT 
    table_schema, 
    table_name
FROM 
    information_schema.tables t
WHERE 
    table_schema = 'myown' -- 특정 스키마 이름으로 변경
    AND table_type = 'BASE TABLE'
    AND NOT EXISTS (
        SELECT 1 
        FROM information_schema.table_constraints tc
        WHERE 
            tc.table_schema = t.table_schema
            AND tc.table_name = t.table_name
            AND tc.constraint_type = 'PRIMARY KEY'
    )
    and table_name not like 'z%'
ORDER BY 
    table_name;

• 테이블/컬럼 주석 조회

SELECT 
    n.nspname AS schema_name,       -- 스키마 이름
    c.relname AS table_name,        -- 테이블 이름
    d.description AS table_comment, -- 테이블 설명
    col_description(a.attrelid, a.attnum) AS column_comment, -- 컬럼 설명
    a.attname AS column_name,       -- 컬럼 이름
    a.attnum AS column_order,       -- 컬럼 순서
    UPPER(t.typname) AS data_type,         -- 데이터 타입
    CASE
        WHEN t.typname IN ('varchar', 'char', 'bpchar') THEN a.atttypmod - 4
        WHEN t.typname = 'numeric' THEN (a.atttypmod - 4) >> 16  -- 정밀도
        ELSE NULL
    END AS character_maximum_length -- 길이 (문자열일 경우)
FROM 
    pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0
    JOIN pg_attribute a ON a.attrelid = c.oid
    JOIN pg_type t ON t.oid = a.atttypid
WHERE 
    a.attnum > 0 AND NOT a.attisdropped
    and c.relkind = 'r'                 -- 'r'은 일반 테이블을 의미합니다.
    AND a.attnum > 0                -- 시스템 컬럼 제외
    AND NOT a.attisdropped          -- 삭제된 컬럼 제외
--   and n.nspname = 'myown'
   and c.relname = 'tb_dept'
--   and c.relname like '%tb_member%'
--   and c.attname like '%%'
ORDER BY 
    schema_name, table_name, column_order