Tag: postgresql

Fetch multi-participant conversations with last message for each

database-design greatest-n-per-group postgresql sql

I am trying to create a simple chat application database schema, and query the conversations. My current table setup is the following: So basically each conversation has its own title, and multiple participants. What I would like to fetch my conversations sorted by the date of the latest message in the conversation (so the conversations with the newest messages are

SQL select data based on a one to many relation

jpa postgresql spring-data-jpa sql

I struggle a little with the following database entity relations: I would like to create a query where I get all events for a user that is either the owner (item-user_id) or a participant (participant-user_id) I got back to native queries, cause they are more easy to try out. But the one to many relation between the registration and the

A correct query produces a syntax error when used as a sub-query

postgresql sql

First, I have this query, which works just fine: But then, I use it as a sub-query: And get this error: How do I fix this and achieve the intended result? How, in general, can a correct query become an incorrect sub-query? Answer Use a CTE:

postgresql subtract between row while column shift

postgresql sql

I have a simple table like this I would like to subtract row of yesterday on day(i+1) to row of today on day(i). For example, if today is 2020-01-02 the expected results should be the 0 (day0) was obtained by 2 (from 2020-01-01 day1) – 2 (from 2020-01-02 day0) the -2 (day1) was obtained by 1 (from 2020-01-01 day2) –

Getting bounds in result with width_bucket

postgresql sql

I’m trying to do a query using width_bucket and having a column in the results with the bounds of my buckets. Is there a simple way to do this? My query: Result: What I’m trying to get: Answer As you have a fixed sets of buckets, another option is to join against a list of ranges:

Filling missing dates in each group while querying data from PostgreSQL

postgresql sql

I have data in my table of the following form: Now while querying this data, I want the missing dates to be filled with null values. In PostgreSQL, I can use generate_series() to create a series of dates and then left join with it. But that only works if I have just a series of dates and values. In this

Comparing TIME WITH TIME ZONE returns unexpected result

datetime postgresql sql where-clause

Why does this query return false? Is it because of the 22:51:13.202248 +01:00 format? SELECT now()::time at TIME ZONE ‘Europe/London’; — 22:51:13.202248 +01:00 SELECT now()::time at time zone ‘…

SQL query to filter records based on count and status

postgresql sql

I have to filter records based on the status when the count is more than 1. Column names: Student_id, Status, term, and course. DB: Postgres Condition to filter: If there exists only one record for the student then the status(true or false) does not matter. Fetch the record. If record count for a student is more than one then fetch

PostgreSQL Percent Change using Row Number

postgresql sql window-functions

I’m trying to find the percent change using row number with PostgreSQL but I’m running into an error where my “percent_change” column shows 0. Here is what I have as my code. Here is my SQL table in case it’s needed. Thank you in advance, I greatly appreciate it. Answer You can use LAG() for your requirement: or you can

How to insert column of other table in postgresql

postgresql sql sql-insert

I want to insert a column of other table and one parameter of this table have a hard coded value. I want to iterate this value with the each return value of other table column. How can i do this with insert query? let 3,4,6 is the return column value of other table. Return column Answer Get rid of the