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 somethin…

Select rows which have a field in common with another row

postgresql sql

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…

SQL delete duplicate rows based on multiple fields

duplicates postgresql sql sql-delete

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

arrays json jsonb postgresql sql

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

arrays json jsonb postgresql sql

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 } } }, { “…

SQL how to convert array to values for use in IN clause

arrays in-clause postgresql sql

Working in Postgres SQL: My query argument comes in as a comma separated string: ‘ABC,BCD’ I am trying to construct the query below, but getting an error: ERROR: Operator does not exist: character varying = text[] My question is how to convert ‘ABC,BCD’ to a list of values to use in th…

Group rows by datediff and then use diff function

postgresql sql

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 betw…