查看数据库
\l
添加删除数据库
CREATE DATABASE dbname;
DROP DATABASE [ IF EXISTS ] dbname;
连接数据库
\c database_name
查看数据库大小
select pg_size_pretty(pg_database_size('dbname'));
查询主键
SELECT
pg_constraint.conname AS pk_name,
pg_attribute.attname AS colname,
pg_type.typname AS typename
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.atttypid
WHERE
pg_class.relname = ' table_name'
AND pg_constraint.contype = 'p';
查询索引
SELECT
tablename,
indexname,
indexdef
FROM
pg_indexes
WHERE
schemaname='public'
ORDER BY
tablename,
indexname;
查看索引大小
select pg_relation_size('index_name');
添加删除主键
查看主键
\d [tablename]
....
"table_name_id_pkey" PRIMARY KEY, ....
添加主键
alter table server add primary key (id) ;
删除主键
ALTER TABLE [tablename] DROP CONSTRAINT table_name_id_pkey;
级联删除主键
ALTER TABLE [tablename] DROP CONSTRAINT table_name_id_pkey CASCADE;
删除外键
查看外键约束
\d [tablename]
....
"table_name_id_fkey" FOREIGN KEY (id) REFERENCES other_table(id) ....
删除外键
ALTER TABLE [tablename] DROP CONSTRAINT table_name_id_fkey;
添加索引
create index tbl_index on tablename(id,name);
添加hash索引
create index tbl_index on tablename using hash(id,name);
删除索引
DROP INDEX tbl_index;
屏蔽激活索引
屏蔽
update pg_index set indisvalid = false where indexrelid = 'test_pkey'::regclass
激活
update pg_index set indisvalid = true where indexrelid = 'test_pkey'::regclass