Skip to main content

Questions tagged [array]

An array is an ordered linear data structure consisting of a collection of elements (values, variables, or references), each identified by one or more indexes. When asking about specific variants of arrays, use related tags as well. When using this tag, in a question that is specific to a database, tag the question with the database being used.

Filter by
Sorted by
Tagged with
19 votes
2 answers
21k 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.8k
140 votes
8 answers
455k 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,593
25 votes
1 answer
26k 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 ...
swasheck's user avatar
  • 10.7k
25 votes
2 answers
83k 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 ...
Rabi C Shah's user avatar
11 votes
1 answer
11k 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 ...
Jake Feasel's user avatar
8 votes
1 answer
24k 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 ...
spa's user avatar
  • 213
12 votes
2 answers
42k 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&...
Kobus's user avatar
  • 151
0 votes
1 answer
8k views

Array of strings when updating a field

Unintentionally discovered that following query works in PostgreSQL: UPDATE "sometable" SET "somefield" = ('string1', 'string2') WHERE "id" = 1; I'm passing an array of strings to field and the ...
happy_marmoset's user avatar
54 votes
2 answers
72k 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
  • 754
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 ...
Xodarap's user avatar
  • 233
7 votes
3 answers
21k 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(...
genichm's user avatar
  • 179
6 votes
1 answer
4k 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: ...
timpone's user avatar
  • 657
32 votes
3 answers
36k 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 ...
user2965107's user avatar
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"') ,('[]', '"...
Antonio's user avatar
  • 201
10 votes
1 answer
12k 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 ...
timpone's user avatar
  • 657
9 votes
1 answer
19k 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 ...
Fawn's user avatar
  • 193
7 votes
2 answers
23k 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: ...
Postgresql_enthu's user avatar
5 votes
1 answer
4k views

Index for finding combinations of attributes in JSON array

Based on this related question on SO. This works fine if you only want simple matches. Suppose tracks have both Artist and Title field in the JSON data. So we have something like: INSERT INTO tracks ...
Ken Li's user avatar
  • 153
5 votes
2 answers
26k views

How do I select arrays that are not empty?

Why is this so tricky, what is token set to that it isn't equal to null nor an empty string? SELECT lexemes FROM ts_debug('This is a title') WHERE alias = 'asciiword'; lexemes --------- {} {} {}...
Evan Carroll's user avatar
  • 63.8k
4 votes
2 answers
3k views

ERROR: data type text[] has no default operator class for access method "gist"

Whenever I try to try to create a GIST index on text[], I get the above error. CREATE TABLE bar(f) AS VALUES (ARRAY['foo','bar','baz']); CREATE INDEX ON bar USING gist(f); ERROR: data type text[]...
Evan Carroll's user avatar
  • 63.8k
4 votes
2 answers
7k views

How to do WHERE x IN (val1, val2,...) in plpgsql

I would like to update a selection of rows in a table; this works... UPDATE t1 SET col1 = 'newvalue' WHERE col0 in (2, 4, 5); But how can I do the same in a plpgsql function? The following gives a ...
Dave's user avatar
  • 143
4 votes
4 answers
2k views

Should I normalize my string arrays, or just keep it a simple string?

I am working on a project involving cards from the Magic: The Gathering game. To be of any use, I must load the basic information about each card into my database, but some of the fields may contain ...
cornergraf's user avatar
2 votes
1 answer
869 views

Why is intarray's push `+` so much faster than array-to-element concatenation `||`?

When it comes to using arrays of ints, I always default to using intarray. But, now I'm wondering why is the default concatenation so much slower. This answer probably requires some understanding of ...
Evan Carroll's user avatar
  • 63.8k
34 votes
1 answer
78k 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)...
BartZ's user avatar
  • 443
33 votes
1 answer
38k 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,...
Adam Matan's user avatar
  • 11.8k
30 votes
4 answers
63k 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 ...
Evan Carroll's user avatar
  • 63.8k
18 votes
1 answer
64k 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.
Worker's user avatar
  • 519
16 votes
4 answers
56k 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 ...
Sig's user avatar
  • 383
13 votes
2 answers
28k 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 ...
Alexandros's user avatar
  • 1,012
12 votes
2 answers
14k 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,...
OrangeDog's user avatar
  • 338
11 votes
5 answers
35k 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) ...
antsyawn's user avatar
  • 255
10 votes
2 answers
10k 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: ...
Amanda's user avatar
  • 305
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 ...
Vakarelov's user avatar
  • 103
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, ...
user avatar
8 votes
7 answers
2k 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}, ...
Bo Jeanes's user avatar
  • 353
8 votes
1 answer
9k 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 ...
Eoin's user avatar
  • 191
7 votes
1 answer
11k views

Step through multiple arrays in parallel with FOREACH

Is it possible to loop over multiple array with FOREACH in PL/pgSQL? In my case, 3 arrays with identical dimensions. How can I pass the elements like: for(int i = 0 ; i < array1.length ; i++){ ...
sarit's user avatar
  • 295
7 votes
1 answer
10k 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 ...
Fabrizio Mazzoni's user avatar
6 votes
1 answer
17k views

PostgreSql : flatten json array data

From my current query, I obtain this jsonb data : values: "a1", ["b1", "b2"] And I want to flatten it on one level only, like this : values: "a1", "b1", "b2" Here is a simplfied way to get data ...
vibby's user avatar
  • 123
6 votes
1 answer
7k 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 ...
Dave Jarvis's user avatar
5 votes
1 answer
191 views

Match .csv values as INTs. If one value from a group in the .csv matches one in another group, then merge strings

Here we have two sets of numbers. The problem is that I can't figure out how to get from the input to the output of numbers (DDL and DML below and also in the fiddle here). current_data 1,2,3 1,4 1,5,...
user3050153's user avatar
5 votes
2 answers
11k views

ERROR: could not find array type for datatype information_schema.sql_identifier

I am trying to run the below sql command: SELECT ARRAY( SELECT column_name FROM information_schema.columns WHERE table_name ='gis_field_configuration_stage' ); and I get the below ...
John Mitchell'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
4 votes
2 answers
921 views

Unnest ids from arrays based on priority with no duplication

Here is the sample schema for my database: Priority | Productive | UnProductive | Neutral | ------------------------------------------------- High | [1, 2] | [] | [4, 5] | ...
PaxPrz's user avatar
  • 219
4 votes
2 answers
459 views

Use PostgreSQL builtin operator <@ after including extension intarray

I added the intarray extension to my PostgreSQL database in order to make use of the - operator, but I still want to use the builtin <@ operator, not intarray's <@ operator. Is there a way to do ...
Jellicle's user avatar
  • 200
3 votes
3 answers
51k views

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

I have simple table for the sake of argument. I have a function that selects ids and loops through them called loop_test. I can select an array of ids and loop through them, causing my changes in a ...
Alexander Kleinhans's user avatar
2 votes
1 answer
6k views

How to pass an array to a plpgsql function with VARIADIC parameter

I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array. I have already defined the function CREATE OR REPLACE FUNCTION mix_table_fields(...
Darth Kangooroo's user avatar
2 votes
3 answers
34k views

Looping with array of values in PL/pgSQL

Can we use RETURN QUERY within this loop to display all values from UserResponseList instead of RAISE NOTICE? FOR i IN 1 .. array_upper(UserResponseList, 1) LOOP RAISE NOTICE '%, %, %', ...
M.Karthika's user avatar
1 vote
1 answer
650 views

Remap array columns to single column with value

Here is the sample database schema. | id | Critical | High | Low | -------------------------------------------- | 1 | {apple, ball} | {cat} | {dog, egg} | Now I want to remap these to ...
PaxPrz's user avatar
  • 219
1 vote
1 answer
2k views

Passing array from one plpgsql function to another

I have a function in PostgreSQL 9.4 that calls another function. Both are written in plpgsql. The child function takes an array argument representing IDs from a table that is used to create a new ...
spencerrecneps's user avatar