\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:

su - postgres
pgsql

To add a password: \password

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.

$ 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

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

You can create a table with json data:

create table macarrodes.bookmarks (
  id serial primary key,
  title text,
  meta jsonb
);

Simple query showing a field:

select distinct meta->'tags' from macarrodes.macarrodes.bookmarks;

Query by field:

SELECT * FROM users WHERE metadata @> '{"country": "Peru"}'; 
SELECT * FROM users WHERE metadata->>'country' = 'Peru';

Query nested values:

SELECT * FROM users WHERE metadata->'company'->>'name' = "Mozilla";
SELECT * FROM users WHERE metadata @> '{"company":{"name": "Mozilla"}}';

Query if exists:

SELECT * FROM users WHERE metadata->>'country' IS NOT NULL;

Query in array:

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

Remove an attribute:

UPDATE users SET metadata = metadata - 'country';

• 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. Se ha de definir antes el tipo 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

There is a command to create a DB:

createdb -T template0 restored_database

To create a backup:

pg_dump postgres > postgres_db.bak

To restore a backup:

createdb -T template0 restored_database
psql restored_database < database.bak

Backup and restore all DB:

pg_dumpall > backup_file

You can restore the databases by passing the file to psql, with the default database:

psql -f backup_file postgres

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

export PGPASSWORD=mysecretpassword && psql -h 127.0.0.1 -p {db.local_port} -U postgres -c 'create database puma;'

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

Para permitir otras conexiones locales campiaremos

local   all             postgres                                peer

Por:

local   all             postgres                                md5