psql -U postgres enter into console with user postgres.

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

\q exit.

create schema api;

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');

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;

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

… Other:

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;

ALTER USER user_name WITH PASSWORD 'new_password';

• https://www.postgresql.org/docs/9.2/static/functions-conditional.html

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

case when p.parent_id is null then false else true end as has_parent

Crear un tipo:

CREATE TYPE temp_options_type AS (id integer, description text);

Castear:

CAST ( expression AS type )
expression::type

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
    ...

ROW(t_bookmarks.folder, t_folders.name)::inner_value crea un objeto inner_value.

array(…) crea un array de inner_values.

También podrías coger una tabla entera como tipo:

select 
    dep.*,
    array(
        select row(emp.*)::emp from emp 
        where emp.depid = dep.depid
    ) emps
from dep

Script for restart and adding data using docker.

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