PostgreSQL11: psql 新增 \gdesc 显示查询结果的列名和类型
栏目: 数据库 · PostgreSQL · 发布时间: 5年前
内容简介：PostgreSQL 11 的\gdescShows the description (that is, the column names and data types) of the result of the current query buffer. The query is not actually executed; however, if it contains some type of syntax error, that error will be reported in the norm
PostgreSQL 11 的 psql
新增 \gdesc 选项，此选项可以返回查询结果的列名和类型，而不实际执行SQL。
Release 说明
psql Add psql command \gdesc to display the column names and types of the query output (Pavel Stehule)
\gdesc 选项说明
\gdesc
Shows the description (that is, the column names and data types) of the result of the current query buffer. The query is not actually executed; however, if it contains some type of syntax error, that error will be reported in the normal way.
If the current query buffer is empty, the most recently sent query is described instea
\gdesc 只是显示查询结果的列名和类型，并不实际执行SQL，下面演示下。
\gdesc 选项演示
数据库中存在一张大表big，结构如下:
[pg11@pghost2 ~]$ psql francs francs
psql (11beta3)
Type "help" for help.
francs=> \d big
Table "francs.big"
Column | Type | Collation | Nullable | Default
-----------+--------------------------------+-----------+----------+-------------------
user_id | integer | | |
user_name | text | | |
ctime | timestamp(6) without time zone | | | clock_timestamp()
Indexes:
"idx_big_ctime" btree (ctime)
"idx_big_username" btree (user_name)
执行以下查询，如下:
francs=> \timing Timing is on. francs=> SELECT count(*),sum(hashtext(user_name)) FROM big; count | sum ----------+---------------- 30000000 | 11924569894736 (1 row) Time: 1347.527 ms (00:01.348)
执行时间为 1347 ms 左右。
使用 \gdesc 选项查询，如下:
francs=> SELECT count(*),sum(hashtext(user_name)) FROM big \gdesc Column | Type --------+-------- count | bigint sum | bigint (2 rows) Time: 0.634 ms
以上返回了查询结果的列和数据类型，执行很快，只需要 0.634 ms，可见没有实际执行SQL。
另一个示例，查询 pg_class 系统表，如下:
francs=> SELECT * FROM pg_class \gdesc
Column | Type
---------------------+--------------
relname | name
relnamespace | oid
reltype | oid
reloftype | oid
relowner | oid
relam | oid
relfilenode | oid
reltablespace | oid
relpages | integer
reltuples | real
relallvisible | integer
reltoastrelid | oid
relhasindex | boolean
relisshared | boolean
relpersistence | "char"
relkind | "char"
relnatts | smallint
relchecks | smallint
relhasoids | boolean
relhasrules | boolean
relhastriggers | boolean
relhassubclass | boolean
relrowsecurity | boolean
relforcerowsecurity | boolean
relispopulated | boolean
relreplident | "char"
relispartition | boolean
relrewrite | oid
relfrozenxid | xid
relminmxid | xid
relacl | aclitem[]
reloptions | text[]
relpartbound | pg_node_tree
(33 rows)
这个特性不需要实际执行 SQL 就能返回查询结果的列和数据类型，在某些特定场景比较有用。
参考
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，也希望大家多多支持 码农网
猜你喜欢:
- SQLServer获取临时表所有列名或是否存在指定列名的方法
- pandas 修改 DataFrame 列名
- SQL SERVER动态列名
- 不知道列名的情况下注入
- postgresql – 在postgres中将表列名更改为大写
- MySQL列名中包含斜杠或者空格的处理方法
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
