$ sudo apt-get install postgresql-9.4
列出已存在的資料庫：
$ sudo -u postgres psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
建立資料庫：
$ sudo -u postgres createdb MyDB
刪除資料庫：
$ sudo -u postgres dropdb MyDB
建立使用者(-P: 建立密碼, -l: 有登入權限[預設], -L: 禁止登入, -s: 超級使用者, -d: 建立資料庫權限)：
$ sudo -u postgres createuser -P -s -d -l root
$ sudo -u postgres createuser -s -d -l user1
$ sudo -u postgres createuser -s -l user2
$ sudo -u postgres createuser -l user3
$ sudo -u postgres createuser -d user4
$ sudo -u postgres createuser -s user5
列出使用者列表：
$ sudo -u postgres psql -c "SELECT * FROM pg_user";
usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig
----------+----------+-------------+----------+-----------+---------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
user1 | ####1 | t | t | t | f | ******** | |
user2 | ####2 | t | t | t | f | ******** | |
user3 | ####3 | f | f | f | f | ******** | |
user4 | ####4 | t | f | f | f | ******** | |
user5 | ####5 | t | t | t | f | ******** | |
root | ####6 | t | t | t | f | ******** | |
(7 rows)
刪除帳號：
$ sudo -u postgres dropuser user1
$ sudo -u postgres dropuser user2
$ sudo -u postgres dropuser user3
$ sudo -u postgres dropuser user4
$ sudo -u postgres dropuser user5
遠端登入資料庫（資料庫跟使用者必須先建立）：
$ sudo -u postgres createdb helloworld
$ sudo -u postgres createuser -P -l user1
$ sudo -u postgres createuser -P -L user2
$ psql -h localhost -U user1
Password for user user1:
psql: FATAL: database "user1" does not exist
$ psql -h localhost -U user1 -d helloworld
Password for user user1:
psql (9.4.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
helloworld=>\q
$ psql -h localhost -U user2 -d helloworld
Password for user user2:
psql: FATAL: role "user2" is not permitted to log in
以下皆在 helloworld 資料庫行動：
$ psql -h localhost -U user1 -d helloworld
Password for user user1:
psql (9.4.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
helloworld=>
建立資料表：
helloworld=> CREATE TABLE haha ( field1 INT, field2 VARCHAR(64) );
CREATE TABLE
helloworld=> CREATE TABLE hehe ( field1 TEXT );
列出資料表：
helloworld=> \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | haha | table | user1
public | hehe | table | user1
(2 rows)
查詢資料表定義：
helloworld=> \d haha
Table "public.haha"
Column | Type | Modifiers
--------+-----------------------+-----------
field1 | integer |
field2 | character varying(64) |
刪除資料表：
helloworld=> DDROP TABLE haha;
DROP TABLE
建立索引：
helloworld=> CREATE INDEX index_test ON hehe ( field1 );
helloworld=> \d hehe
Table "public.hehe"
Column | Type | Modifiers
--------+------+-----------
field1 | text |
Indexes:
"index_test" btree (field1)
刪除索引：
helloworld=> DROP INDEX index_test;
DROP INDEX
helloworld=> \d hehe
Table "public.hehe"
Column | Type | Modifiers
--------+------+-----------
field1 | text |
建立 Partial Indexes:
helloworld=> CREATE INDEX index_substring ON hehe ( substring(field1, 0, 32) );
CREATE INDEX
helloworld=> CREATE INDEX index_condition ON hehe ( field1 )
helloworld-> WHERE substring(field1, 0, 1) = 'a' ;
CREATE INDEX
helloworld=> \d hehe
Table "public.hehe"
Column | Type | Modifiers
--------+------+-----------
field1 | text |
Indexes:
"index_condition" btree (field1) WHERE "substring"(field1, 0, 1) = 'a'::text
"index_substring" btree ("substring"(field1, 0, 32))
從 STDIN 輸入資料至 helloworld.hehe 中：
$ bash --version
GNU bash, version 4.3.30(1)-release (x86_64-pc-linux-gnu)
Copyright (C) 2013 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software; you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
$ cat <<EOF | psql -h localhost -U user1 -W -d helloworld
> COPY hehe (field1) FROM stdin;
> 1
> 2
> 3
> 4
> 5
> 6
> EOF
Password for user user1:
COPY 6
$ psql -h localhost -U user1 -W -d helloworld -c 'SELECT * FROM hehe;'
Password for user user1:
field1
--------
1
2
3
4
5
6
(6 rows)
