postgre sql常用命令

—帮助命令
test=# \h

—查看表结构
postgres-# \d pg_database
Table “pg_catalog.pg_database”
Column | Type | Collation | Nullable | Default
---------------±----------±----------±---------±--------
datname | name | | not null |
datdba | oid | | not null |
encoding | integer | | not null |
datcollate | name | | not null |
datctype | name | | not null |
datistemplate | boolean | | not null |
datallowconn | boolean | | not null |
datconnlimit | integer | | not null |
datlastsysoid | oid | | not null |
datfrozenxid | xid | | not null |
datminmxid | xid | | not null |
dattablespace | oid | | not null |
datacl | aclitem[] | | |

–找出数据库对应的OID
postgres=# Select datname,oid from pg_database where datname=‘test’;
datname | oid
---------±------
test | 16384

—找出表对应的oid
test=# \d
List of relations
Schema | Name | Type | Owner
--------±------±------±---------
public | test1 | table | postgres

select relname,oid from pg_class where relname=‘test1’;
relname | oid
---------±------
test1 | 16385

—可以根据oid的关系找到相应的路径
test=# select pg_relation_filepath(‘test1’);
pg_relation_filepath

base/16384/24577
(1 row)

–创建表
test=# create table test1 (id int);
CREATE TABLE

–创建新的数据库执行：
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace_name ]
[ CONNECTION LIMIT [=] connlimit ] ]

ostgres=# CREATE DATABASE test WITH OWNER postgres ENCODING=‘UTF-8’;

—创建数据库并指定表空间
CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;

创建数据目录
/monchickey/pgsql/bin/nitdb -D /monchickey/data
或者
/monchickey/pgsql/bin/pg_ctl -D /monchickey/data initdb

创建表空间
postgres=# CREATE TABLESPACE indexspace OWNER postgres LOCATION ‘/monchickey’;
CREATE TABLESPACE

查看基础目录中的文件每个编号对应着一个数据库
[postgres@ora11r2 base]$ pwd
/monchickey/data/base
[postgres@ora11r2 base]$ ls -lrt
total 12
drwx------. 2 postgres postgres 4096 Jun 29 04:05 1
drwx------. 2 postgres postgres 4096 Jun 29 04:05 13801
drwx------. 2 postgres postgres 4096 Jun 29 04:05 13802
查看这些数字目录对应是是那几个库
test=# Select datname,oid from pg_database where oid=13801;
datname | oid
-----------±------
template0 | 13801
(1 row)

test=# Select datname,oid from pg_database where oid=13802;
datname | oid
----------±------
postgres | 13802
(1 row)