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
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
Find the file pg_hba.conf. It may be located, for example, in /etc/postgresql-9.1/pg_hba.conf. cd /etc/postgresql-9.1/ Back it up cp pg_hba.conf pg_hba.conf-backup Place the following line (as either the first uncommented line, or as the only one): For all occurrence of below (local and host) , except replication section if you don’t have any it has to … Read more
You can use the provided C function crosstab_hash for this. The manual is not very clear in this respect. It’s mentioned at the end of the chapter on crosstab() with two parameters: You can create predefined functions to avoid having to write out the result column names and types in each query. See the examples … Read more
To get individual columns instead of the row type, call the function with: SELECT * FROM testfunction(); Just like you would select all columns from a table. Also consider this reviewed form of your test function: CREATE OR REPLACE FUNCTION testfunction() RETURNS TABLE(a int, b int) LANGUAGE plpgsql AS $func$ DECLARE _a int := 0; … Read more
For testing purposes you can force the use of the index by “disabling” sequential scans – best in your current session only: SET enable_seqscan = OFF; Do not use this on a productive server. Details in the manual here. I quoted “disabling”, because you cannot actually disable sequential table scans. But any other available option … Read more
I disagree with some of the advice in other answers. This can be done with PL/pgSQL and I think it is mostly far superior to assembling queries in a client application. It is faster and cleaner and the app only sends the bare minimum across the wire in requests. SQL statements are saved inside the … Read more
You can also do select table_name from information_schema.columns where column_name=”your_column_name”
You can use the alternative syntax with a array literal instead of the array constructor, which is a Postgres function-like construct and may cause trouble when you need to pass values – like in a prepared statement: SELECT myschema.myfunc(‘0d6311cc-0d74-4a32-8cf9-87835651e1ee’ , ‘{“(0d6311cc-0d74-4a32-8cf9-87835651e1ee, 25)” , “(6449fb3b-844e-440e-8973-31eb6bbefc81, 10)”}’::mytype[]); I added a line break between the two row types … Read more