RETURNING That’s possible with a single round-trip to the database: INSERT INTO tbl(filename) VALUES (‘my_filename’) RETURNING tbl_id; tbl_id would typically be a serial or IDENTITY (Postgres 10 or later) column. More in the manual. Explicitly fetch value If filename needs to include tbl_id (redundantly), you can still use a single query. Use lastval() or the … 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
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
You can also do select table_name from information_schema.columns where column_name=”your_column_name”
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
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
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
Thanks to function type resolution we can also pass date values to generate_series() because there is an implicit cast from date to timestamp as well as from date to timestamptz. Would be ambiguous, but timestamptz is “preferred” among “Date/time types”. Detailed explanation: Generating time series between two dates in PostgreSQL For a bare date the … 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
In Postgres 9.3 or later use a LATERAL join: SELECT v.col_a, v.col_b, f.* — no parentheses, f is a table alias FROM v_citizenversions v LEFT JOIN LATERAL f_citizen_rec_modified(v.col1, v.col2) f ON true WHERE f.col_c = _col_c; Why LEFT JOIN LATERAL … ON true? Record returned from function has columns concatenated For older versions, there is … Read more