I have a local PG instance populated with 10's of millions of rows of data and need to run some relatively complex queries for some data analysis work. These queries are currently taking 10+ minutes to return (even after adding…
I'm new to Postgres, and I want to delete a role named rp_ts04, but it complains about dependencies. I don't know how to check for these dependencies. I've checked information_schema but it doesn't show any relation to rp_ts04. Can anyone…
while studying about the As statment i get to knwon it is executed at the end of the uqery so if in case i am trying to use the alisd name i have created in a query like select film…
I have the following table: CREATE TABLE IF NOT EXISTS import.dre ( idmov integer, companynumber integer, idproduct integer, dtdate date, nrtank integer, dailybalance numeric, startbalance numeric, endbalance numeric ) Populated with the following query (10 first lines only): INSERT INTO…
What I am trying to do is to calculate the sum of the 2 tableSpaces I have in my PostgreSQL DB. SELECT pg_size_pretty (pg_tablespace_size ('pg_default') ); with this query I am able to find the used space but when I…
In one of my Postgres Transaction (executing a procedure), I'm using more than 50 temp tables and for each temp table it is creating pg_toast_temptableOID, pg_toast_temptableOID_index with AccessExclusiveLock and fastpath = false for these pg_toast tables. It is creating slowness…
I am implementing data partitioning on mysql server(8.0). As a "must-have requirement of partitioning" I have to add my partition key as a primary key, which is created_at in my table. eg: alter table notifications drop primary key, add primary…
I have a user, who has read access to all the schema. I want to extend his access to create, insert, update and select for only one schema, is there a way. I tried like ALTER DEFAULT PRIVILEGES FOR ROLE…
I'm practicing with queries optimization. In particular, tuning work_mem parameter. There's the DB diagram: https://postgrespro.com/docs/postgrespro/12/apks02. I have a sql function get_passengers_and_flights: CREATE FUNCTION get_passengers_and_flights(d timestamptz) RETURNS TABLE(passenger_name text, flight_no text) AS $$ SELECT t.passenger_name, f.flight_no FROM tickets t JOIN ticket_flights…
I am trying to install ApacheAGE and for this I am following the instruction in the following article but I have installed postgres 12 instead of 11. The installation was successful but when I tried to initialize DB using the…