顯示數據庫名

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

datname | size -----------------+-------------postgres | 7240216template1 | 7119364template0 | 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=# \lList of databasesName | 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/postgrestemplate1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +| | | | | postgres=CTc/postgres (4 rows)

\dt ? 列舉表，相當于mysql的show tables
cs_dev=# \dtList of relationsSchema | Name | Type | Owner --------+----------+-------+----------public | user_tbl | table | postgres (1 row)

查看表結構，相當于desc tblname,show columns from tbname
\d tblname

cs_dev=# \d user_tblTable "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-04cs | 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 | varcharcs_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

總結

以上是生活随笔為你收集整理的postgresql操作的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。