SQL Sub queries in check constraint

It is not supported to look beyond the current row in a CHECK constraint. http://www.postgresql.org/docs/9.1/interactive/sql-createtable.html says: A check constraint specified as a column constraint should reference that column’s value only, while an expression appearing in a table constraint can reference multiple columns. Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns … Read more

Switch role after connecting to database

–create a user that you want to use the database as: create role neil; –create the user for the web server to connect as: create role webgui noinherit login password ‘s3cr3t’; –let webgui set role to neil: grant neil to webgui; –this looks backwards but is correct. webgui is now in the neil group, so … Read more

Update timestamp when row is updated in PostgreSQL

Create a function that updates the changetimestamp column of a table like so: CREATE OR REPLACE FUNCTION update_changetimestamp_column() RETURNS TRIGGER AS $$ BEGIN NEW.changetimestamp = now(); RETURN NEW; END; $$ language ‘plpgsql’; Create a trigger on the table that calls the update_changetimestamp_column() function whenever an update occurs like so: CREATE TRIGGER update_ab_changetimestamp BEFORE UPDATE ON … Read more

Change type of varchar field to integer: “cannot be cast automatically to type integer”

There is no implicit (automatic) cast from text or varchar to integer (i.e. you cannot pass a varchar to a function expecting integer or assign a varchar field to an integer one), so you must specify an explicit cast using ALTER TABLE … ALTER COLUMN … TYPE … USING: ALTER TABLE the_table ALTER COLUMN col_name … Read more

PostgreSQL ERROR: canceling statement due to conflict with recovery

No need to touch hot_standby_feedback. As others have mentioned, setting it to on can bloat master. Imagine opening transaction on a slave and not closing it. Instead, set max_standby_archive_delay and max_standby_streaming_delay to some sane value: # /etc/postgresql/10/main/postgresql.conf on a slave max_standby_archive_delay = 900s max_standby_streaming_delay = 900s This way queries on slaves with a duration less … Read more

PSQLException: current transaction is aborted, commands ignored until end of transaction block

I got this error using Java and PostgreSQL doing an insert on a table. I will illustrate how you can reproduce this error: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block Summary: The reason you get this error is because you have entered a transaction and one of your SQL … Read more

Sorting array elements

The best way to sort an array of integers is without a doubt to use the intarray extension, which will do it much, much, much faster than any SQL formulation: CREATE EXTENSION intarray; SELECT sort( ARRAY[4,3,2,1] ); A function that works for any array type is: CREATE OR REPLACE FUNCTION array_sort (ANYARRAY) RETURNS ANYARRAY LANGUAGE … Read more