postgresql-9.5 – IT Nursery

PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values

May 31, 2022 by IT Nursery

When you are upserting a row (PostgreSQL >= 9.5), and you want the possible INSERT to be exactly the same as the possible UPDATE, you can write it like this: INSERT INTO tablename (id, username, password, level, email) VALUES (1, ‘John’, ‘qwerty’, 5, ‘[email protected]’) ON CONFLICT (id) DO UPDATE SET id=EXCLUDED.id, username=EXCLUDED.username, password=EXCLUDED.password, level=EXCLUDED.level,email=EXCLUDED.email Is … Read more

Postgresql – unable to drop database because of some auto connections to DB

May 21, 2022 by IT Nursery

Whenever I try to drop database I get: ERROR: database “pilot” is being accessed by other users DETAIL: There is 1 other session using the database. When I use: SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname=”TARGET_DB”; I terminated the connection from that DB, but if I try to drop database after that somehow someone automatically connects … Read more