Consider a table with an id PK and 26 columns. There is an index on (a,b) I’m trying to select the row where the unique pairing of (a,b) is most recent. IE what was the last record for each (a,b) Because ID is autoincrementing, know that the max is the last row. However, is there any way to have the
Tag: postgresql
subquery must return only one column during insert data for returning
is that any way for returning join column during INSERT INTO ? here is my query the last sub query is not work because i want return the villages table with two columns is that any way to pass that 2 columns ?? Answer You can use a CTE with returning . . . and then a query: However, the
PostgreSQL cyclic foreign keys to multiple tables
I have 3 tables. chat_room group_chat_room private_chat_room As you can see chat_room has foreign key constraints that refer to group_chat_room and private_chat_room. Also both group_chat_room and private_chat_room have FK constraints that refer to chat_room. When I want to INSERT a row into group_chat_room I…
Postgres, how to limit number of rows returned from joined tables
I have the following query that return the data I want, however for the joined tables, I want to limit the number of rows returned and preferrably be able to specify for each joined table. I tried …
Bi-temporal SQL table querying
I’m trying to model a SQL table to store salary changes for employees. One approach is using a bi-temporal table as follows: Here an employee was hired on 10/1/2015 with salary of 100,000. Then on 2/15/2016 he had a performance review and his boss said, “we’re bumping your salary to 110,000 …
How to let DELETE to remove the rows with no constraint problem anyways?
When there is a constraint and one tries to DELETE data on a relation, but some rows can’t be deleted because some constraint, how can one Select those rows that had no problem with the constraint to delete them anyways? Example: Table h1 has 2 elements: h1_1 and h1_2 Table h2 has 1 element: h2_1 h2_1 r…
SQL Concatenate all values in column B that have same value in column A for all values in column A
I am running PostgreSQL 12.4. I have a relatively large table like the following where column 1 and 2 are both of character varying type: I would like to create something like the following: Is there an easy way to do this? Answer You can use string_agg: You can find more info here.
How to order a query result by two columns with nulls last in one of the columns
I have the following table ‘client’ I want to fetch all rows from this table ordered by ID Desc, with nulls in the column filecount ordered last. So after the sorting I would like to get the following order: I tried the following but it doesn’t work: Answer I would use a CASE expression here…
sum of amount earned medals for 1-day, 2-day and 3-day based on daily cohort users
I need some help with cohort analys. I have: users user_id installed_at 111 01.03.2020 112 01.03.2020 119 02.03.2020 120 02.03.2020 …
SQL Column Contains ID of Another Row
Suppose I have a SQL database my_table of the following form where one column contains the id of another row. How can I select both the name column of the given row and the name of the underlying id …