PostgreSQL

PostgreSQL Commands QuickStart：
[url]http://www.thegeekstuff.com/2009/04/15-practical-postgresql-database-adminstration-commands/[/url]
[url]http://www.linuxweblog.com/postgresql-reference[/url]

#查看 PostgreSQL 命令行工具的帮助，最有用的一个！！！！
\?
#重启 postgresql 服务 (ubuntu下)
$ sudo service postgresql restart
#登陆 PostgreSQL 命令行 (postgres 为其内置用户；这个默认的用户没有密码)
su - postgres
psql
#查看 SQL 语句的帮助
\h
#退出 PostgreSQL 命令行
\q
#列出所有库
\l
#切换数据库、用户等
\c[onnect] [DBNAME|- USER|- HOST|- PORT|-]
#查看当前库
select current_database();
#新建库
create database dbName;
#删除库
drop database dbName;
#列出当前库中的所有表
\d
#连接远程库 ( https://www.blog.akendo.eu/enable-remote-access-postgresql/ )
#如果连接被拒绝，参见 http://www.cyberciti.biz/faq/postgresql-remote-access-or-connection/
psql -h hostOfRemoteDB -U userOfRemoteDB -d nameOfRemoteDB
#查看所有用户
select * from pg_user;
查看当前用户是谁
select current_user;
#创建新用户
create user scott password 'tiger';
create user "www-data" password 'www-data'; //用户名中含特殊字符需用双引号引起来
#修改用户密码
\password
alter user postgres with password 'postgres';
#使某一用户拥有superuser角色，superuser对应的所有权限自然也赋给了该用户(当前用户需有权限做这个事，如默认的postgres)
alter user "www-data" with superuser;
# update query with join on two tables (http://stackoverflow.com/questions/2815953/update-query-with-join-on-two-tables)
UPDATE address 
SET cid = customers.id
FROM customers 
WHERE customers.id = address.id

PostgreSQL 9.2 Documentation：
[url]http://www.postgresql.org/docs/9.2/static/index.html[/url][quote]关于 Data Types - Numeric Types - Serial Types：
postgresql的Serial数据类型最终是通过sequence实现的，即：

CREATE TABLE tablename (
    colname SERIAL
);

完全等价与：

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

[/quote]


当使用某一用户连接提示 "Peer authentication failed for user" 时：
[url]http://blog.deliciousrobots.com/2011/12/13/get-postgres-working-on-ubuntu-or-linux-mint/[/url][quote]正确答案在该博文的评论里：
[b]You don't necessarily have to change the configuration. If you add -h 127.0.0.1 to the psql command it will connect through TCP instead of a Unix socket, in which case password authentication is used.[/b][/quote]


[b]shell脚本中执行psql，怎么免去输入密码的麻烦(psql command 没有提供输入密码的参数)：[/b]
[url]http://stackoverflow.com/questions/6405127/how-do-i-specify-a-password-to-psql-non-interactively[/url]

#!/bin/bash

echo "localhost:*:*:www-data:www-data" > $HOME/.pgpass
echo "`chmod 0600 $HOME/.pgpass`"

psql -h localhost -U www-data -w -d email -c "delete from email_logging where receive_date <= current_timestamp - interval'10 days'"

[size=medium][color=red][b][align=center]CRUD[/align][/b][/color][/size]

alter table tab_name add unique (col_name);

postgresql paging：

select user_id,email from email_queue order by id limit 5000
select user_id,email from email_queue order by id offset 5001

INSERT INTO core_email_queue(user_id,email)
 (SELECT user_id,email
    FROM email_queue
   order by id limit 5000 ) 

   INSERT INTO target_email_queue(user_id,email)
 (SELECT user_id,email
    FROM email_queue
   order by id offset 5001) 

Remove duplicate：

----去除按某列dup_col的重复
delete from dup_table
where id not in
(
	 select min(dup.id)
     from dup_table as dup
     group by dup.dup_col
);
-----去除按col1，col2，col3的组合作为重复判断标准的重复
delete from dup_table
where id not in
(
	 select min(dup.id)
     from dup_table as dup
     group by dup.col1, dup.col2, dup.col3
);

remote server copy:[quote]

lee@ubuntu:~$ scp /home/qa.txt root@192.168.1.159:/root
lee@ubuntu:~$ ssh root@192.168.1.159
domU-00-16-3e-00-00-31 ~ # psql -U www-data zhoukan
//注意postgresql copy 命令的 from 路径不能是相对路径，如(当前用户root)这里写成 ‘~/qa.txt’ 是不行的！
zhoukan=# copy target_email_queue(user_id, email) from '/root/qa.txt' with csv;

qa.txt为以下格式的标准csv文档：

20463047,"bestyyz@hotmail.com"
4823001,"xueye_0511@163.com"
19544966,"lei_designcaa@163.com"
20443601,"ne.dingding@gmail.com"
19824195,"312743454@qq.com"
18351606,"kebei123@126.com"
787387,"wztwn@vip.sina.com"
6525245,"bsspirit@163.com"
13635170,"javahuangchengzhao@163.com"
8550177,"sinber@126.com"

[/quote]


[b]Postgresql 中的 Unique Index 和常见的用来加唯一约束的 unique constraint 的区别：[/b]
[url]http://www.postgresql.org/docs/9.2/static/indexes-unique.html[/url][quote]PostgreSQL [b]automatically creates a unique index when a unique constraint or primary key is defined for a table[/b]. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.
[b]The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.（每个参与了 unique constraint 的列都会被隐式建立各自独立的 index，不需要再显式为这些列建立 index）[/b][/quote]


postgresql 中若使用了 distinct select, 则 order by 的列必须出现在 select 中，，否则会报 “for SELECT DISTINCT, ORDER BY expressions must appear in select list”，原因见：
[url]http://stackoverflow.com/questions/12693089/pgerror-select-distinct-order-by-expressions-must-appear-in-select-list[/url][quote] event_id | start_time
----------+------------------------
0 | Mar 17, 2013 12:00:00
1 | Jan 1, 1970 00:00:00
1 | Aug 21, 2013 16:30:00
2 | Jun 9, 2012 08:45:00
Now you want to grab a list of distinct event_ids, ordered by their respective start_times. But where should 1 go? Should it come first, because the one tuple starts on Jan 1, 1970, or should it go last because of the Aug 21, 2013?[/quote]