Linked Questions

139 votes
8 answers
452k views

How to turn JSON array into Postgres array?

I have a column data of type json that holds JSON documents like this: { "name": "foo", "tags": ["foo", "bar"] } I would like to turn the nested tags array into a concatenated string ('foo, ...
Christoph's user avatar
  • 1,583
53 votes
2 answers
71k views

Convert right side of join of many to many into array

When using join on many to many relationship the result is split on multiple rows. What I'd like to do is convert the right side of a join into an array so the result is one row. Example with 3 tables:...
Ced's user avatar
  • 744
19 votes
2 answers
20k views

Why is array_agg() slower than the non-aggregate ARRAY() constructor?

I was just reviewing some old code written for pre-8.4 PostgreSQL, and I saw something really nifty. I remember having a custom function do some of this back in the day, but I forgot what pre-...
Evan Carroll's user avatar
  • 63.7k
14 votes
3 answers
29k views

How to perform conditional insert based on row count?

I'm using Postgres 9.3, and I need to prevent inserts into a table based on a count of specific rows already in the table. Here's the table: Table "public....
Rob Johansen's user avatar
12 votes
3 answers
5k views

Fastest way to count how many dates' ranges cover each date from series

I have a table (in PostgreSQL 9.4) that looks like this: CREATE TABLE dates_ranges (kind int, start_date date, end_date date); INSERT INTO dates_ranges VALUES (1, '2018-01-01', '2018-01-31'), ...
BartekCh's user avatar
  • 231
3 votes
1 answer
5k views

How can I match the items in an array column, with a different table in Postgres?

I have a table in a Postgres 12 database with that has an array column with a bunch of dog breed guids. I want to lookup these values and basically return the lookup values instead of the guids. ...
Kamilski81's user avatar
4 votes
1 answer
2k views

Preserve order of array elements after join

I have a query that returns a CTE looking like +-----------+-------------+ | node_id | ancestors | |-----------+-------------| | 1 | [] | | 2 | [] | | 3 |...
Eric Koslow's user avatar
3 votes
2 answers
3k views

Select into specific array positions with array_agg()?

Is there a way to set values in specific positions inside an array, based on information from other columns? (Postgres 9.3 or later.) For example, I would like to select an item and its stock ...
plaes's user avatar
  • 175
2 votes
1 answer
3k views

Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5

How do I calculate the percentile of the nth element in a field relative to the ones previous to it in a time series for each subset (partition) of my data? For example, this is exactly what I want ...
mountainclimber11's user avatar
3 votes
2 answers
208 views

Managing the order of aggregation in Postgres

Suppose I have the following merging function that merges two jsonb values overwriting duplicate keys with the values from the second create or replace function jsonb_concat(a jsonb, b jsonb) returns ...
Dragas's user avatar
  • 151