kubada

Это старая версия документа!

Преобразовать тип колонки из text в datetime

ALTER TABLE {TABLE} ALTER COLUMN {COLUMN} TYPE TIMESTAMP WITHOUT TIME zone 
USING ("{column}"::text::timestamptz);

Бэкап из консоли

pg_dump --dbname=postgresql://USER:PASS@127.0.0.1:5432/DB > backup_name

row_id

CREATE TABLE {TABLE} (
	"id" SERIAL PRIMARY KEY,
	lalala
);

ALTER TABLE {TABLE}
ADD COLUMN id SERIAL PRIMARY KEY;

выбор даты минус n дней

SELECT * FROM {TABLE} WHERE DATE > CURRENT_DATE - INTERVAL '30' DAY

ERROR: encoding UTF8 does not match locale en_US Detail: The chosen LC_CTYPE setting requires encoding LATIN1.

postgres=# UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
postgres=# DROP DATABASE Template1;
postgres=# CREATE DATABASE template1 WITH owner=postgres ENCODING = 'UTF-8' lc_collate = 'en_US.utf8' lc_ctype = 'en_US.utf8' template template0;
postgres=# UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';