postgresql命令记录

查看数据库

\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