Postgresql – How to get the position of a record
This might be a dumb and simple question, but since I am a completely newbie I could not find a workaround. Lets say I have these users in the DB : Me with maxScore : 4 John with maxScore: 3…
Postgresql – Remove duplicates using multiple criteria in SQL
I've tried all afternoon to dedup a table that looks like this: ID1 | ID2 | Date | Time |Status | Price ----+-----+------------+-----------------+--------+------- 01 | A | 01/01/2022 | 10:41:47.000000 | DDD | 55 01 | B | 02/01/2022 |…
Postgresql – Is there a way to limit the number of records for a user using Prisma?
The simplest example I can give, is a User that can create multiple Posts. A one-to-many relationship where multiple posts can be tied to a single user. But what if I want the User to only be able to have…
Postgresql – Postgres Date checking – is_date fn shortcircuit
I have a set of records in a table, with some records having invalid date. I wanted to ignore those invalid records and do a check with rest of the records. I framed a query like below but I don't…
Postgresql: remove ability to query every row in a table
I have the following table called "profile": id, name, created_at Every user ( with SupaBase ) has his own row in the "profile" table with his user_id as the key ( id ) I added RLS so that only the…
PostgreSQL PgAgent syntax error at or near DECLARE
Could someone tells me what is wrong please. I try to create a job with using PgAgent with declaring some variables. When I run this code manually, it works successfully. But when I try to put this code in job…
Cannot psql into docker postgres image port forwarded locally
I'm continuously hitting an error when trying to psql into a docker composed postgres image that has its ports forwarded. (this issue seems to persist also when attempting to access the DB programatically via node application). Running docker-compose up -d…
Postgresql – Compare all values of OLD and NEW and store only changed values in audit table
My end user is going to enter query like this: INSERT INTO course( id, column_1, column_2, column_3, column_4, column_5) VALUES ('f34-gr5-t46','ABC', '2022-10-18 07:19:29', 2, 'false', now()) ON CONFLICT (id) DO UPDATE SET (column_1, column_2, column_3, column_4, column_5) = (EXCLUDED.column_1, EXCLUDED.column_2,…
Postgresql – Looking for a Cumulative Total for Unique Customers Only Month by Month
I am trying to create a cumulative count for unique customers only by the month they purchased. The example Table is: customer_email cohortMonth [email protected] 10/2019 [email protected] 10/2019 [email protected] 10/2019 [email protected] 11/2019 [email protected] 11/2019 [email protected] 12/2019 The output I am looking…
Select max and latest value from table in PostgreSQL
I have a table like this: value ts 2.0 1 3.0 5 7.0 3 1.0 2 5.0 4 I need to select max value, min value and value with max ts. Is it possible to do it with one query?…