postgresql操作

显示数据库名

postgres=# select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database;
  

     datname     |    size     
-----------------+-------------
 postgres        |     7240216
 template1       |     7119364
 template0       |     7119364
(3 rows)

session问题

postgres=# DROP DATABASE pre_dev;                                                                       
ERROR:  database "pre_dev" is being accessed by other users
DETAIL:  There is 1 other session using the database.
postgres=# SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='pre_dev' AND pid<>pg_backend_pid();
 pg_terminate_backend 
----------------------
 t
(1 row)

修改数据库名

postgres=# ALTER DATABASE pre_dev RENAME  TO pre_devbak;                                                            
ALTER DATABASE

\c dbname    切换数据库,相当于mysql的use dbname

postgres=# \c pre_dev;
You are now connected to database "pre_dev" as user "postgres".

\l 列举数据库，相当于mysql的show databases

postgres=# \l
                                    List of databases
      Name       |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------------+----------+----------+------------+------------+-----------------------
 postgres        | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 pre_dev         | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0       | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                 |          |          |            |            | postgres=CTc/postgres
 template1       | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                 |          |          |            |            | postgres=CTc/postgres
(4 rows)

\dt   列举表，相当于mysql的show tables

cs_dev=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | user_tbl | table | postgres
(1 row)

查看表结构，相当于desc tblname,show columns from tbname
\d tblname

cs_dev=# \d user_tbl
             Table "public.user_tbl"
   Column    |         Type          | Modifiers 
-------------+-----------------------+-----------
 name        | character varying(20) | 
 signup_date | date         

\di 查看索引 

创建数据库

postgres=# create database pre_dev; 
CREATE DATABASE

创建表

cs_dev=# CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
CREATE TABLE

显示所有表 相当于mysql的show tables;  

cs_dev=# SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
 table_name 
------------
 user_tbl
(1 row)

插入数据

cs_dev=# INSERT INTO user_tbl(name, signup_date) VALUES('cs', '2017-12-25');
INSERT 0 1
cs_dev=# select * from user_tbl;                                            
 name | signup_date 
------+-------------
      | 2017-12-04
 cs   | 2017-12-25
(2 rows)

显示表结构 相当与mysql的describe table_name; 

cs_dev=# SELECT table_catalog, table_schema,table_name, column_name,udt_name FROM information_schema.columns WHERE table_name ='user_tbl';
 table_catalog | table_schema | table_name | column_name | udt_name 
---------------+--------------+------------+-------------+----------
 cs_dev        | public       | user_tbl   | name        | varchar
 cs_dev        | public       | user_tbl   | signup_date | date
(2 rows)

显示所有表的记录

select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r' and relnamespace = (select oid from pg_namespace where nspname='public') order by rowCounts desc limit 200 offset 0;

cs_dev=# select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r' and relnamespace = (select oid from pg_namespace where nspname='public') order by rowCounts desc limit 200 offset 0;
 table_name | rowcounts 
------------+-----------
 user_tbl   |         2
(1 row)

导出

pg_dump -h 127.0.0.1 -p 5432 -U postgres   core_dev >  D:/java/git/core_dev.sql

导入

psql -d core_dev -U postgres  < D:/java/git/core_dev.sql