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 usersDETAIL:  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 1cs_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