I want to get room’s member list, room’s owner member in case of he doesn’t exists in other table and admin member at the same time. Currently i fetch them individually. CREATE TABLE public….
Tag: postgresql
SQL – create SQL to join lists
I have the following table: Add Data: Find users who share addresses. Expected Results: Question: How do I formulate a SQL query to obtain the expected result? Much appreciated. More info: PostgreSQL 9.5.19 Answer I don’t know if this is the most efficient method, but I can’t come up with something better right now. I assume this will have a
Select rows which have a field in common with another row
I have two tables: products and postings. A product is a consumer product (ex. iPhone X), and a posting is a listing for a product on an online marketplace (ex. eBay posting). A single product has zero or more associated postings. Is there any way to select only postings which have a “sibling”? ie. Select all postings whose product column
SQL delete duplicate rows based on multiple fields
I have the following table in sql: id | trip_id | stop_id | departure_time —————————————- 1 | 1| 1| 06:25:00 2 | 1| 2| 06:35:00 3 …
LIMIT and Contains in a single JSONB Query
I have 2 queries that separately run perfectly: This one limits the results: SELECT jsonb_agg(elem) as data FROM ( SELECT * FROM file_data, jsonb_array_elements(file_data) a(elem) LIMIT 3 ) …
Find rows based on nested key in jsonb array
I have a jsonb column in Postgres 9.6 that contains JSON arrays of the following form: [ { “courses”: { “course-1”: { “graduated”: false }, “course-5”: { “graduated”: true } } }, { “…
Group rows by datediff and then use diff function
I have table in PostgreSQL with timestamp column created_at and integer column user_id. Each row represents some action from user. I need to calculate average user session length. Session is defined as group of actions with time difference less than 10 minutes. When there is difference 10 minutes or more between two user actions, new session started. I was thinking
JSONB sort aggregation
I found this query that suits my needs thanks to this answer here in order to sort fields of data in a JSON document. (Fake, generated random data) SELECT jsonb_agg(elem) FROM ( SELECT * FROM …
Reformatting multi-feature timeseries data with SQL
I have a table that has the following formatting: id | uid | created_at | feature | value 1, 1, 2019-10-1 20:26:32, ‘weight’, 155.0, 2, 1, 2019-10-1 23:26:32, ‘weight’, 150.0, 3, 1, 2019-10-2 10:00:…
How to create index on records for last 90 days in Postgres Making now() immutable
I have a case where due to speed issues I only want to create the index on records for last 90 days. When I try to create index like this: create index if not exists …