PostgreSQL查询表名称及表结构
1. 查询表名称
在psql状态下查询表名称
\dt
postgres 基本操作
登录
$ psql (连接数据库，默认用户和数据库都是postgres)
$ psql -U <user> -d <dbname>
1
2
数据库操作
// 列举数据库，相当于show databases
$ \l
1
// 切换数据库，相当与use dbname
$ \c <dbname>
1
// 列举表，相当与show tables
$ \dt
1
// 查看表结构，相当于desc
$ \d tblname
1
// 创建数据库
$ create database <dbname>
1
// 删除数据库
$ drop database <dbname>
1
// 创建表
$ create table ([字段名1] [类型1] ;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;);
1
// 在表中插入数据
$ insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......);
1
// 备份数据库
$ pg_dump -U postgres -f /tmp/postgres.sql postgres (导出postgres数据库保存为postgres.sql)
$ pg_dump -U postgres -f /tmp/postgres.sql -t test01 postgres (导出postgres数据库中表test01的数据)
$ pg_dump -U postgres -F t -f /tmp/postgres.tar postgres (导出postgres数据库以tar形式压缩保存为postgres.tar)
1
2
3
// 恢复数据库
$ psql -U postgres -f /tmp/postgres.sql bk01 (恢复postgres.sql数据到bk01数据库)
$ pg_restore -U postgres -d bk01 /tmp/postgres.tar (恢复postgres.tar数据到bk01数据库)
1
2
用户操作
// 切换用户
$ \c - <username>
1
// 创建用户并设置密码
$ CREATE USER 'username' WITH PASSWORD 'password';
$ CREATE ROLE 'username' CREATEDB PASSWORD 'password' LOGIN; (创建角色并授予创建数据库及密码登录的属性)
1
2
// 修改用户密码
$ ALTER USER 'username' WITH PASSWORD 'password';
1
// 数据库授权
$ GRANT ALL PRIVILEGES ON DATABASE 'dbname' TO 'username';
1
// 修改用户权限
$ ALTER ROLE 'username' createdb ; (授予创建数据库权限)
$ ALTER ROLE 'username' superuser ;(授予超级管理员权限)
1
2
// 角色属性
属性说明
login只有具有 LOGIN 属性的角色可以用做数据库连接的初始角色名。
superuser数据库超级用户
createdb创建数据库权限
createrole允许其创建或删除其他普通的用户角色(超级用户除外)
replication做流复制的时候用到的一个用户属性，一般单独设定。
password在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关
inherit用户组对组员的一个继承标志，成员可以继承用户组的权限特性
SQL方式查看表名称
select tablename from pg_tables where schemaname='public'
2. 查询表结构
在psql状态下查询表结构
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 = 'udoc_saldiscount'
and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY a.attnum;
创建表
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns ) );
mysql 和 postgres 常用命令对比
1.查看所有表
mysql: SHOW TABLES
postgresql: \d
postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; （显示public下的表，这样指定schema可以过滤一些系统表）
2.查看所有数据库
mysql: SHOW DATABASES
postgresql: \l
postgresql: SELECT datname FROM pg_database;
3.查看表结构
mysql: SHOW COLUMNS （或 DESCRIBE TABLE）
postgresql: \d table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';
PostgreSQL的表一般都是建立在public这个schema下的，假如现在有个数据表t_student，可以用以下几种方式来查询表结构和索引信息。
使用\d元命令查看表字段信息和索引信息
在cmd界面使用psql连接db后，输入\d加上表名即可：
\d t_student
1
通过系统数据字典查询表结构
select
col.table_schema,
col.table_name,
col.ordinal_position,
col.column_name,
col.data_type,
col.character_maximum_length,
col.numeric_precision,
col.numeric_scale,
col.is_nullable,
col.column_default,
des.description
from
information_schema.columns col left join pg_description des on
col.table_name::regclass = des.objoid
and col.ordinal_position = des.objsubid
where
table_schema = 'public'
and table_name = 't_student'
order by
ordinal_position;
或者简单点：
select * from information_schema.columns
where table_schema='public' and table_name='t_student';
通过系统数据字典查询索引信息
select
A.SCHEMANAME,
A.TABLENAME,
A.INDEXNAME,
A.TABLESPACE,
A.INDEXDEF,
B.AMNAME,
C.INDEXRELID,
C.INDNATTS,
C.INDISUNIQUE,
C.INDISPRIMARY,
C.INDISCLUSTERED,
D.DESCRIPTION
from
PG_AM B left join PG_CLASS F on
B.OID = F.RELAM left join PG_STAT_ALL_INDEXES E on
F.OID = E.INDEXRELID left join PG_INDEX C on
E.INDEXRELID = C.INDEXRELID left outer join PG_DESCRIPTION D on
C.INDEXRELID = D.OBJOID,
PG_INDEXES A
where
A.SCHEMANAME = E.SCHEMANAME
and A.TABLENAME = E.RELNAME
and A.INDEXNAME = E.INDEXRELNAME
and E.SCHEMANAME = 'public'
and E.RELNAME = 't_student';
查询所有的表名
select
n.nspname,
relname
from
pg_class c,
pg_namespace n
where
c.relnamespace = n.oid
and nspname = 'public'
and relkind = 'r'
order by
relname
查询主键
select pg_attribute.attname as colname,pg_type.typname as typename,pg_constraint.conname as pk_name from pg_constraint inner join pg_class
on pg_constraint.conrelid = pg_class.oid
inner join pg_attribute on pg_attribute.attrelid = pg_class.oid
and pg_attribute.attnum = pg_constraint.conkey[1]inner join pg_type on pg_type.oid = pg_attribute.atttypidwhere pg_class.relname = 'tablename' and pg_constraint.contype='p'
查询主键和其他字段整合
select ordinal_position as Colorder,column_name as ColumnName,udt_name as TypeName,
coalesce(character_maximum_length,numeric_precision,-1) as Length,numeric_scale as Scale,
case is_nullable when 'NO' then 0 else 1 end as CanNull,column_default as DefaultVal,
case when position('nextval' in column_default)>0 then 1 else 0 end as IsIdentity,
case when b.pk_name is null then 0 else 1 end as IsPK,c.DeText
from information_schema.columns
left join (
select pg_attr.attname as colname,pg_constraint.conname as pk_name from pg_constraint
inner join pg_class on pg_constraint.conrelid = pg_class.oid
inner join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid and pg_attr.attnum = pg_constraint.conkey[1]
inner join pg_type on pg_type.oid = pg_attr.atttypid
where pg_class.relname = 'ceshi' and pg_constraint.contype='p'
) b on b.colname = information_schema.columns.column_name
left join (
select attname,description as DeText from pg_class
left join pg_attribute pg_attr on pg_attr.attrelid= pg_class.oid
left join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelid and pg_desc.objsubid=pg_attr.attnum
where pg_attr.attnum>0 and pg_attr.attrelid=pg_class.oid and pg_class.relname='ceshi'
)c on c.attname = information_schema.columns.column_name
where table_schema='public' and table_name='ceshi' order by ordinal_position asc