Recently I started learning PostgreSQL. I've created an empty database, "mydb". At this point I have 4 databases - 3 default ones (template0, template1, postgres) and my database "mydb". The size of the folder pgdata is about 60 MB. After…
I have below PostgreSQL table, which is small sample of huge table employee emp_id | salary | rating | increment_year -------------------------------------- 28 3000 1 2023 25 2000 2 2023 30 1500 1 2021 28 1000 3 2022 32 1200 1…
I need to alter the Data Type of a named column (let's call them 'photo') across all the tables in a schema. For example: I have 300 tables with identical structure (except the data in each row of course). But…
I have a postgres DB with a "hot" table, meaning it is receiving many updates + deletes every second. I would like to run a query that will UPDATE about 10% of its rows. I do not have an index…
I have following SQL (PostgreSQL) query: DO $$ BEGIN IF EXISTS (SELECT * FROM segment WHERE slug = $1 AND is_active = true) THEN RAISE EXCEPTION $2; ELSE INSERT INTO segment (slug) VALUES ($1) ON CONFLICT(slug) DO UPDATE SET is_active…
I have a "trademarks" table like this: create table public.trademarks ( id bigint generated always as identity, owner_id text null, trademark text null, constraint trademarks_pkey primary key (id) ) tablespace pg_default; My goal is to pass a text to a…
Here is the code: // the JSON of req.body { "title": "title", "reference": "1213", "noteType": "type_1", "tags": [3, 4], // Will be variable length. "text": "Lorem ipsum dolor sit amet." } // Create the row and get the id const…
I am using PyGreSQL to execute queries against my DB. I need to insert the values to be matched at runtime, but the parameters are not being into the query correctly. If my query is: SELECT * FROM Database WHERE…
I have a pg_toast table which has a lot of dead tuples accumulating. The autovacuum job on it runs for a very long time. I wanted to tune the autovacuum config, to make it more aggressive. When i'm trying to…
I have a function in Postgresql where we have a parameter uuid[]. I basically have 2 requirements. uuid[] I would like to make it as optional In the where clause if uuid[] is null or not passed then the condition…