Postgresql – What's wrong with date?
I use a table with a section based on date. The primary key index is built on the id and open_date. If I use the date from the subquery, the planner will search through all partitions. > EXPLAIN analyze SELECT…
Postgresql – Insert into table based on condition in another column
I have the following two tables table c id type 1 B1 2 B2 3 B3 table s id s_id s_type 1 2 C1 2 2 C2 3 3 C2 Now I want to insert new rows in the table…
Postgresql – Why does database not read from newly inserted data when a single query is doing both write and read?
DROP TABLE IF EXISTS A; CREATE TABLE a (id int); CREATE or replace FUNCTION insert_and_return(int) RETURNS int AS $$ BEGIN INSERT INTO a VALUES ($1); RETURN $1; END; $$ LANGUAGE plpgsql; SELECT * FROM insert_and_return(10),A AS y; Above code has…
Issues Restoring PostgreSQL dump.sql: Schema Exists and Unique Constraint Violations
I have a dump.sql file that I want to restore to a local database on my machine. However, when I try to import it, I get an error saying all schemas, functions, relations, constraints, roles are already exist, even though…
Foreign Key Constraint Violation for ManyToMany Mapping (Postgresql)
I have a bidirectional ManyToMany mapping, and I am trying to delete a post entity. When I delete it, the reference in the posts_categories table should also be removed. However, despite trying the following two approaches, it’s not deleting the…
Postgresql – How to make an alias for a common query to run on any database?
I often find myself wanting to run a query like this on multiple different databases: select n, pg_size_pretty(s) from ( select n, pg_total_relation_size(n) s from ( select format('%I.%I', table_schema, table_name) n from information_schema.tables ) ) order by s desc; How…
Postgresql – Why are parameterized queries not possible with DO … END?
The following works fine: conn = psycopg.connect(self.conn.params.conn_str) cur = conn.cursor() cur.execute(""" SELECT 2, %s; """, (1,), ) But inside a DO: cur.execute(""" DO $$ BEGIN SELECT 2, %s; END$$; """, (1,), ) it causes psycopg.errors.UndefinedParameter: there is no parameter $1…
Postgresql – DISTINCT ON needing Bitmap Heap Scan
I have a query like: SELECT DISTINCT ON (services.group_id) services.service_id, ... FROM services WHERE services.group_id IN ('67240181b97477f054f9b1bc', '67240181b97477f054f9b1be') I have an index on group_id. Why does Postgres need to do an extra heap scan? I just want any 1st match…
Postgresql – how to copy a table from one postgres db to another in datagrip?
I tried exporting the data to CSV and importing it to the new database, but it does not seem to respect the case sensitivity of column names. "createdAt" becomes "createdat" and does not change even when trying to modify the…
Postgresql – Accidentally dropped information_schema postgres
I accidentally ran and committed drop schema information_schema cascade on postgres 15.4 as a DBA. How to recreate the said schema?