如何让PostgreSQL为我提供类似MySQL显示列的结果？
如何让PostgreSQL为我提供类似MySQL显示列的结果？
提问于 2019-06-06 04:00:43
在浪费了很多时间之后，我已经接近了(为了我自己，我不关心类型的差异)。但是，我确实想要与MySQL完全相同的输出格式。原因是我正在尝试调整一个只支持MySQL的工具，以便与PostgreSQL一起使用。以下是MySQL的输出示例(尽管列数较少)：
mysql> show columns from users;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(200) | YES | | NULL | |
| institution | varchar(200) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+下面是我用来测试的表格：
Table "public.users"
Column | Type | Collation | Nullable | Default
--------------------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
name | character varying(255) | | |
role_id | integer | | |
image_url | character varying(510) | | |
institution | character varying(255) | | |
qualifications | text | | |
cv_url | character varying(510) | | |
specializations | text | | |
text_collaboration | text | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id) Check constraints:
"users_name_not_null" CHECK (name IS NOT NULL) Foreign-key constraints:
"fk_role_id" FOREIGN KEY (role_id) REFERENCES roles(id) Referenced by:
TABLE "novel_reviews" CONSTRAINT "novels_reviewer_id_fkey" FOREIGN KEY (reviewer_id) REFERENCES users(id)
TABLE "review_translations" CONSTRAINT "review_translations_recorder_id_fkey" FOREIGN KEY (recorder_id) REFERENCES users(id)这是我的查询...它可能做得不好，尤其是。使用GROUP BY部分：
SELECT column_name AS "Field"
, data_type AS "Type"
, is_nullable AS "Null"
, CASE WHEN is_primary=true THEN 'PRI' ELSE NULL END AS "Key"
, column_default as "Default"
, CASE WHEN column_default LIKE 'nextval(%' THEN 'auto_increment' ELSE '' END AS "Extra"
FROM
(
SELECT c.column_name
, c.data_type
, c.is_nullable
, tc.constraint_type='PRIMARY KEY' AS is_primary
, c.column_default
FROM information_schema.columns AS c
LEFT JOIN information_schema.constraint_column_usage AS ccu USING (column_name, table_name)
LEFT JOIN information_schema.table_constraints tc USING (constraint_name)
WHERE c.table_name = 'users'
GROUP BY c.column_name
, c.data_type
, c.is_nullable
, is_primary
, c.column_default
) as sq;这是我目前得到的结果。很抱歉，格式不好。
> Field | Type | Null | Key | Default | Extra
> --------------------+-------------------+------+-----+-----------------------------------+----------------
> | cv_url | character varying | YES | |
> | id | integer | NO | | nextval('users_id_seq'::regclass) | auto_increment
> | id | integer | NO | PRI | nextval('users_id_seq'::regclass) | auto_increment
> | image_url | character varying | YES | |
> | institution | character varying | YES | |
> | name | character varying | YES | |
> | qualifications | text | YES | |
> | role_id | integer | YES | |
> | specializations | text | YES | |
> |
> | (10 rows)我不知道如何让id的第二次出现消失，这一次是从non-primarykey约束发出的。我不知道该怎么丢掉它。我尝试使用WHERE is_primary_key is NULL or is_primary_key=TRUE，但这样做也会删除Name字段，该字段连接到一个也不是主键的约束。
我想要的是从表中获取所有列(每列只有一次)和字符串"PRI“(如果字段是主键)。
帮助!我有点不知所措了。谢谢。
回答 2
Stack Overflow用户
发布于 2019-06-06 06:55:03
这是您需要的查询：
SELECT *
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'users'Stack Overflow用户
发布于 2019-06-07 01:25:08
我的头撞了很多次墙之后才明白的。首先，我提出了一个观点：
CREATE VIEW table_column_constraints as (SELECT c.table_schema, c.table_name, c.column_name
, c.data_type
, c.is_nullable
, tc.constraint_type
, c.column_default
FROM information_schema.columns AS c
LEFT JOIN information_schema.constraint_column_usage AS ccu USING (column_name, table_name)
LEFT JOIN information_schema.table_constraints tc ON tc.constraint_name=ccu.constraint_name WHERE c.table_schema='public');然后，我执行了一种重复数据消除技术，将表与自身进行比较：
SELECT column_name as "Field"
, data_type AS "Type"
, is_nullable AS "Null"
, CASE WHEN constraint_type='PRIMARY KEY' THEN 'PRI' ELSE NULL END AS "Key"
, column_default AS "Default", CASE WHEN column_default LIKE 'nextval(%' THEN 'auto_increment' ELSE '' END AS "Extra"
FROM table_column_constraints as given WHERE given.table_name = 'users'
AND NOT EXISTS (SELECT * FROM table_column_constraints other WHERE other.column_name=given.column_name AND given.constraint_type!='PRIMARY KEY' AND other.constraint_type='PRIMARY KEY');要获得以下结果：
Field | Type | Null | Key | Default | Extra
--------------------+-------------------+------+-----+-----------------------------------+----------------
name | character varying | YES | | |
id | integer | NO | PRI | nextval('users_id_seq'::regclass) | auto_increment
image_url | character varying | YES | | |
institution | character varying | YES | | |
qualifications | text | YES | | |
cv_url | character varying | YES | | |
specializations | text | YES | | |
text_collaboration | text | YES | | |
role_id | integer | YES | | |
(9 rows)我在一个题为“选择按某列排序的行和按另一列的不同行”的问题上受到了https://stackoverflow.com/a/45065229/1151229的启发。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56467204
复制相关文章
相似问题