ALTER TABLE haaletus ADD COLUMN modified TIMESTAMP;
ALTER TABLE haaletus ALTER COLUMN modified SET DEFAULT now();
UPDATE haaletus SET modified = aeg;
ALTER TABLE haaletus ALTER COLUMN modified SET NOT NULL;
 
ALTER TABLE haaletustabel ALTER COLUMN id SET DEFAULT NEXTVAL('item_id_seq'::text);
UPDATE haaletustabel SET id = NEXTVAL('item_id_seq'::text);

CREATE SEQUENCE saali_syndmused_id_seq INCREMENT 1    MINVALUE 1    MAXVALUE 99999999    START 1;
ALTER TABLE saali_syndmused ADD COLUMN id int4;
ALTER TABLE saali_syndmused ALTER COLUMN id SET  DEFAULT NEXTVAL('saali_syndmused_id_seq'::text);
UPDATE saali_syndmused SET id =  NEXTVAL('saali_syndmused_id_seq'::text);
ALTER TABLE saali_syndmused ALTER COLUMN id SET NOT NULL;
ALTER TABLE saali_syndmused ADD COLUMN modified TIMESTAMP;
ALTER TABLE saali_syndmused ALTER COLUMN modified SET DEFAULT now();
UPDATE saali_syndmused SET modified = aeg;
 
-- not null piirangu eemaldamine
ALTER TABLE multisel_cache ALTER COLUMN multisel_code DROP NOT NULL;

Ühe kindla tabeli dumpimine (INSERT lausetega!!!):

pg_dump --table haaletus --inserts -U webdk rene_webdk3 > /tmp/test.sql

Päringu alusel data dumpimine ühest baasist teise (dump faili sisus on COPY käsud):

sync=# \COPY (select * from flyway_schema_history where installed_on < '2022-05-18 15:21:53+03') TO '/tmp/dump.sql'
sync_vln=# \COPY flyway_schema_history FROM '/tmp/dump.sql'

Kogu baasi dumpimine (COPY lausetega):

pg_dump -b -C -f inst/db/ems.sql -F c -Z 9 -U webdk webdk

Baasi taastamine dumpist:

/usr/local/pgsql/bin/pg_restore -d rene_webdk -U webdk -F c /tmp/ems.sql

Uue baasi loomine:

createdb -O webdk -E UTF-8 rene_webdk

Baasi kustutamine:

dropdb rene_webdk

• http://www.alberton.info/postgresql_meta_info.html