postgresql 基本语法

模式

-- 创建模式
create schema myschema;

-- 设置当前模式
set search_path to myschema;

-- 查看当前数据库所有模式
select * from information_schema.schemata;

-- 删除模式
drop schema myschema;

-- 删除模式以及模式下的所有表
drop schema myschema cascade;

查询

-- 创建测试数据
create table pgccc(id int, name varchar(10));
insert into pgccc values (1, 'x');
insert into pgccc values (2, 'john');

-- with 语句
testdb=# with test as (select * from pgccc) select * from test where name = 'x';
 id | name
----+------
  1 | x
(1 row)

-- 递归查询
testdb=# with recursive test(x) as (select 2 union select id from pgccc) select sum(x) from test;
 sum
-----
   3
(1 row)

索引

-- 创建单列索引
create index idx_name on pgccc (name);

-- 创建组合索引
create index idx_id_name on pgccc (id, name);

-- 创建唯一索引
create unique index idx_unique_name on pgccc (name);

-- 查看索引
testdb=# select * from pg_indexes where tablename = 'pgccc';
 schemaname | tablename | indexname | tablespace |                          indexdef
------------+-----------+-----------+------------+------------------------------------------------------------
 myschema   | pgccc     | idx_name  |            | CREATE INDEX idx_name ON myschema.pgccc USING btree (name)
(1 row)


-- 删除索引
drop index idx_name;

表结构修改

-- 创建表
create table t1 (id int);

-- 添加列
alter table t1 add column name varchar(20);

-- 删除列
alter table t1 drop column name;

-- 修改列的数据类型
alter table t1 alter column id type int4;

-- 设置列非空
alter table t1 add name varchar(20);
alter table t1 alter name set not null;

-- 添加唯一索引
alter table t1 add constraint unique_id unique (id);

-- 给列设置检查
-- 注意在 check 中，涉及到 NULL 值的处理被视为 TRUE
alter table t1 add age int2;
alter table t1 add constraint age_check check (age > 0);

-- 添加主键
alter table t1 add constraint primarykey_id primary key (id);

-- 删除主键
alter table t1 drop constraint primarykey_id;

删除表

-- 清空数据但保留结构，立即释放空间
truncate table pgccc;

-- 删除表和数据，立即释放空间
drop table pgccc;

-- 删除数据，不会降低高水位，不会释放空间
delete from table pgccc;


--------------------------------------------------------------
-- 释放演示
--------------------------------------------------------------

-- 查看表大小
testdb=# select pg_size_pretty(pg_relation_size('pgccc'));
 pg_size_pretty
----------------
 8192 bytes
(1 row)

-- delete 删除数据
testdb=# delete from pgccc ;
DELETE 3

-- 再次查看表大小，空间未释放
testdb=# select pg_size_pretty(pg_relation_size('pgccc'));
 pg_size_pretty
----------------
 8192 bytes
(1 row)

-- truncate 删除数据
truncate table pgccc;

-- 再次查看表大小，已释放
testdb=# select pg_size_pretty(pg_relation_size('pgccc'));
 pg_size_pretty
----------------
 0 bytes
(1 row)