PostgreSQL next value of the sequences?

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

Postgres query optimization (forcing an index scan)

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

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

How to pass custom type array to Postgres function

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

Generate series of dates – using date type as input

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

I forgot the password I entered during PostgreSQL installation

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