Pg开发常用语句

Postgresql官方文档 https://www.postgresql.org/docs/current/

查看建表语句

SELECT array_to_string( ARRAY (
		SELECT ret FROM (
			SELECT 'CREATE TABLE 模式名称.表名称 (' || array_to_string( ARRAY (
				SELECT SQL FROM (
						(--字段信息
							SELECT 
								array_to_string( ARRAY (
									SELECT 
									       -- 字段名称
									       A.attname || ' ' || 
										   -- 字段类型
										   concat_ws ( '', T.typname, SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' ) ) || ' ' ||
										   -- 非空约束
										   CASE A.attnotnull  WHEN 't' THEN 'NOT NULL' ELSE '' END || ' ' ||
										   -- 默认数值
										   CASE WHEN D.adbin IS NOT NULL THEN ' DEFAULT ' || pg_get_expr ( D.adbin, A.attrelid ) ELSE '' END 
									FROM 	pg_attribute             A 
											LEFT JOIN pg_description B ON A.attrelid = B.objoid  AND A.attnum = B.objsubid
											LEFT JOIN pg_attrdef     D ON A.attrelid = D.adrelid AND A.attnum = D.adnum,
											pg_type 				 T 
									WHERE
										A.attstattarget =- 1 
										AND A.attrelid = '模式名称.表名称' :: REGCLASS :: OID 
										AND A.attnum > 0 
										AND NOT A.attisdropped 
										AND A.atttypid = T.OID 
									ORDER BY A.attnum  
									), ',' || CHR( 10 ) 
								) as SQL, 
								1 as seri 
						) 
						UNION
						(--约束信息（含主外键）
							SELECT 
								'CONSTRAINT ' || conname || ' ' || pg_get_constraintdef ( OID ) as SQL, 
								2 as seri 
							FROM pg_constraint T 
							WHERE conrelid = '模式名称.表名称' :: REGCLASS :: OID 
							ORDER BY contype DESC 
						) 
						ORDER BY seri  
				) T ), ',' || CHR( 10 )  ) || ')' AS ret, 1 AS orderby 
			UNION --索引信息
				SELECT array_to_string(ARRAY ( 
					SELECT pg_get_indexdef ( indexrelid ) 
					FROM pg_index 
					WHERE indrelid = '模式名称.表名称' :: REGCLASS :: OID 
					AND indisprimary = 'f' 
					AND indisunique = 'f' 
				),';' || CHR( 10 ) ) AS ret,2 AS orderby 
			UNION --注释信息
				SELECT array_to_string(ARRAY (
					SELECT 'COMMENT ON COLUMN ' || '模式名称.表名称.' || A.attname || ' IS ''' || b.description || '''' 
					FROM pg_attribute A 
					LEFT JOIN pg_description b ON A.attrelid = b.objoid AND A.attnum = b.objsubid 
					WHERE A.attstattarget =- 1 
						AND A.attrelid = '模式名称.表名称' :: REGCLASS :: OID 
						AND b.description IS NOT NULL 
					ORDER BY A.attnum 
				), ';' || CHR( 10 )  ) AS ret,3 AS orderby 
				ORDER BY orderby 
			) results 
		), ';' || CHR( 10 ) || CHR( 13 ) 
)

上述查看建表语句的sql，当数据表是分区表时，是无法查看到分区表的结构的，此时可以将表结构导出查看。

查看表索引

SELECT 
	*
FROM
    pg_indexes
WHERE
    tablename like 'alarm_data_all%'
ORDER BY 
	tablename;