PostgreSQL11 MYSQL_postgresql11 查看表结构和系统视图

postgres=# select * from pg_tables where tablename='t';

schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity

------------+-----------+------------+------------+------------+----------+-------------+-------------

public | t | postgres | | t | f | f | f

(1 row)

postgres-# \d t

Table "public.t"

Column | Type | Collation | Nullable | Default

--------+-----------------------+-----------+----------+---------

id | integer | | not null |

name | character varying(20) | | |

Indexes:

"t_pkey" PRIMARY KEY, btree (id)

sql语句查看表结构

SELECT A

.attnum,

A.attname AS field,

T.typname AS TYPE,

A.attlen AS LENGTH,

A.atttypmod AS lengthvar,

A.attnotnull AS NOTNULL,

b.description AS COMMENT

FROM

pg_class C,

pg_attribute

A LEFT OUTER JOIN pg_description b ON A.attrelid = b.objoid

AND A.attnum = b.objsubid,

pg_type T

WHERE

C.relname = 't'

AND A.attnum > 0

AND A.attrelid = C.oid

AND A.atttypid = T.oid

ORDER BY

A.attnum;

查看系统视图

postgres-# \df pg_last*

List of functions

Schema | Name | Result data type | Argument data types | Type

------------+-------------------------------+--------------------------+-------------------------------------------------------+------

pg_catalog | pg_last_committed_xact | record | OUT xid xid, OUT "timestamp" timestamp with time zone | func

pg_catalog | pg_last_wal_receive_lsn | pg_lsn | | func

pg_catalog | pg_last_wal_replay_lsn | pg_lsn | | func

pg_catalog | pg_last_xact_replay_timestamp | timestamp with time zone | | func

(4 rows)

系统视图 官方链接

https://www.postgresql.org/docs/current/catalogs.html