pg_class:oid与relname互查
SELECT oid FROM pg_class WHERE relname = 'mytable';
SELECT relname FROM pg_class WHERE oid in (16391,16394,16397);
根据表名称获取其所有属性:
SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;
参考> https://www.jianshu.com/p/1f096df47f83
- 表的oid与对应的文件
postgres=# create table test(id int) with oids;
CREATE TABLE
postgres=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
Has OIDs: yes
postgres=# create table test2(id int) ;
CREATE TABLE
postgres=# \d+ test2
Table "public.test2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
postgres=# select oid ,relname from pg_class where relname in ('test','test2');
oid | relname
-------+---------
16737 | test
16740 | test2
(2 rows)
postgres=# SELECT
postgres-# c.relname,
postgres-# t.spcname
postgres-# FROM
postgres-# pg_class c
postgres-# JOIN pg_tablespace t ON c.reltablespace = t.oid
postgres-# WHERE
postgres-# t.spcname = 'test';
relname | spcname
----------------------+---------
pg_toast_16731 | test
pg_toast_16731_index | test
tbl_test | test