Questions tagged [array]
The array tag has no usage guidance.
50
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
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
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
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
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
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
50
votes
2
answers
66k
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
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
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
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:
...
- 635
0
votes
1
answer
7k
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 ...
- 479
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
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
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
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
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:
...
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 ...
- 153
5
votes
1
answer
24k
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
---------
{}
{}
{}...
- 62.1k
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 ...
- 183
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[]...
- 62.1k
4
votes
2
answers
6k
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 ...
- 143
2
votes
1
answer
814
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 ...
- 62.1k
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
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
17
votes
1
answer
60k
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
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
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
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
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
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
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
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
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
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
6
votes
1
answer
10k
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++){
...
- 285
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 ...
- 833
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 ...
- 195
5
votes
1
answer
186
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,...
- 67
4
votes
2
answers
763
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] |
...
- 219
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 |...
- 143
4
votes
2
answers
423
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 ...
- 200
4
votes
1
answer
16k
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 ...
- 93
3
votes
3
answers
48k
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 ...
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(...
- 301
2
votes
3
answers
32k
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 '%, %, %', ...
- 21
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 ...
- 353
1
vote
1
answer
560
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 ...
- 219