PostgreSQL: dump and restore some schemas

Dump

pg_dump --host localhost --port 5432 --username "postgres"  --clean --no-owner --no-password  --format custom --encoding UTF8 --verbose --file "/tmp/dwhmbqt_2017-05-24.dump.custom" --schema resource_db --schema crm_db --schema iot_db --schema offer_db --schema shared_db dwhmbqt

Restore

pg_restore -U postgres -d dwhmbqt /tmp/dwhmbqt_2017-05-24.dump.custom

PostgreSQL: dump functions code

source: How to take backup of functions only in Postgres

PostgreSQL: copy in and out

Source:

•   In and out of PostgreSQL using COPY 
•   How to insert csv data into postgresql db (remote db)

Import from local CSV to remote DB:

psql -Upostgres -h my_host -d my_db -c "\copy public.nwkmonitor from '/tmp/in.csv' with delimiter as ',' null as '';"

Export from remote DB to local CSV:

psql -Upostgres -h my_host -d my_db -c "\copy public.nwkmonitor2 to '/tmp/out.csv' csv delimiter as ',' header;"

PostgreSQL: lateral join tutorial

Source: Postgres LATERAL JOIN

or better, from Erwin Brandstetter:
Source: Split function-returned record into multiple columns

originally correlated to:
Source: error : subquery must return only one column

PostgreSQL: immutable vs stable

Reminder:

• STABLE : If you call it with the same arguments more than once during the execution of a single query it will return the same value
• IMMUTABLE : It will always return the same value for the same arguments, regardless of database contents or the passage of time

PostgreSQL: move from child table to parent table

Source: Postgres table inheritance: move from parent to child and vice versa

with deleted as (delete from  returning *) insert into  select * from deleted