====== PostgreSQL ======

Notes on PostgreSQL

===== Install from Ubuntu repo =====

<code>
root@study:~# apt-get install postgresql-client postgresql
...edited...
Setting up postgresql-common (154ubuntu1.1) ...
Adding user postgres to group ssl-cert

Creating config file /etc/logrotate.d/postgresql-common with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
  en_au
  en_gb
  en_us
  en_za
Removing obsolete dictionary files:
 * No PostgreSQL clusters exist; see "man pg_createcluster"
Setting up postgresql-client (9.3+154ubuntu1.1) ...
Processing triggers for ureadahead (0.100.0-16) ...
Setting up postgresql-9.3 (9.3.23-0ubuntu0.14.04) ...
Creating new cluster 9.3/main ...
  config /etc/postgresql/9.3/main
  data   /var/lib/postgresql/9.3/main
  locale en_GB.UTF-8
  port   5432
update-alternatives: using /usr/share/postgresql/9.3/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
 * Starting PostgreSQL 9.3 database server                                                    [ OK ] 
Setting up postgresql (9.3+154ubuntu1.1) ...
Processing triggers for libc-bin (2.19-0ubuntu6.14) ...
root@study:~#
</code>


===== Starting Client =====

<code>
root@study:~# psql 
psql: FATAL:  role "root" does not exist
root@study:~# 
</code>

<code>
andrew@study:~/tmp$ sudo -u postgres createuser --superuser $USER
[sudo] password for andrew: 
andrew@study:~/tmp$ sudo -u postgres createdb $USER
andrew@study:~/tmp$ touch .psql_history
andrew@study:~/tmp$ 
andrew@study:~/tmp$ psql
psql (9.3.23)
Type "help" for help.

andrew=# 
</code>


<code>
andrew=# help;
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
andrew=# \h
Available help:
  ABORT                  ALTER TYPE         CREATE RULE                      DROP GROUP                       LOCK
  ALTER AGGREGATE        ALTER USER         CREATE SCHEMA                    DROP INDEX                       MOVE
...edited...
</code>



===== Basic navigation =====

==== List databases ====


<code>
andrew=# \list
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 andrew    | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 postgres  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 template0 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

andrew=#
</code>

==== Connect to database ====

<code>
andrew=# \connect andrew
You are now connected to database "andrew" as user "andrew".
andrew=# 
</code>

==== Create Table ====

<code>
andrew=# CREATE TABLE test1 (id INTEGER PRIMARY KEY, name VARCHAR);
CREATE TABLE
andrew=# 
</code>

==== Insert data into table ====

<code>
andrew=# INSERT INTO test1 (id,name) VALUES (0, 'row0');
INSERT 0 1
andrew=# 
andrew=# INSERT INTO test1 (id,name) VALUES (1, 'row1');
INSERT 0 1
andrew=# select * from test1;
 id | name 
----+------
  0 | row0
  1 | row1
(2 rows)

andrew=# 
</code>