命令行获取表结构定义

PostgreSQL无法像MySQL一样在命令行直接查看到表结构的定义，下面语句通过查询PG的各元数据表，可以获取到表的基本定义（表字段、约束、注释和索引等），但是此语句还有其它信息未获取（分区、继承、表参数等），可在下面的语句中自行补充。

psql -qAtX -d test -U joan -c "
WITH temp_table AS
 (SELECT quote_ident('public') || '.' || quote_ident('empsalary') AS table_name) --注意修改模式和表名
SELECT 'CREATE TABLE ' || table_name || ' (' create_table_sql
  FROM temp_table
UNION ALL
SELECT t.table_col
  FROM (SELECT quote_ident(a.attname) || ' ' || format_type(a.atttypid, a.atttypmod) || CASE
                   WHEN a.attnotnull = TRUE THEN
                    ' NOT NULL'
                   ELSE
                    ''
               END || coalesce((SELECT CASE
                                          WHEN adsrc IS NOT NULL THEN
                                           ' DEFAULT ' || adsrc
                                      END
                                 FROM pg_attrdef d
                                WHERE d.adrelid = a.attrelid
                                  AND d.adnum = a.attnum
                                  AND a.atthasdef), '') || ',' AS table_col
          FROM pg_attribute a,
               temp_table   b
         WHERE a.attrelid = b.table_name ::regclass
           AND a.attnum > 0
           AND NOT a.attisdropped
         ORDER BY a.attnum) t
UNION ALL
SELECT 'CONSTRAINT ' || conname || ' ' || pg_get_constraintdef(OID, 't')
  FROM pg_constraint,
       temp_table
 WHERE conrelid = table_name ::regclass
UNION ALL
SELECT ')' || CASE
           WHEN tablespace IS NOT NULL THEN
            'TABLESPACE ' || tablespace
           ELSE
            ''
       END || ';'
  FROM pg_tables,
       temp_table
 WHERE schemaname = split_part(table_name, '.', 1)
   AND tablename = split_part(table_name, '.', 2)
UNION ALL
SELECT CASE
           WHEN quote_ident(b.attname) IS NULL THEN
            'COMMENT ON TABLE ' || table_name || ' '
           ELSE
            'COMMENT ON COLUMN ' || table_name || '.'
       END || coalesce(quote_ident(b.attname), '') || ' IS ''' || a.description || ''';'
  FROM pg_description a
  LEFT JOIN pg_attribute b
    ON a.objoid = b.attrelid AND a.objsubid = b.attnum
  JOIN temp_table c
    ON TRUE
 WHERE a.objoid = c.table_name ::regclass
UNION ALL
SELECT pg_get_indexdef(indexrelid) || ';'
  FROM pg_index,
       temp_table
 WHERE indrelid = table_name ::regclass 
   AND NOT indisprimary;"


复制

说明：

• 本语句可以学习PG中与表相关的元数据表；
• 还可以用作PG迁移到其它数据库时，对表结构作自定义导出。