Rename enum item in PostgreSQL
In PostgreSQL version 10, the ability to rename the labels of an enum has been added as part of the ALTER TYPE syntax: ALTER TYPE name RENAME VALUE ‘existing_enum_value’ TO ‘new_enum_value’
postgresql-9.1
How to use SQL LIKE condition with multiple values in PostgreSQL?
Perhaps using SIMILAR TO would work ? SELECT * from table WHERE column SIMILAR TO ‘(AAA|BBB|CCC)%’;
Postgresql Database export to .sql file
pg_dump defaults to plain SQL export. both data and structure. open command prompt and run pg_dump -U username -h localhost databasename >> sqlfile.sql Above command is preferable as most of the times there will be an error which will be something like – …FATAL: Peer authentication failed for user …
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
Printing to the screen in a .sql file in PostgreSQL
If you’re just feeding a big pile of SQL to psql then you have a couple of options. You could run psql with –echo-all: -a –echo-all Print all input lines to standard output as they are read. This is more useful for script processing than interactive mode. This is equivalent to setting the variable ECHO … Read more
How to determine the OID of a Postgres table?
To get a table OID, cast to the object identifier type regclass (while connected to the same DB): SELECT ‘mytbl’::regclass::oid; This finds the first table (or view, etc.) with the given name along the search_path or raises an exception if not found. Schema-qualify the table name to remove the dependency on the search path: SELECT … Read more
Update with result from CTE
Try your UPDATE with the following syntax; UPDATE job SET jobdate = cte.date FROM cte WHERE job.jobid = cte.jobid
extract week number from date postgres
To get the year and the week in a single character value, use to_char() select to_char(current_date, ‘IYYY-IW’); IW returns the year and the week number as defined in the ISO standard and IYYY returns the corresponding year (which might be the previous year). If you need the year and the week number as numbers, use … Read more
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