Questions tagged [array]
The array tag has no usage guidance.
316
questions
134
votes
8
answers
431k
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, ...
- 1,533
50
votes
2
answers
67k
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:...
- 714
34
votes
1
answer
75k
views
Query JSON array of obejcts against multiple values
I want to write a query against a jsonb type table-column in Postgres that, given an array of customers IDs, will find corresponding groups.
Given this example table:
CREATE TABLE grp(d jsonb NOT NULL)...
- 443
32
votes
3
answers
35k
views
Foreign key constraint on array member?
Suppose I have a table containing job roles:
CREATE TABLE roles
(
"role" character varying(80) NOT NULL,
CONSTRAINT "role" PRIMARY KEY (role)
);
Suppose I further have a table, users, and each ...
- 321
29
votes
1
answer
35k
views
Why does PostgreSQL allow querying for array[0] even though it uses 1-based arrays?
I have been playing with arrays in one of my PostgreSQL databases.
I have created a table with a geometry array with at least one element:
CREATE TABLE test_arrays (
polygons geometry(Polygon,...
- 11.5k
26
votes
4
answers
54k
views
How can I get a unique array in PostgreSQL?
The following
SELECT ARRAY[a,b,c,d]
FROM ( VALUES
('foo', 'bar', 'foo', 'baz' )
) AS t(a,b,c,d);
Returns {foo,bar,foo,baz} of type text[]. I would like to get {foo,bar,baz} of type text[] with one ...
- 62.1k
25
votes
2
answers
82k
views
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 ...
- 251
25
votes
1
answer
25k
views
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 ...
- 10.6k
19
votes
2
answers
18k
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-...
- 62.1k
18
votes
3
answers
16k
views
When to use JSON or JSONB array vs SQL array in postgres
I am looking for the main points to consider when choosing between a JSON or JSONB array and the regular SQL postgres array. If you know that you will only be inserting one data type into an array, ...
- 283
17
votes
1
answer
61k
views
Pass an array or record to a function in PostgreSQL?
I have a task to pass arrays, records, and in some cases array of records as a parameter to functions in PostgreSQL.
- 509
16
votes
1
answer
38k
views
Postgres query to return JSON object keys as array
Is it possible to return a JSON object keys as an array of values in PostgreSQL?
In JavaScript, this would simply be Object.keys(obj), which returns an array of strings.
For example, if I have a ...
- 1,571
16
votes
4
answers
49k
views
Casting an array of texts to an array of UUIDs
How can I cast an array of texts into an array of UUIDs?
I need to do a join between two tables: users and projects.
The users table has an array field named project_ids containing the project IDs ...
- 371
15
votes
1
answer
46k
views
What is the fastest way to verify that a json[] array is not empty?
I want to verify that an json[] column the_array is not empty. For a normal array, I could check on:
the_array != '{}'
However, that does not work for a json[], as demonstrated below:
select '{}'::...
- 415
15
votes
2
answers
102k
views
How to cast to int array in Postgresql?
I would like to cast from ARGV[] which is text to int array in PostgreSQL where I marked the pseudocode by TODO in the code.
Code in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (...
14
votes
2
answers
32k
views
Creating an array from multiple columns without NULL elements
I'm trying to build a query to aggregate together multiple columns in a legacy table stored in a similar structure as below:
CREATE TEMPORARY TABLE foo AS
SELECT * FROM ( VALUES
(1,'Router','...
- 243
13
votes
2
answers
6k
views
Does it make sense to store a couple of Boolean values as array?
I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.)
Instead of having Boolean columns, I could have a single array ...
- 233
13
votes
2
answers
26k
views
Efficient merging (removing duplicates) of arrays
I have two tables, left2 and right2. Both tables will be large (1-10M rows).
CREATE TABLE left2(id INTEGER, t1 INTEGER, d INTEGER);
ALTER TABLE left2 ADD PRIMARY KEY (id,t1);
CREATE TABLE right2( t1 ...
- 1,012
12
votes
2
answers
12k
views
Combining array_agg and unnest
Given a dataset (with a GIN index on values):
key | values
-------------
1 | {4,2,1}
1 | {2,5}
2 | {4,1,3}
I want to aggregate the arrays:
key | values
-------------
1 | {4,2,1,5}
2 | {4,...
- 296
12
votes
3
answers
31k
views
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 ...
- 293
11
votes
5
answers
34k
views
Build JSON object from one-to-many relationship data in a single query?
I have a PostgreSQL 9.5.3 DB with tables like this:
container
id: uuid (pk)
... other data
thing
id: uuid (pk)
... other data
container_thing
container_id: uuid (fk)
...
- 255
11
votes
2
answers
36k
views
Find rows containing a key in a JSONB array of records
I'm trying to query for a key present in an array of objects. This structure:
column jdata
{"name": "Somedata",
"array": [ {"name":"bla1", "attr&...
- 141
11
votes
1
answer
23k
views
PostgreSQL filtering on array length inside JSON
I have a table table with a JSONB field data, which contains a variable-length array, e.g.
{"label": "xyz", "items": [ ... ]}
I created an index on the length of the "items" element:
CREATE INDEX ...
- 225
11
votes
1
answer
7k
views
PostgreSQL PREPARE query with IN () parameters
I'm trying to prepare a query from PHP like:
pg_prepare($con, "prep", "select * from test where tid in ($1)");
and then execute it with:
$strpar = "3,4,6,8,10";
pg_execute($con, "prep", array($...
- 1,940
11
votes
1
answer
10k
views
How to use an array as argument to a VARIADIC function in PostgreSQL?
I am trying to make a case-insensitive version of json_extract_path_text(), using the citext module.
I would like this to be a simple wrapper around the built-in function, the only difference being ...
- 607
10
votes
2
answers
14k
views
Array integer[]: how to get all distinct values in a table and count them?
I'm not so good with SQL (PostgreSQL). Here is what I want to do:
I have a table, fields:
id SERIAL
inet INET
ports integer[]
id | inet | ports
----+------------+------------
2 | 1.2.2.1 ...
- 203
10
votes
1
answer
13k
views
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 ...
- 103
10
votes
2
answers
9k
views
ORDER BY gives ERROR: function array_position(text[], character varying) does not exist
I have a pretty basic categorical column in a Postgres database that is currently stored as VARCHAR. I can select a count of each with:
I though adding an ORDER BY array_position() would do it:
...
- 305
10
votes
1
answer
11k
views
How to get 0 as array_length() result when there are no elements
I have a query like this:
select id, array_length(users_who_like_ids,1) as ct
from queryables
order by 2 desc;
But empty arrays (with no elements) sort on top. I'd rather have array_length() return 0 ...
- 635
10
votes
2
answers
27k
views
Unnest multiple arrays into rows
I have been shown a great way to bulkinsert with this example:
WITH p AS (
INSERT INTO parent_table (column_1)
SELECT $1
RETURNING id)
INSERT INTO child_table (parent_table_id, ...
user32234
10
votes
2
answers
13k
views
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"')
,('[]', '"...
- 201
10
votes
2
answers
2k
views
Find rows where integer sequence contains a given subsequence
Problem
Note: I refer to the mathematical sequences, not the sequences mechanism of PostgreSQL.
I have a table representing sequences of integers. The definition is:
CREATE TABLE sequences
(
id ...
- 201
9
votes
3
answers
10k
views
How to pass a table type with an array field to a function in postgresql
i have a table called book
CREATE TABLE book
(
id smallint NOT NULL DEFAULT 0,
bname text,
btype text,
bprices numeric(11,2)[],
CONSTRAINT key PRIMARY KEY (id )
)
and a ...
- 515
9
votes
1
answer
18k
views
Delete array element by index
Is it possible to delete a Postgres array element by index? (Using Postgres 9.3.)
I don't see anything for this in the docs (http://www.postgresql.org/docs/9.3/static/functions-array.html) but ...
- 193
9
votes
2
answers
8k
views
Accumulate values into an array with Postgres
Currently I have this query:
select
sum(case when my_table.property_type = 'FLAT' then my_table.price else 0 end) as Flat_Current_Asking_Price,
sum(case when my_table.property_type_mapped = '...
- 1,163
9
votes
1
answer
5k
views
Is there any sense in using Postgres array of JSONB values instead of only JSONB?
Is there any reason to use array of JSONB values instead of just raw JSONB?
My use case is that I need to store custom forms definition in list of JSON objects. And my question is whether it would be ...
- 205
8
votes
7
answers
1k
views
How can I generate all trailing substrings following a delimeter?
Given a string that may contain multiple instances of a delimiter, I want to generate all substrings starting after that character.
For example, given a string like 'a.b.c.d.e' (or array {a,b,c,d,e}, ...
- 353
8
votes
1
answer
23k
views
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 ...
- 193
8
votes
1
answer
20k
views
array_length if array is empty behaviour
Please explain to me this behavior.
This SQL finds elements where ids (type is array::bigint) is empty.
SELECT * FROM rises WHERE ids = '{}'
-- finds, e.g., 9 rows
This SQL finds no rows:
SELECT * ...
- 181
8
votes
1
answer
22k
views
How does unnest() treat NULL values and empty arrays?
I am trying to achieve something like LEFT JOIN with array unnest() function. I want the query to return a row with a null value if the array is empty. Therefore, by using a CASE construct, I wanted ...
- 1,355
8
votes
1
answer
4k
views
Fastest way to get the length of the intersection of two text arrays in PostgreSQL
This is what I am currently using:
CREATE FUNCTION array_intersect(a1 anyarray, a2 anyarray)
RETURNS anyarray AS
$$
SELECT ARRAY(
SELECT unnest($1)
INTERSECT SELECT unnest($2)
ORDER BY ...
- 8,343
7
votes
2
answers
57k
views
POSTGRES declaring variable containing date array in functions
I am trying to declare a date array.
I have tried this:
DECLARE
dateVal DATE[] := ['2018-01-01','2018-02-01'];
I get:
ERROR: syntax error at or near "["
How do I properly declare it?
- 173
7
votes
1
answer
9k
views
PostgreSQL list of integers separated by comma or integer array for performance?
As per subject, I have a field which is varchar in which i store some numbers separated by commas. The list grows and shrinks based on clients choices. I then use it in queries to check if a ...
- 1,940
7
votes
2
answers
22k
views
Use array of composite type as function parameter and access it
I have created a type Books in Postgres which has 2 numeric fields and 2 varchar fields. I want to send an array of Books to a function to INSERT those values in a matching table.
This is my type:
...
7
votes
2
answers
2k
views
What is the overhead for an array in Postgres?
What is the additional overhead of an array compared to a normal column of that same datatype? In other words, if an array will almost always have one value in it, how much space would I be "wasting" ...
- 699
7
votes
1
answer
6k
views
What is the difference between POSTGRES _text and text[][] array types?
I'm using a postgres database created by a script (Osm2pgsql). It has created some fields with the type _text for two dimensional arrays of text. I'm trying to understand why it didn't just create a ...
- 181
7
votes
1
answer
4k
views
Group and count array elements using intarray
I am working on a Postgres 9.4 project with the intarray extension enabled. We have a table that looks like this:
items
-------------------------------------
id name tag_ids
----...
- 635
7
votes
1
answer
14k
views
Inner join on array column in Postgres
In Postgres 9.4, I'm trying to pull the names of all columns that are involved in a UNIQUE constraint for a given table in Postgres.
It looks like the names of such columns are contained in ...
- 319
7
votes
2
answers
2k
views
How to store high-dimensional (N > 100) vectors and index for fast lookup by cosine similarity?
I am trying to store vectors for word/doc embeddings in a PostgreSQL table, and want to be able to quickly pull the N rows with highest cosine similarity to a given query vector. The vectors I'm ...
- 379
7
votes
3
answers
20k
views
Pushing or appending to JSON array in PostgreSQL 9.4
I have table where one of the fields is JSON array. I need to append received JSON array into that field without overriding existing values.
Something like that:
CREATE OR REPLACE FUNCTION add_array(...
- 179