Postgre指定表按照两列转JSON数据 Postgre按照指定列获取JSON格式的字符串

最新推荐文章于 2024-02-22 16:11:28 发布

等待～

最新推荐文章于 2024-02-22 16:11:28 发布

阅读量229

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_43620015/article/details/127006277

版权

    SELECT
    jsonb_object_agg ( T.字段名, T.注释 ) AS json
    FROM
    (
    SELECT A
    .attname AS "字段名",
    d.description AS "注释"
    FROM
    pg_attribute
    A INNER JOIN pg_class C ON A.attrelid = C.oid
    INNER JOIN pg_type T ON A.atttypid = T.oid
    LEFT JOIN ( SELECT conrelid, UNNEST ( conkey ) AS pk FROM pg_constraint WHERE contype = 'P' ) S ON S.conrelid = C.oid
    AND A.attnum = S.pk
    LEFT JOIN pg_description d ON d.objoid = A.attrelid
    AND d.objsubid = A.attnum
    WHERE
    C.relname = #{tableName}
    AND A.attnum > 0
    AND A.attname <![CDATA[ <> ]]> 'id'
    ) T