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
You are correct that you need a trigger, because setting a default value for the column won’t work for you – default values only work for null values and don’t help you in preventing blank values. In postgres there are a couple of steps to creating a trigger: Step 1: Create a function that returns … Read more
Assuming the psql command-line tool, you may use \copy instead of copy. \copy opens the file and feeds the contents to the server, whereas copy tells the server the open the file itself and read it, which may be problematic permission-wise, or even impossible if client and server run on different machines with no file … Read more
–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
I faced the same. But an array in my case is created via array_agg function. And fortunately it allows to aggregate DISTINCT values, like: array_agg(DISTINCT value) This works for me.
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
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
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
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
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