My users table contains Alice, Bob and Charles. Alice and Bob have a 3 and 2 fruits respectively. Charles has none. A relationship is established using a foreign key constraint foreign key (user_id) references users (id) and a unique (user_id, name) constraint, allowing zero or one fruit per user. Charles doe…
Tag: postgresql
Is creating a new table for each `list` the best way to speed up database queries here?
My postgres database has a lists and list_items table. There is an index on the list_id column. To list all items in a list, I have been searching through list_items for where the list_id equals id from the lists table. lists: id title 0 “foo” 1 “bar” list_items: id content list_id 0 &…
SQL write a query, question from interview problem
At the beginning I need to mention that I am not very good at SQL so I would like to ask you. Here is a tables relation: tables What I need to do with them is “Show name, surname and number of movies those directors (Reżyser means director), who made more movies that the average number is for all direct…
Add a number for duplicate values in posgresql
I’ve a query in MySQL and I’m looking for a query which can perform below operation using Posgres MySQL Query : Output: Email Row Number Aamir 1 Aamir 2 Aamir 3 Suresh 1 Suresh 2 Hafiz 1 Answer https://www.db-fiddle.com/f/3RqNHRGFjkU74v33upyfhi/0
Graphql nested cursor based pagination, resolver and SQL query
Is there a way to implement graphql cursor based pagination with nested pagination queries in a performant way? Let’s say we have 3 pseudo graphql types: For simplicity let’s say user could read thousands of books and each book can have hundred of pages. user database table: book database table: p…
how to get json result to fixed number by grouping in postgresql?
I want to get expected results to one query execution. the “where” clause should one value. so I can’t use this query as sub-query of main-query. could you tell me how to do it? Sample sql query: Expected results: results [{“id”:1,”name”:”hello”},{“i…
PostgreSQL subquery COUNT fails when the subquery is joined more than once
I have 2 tables: Table class: Table class_event, where I store events related to classes, such as “started” and “ended”. I need a query the amount of times each class has been started and ended. This works: But when I do exactly the same to get the amount of ended classes it shows inco…
Full Outer Join failing to return all records from both tables
I have a pair of tables I need to join, I want to return any record that’s in tableA, tableB or both. I think I need a FULL OUTER JOIN This query return 1164 records And this one return 3339 records And this one returns 3369 records (so there must be 30 records in tableA that aren’t in tableB) I
Enforce 2 unique constraints on upserts in Postgres
I have a table where I save contacts data I add data to this table from several sources. Some sources have Linkedin profiles data about people, other sources have email data. Sometimes the full names are not equal, even if they refer to the same person. And I want to do upserts to not have duplicated data. Fo…
PSQL: How to compare record to previous record (2 tables)?
I have two tables: 01_System_Log Records I need to create a view, where the records of the last request id (3) are compared with the previous (2). So far I made a view, where the most actual records are shown. I am not sure how to proceed, while in all compare examples, which I have found, only one table was