Linked Questions

74 votes
4 answers

Function Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I ...
Derek Downey's user avatar
  • 23.5k
34 votes
2 answers

PostgreSQL joining using JSONB

I have this SQL: CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB); INSERT INTO test(data) VALUES ('{"parent":null,"children":[2,3]}'), ('{"parent":1, "children":[4,5]}'), ('{"parent":...
Kokizzu's user avatar
  • 1,373
31 votes
4 answers

Unquoting JSON strings; print JSON strings without quotes

SELECT json_array_elements('["one", "two"]'::json) gives result | json_array_elements | | :------------------ | | "one" | | "two" | I would like to ...
Maxim Yefremov's user avatar
25 votes
1 answer

How to preserve the original order of elements in an unnested array?

Given the string: 'I think that PostgreSQL is nifty' I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details and ...
swasheck's user avatar
  • 10.7k
25 votes
2 answers

How to get particular object from jsonb array in PostgreSQL?

I have a field called 'user' that holds a json array that roughly looks like this: "user": [{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}] Now I want a query like: select count from ...
Rabi C Shah's user avatar
19 votes
2 answers

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.6k
19 votes
1 answer

What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

On PG 14's documentation, in the CREATE FUNCTION section, the manual says that the body of a LANGUAGE SQL function can either be a single statement: RETURN expression or a block: BEGIN ATOMIC ...
ARX's user avatar
  • 1,439
13 votes
4 answers

Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?

I stumbled upon this question on a Twitter conversation with Lukas Eder. Although the correct behavior would be to apply the ORDER BY clause on the outermost query, because, here, we are not using ...
Vlad Mihalcea's user avatar
13 votes
1 answer

Faster query with pattern-matching on multiple text fields

I have one Postgres table with more than 20M tuples: first_name | last_name | email ------------------------------------------- bat | man | [email protected] arya | vidal | foo@...
Victor's user avatar
  • 233
12 votes
3 answers

How to unnest and GROUP BY elements of a JSON array?

Given the band table, with a json column holding an array : id | people ---+------------- 1 | ['John', 'Thomas'] 2 | ['John', 'James'] 3 | ['James', 'George'] How to list the number of bands each ...
Bax's user avatar
  • 303
12 votes
1 answer

Is it possible to select distinct values in a json document using PostgreSQL?

I have a column that is using the JSON type. I would like to perform a query to select all distinct records for a particular field in the JSON string: I.e. given these three documents { id: 1, s: ...
Trasplazio Garzuglio's user avatar
10 votes
1 answer

Extract JSON array of numbers from JSON array of objects

I have a table containing a json array of objects similar to this: id | record ____________________ name1 | [{"a":0, "b":x}, {"a":1, "b":y}, {"a":2, "b":z}, ...] I would like to get a table ...
Vakarelov's user avatar
  • 103
10 votes
2 answers

How to combine ORDER BY and LIMIT with an aggregate function?

Here is a fiddle for my question. I have a simple table layout: class person: belongs to a class I want to select all classes, and for each class, I want the first two person identifiers of the ...
Jarius Hebzo's user avatar
10 votes
2 answers

Customize jsonb key sort order involving arrays

I have a table in PostgreSQL with some data: create table t2 ( key jsonb, value jsonb ); INSERT INTO t2(key, value) VALUES ('1', '"test 1"') ,('2', '"test 2"') ,('3', '"test 3"') ,('[]', '"...
Antonio's user avatar
  • 201
8 votes
1 answer

How to remove known elements from a JSON[] array in PostgreSQL?

I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
spa's user avatar
  • 213

15 30 50 per page