PGSQL-常用方法

一：获取表信息

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    d.description AS table_comment
FROM
    pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0
WHERE
    -- c.relname = 'your_table_name' and  -- 替换为你的表名
     c.relkind = 'r' -- 'r' 表示普通表
		 and n.nspname='registration-1'

SELECT *
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
AND table_type='BASE TABLE'
and table_schema='registration-1'
ORDER BY  table_name;

二：表字段信息

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
    CASE
        WHEN a.atttypid = 'char'::regtype OR a.atttypid = 'varchar'::regtype OR a.atttypid = 'text'::regtype THEN a.atttypmod - 4
        ELSE NULL
    END AS length,
    d.adsrc AS default_value,
    CASE
        WHEN a.attnotnull THEN 'NOT NULL'
        ELSE 'NULLABLE'
    END AS is_nullable,
    pg_catalog.col_description(a.attrelid, a.attnum) AS comment
FROM
    pg_catalog.pg_attribute a
    JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
    JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
    LEFT JOIN pg_catalog.pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE
    a.attnum > 0
    AND NOT a.attisdropped
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
		and c.relname in ('')
ORDER BY
    n.nspname,
    c.relname,
    a.attname;

SELECT 
    n.nspname AS schema_name,
    c.relname AS table_name,
    a.attname AS column_name,
    CASE
        WHEN t.typname = 'char' THEN 'char(' || CASE WHEN a.atttypmod>4 THEN a.atttypmod-4  ELSE 0 END  || ')'    
				WHEN t.typname = 'int2' THEN 'number(2,0)'
				WHEN t.typname = 'int4' THEN 'number(4,0)'
				WHEN t.typname = 'int8' THEN 'number(16,0)'
        WHEN t.typname = 'varchar' THEN 'varchar(' || CASE WHEN a.atttypmod>4 THEN a.atttypmod - 4 ELSE 0 END  || ')'
        WHEN t.typname = 'text' THEN 'varchar'
        WHEN t.typname = 'bpchar' THEN 'char(' || CASE WHEN a.atttypmod>4 THEN a.atttypmod - 4 ELSE 0 END  || ')'
        WHEN t.typname LIKE '%time%' OR t.typname = 'date' OR t.typname = 'timestamp' OR t.typname = 'timestamptz' THEN 'Date'
        WHEN t.typname IN ('int2', 'int4', 'int8', 'smallint', 'integer', 'bigint', 'numeric', 'decimal') THEN 
				                  
            CASE 
                WHEN a.atttypmod = -1 THEN 'number'
                ELSE 'number(' || (CASE WHEN a.atttypmod > 0 THEN (a.atttypmod - 4) / 65536 ELSE 0 END) || ',' || 
                    (CASE WHEN a.atttypmod > 0 THEN (a.atttypmod - 4) % 65536 ELSE 0 END) || ')'
            END
        ELSE t.typname
    END AS data_type,
    d.adsrc AS default_value,
    CASE WHEN a.attnotnull THEN 'NO' ELSE 'YES' END AS is_nullable,
    pg_catalog.col_description(a.attrelid, a.attnum) AS comment
FROM 
    pg_catalog.pg_attribute a
    JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
    JOIN pg_catalog.pg_type t ON a.atttypid = t.oid
    JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
    LEFT JOIN pg_catalog.pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE 
    a.attnum > 0 
    AND NOT a.attisdropped
    AND c.relkind = 'r'  -- Only tables, no views or other objects
		and c.relname in  (
		SELECT
    c.relname
FROM
    pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0
WHERE
    -- c.relname = 'your_table_name' and  -- 替换为你的表名
     c.relkind = 'r' -- 'r' 表示普通表
		 and n.nspname='registration-1')
ORDER BY 
    schema_name, 
    table_name, 
    a.attnum;