Skip to main content
Share Your Experience: Take the 2024 Developer Survey
45 votes
Accepted

How can I get a unique array in PostgreSQL?

While there is no function to accomplish that, you can use unnest() to convert an array of elements, to a table of rows of one-column, DISTINCT to remove duplicates ARRAY(query) to recreate the row. ...
Evan Carroll's user avatar
  • 63.5k
37 votes
Accepted

Convert right side of join of many to many into array

To aggregate most rows While querying all or most items, it is typically substantially faster to aggregate rows from the "many"-table first and join later: SELECT id, i.title AS item_title, ...
Erwin Brandstetter's user avatar
29 votes
Accepted

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

There is nothing "old school" or "outdated" about an ARRAY constructor (That's what ARRAY(SELECT x FROM foobar) is). It's modern as ever. Use it for simple array aggregation. The manual: It is ...
Erwin Brandstetter's user avatar
22 votes
Accepted

Creating an array from multiple columns without NULL elements

I would suggest not using a JSON array, and instead using the native SQL array syntax which is likely much faster and more efficiently stored. It's also stronger typed. The JSON array is "possibly-...
Evan Carroll's user avatar
  • 63.5k
22 votes

What is the fastest way to verify that a json[] array is not empty?

This is not returning a JSON Array,. SELECT '{}'::json[] The type json[] is not a "JSON Array", it's a SQL Array of type JSON. You should never be using this kind of structure. It's almost certainly ...
Evan Carroll's user avatar
  • 63.5k
21 votes

Convert right side of join of many to many into array

You need to add the group by clause and use array_agg. SELECT i.id, i.title, array_agg(i.title) FROM items i INNER JOIN items_tags it ON it.item_id = i.id INNER JOIN tags t ON t.id = it.tag_id GROUP ...
Evan Carroll's user avatar
  • 63.5k
19 votes
Accepted

Build JSON object from one-to-many relationship data in a single query?

If containers can be empty, the currently accepted solution does not work for you. It has to be an outer join to preserve rows without match - to get equivalent results to the correlated subqueries ...
Erwin Brandstetter's user avatar
17 votes
Accepted

PostgreSQL filtering on array length inside JSON

Without knowing your data, I can only guess that the selectivity of your index is low (which happen if the length of the array does not vary much). One trick to overcome this might be changing the ...
András Váczi's user avatar
17 votes
Accepted

Find rows containing a key in a JSONB array of records

Postgres 12 or later: with SQL/JSON path expression SELECT * FROM tbl WHERE jdata->'array' @? '$ ? (exists (@."attr"))'; You can look for keys or values, only in the outer nesting ...
Erwin Brandstetter's user avatar
14 votes
Accepted

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

The data type of the column people is json, as is the result of json_array_elements(people). And there is no equality operator (=) for the data type json. So you also cannot run GROUP BY on it. More: ...
Erwin Brandstetter's user avatar
13 votes
Accepted

How to use an array as argument to a VARIADIC function in PostgreSQL?

Pass the array as is using the VARIADIC key word in the call: CREATE OR REPLACE FUNCTION json_extract_path_text(string citext, VARIADIC params text[]) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL ...
Erwin Brandstetter's user avatar
13 votes
Accepted

ORDER BY gives ERROR: function array_position(text[], character varying) does not exist

Sample data You have a table like, CREATE TABLE research(colors) AS VALUES ('Blue'), ('Orange'), ('Yellow'); ENUM type You have an enumerated list of colors. So the easy thing here would be to ...
Evan Carroll's user avatar
  • 63.5k
12 votes
Accepted

array_length if array is empty behaviour

It looks like PostgreSQL returns NULL for empty arrays. Try: SELECT array_length(ARRAY[]::bigint[], 1), array_length(ARRAY[]::bigint[], 1) IS NULL, array_lower(ARRAY[]::bigint[], 1), ...
Adam's user avatar
  • 431
12 votes

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

I believe the accepted answer by Erwin could be added with the following. Usually, we are working with regular tables with indices, instead of temporary tables (without indices) as in the original ...
pbillen's user avatar
  • 291
12 votes
Accepted

POSTGRES declaring variable containing date array in functions

You can use ARRAY keyword: create table tbl (dateval date[]); insert into tbl values(array ['20180101','20180102'::date]); Or in a block of code: do $$ declare dateval date[]; begin ...
McNets's user avatar
  • 23.8k
11 votes
Accepted

Does it make sense to store a couple of Boolean values as array?

TL;DR: Do not use an array. Use individual boolean columns. Your coworker may not be aware of actual storage requirements. Null storage is very cheap and efficient in Postgres. See: Do nullable ...
Erwin Brandstetter's user avatar
10 votes
Accepted

Why is postgreSQL using 1-based arrays?

While I think Postgresql has had arrays since before SQL-99, the SQL-99 standard specifies 1-indexed arrays, and it's consistent with other areas in SQL where the first element is 1 instead of 0. ...
Craig Ringer's user avatar
  • 56.6k
10 votes

How to turn JSON array into Postgres array?

I've tested a few options. Here is my favorite query. Suppose we have a table containing id and json field. The json field contains array, which we want to turn into pg array. SELECT * FROM test ...
FiscalCliff's user avatar
10 votes
Accepted

How to select multiple values into an array and loop through? (postgres 9.3)

A much better way, yet: just update. No loop needed. UPDATE loop_test SET id_copy = id , other_id_copy = other_id; WHERE id IS NOT NULL; The WHERE condition is only useful if id can be null ...
Erwin Brandstetter's user avatar
10 votes
Accepted

Postgres not returning data on array_agg query as below

A LEFT JOIN can solve it, like Laurenz provided. But I suggest an ARRAY constructor in a LATERAL subquery instead: SELECT l.name, b.book_list FROM library l CROSS JOIN LATERAL ( SELECT ARRAY( ...
Erwin Brandstetter's user avatar
10 votes
Accepted

What is the difference between POSTGRES _text and text[][] array types?

_text and text[] are interchangeable in PostgreSQL. Also text[][] is the same data type. To make things clearer: type[] means "the array type whose elements are of type type", and the actual ...
Laurenz Albe's user avatar
  • 52.9k
9 votes

Use array of composite type as function parameter and access it

I answered a similar question on SO some time ago, to suggest the same solution with unnest() like @a_horse did: Array of composite type as stored procedure input If books_table has the same row ...
Erwin Brandstetter's user avatar
9 votes
Accepted

Accumulate values into an array with Postgres

If your PostggreSQL version is 9.4 or later use FILTER clause: select array_agg(my_table.price) filter(where my_table.property_type = 'FLAT' ) as Flat_Current_Asking_Price, array_agg(my_table....
Abelisto's user avatar
  • 1,559
9 votes
Accepted

Proper use of varbinary type in MS SQL Server

I was told to implement as demonstrated below as this is how its been done before. ... this smells funny to me. You know what smells funnier than this approach? That line of "reasoning". The old "...
Solomon Rutzky's user avatar
9 votes
Accepted

Casting an array of texts to an array of UUIDs

Like has been commented, the column project_ids should be uuid[], which would preclude the problem. It would also be more efficient. To change (with no illegal data in column like you asserted): ALTER ...
Erwin Brandstetter's user avatar
8 votes

Customize jsonb key sort order involving arrays

First off, your question as well as your column name "key" are misleading. The column key does not contain any JSON keys, only values. Else we could use the function jsonb_object_keys(jsonb) to ...
Erwin Brandstetter's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible