SHOW COLUMNS

SHOW COLUMNS

功能描述

查看指定表的列元信息。

注意事项

临时表需要指定临时表对应的schema查询。
复合主键索引所有参与列都会在Key字段中显示为PRI。
复合唯一索引所有参与列都会在Key字段中显示为UNI。
如果一个列参与了多个索引的创建，以该列第一个创建的索引为准显示Key字段。
生成列会在Default中显示生成式。
表名中含schemaname/dbname并且同时指定dbname时，仅匹配指定的dbname。
结果仅显示当前查询用户具有SELECT权限的列信息。

语法格式

1. SHOW [FULL] {COLUMNS | FIELDS}
2. {FROM | IN} tbl_name
3. [{FROM | IN} db_name]
4. [LIKE 'pattern' | WHERE expr]

参数说明

{COLUMNS | FIELDS}

使用COLUMNS和FIELDS效果是等价的。 - tbl_name

表名，可指定表名。也可以指定schema_name.table_name。 - db_name

库名（或schema），当tbl_name中也指定库名（或schema名）时，优先选择本选项。 - LIKE ‘pattern’

pattern匹配显示结果的Field列。

示例

1. --创建简单表
2. openGauss=# CREATE SCHEMA tst_schema1;

4. openGauss=# SET SEARCH_PATH TO tst_schema1;

6. openGauss=# CREATE TABLE tst_t1
7. openGauss-# (
8. openGauss(# id int primary key,
9. openGauss(# name varchar(20) NOT NULL,
10. openGauss(# addr text COLLATE "de_DE",
11. openGauss(# phone text COLLATE "es_ES",
12. openGauss(# addr_code text
13. openGauss(# );
14. openGauss=# COMMENT ON COLUMN tst_t1.id IS 'identity';

16. --查看表的列元信息
17. openGauss=# SHOW COLUMNS FROM tst_t1;
18. Field   |         Type          | Null | Key | Default | Extra
19. -----------+-----------------------+------+-----+---------+-------
20. id        | integer               | NO   | PRI | NULL    |
21. name      | character varying(20) | NO   |     | NULL    |
22. addr      | text                  | YES  |     | NULL    |
23. phone     | text                  | YES  |     | NULL    |
24. addr_code | text                  | YES  |     | NULL    |

26. openGauss=# show FULL COLUMNS FROM tst_t1;
27. Field   |         Type          | Collation | Null | Key | Default | Extra |               Privileges                | Comment
28. -----------+-----------------------+-----------+------+-----+---------+-------+-----------------------------------------+----------
29. id        | integer               | NULL      | NO   | PRI | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | identity
30. name      | character varying(20) | NULL      | NO   |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT |
31. addr      | text                  | de_DE     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT |
32. phone     | text                  | es_ES     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT |
33. addr_code | text                  | NULL      | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT |

35. openGauss=# show FULL COLUMNS FROM tst_schema1.tst_t1;
36. Field   |         Type          | Collation | Null | Key | Default | Extra |               Privileges                | Comment
37. -----------+-----------------------+-----------+------+-----+---------+-------+-----------------------------------------+----------
38. id        | integer               | NULL      | NO   | PRI | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | identity
39. name      | character varying(20) | NULL      | NO   |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT |
40. addr      | text                  | de_DE     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT |
41. phone     | text                  | es_ES     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT |
42. addr_code | text                  | NULL      | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT |

44. --模糊匹配、过滤
45. openGauss=# show full columns from tst_t1 like '%addr%';
46. Field   | Type | Collation | Null | Key | Default | Extra |               Privileges                | Comment
47. -----------+------+-----------+------+-----+---------+-------+-----------------------------------------+---------
48. addr      | text | de_DE     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT |
49. addr_code | text | NULL      | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT |

51. openGauss=# show full columns from tst_t1 where Type='text';
52. Field   | Type | Collation | Null | Key | Default | Extra |               Privileges                | Comment
53. -----------+------+-----------+------+-----+---------+-------+-----------------------------------------+---------
54. addr      | text | de_DE     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT |
55. phone     | text | es_ES     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT |
56. addr_code | text | NULL      | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT |

58. --显示权限过滤
59. openGauss=# CREATE USER tst_u1 PASSWORD 'tst_u1@123';
60. openGauss=# SET ROLE tst_u1 PASSWORD 'tst_u1@123';
61. opengauss=> SET SEARCH_PATH TO tst_schema1;

63. openGauss=> show full columns from tst_t1;
64. Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment
65. -------+------+-----------+------+-----+---------+-------+------------+---------
66. (0 rows)

68. openGauss=# RESET ROLE;
69. opengauss=# GRANT SELECT (addr, phone) on tst_t1 to tst_u1;
70. openGauss=# SET ROLE tst_u1 PASSWORD 'tst_u1@123';

72. opengauss=> SET SEARCH_PATH TO tst_schema1;
73. openGauss=> show full columns from tst_t1;
74. Field   | Type | Collation | Null | Key | Default | Extra | Privileges  | Comment
75. -----------+------+-----------+------+-----+---------+-------+------------------+---------
76. addr      | text | de_DE     | YES  |     | NULL    |       | SELECT     |
77. phone     | text | es_ES     | YES  |     | NULL    |       | SELECT     |