Questions tagged [array]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
95
votes
5answers
274k 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, ...
30
votes
3answers
26k 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 ...
26
votes
2answers
31k 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:...
25
votes
1answer
45k views

PostgreSQL JSON query array against multiple values

I want to write a query against jsonb type in Postgres that given an array of customers IDs will find corresponding groups. Given this example table: CREATE TABLE grp(d JSONB NOT NULL); INSERT INTO ...
21
votes
1answer
68k 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 ...
21
votes
1answer
19k 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 ...
16
votes
2answers
10k 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-...
16
votes
1answer
38k 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.
15
votes
1answer
16k 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
votes
2answers
15k 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 ...
11
votes
2answers
18k 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 ...
10
votes
1answer
5k 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($...
10
votes
1answer
6k 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 ...
10
votes
1answer
14k 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 ...
10
votes
2answers
10k 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"') ,('[]', '"...
9
votes
5answers
25k 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) ...
9
votes
2answers
12k 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 ...
9
votes
2answers
21k 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','...
9
votes
3answers
18k 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 ...
9
votes
1answer
17k 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 '{}'::...
9
votes
2answers
4k 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 ...
9
votes
2answers
1k 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 ...
8
votes
7answers
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}, ...
8
votes
3answers
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 ...
8
votes
1answer
22k 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 ...
8
votes
1answer
18k 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 ...
8
votes
2answers
22k 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, ...
8
votes
1answer
7k 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 ...
8
votes
1answer
3k 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 ...
7
votes
2answers
59k 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 (...
7
votes
1answer
7k 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 ...
7
votes
1answer
3k 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: ...
7
votes
1answer
12k 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 ...
6
votes
5answers
2k views

PostgreSQL: how can I shorten a multi-word string to few words?

I'm using PostgreSQL 9.5.6. I have space separated multi-word strings that I need to shorten to say 3 words? I've looked at the documentation and in order to use the substring() functions I need to ...
6
votes
1answer
12k 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 ...
6
votes
1answer
8k 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 ...
6
votes
2answers
4k 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 = '...
6
votes
1answer
4k 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 ...
6
votes
1answer
2k views

Order result by count of common array elements

Using Postgres 9.4, I'm interested in having an array of integers like user_ids_who_like and provide an array of users (like user_ids_i_am_following) to sort that intersection. Something like: ...
6
votes
1answer
6k views

Transpose two-dimensional array from n rows to 2 columns

Background Using PostgreSQL 9.1, so WITH ORDINAL (a 9.4 feature) is not available. Problem Looking to simplify the code that pivots a two-dimensional array. Code A working, overly-verbose example ...
6
votes
1answer
4k views

PostgreSQL, finding elements by value in numeric JSON arrays

I have a table defined as: create table dummy (jdata jsonb); I inserted the following two rows: insert into dummy values ('["dog","cat","elephant","waffle"]'); insert into dummy values ('[1,2,3,4]')...
6
votes
1answer
962 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 ...
6
votes
1answer
8k views

How does postgres store array values?

Postgres seems to do some sort of compression on array values. I have a table with 2.1 million rows, each has 2 columns of smallint arrays, all populated with 1440 values. most of these values are -...
5
votes
2answers
254 views

Postgres not returning data on array_agg query as below

The problem arise when there are no data for books in specific library. Consider a following working scenario. Table library -------------------------------- | id | name | owner | ----------...
5
votes
2answers
17k 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: ...
5
votes
1answer
4k 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,...
5
votes
1answer
3k views

How to remove duplicates from any array and preserve ordering in PostgreSQL?

I'm looking for a way to eliminate duplicates from a PostgreSQL array while preserving the ordering of its elements. What I currently have are the following functions: create function array_unique( ...
5
votes
2answers
15k 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&...
5
votes
4answers
11k views

ERROR: operator does not exist: text[] ~~ text

We have an easy syntax that allows us to look into an array for a single scalar, SELECT 'foo' = ANY(ARRAY['foo', 'bar', 'baz']); We can use the same method to match with LIKE SELECT 'foobar' LIKE ...
5
votes
3answers
3k views

Element of an array in a daterange?

I have an array of dates, and I want to find out if any of the elements in the array appear within a given daterange. For example (pseudocode - doesn't work, wrapped for readability): SELECT ARRAY['...

1
2 3 4 5 6