) t1leftjoin(SELECTns.nspnameastable_schema,-- 模式名t.relnameAStable_name,-- 表名c.conname,-- 约束名称c.contype,-- 约束类型。p:primary key;unnest(c.conkey)asordinal_position-- 字段排序号FROMpg_constraint cLEFTJOINpg_namespace nsON(c.connamespace=ns.oid)LEFTJOINpg_class tON(c.conrel...
SELECT column_name, ordinal_position FROM information_schema.columns WHERE table_name = 'table_name' ORDER BY ordinal_position; 这将返回指定表中各列的名称及其在表中的位置(即顺序)。 字段顺序对数据库性能和查询的影响 在大多数情况下,字段顺序对数据库性能和查询结果的影响可以忽略不计。然而,在以下情...
col.numeric_scale, col.is_nullable, col.column_default, des.description from information_schema.columns col left join pg_description des on col.table_name::regclass = des.objoid and col.ordinal_position = des.objsubid where table_schema = 'public' and table_name = 't_student' order by ...
COALESCE("parameters".parameter_name, '') || ' ' || COALESCE("parameters".udt_name, '') AS params, "parameters".ordinal_position FROM "information_schema"."routines" LEFT JOIN "information_schema"."parameters" ON "routines".specific_name="parameters".specific_name WHERE "routines".specific_...
and col.ordinal_position = des.objsubid where table_schema = 'public' and table_name = 't_student' order by ordinal_position; 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 或者简单点: ...
, col.is_nullable, col.column_default, des.description from information_schema.columns col left join pg_description des on col.table_name::regclass = des.objoid and col.ordinal_position = des.objsubid where table_schema = 'public' and table_name = 't_student' order by ordinal_position;...
"parameters".ordinal_positionFROM"information_schema"."routines"LEFTJOIN"information_schema"."parameters"ON"routines".specific_name="parameters".specific_nameWHERE"routines".specific_schema='abc'ORDERBY1,2,6; AI代码助手复制代码 这里我们再使用聚合函数: string_agg 把字段 params所有行连接成字符串,并用...
ordinal_position WHERE col.table_schema = 'test_schema' AND description IS NULL ORDER BY col.table_name, col.ordinal_position; 参考: http://postgres.cn/docs/12/catalog-pg-class.html http://postgres.cn/docs/12/catalog-pg-description.html http://postgres.cn/docs/12/sql-comment.html 本文...
COMMENT ON COLUMN public.tb_car.car_id IS '主键'; COMMENT ON COLUMN public.tb_car.car_index_code IS '车辆编码'; COMMENT ON COLUMN public.tb_car.plate_no IS '车牌号码'; 用如下语句查询 SELECT col.table_name, col.column_name, col.ordinal_position AS o, d.description FROM information_...
ORDER BY col.table_name, col.ordinal_position; -- 查询所有没注释的表 SELECT tb.table_name, d.description FROM information_schema.tables tb JOIN pg_class c ON c.relname = tb.table_name LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = '0' ...