[Linux] PostgreSQL 9.4 簡易筆記 - 建立資料庫、使用者、索引 @ Ubuntu 14.04

安裝:

$ 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)