PostgreSQL常用命令大全

[postgres@cjc-db-01 ~]$ pg_ctl -D pg/data -l pg/log/pg.log start

[postgres@cjc-db-01 ~]$ psql
[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W cjcdb

cjcdb=# \q

cjcdb=# \h
cjcdb=# \h create table

cjcdb=# \?

cjcdb=# select version();
cjcdb=# show server_version;

cjcdb=# \l
cjcdb=# \l+
cjcdb=# select oid,datname from pg_database;

cjcdb=# select current_database();

postgres=# \c cjcdb

cjcdb=# \c - chen

cjcdb=# select user;
cjcdb=# select current_user;

cjcdb=> \d
cjcdb=> select tableowner,schemaname,tablename,tablespace from pg_tables where tableowner='cjc';

cjcdb=# \di

cjcdb=# select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 't1';
cjcdb=# select pg_get_indexdef(上面语句查到的b.indexrelid);

cjcdb=# \conninfo

cjcdb=# select * from pg_stat_activity;

cjcdb=> \d t1;
cjcdb=> \d+ t1;  
cjcdb=> select table_schema,table_name,column_name,data_type,character_maximum_length from information_schema.columns where table_name='t1';

cjcdb=# \dv
cjcdb=# select * from pg_views where schemaname = 'public';
cjcdb=# select * from information_schema.views where table_schema = 'public';

cjcdb=# select * from information_schema.triggers;

cjcdb=# select * from information_schema.sequences where sequence_schema = 'public';

cjcdb=# select * from pg_constraint where contype = 'p'
cjcdb=# select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid and a.relname = 't1';

cjcdb=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 't1';

cjcdb=# select pg_relation_size('t1');

cjcdb=# select pg_size_pretty(pg_relation_size('i_t1_id'));

cjcdb=# select pg_size_pretty(pg_total_relation_size('t1'));

cjcdb=# select pg_size_pretty(pg_tablespace_size('cjctbs'));

cjcdb=# select rolname,rolsuper,rolcreatedb from pg_roles;

cjcdb=# \dg
cjcdb=# \du

cjcdb=# select * from information_schema.table_privileges where grantee='cjc';
cjcdb=# \dp

cjcdb=# \db

cjcdb=# show data_directory;

cjcdb=# show config_file;

cjcdb=# select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database; 
cjcdb=# select pg_size_pretty(pg_database_size('cjcdb'));

cjcdb=# \x
Expanded display is on.
cjcdb=# select * from t1;
...     
cjcdb=# \x
Expanded display is off.

cjcdb=# \timing on
cjcdb=# \timing of

cjcdb=# \! pwd

cjcdb=# select * from t1;
 id |    name    
----+------------
  1 | a         
  2 | aaa       
(2 rows)


cjcdb=# \pset border 2
Border style is 2.
cjcdb=# select * from t1;
+----+------------+
| id |    name    |
+----+------------+
|  1 | a          |
|  2 | aaa        |
+----+------------+
(2 rows)

cjcdb=# \pset border 0
Border style is 0.
cjcdb=# select * from t1;
id    name    
-- ----------
 1 a         
 2 aaa       
(2 rows)

cjcdb=# \pset format unaligned
Output format is unaligned.
cjcdb=# select * from t1;
id|name
1|a         
2|aaa       
(2 rows)

cjcdb=# \pset fieldsep '\t'
Field separator is "  ".
cjcdb=# select * from t1;
id  name
1  a         
2  a

cjcdb=# \o t1.txt
cjcdb=# select * from t1;
[postgres@cjc-db-01 ~]$ cat t1.txt 
id  name
1  a         
2  aaa       
(2 rows)

cjcdb=# \echo hahaha
hahaha

[postgres@cjc-db-01 ~]$ cat t1.sql 
select * from t1;
[postgres@cjc-db-01 ~]$ psql -h 172.16.6.137 -p 5678 -U cjc -W cjcdb -f t1.sql 
Password for user cjc: 
 id |    name    
----+------------
  1 | a         
  2 | aaa       
(2 rows)

cjcdb=# \l

cjcdb=# \l
********* QUERY **********
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
**************************


                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 cjcdb     | cjc      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/cjc              +
           |          |          |             |             | cjc=CTc/cjc          +
           |          |          |             |             | chen=CTc/cjc
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | cjc=c/postgres
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)