PostgreSQL：查询数据表信息

PostgreSQL 14.0

pgAdmin 4 Version 5.7

--

 

0、序章

要查询数据表信息，需要用到 系统表或系统视图等，比如，pg_tables、pg_class、information_schema 等。

使用 pgAdmin 4 连接数据库，可以看到这些 系统表或系统视图 在下面的位置：下图中的 lib1 为 数据库名称

注，pgAdmin 4 中没有系统表的注释及字段注释，但可以从 官文（参考连接#2 下载） 中获取。

 

建立测试 schema 及 其下的测试 数据表：schema2023.some_info

创建模式及表

-- SCHEMA: schema2023

-- DROP SCHEMA schema2023 ;

CREATE SCHEMA schema2023
    AUTHORIZATION postgres;

-- Table: schema2023.some_info

-- DROP TABLE schema2023.some_info;

CREATE TABLE IF NOT EXISTS schema2023.some_info
(
    id character varying(128) COLLATE pg_catalog."default" NOT NULL,
    name character varying(100) COLLATE pg_catalog."default" NOT NULL,
    money double precision,
    days integer,
    create_time timestamp without time zone NOT NULL,
    CONSTRAINT some_info_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE schema2023.some_info
    OWNER to postgres;

COMMENT ON TABLE schema2023.some_info
    IS '一些信息';

COMMENT ON COLUMN schema2023.some_info.id
    IS '主键';

COMMENT ON COLUMN schema2023.some_info.name
    IS '名称';

COMMENT ON COLUMN schema2023.some_info.money
    IS '价值：人民币计价';

COMMENT ON COLUMN schema2023.some_info.days
    IS '有效天数';

COMMENT ON COLUMN schema2023.some_info.create_time
    IS '创建时间';

 

查询一些全局信息

select * from pg_namespace

结果：存在模式 schema2023 的信息，其 oid 的字面值为 222485。ben发布于博客园

 

select * from pg_tablespace

结果：

 

1、数据表本身的信息

pg_tables 中查询：ben发布于博客园

select * from pg_tables
where schemaname = 'schema2023' and tablename = 'some_info'

结果：

 

pg_class 中查询：

select * from pg_class
where relname = 'some_info'
limit 2

结果：

ben发布于博客园

pg_description 中查询：使用函数 to_regclass

select * from pg_description
where objoid = to_regclass('schema2023' || '.' || 'some_info')::REGCLASS::OID

结果：其中 objsubid = 0 所在行 为 表注释，其它的为 表字段注释；这里的 objoid 的字面值为 222486，为 数据表 对应的 oid（pg_class 表中）；

 

综合查询

-- 整个数据库仅有一个名为 some_info 的表情况下
select pgc.oid, 
(select nspname from pg_namespace pgns where pgns.oid = pgc.relnamespace) as schemaname,
pgc.relname as tablename,
(select description from pg_description pgds 
 where pgds.objoid = pgc.oid and objsubid = 0
) as description
from pg_class pgc
where pgc.relname = 'some_info'

结果：

 

2、数据表中字段的信息

information_schema.columns 中查询：ben发布于博客园

select * from information_schema.columns
limit 4

结果：

information_schema.columns 中部分字段：

table_catalog, table_schema, table_name, column_name, ordinal_position, is_nullable, data_type 等

 

查询测试表的字段信息：

select * from information_schema.columns
where table_schema = 'schema2023' and table_name = 'some_info'

结果：

ben发布于博客园

综合查询：包括字段注释

sql语句：

select 
table_catalog, table_schema, table_name, column_name, 
ordinal_position, is_nullable, data_type,
(select description from pg_description pgds 
 where pgds.objoid = to_regclass('schema2023' || '.' || 'some_info')::REGCLASS::OID 
 and objsubid = isc.ordinal_position
) as description
from information_schema.columns isc 
where table_schema = 'schema2023' and table_name = 'some_info'

结果：

ben发布于博客园

疑问：

怎么查询数据表中的 主键？TODO

 

3、背景知识

函数：to_regclass ( text ) → regclass

 

The catalog pg_class：官文

The catalog pg_class catalogs tables and most everything else that has columns or is otherwise
similar to a table. This includes indexes (but see also pg_index), sequences (but see also pg_sequence), views, 
materialized views, composite types, and TOAST tables; see relkind. Below,
when we mean all of these kinds of objects we speak of “relations”. Not all columns are meaningful
for all relation types.

ben发布于博客园

The catalog pg_description：官文

The catalog pg_description stores optional descriptions (comments) for each database object.
Descriptions can be manipulated with the COMMENT command and viewed with psql's \d commands.
Descriptions of many built-in system objects are provided in the initial contents of pg_description.
See also pg_shdescription, which performs a similar function for descriptions involving objects
that are shared across a database cluster.

 

The view columns：官文

The view columns contains information about all table columns (or view columns) in the database.
System columns (ctid, etc.) are not included. Only those columns are shown that the current user
has access to (by way of being the owner or having some privilege).

 

---END---

 

本文链接：

https://www.cnblogs.com/luo630/p/17076045.html

ben发布于博客园

参考资料

1、PostgreSQL 中的 OID

https://www.jianshu.com/p/ffb833bd4fb5

2、PostgreSQL 官文

https://www.postgresql.org/docs/

3、

 

 

ben发布于博客园