1.PostgreSQL中的serial

等效于 MySQL 里头的 int auto_increment ，比如：

create table (
  id int auto_increment
);

等效于 PostgreSQL 里头的：

create table (
  id serial
);

CREATE TABLE foo (
  id SERIAL,
  bar varchar
);

INSERT INTO "foo" (bar) values ('blah');
INSERT INTO "foo" (bar) values ('blah');

SELECT * FROM foo;
1,blah
2,blah

参考链接：

• postgresql int auto_increment
• http://stackoverflow.com/questions/787722/postgresql-autoincrement
• http://www.postgresql.org/docs/9.3/static/datatype-numeric.html
• http://www.postgresqltutorial.com/postgresql-data-types/
• http://dba.stackexchange.com/questions/1281/how-do-i-specify-that-a-column-should-be-auto-incremented-in-pgadmin

2.PostgreSQL的建表、插入操作

create table company(
   id int primary key     not null,
   name           text    not null,
   age            int     not null,
   address        char(50),
   salary         real,
   join_date	  date
);

insert into company (id,name,age,address,salary,join_date) values (1, 'paul', 32, 'california', 20000.00 ,'2001-07-13');
insert into company (id,name,age,address,join_date) values (2, 'allen', 25, 'texas', '2007-12-13');
insert into company (id,name,age,address,salary,join_date) values (3, 'teddy', 23, 'norway', 20000.00, default );
insert into company (id,name,age,address,salary,join_date) values (4, 'mark', 25, 'rich-mond ', 65000.00, '2007-12-13' ), (5, 'david', 27, 'texas', 85000.00 , '2007-12-13');

参考链接：

• http://www.postgresqltutorial.com/postgresql-insert/
• http://www.tutorialspoint.com/postgresql/postgresql_insert_query.htm
• http://www.postgresql.org/docs/9.0/static/dml-insert.html
• http://www.tutorialspoint.com/postgresql/postgresql_create_table.htm
• http://www.tutorialspoint.com/postgresql/postgresql_insert_query.htm

3.如何快速获取PostgreSQL数据库中行的个数？

cur.execute("select reltuples from pg_class where oid = 'public.%s'::regclass;" % (table_name,))
cur.execute("select n_live_tup from pg_stat_user_tables where relname=%s;" % (table_name,))
cur.execute("select count(*) from %s;" % (table_name,))

• http://stackoverflow.com/questions/14570488/how-do-i-speed-up-counting-rows-in-a-postgresql-table
• https://wiki.postgresql.org/wiki/Slow_Counting
• https://wiki.postgresql.org/wiki/Count_estimate

4.PostgreSQL常见/有用的操作命令

1.查找最大的表
# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;

relname – name of the relation/table.
relpages - relation pages ( number of pages, by default a page is 8kb )
pg_class – system table, which maintains the details of relations
limit 1 – limits the output to display only one row.

2.计算PostgreSQL数据库在磁盘中所占大小
# SELECT pg_database_size('geekdb');
# SELECT pg_size_pretty(pg_database_size('geekdb'));

3.计算某个表在磁盘中所占大小
# SELECT pg_size_pretty(pg_total_relation_size('big_table'));
# SELECT pg_size_pretty(pg_relation_size('big_table'));	#不包括索引

4.查看某一表的所有索引信息
# \d table_name

5.快速产生大量连续数字并插入表中
# INSERT INTO numbers (num) VALUES ( generate_series(1,1000));

6.计算某个表中行的个数
# select count(*) from table;
# select count(col_name) from table;	#列col_name中非空行的个数
# select count(distinct col_name) from table;

7.计算某个表某一列的最值
# select max(col_name) from table;
# SELECT MAX(num) from number_table where num < ( select MAX(num) from number_table );	#次大值
# select min(col_name) from table;
# SELECT MIN(num) from number_table where num > ( select MIN(num) from number_table );	#次小值

8.查看PostgreSQL中有哪些可用的基础数据类型
# SELECT typname,typlen from pg_type where typtype='b';

typname – name of the datatype
typlen – length of the datatype

9.将查询的结果导出至文件
# \o output_file
# SELECT * FROM pg_class;
# \o

10.查看当前所在数据库
# SELECT current_database();
# \conninfo

• http://www.thegeekstuff.com/2009/05/15-advanced-postgresql-commands-with-examples/  #Nice
• http://razorsql.com/articles/postgresql_limit_query.html
• https://www.digitalocean.com/community/tutorials/how-to-create-data-queries-in-postgresql-by-using-the-select-command
• http://stackoverflow.com/questions/2276644/list-all-tables-in-postgresql-information-schema

5.相关调试信息

• sql – http://stackoverflow.com/questions/5267715/whats-the-correct-query-for-getting-the-current-number-of-connections-in-a-postg
• sql – http://stackoverflow.com/questions/18907047/postgres-db-size-command
• http://stackoverflow.com/questions/464623/how-can-you-get-the-active-users-connected-to-a-postgresql-database-via-sql
• postgresql – http://dba.stackexchange.com/questions/58312/psql-show-current-database

6.PostgreSQL中的show databases命令：“postgresql show databases of select”

mysql: show databases;
postgresql: \l
postgresql: SELECT datname FROM pg_database;
postgresql: SELECT datname FROM pg_database WHERE datistemplate = false;

• http://dba.stackexchange.com/questions/1285/how-do-i-list-all-databases-and-tables-using-psql

7.PostgreSQL中的show tables命令：“postgresql show tables of select”

mysql: show tables;
postgresql: \d
postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
postgresql: SELECT table_schema || '.' || table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema');
postgresql: SELECT table_schema,table_name FROM information_schema.tables where table_schema = 'public' ORDER BY table_schema,table_name;

• http://stackoverflow.com/questions/12445608/psql-list-all-tables
• http://onewebsql.com/blog/list-all-tables
• http://stackoverflow.com/questions/769683/show-tables-in-postgresql
• http://stackoverflow.com/questions/2276644/list-all-tables-in-postgresql-information-schema

8.PostgreSQL中的describe table命令：“postgresql show tables of select”、“postgresql show columns”

mysql: show columns;
postgresql: \d table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';
postgresql: select column_name from information_schema.columns;
postgresql: select table_name, column_name from information_schema.columns;
postgresql: select column_name from information_schema.columns where table_name='pg_proc';

• http://stackoverflow.com/questions/109325/postgresql-describe-table
• http://dba.stackexchange.com/questions/22362/how-do-i-list-all-columns-for-a-specified-table
• http://www.linuxscrew.com/2009/07/03/postgresql-show-tables-show-databases-show-columns/
• http://www.linux4beginners.info/?q=node/pgsql-show-tables-show-databases-show-columns

9.PostgreSQL中的select top N命令：“postgresql show tables of select”

• http://stackoverflow.com/questions/2597098/get-the-last-n-rows-in-the-database-in-order

10.如何在PostgreSQL中查询另外一个数据库的表内容？

比如在MySQL中是：

Current_DB_1 #> Select * From DB_2.TABLE_2;

参考信息：

不能直接查，需要安装dblink，或者postgres_fdw

# With PostgreSQL 9.1 or later, installation of additional modules has been simplified.

安装 dblink 扩展
postgres=# create extension dblink;
如果提示“CREATE EXTENSION”，说明安装成功。

安装 postgres_fdw 扩展
postgres=# create extension postgres_fdw;
如果提示“CREATE EXTENSION”，说明安装成功。

因为PostgreSQL更推荐一个db，下面建多个schema；和Oracle类似，MySQL没有schema的概念。

参考链接：

• https://wiki.postgresql.org/wiki/FAQ
• http://stackoverflow.com/questions/46324/possible-to-perform-cross-database-queries-with-postgres
• http://www.postgresql.org/docs/9.4/static/postgres-fdw.html
• http://www.postgresql.org/docs/9.4/static/dblink.html
• http://stackoverflow.com/questions/3862648/how-to-use-install-dblink-in-postgresql
• http://stackoverflow.com/questions/5075193/installing-dblink-for-postgres-9
• http://michael.otacoo.com/postgresql-2/first-steps-with-dblink-on-postgres/
• =
• PostgreSQL数据库dblink和postgres_fdw扩展使用比较
• Postgresql 9.3外部数据封装的新功能
• Postgresql DBLink – Dufe王彬 – 博客园
• Postgresql中常用dblink扩展的使用
• PostgreSQL DBLink的使用
• postgresql 如何安装dblink_百度知道
• PostgreSQL的小技巧 dblink
• Postgres访问其他PostgresQL数据库的功能DBLINK