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

===== Basics =====
''\q'' exit.

Know the postgresql version: ''SELECT version();''

To explore the server:
  * ''\list'' or ''\l'': list all databases
  * ''\dt'': list all tables in the current database
  * ''\connect database_name'' to switch database

To access to a recently installed postgres server:
<code>
su - postgres
pgsql
</code>

To add a password: ''\password''

==== Command-line utils ====
''psql -U postgres'' enter into console with user postgres. 

''psql -U postgres -h 127.0.0.1'' for doing by TCP.

''export PGPASSWORD=mysecretpassword && psql -h 127.0.0.1 -U postgres'' using a password.

<code>
$ export PGPASSWORD=mysecretpassword && dropdb -h 127.0.0.1 -U postgres fok
$ export PGPASSWORD=mysecretpassword && createdb -T template0 -h 127.0.0.1 -U postgres fok
</code>
==== Create schema ====
<code>
create schema api;
</code>

==== Create table and insert ====
<code>
create table api.todos (
  id serial primary key,
  done boolean not null default false,
  task text not null,
  due timestamptz
);

insert into api.todos (task) values
  ('finish tutorial 0'), ('pat self on back');
</code>

==== Make a role ====
<code>
create role web_anon nologin;
grant web_anon to postgres;

grant usage on schema api to web_anon;
grant select on api.todos to web_anon;
</code>

The web_anon role has permission to access things in the api schema, and to read rows in the todos table.

... Other:
<code>
create role todo_user nologin;
grant todo_user to postgres;

grant usage on schema api to todo_user;
grant all on api.todos to todo_user;
grant usage, select on sequence api.todos_id_seq to todo_user;
</code>

==== Change user password ====
<code>
ALTER USER user_name WITH PASSWORD 'new_password';
</code>

===== JSONB =====
You can create a table with json data:
<code>
create table macarrodes.bookmarks (
  id serial primary key,
  title text,
  meta jsonb
);
</code>

==== Query ====
Simple query showing a field:
<code>
select distinct meta->'tags' from macarrodes.macarrodes.bookmarks;
</code>

Query by field:
<code>
SELECT * FROM users WHERE metadata @> '{"country": "Peru"}'; 
SELECT * FROM users WHERE metadata->>'country' = 'Peru';
</code>

Query nested values:
<code>
SELECT * FROM users WHERE metadata->'company'->>'name' = "Mozilla";
SELECT * FROM users WHERE metadata @> '{"company":{"name": "Mozilla"}}';
</code>

Query if exists:
<code>
SELECT * FROM users WHERE metadata->>'country' IS NOT NULL;
</code>

Query in array:
<code>
SELECT * FROM users WHERE metadata @> '{"companies": ["Mozilla"]}';
SELECT * FROM users WHERE metadata->>'countries' IN ('Chad', 'Japan');
</code>

Remove an attribute:
<code>
UPDATE users SET metadata = metadata - 'country';
</code>
===== Funciones =====

==== Condicional CASE ====
  * https://www.postgresql.org/docs/9.2/static/functions-conditional.html
<code>
SELECT * FROM test;

 a
---
 1
 2
 3


SELECT a,
       CASE WHEN a=1 THEN 'one'
            WHEN a=2 THEN 'two'
            ELSE 'other'
       END
    FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other
</code>
<code>
case when p.parent_id is null then false else true end as has_parent
</code>
==== CAST y tipos ====
Crear un tipo:
<code>
CREATE TYPE temp_options_type AS (id integer, description text);
</code>
Castear:
<code>
CAST ( expression AS type )
expression::type
</code>

==== Objetos anidados con array y row ====
<code>
create type inner_value as (id integer, name text);
create view macarrodes.bookmarks as
  select 
    ...
    case 
      when t_bookmarks.folder is null then null
      else ROW(t_bookmarks.folder, t_folders.name)::inner_value 
    end as folder,
    array(
        select 
          row(macarrodes.t_tags.id, macarrodes.t_tags.name)::inner_value
        from
          macarrodes.t_tags, macarrodes.t_bookmarks_tags
        where 
          t_tags.id = t_bookmarks_tags.tag
          AND t_bookmarks.id = t_bookmarks_tags.bookmark
    ) as tags
  from 
    macarrodes.t_bookmarks
    ...
</code>
''ROW(t_bookmarks.folder, t_folders.name)::inner_value'' crea un objeto ''inner_value''.  Se ha de definir antes el tipo ''inner_value''.

''array(...)'' crea un array de ''inner_value''s.

También podrías coger una tabla entera como tipo:
<code>
select 
    dep.*,
    array(
        select row(emp.*)::emp from emp 
        where emp.depid = dep.depid
    ) emps
from dep
</code>

===== Notes =====

==== Backups and tools ====

There is a command to create a DB:
<code>
createdb -T template0 restored_database
</code>

To create a backup:
<code>
pg_dump postgres > postgres_db.bak
</code>

To restore a backup:
<code>
createdb -T template0 restored_database
psql restored_database < database.bak
</code>

Backup and restore all DB:
<code>
pg_dumpall > backup_file
</code>
You can restore the databases by passing the file to psql, with the default database:
<code>
psql -f backup_file postgres
</code>
==== Tips and Tricks ====
Script for restart and adding data using docker.
<code>
docker stop `cat last_docker_id.txt`
docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword postgres:9.4 > last_docker_id.txt
sleep 6
export PGPASSWORD=mysecretpassword 
psql -h 127.0.0.1 -U postgres -f /home/alfred/Workspaces/macarrodes/pgsql/schema.pgsql
psql -h 127.0.0.1 -U postgres -f /home/alfred/Workspaces/macarrodes/pgsql/fake_data.pgsql
</code>

=== Launch a command on CLI ===
<code>
export PGPASSWORD=mysecretpassword && psql -h 127.0.0.1 -p {db.local_port} -U postgres -c 'create database puma;'
</code>
==== Instalar servidor ====
<code>
apt-get install postgresql postgresql-contrib
# Crear un cluster para 9.3
pg_createcluster 9.3 main --start
# Crear servicio
update-rc.d postgresql enable
# Activar servicio
service postgresql start
# Conecta correctamente?
su postgres
psql
\q
# Ctrl+d
</code>
Para permitir otras conexiones locales campiaremos
<code>
local   all             postgres                                peer
</code>
Por:
<code>
local   all             postgres                                md5
</code>