postgresql 通过数据字典建表，不用create TABLE

概述postgresql 通过数据字典建表，不用create TABLE 今天对postgresql的数据字典有个简单的了解，postgre的表结构等信息都是放在数据字典中的，那我们能不能简单的往数据字典中插入数据来实现建表的功能呢？ 以下做了一个简单的实验： 1.在postgresql的实例中创建一个表 create table test(a int); 然后去查以下几个数据字典，结果如下 pg_c

postgresql 通过数据字典建表，不用create table

今天对postgresql的数据字典有个简单的了解，postgre的表结构等信息都是放在数据字典中的，那我们能不能简单的往数据字典中插入数据来实现建表的功能呢？
以下做了一个简单的实验：

1.在postgresql的实例中创建一个表
create table test(a int);

然后去查以下几个数据字典，结果如下
pg_class:
select oID,* from pg_class where relname ='test';--表名
oID | relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelID | reltoastIDxID | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoIDs | relhaspkey | relhasrules | relhassubclass | relfroZenxID | relacl | reloptions
-------+---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------------+--------+------------
73728 | test | 2200 | 73729 | 10 | 0 | 73728 | 0 | 0 | 0 | 0 | 0 | f | f | r | 1 | 0 | 0 | 0 | 0 | 0 | f | f | f | f | 814 | |

--以下通过test表的oID查询
pg_attribute:
select * from pg_attribute where attrelID ='73728';
attrelID | attname | atttypID | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount
----------+----------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+--------------+------------+-------------
73728 | tableoID | 26 | 0 | 4 | -7 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0
73728 | cmax | 29 | 0 | 4 | -6 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0
73728 | xmax | 28 | 0 | 4 | -5 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0
73728 | cmin | 29 | 0 | 4 | -4 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0
73728 | xmin | 28 | 0 | 4 | -3 | 0 | -1 | -1 | t | p | i | t | f | f | t | 0
73728 | ctID | 27 | 0 | 6 | -1 | 0 | -1 | -1 | f | p | s | t | f | f | t | 0
73728 | a | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | f | f | f | t | 0

pg_depend:
select * from pg_depend where refobjID ='73728';
classID | objID | obJsubID | refclassID | refobjID | refobJsubID | deptype
---------+-------+----------+------------+----------+-------------+---------
1247 | 73729 | 0 | 1259 | 73728 | 0 | i

pg_type:
select oID,* from pg_type where typrelID = '73728';
oID | typname | typnamespace | typowner | typlen | typbyval | typtype | typisdefined | typdelim | typrelID | typelem | typinput | typoutput | typreceive | typsend | typanalyze | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typdefaultbin | typdefault
-------+---------+--------------+----------+--------+----------+---------+--------------+----------+----------+---------+-----------+------------+-------------+-------------+------------+----------+------------+------------+-------------+-----------+----------+---------------+------------
73729 | test | 2200 | 10 | -1 | f | c | t |,| 73728 | 0 | record_in | record_out | record_recv | record_send | - | d | x | f | 0 | -1 | 0 | |

tableoID,cmax,xmax,cmin,xmin,ctID都是这个表的隐藏字段，select的时候指定，是可以找到的
MysqL=# select tableoID,ctID,a from test;
tableoID | cmax | xmax | cmin | xmin | ctID | a
----------+------+------+------+------+------+---
(0 rows)

MysqL=# insert into test values(1);
INSERT 0 1
MysqL=# select tableoID,a from test;
tableoID | cmax | xmax | cmin | xmin | ctID | a
----------+------+------+------+------+-------+---
73728 | 0 | 0 | 0 | 842 | (0,1) | 1

可以看到，数据库中这个数据文件的大小是0
[MysqL@pttest4 data]$ ll ./base/16386/73728
-rw------- 1 MysqL MysqL 0 Dec 1 22:11 ./base/16386/73728

2.我们已经知道上述四个数据字典的数据了，这样子我们模仿，给这四个数据字典插入数据，是不是我们也可以新建一张表呢。
下面我们就新建一张 test_cxf的表，字典跟上面的一样。

a.首先往pg_class中插入数据：
MysqL=# insert into pg_class(relname,relnamespace,reltype,relowner,relam,relfilenode,reltablespace,relpages,reltuples,reltoastrelID,reltoastIDxID,relhasindex,relisshared,relkind,relnatts,relchecks,reltriggers,relukeys,relfkeys,relrefs,relhasoIDs,relhaspkey,relhasrules,relhassubclass,relfroZenxID,relacl,reloptions) values('test_cxf','2200','73731','10','0','73730','f','r','1','814','{}','{}');
INSERT 73730 1

可以看到，这一行的oID为73730

b.接着往pg_type中插入数据：
MysqL=# insert into pg_type(typname,typnamespace,typowner,typlen,typbyval,typtype,typisdefined,typdelim,typrelID,typelem,typinput,typoutput,typreceive,typsend,typanalyze,typalign,typstorage,typnotnull,typbasetype,typtypmod,typndims,typdefaultbin,typdefault) values('test_cxf','-1','c','t',','record_in','record_out','record_recv','record_send','-','d','x','','');
INSERT 73731 1

c.将pg_class中的reltype信息更新
UPDATE pg_class SET reltype=73731 WHERE oID = 73730;

d.往pg_attribute跟pg_depend中插入数据
insert into pg_attribute(attrelID,attname,atttypID,attstattarget,attlen,attnum,attndims,attcacheoff,atttypmod,attbyval,attstorage,attalign,attnotnull,atthasdef,attisdropped,attislocal,attinhcount ) values('73730','tableoID','26','4','-7','p','i','0');
insert into pg_attribute(attrelID,'cmax','29','-6','xmax','28','-5','cmin','-4','xmin','-3','ctID','27','6','s','a','23','0');
insert into pg_depend(classID,objID,obJsubID,refclassID,refobjID,refobJsubID,deptype) values('1247','1259','i');

e.查询数据
MysqL=# select * from test_cxf;
ERROR: Could not open relation 1663/16386/73800: No such file or directory
报错，因为没有数据文件，我们在base目录下touch一个空文件
[MysqL@pttest4 base]$ cd 16386
[MysqL@pttest4 16386]$ touch 73800
[MysqL@pttest4 16386]$ ll 73800
-rw-rw-r-- 1 MysqL MysqL 0 Dec 1 23:27 73800

简单查询一下数据，做一些数据 *** 作
MysqL=# /d test_cxf
table "public.test_cxf"
Column | Type | ModifIErs
--------+---------+-----------
a | integer |

MysqL=# select * from test_cxf;
a
---
(0 rows)
MysqL=# insert into test_cxf values(124);
INSERT 0 1
MysqL=# insert into test_cxf select * from test_cxf;
INSERT 0 1
MysqL=# insert into test_cxf select * from test_cxf;
INSERT 0 2
MysqL=# insert into test_cxf select * from test_cxf;
INSERT 0 4
MysqL=# select * from test_cxf;
a
-----
124
124
124
124
124
124
124
124
(8 rows)

MysqL=# create index a_index on test_cxf(a);
CREATE INDEX
MysqL=# /d test_cxf
table "public.test_cxf"
Column | Type | ModifIErs
--------+---------+-----------
a | integer |
Indexes:
"a_index" btree (a)

做了一个简单的测试，是可以的

使用这种方法要对数据字典非常数据，我们刚刚只是建了一个简单的表，所涉及的数据字典比较少，如果有分区表，索引表等就会非常复杂了。

总结

以上是内存溢出为你收集整理的postgresql 通过数据字典建表，不用create TABLE全部内容，希望文章能够帮你解决postgresql 通过数据字典建表，不用create TABLE所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。