PostgreSQL常用语句

最近一直用到PostgreSQL，这里总结一下常用的sql。

一、数据库

1.查询服务器版本

1.1查询详细信息

select version();

1.2查看版本信息

show server_version;

 1.3查看数字版本信息包括小版本号

show server_version_num;

或 

select current_setting('server_version_num');

 注意：select current_setting(‘server_version_num’);返回类型为text，如果需要可以转换为interger

select current_setting('server_version_num')::integer;

2.创建数据库 

create database testdb;

3.修改数据库

重命名数据库名称：

alter database testdb rename to new_name;

修改数据库并发连接数量：

alter database testdb connection limit 10;

修改数据库的表空间：

alter database testdb set tablespace new_tablespace;

4.删除数据库

drop database if exists testdb；

5.其他

查询用户

select usename from pg_user; 

二、表

1.新建表

create table student(
    id serial primary key,
    name varchar(64) not null,
    age integer not null,
    sex integer not null,
    createtime timestamp without time zone not null default now(),
    updatetime timestamp without time zone
);

id为主键，其中serial代表自增，默认从1开始增加，每次自增1。 

复制表：

create table student_copy as select * from student;

2.删除表

delete table student;

3.查询表

查询student表是否存在：

select * from pg_class where relname = 'student' and relkind = 'r';
or
select * from pg_tables where tablename = 'student';

4.修改表

4.1对表本身操作

4.1.1重命名表名称

alter table student rename to new_student;

4.1.2添加表字段

alter table student add column height integer not null;

4.1.3删除表字段

alter table student drop column sex;

4.1.4重命名表字段

alter table student rename column name to new_name;

4.1.5更改字段属性

a)查询表所有字段属性

select c.relname, col_description(a.attrelid, a.attnum) as comment, format_type(a.atttypid, a.atttypmod) as type, a.attname as name, a.attnotnull as notnull from pg_class as c, pg_attribute as a where a.attrelid = c.oid and a.attnum > 0 and c.relname = 'student';

这里用到了pg_class和pg_attribute系统表，pg_class系统表记录了数据表、索引(仍然需要参阅pg_index)、序列、视图、复合类型和一些特殊关系类型的元数据；pg_attribute系统表存储所有表(包括系统表，如pg_class)的字段信息，数据库中的每个表的每个字段在pg_attribute表中都有一行记录。pg_class系统表中字段relname记录表名称，oid字段标识表的唯一id；pg_attribute系统表中attrelid也标识表的id。

b)查询表中指定字段的属性

select c.relname, col_description(a.attrelid, a.attnum) as comment, format_type(a.atttypid, a.atttypmod) as type, a.attname as name, a.attnotnull as notnull from pg_class as c, pg_attribute as a where a.attrelid = c.oid and a.attnum > 0 and c.relname = 'student' and a.attname = 'name';

 pg_attribute系统表中atttypid与attname字段分别是表字段类型与名称。

c)更改字段由int4更改为int8:

alter table student alter column sex type bigint;

d)如果把字段name把属性Text转化为int，原来text里面存在空啥的，可以

alter table student alter column name type integer using (trim(name))::integer;

e)增加/删除字段约束

e1.增加/删除字段的非空约束

增加字段的非空约束：

alter table student alter column updatetime set not null;

这个约束会立即进行数据检查，所以表在加入约束之前必须确保已有数据符合约束条件，否则执行会报错；

这种情况下，可以先删除为null的数据行，再执行增加非空约束：

delete from student where updatetime  is null;
alter table student alter column updatetime set not null;

注意，只有增加非空约束才这样使用，而且非空约束没有名字。

删除非空约束：

alter table student alter column updatetime drop not null;

也只有非空约束才能这样删除。 

e2.增加/删除 字段的检查约束：

在增加检查约束前，需要先删除已存在的不满足检查约束的数据：

delete from student where age <= 3;
--ck_student_cheack_age是检查约束名称
alter table student add constraint ck_student_check_age check(age > 3);

删除字段检查约束：

alter table student drop constraint ck_student_check_age ;

e3. 增加/删除 一个唯一约束：

在增加字段的唯一约束：

--唯一约束名称uk_student_nuique_name_age 为约名称，给name和age字段添加唯一约束
alter table student add constraint uk_student_unique_name_age unique(name,age);

删除字段的唯一约束：

alter table student drop constraint uk_student_unique_name_age;

4.2对表记录操作

4.2.1插入一条记录

insert into student (name, age, sex, createtime, updatetime) values('Tom', '18', 1, '2018-11-29 17:00:02', '2018-11-29 17:00:02') ;

可以把student1表符合条件的记录拷贝到表student2：

insert into student1 select * from student2 where age > 18;

4.2.2删除记录

删除符合条件的记录：

delete from student where id = 1;

delete from student where age > 18;

delete from student where createtime <= '2018-01-01 00:00:00';

清空表记录：

delete from student;
or
truncate table student;

区别：truncate table 表名 (注:不带where语句) 速度快,而且效率高。因为delete语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate table通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

4.2.3查询记录

--查询全部记录
select * from student;

--查询符合条件的记录
select * from student where age > 18;

--指定查询条件和要查询的字段
select name, age, sex from student where age > 18;

另外可以使用下面的sql查询数据库的连接信息：

select * from pg_stat_activity;

包含：客户端user、ip、执行语句，状态、时间 。

4.2.4修改记录

--更新符合条件记录的更新时间，保留到系统时间的秒位
update student set updatetime = date_trunc('second', now())  where age = 18;