Questions tagged [array]
The array tag has no usage guidance.
296
questions
2
votes
1
answer
48
views
Select rows where array of letters is contained in given array
I want to search for the letters in the "spelling" (text[]) column:
a n n o y t
I need it to find the words: any, annoy, no, an, toy
But not find: derivatives of annoy (annoying, annoyance), ...
- 133
1
vote
1
answer
68
views
Any performance reasons to prefer idx() (intarray) over array_position()?
Are there performance reasons (or other reasons) to prefer idx (from intarray) over array_position to find the position of an integer in a list of integers?
I ask because we've used idx since before ...
- 187
2
votes
2
answers
46
views
does a btree index on an array field actually do anything?
In PG you can create a table like
CREATE TABLE foo (
id uuid PRIMARY KEY DEFAULT gen_random_uuid (),
name text NOT NULL,
things text[] NOT NULL DEFAULT ARRAY[] ::text[]
)
and a related index ...
- 131
-1
votes
2
answers
112
views
SQL (Postgres): query array Column which must be a subset of an array
I have a table "article" and for each entry there are some "tags" stored (array column)
name | tags
test1 | {t1,t3}
test2 | {t2,t3}
test3 | {t3}
test4 | {t1,t5}
test5 | {...
1
vote
0
answers
18
views
Storing Image Filenames As An Array So I Can Add New Values Without Having To Re-Write The Entire Array - MySQL
I have an image library where I would like to create the functionality where users can create boards of selected images. The boards will essentially be collections of images on the site along a ...
- 111
1
vote
1
answer
59
views
Aggregate multiple columns and rows into a json array
I try to query multiple columns and rows from a table to create the xy-pairs for a step-linechart in json format.
I need the json like this:
[[63115200,213.4], [139769280,213.4], [139769280,213.39],
[...
- 11
0
votes
1
answer
44
views
Convert right side of join of many to many into array of objects
I have 2 tables:
Table subjects: id, class_id, title, created_at, updated_at
Table marks: id, student_id, class_id, mark, created_at, updated_at, subject_id
I want to query subjects table and have it ...
- 3
1
vote
1
answer
114
views
Unnest array of arrays
In my parameterized query I have to unnest arrays with 1 dimension and 2 dimensions:
$1: ARRAY['id1', 'id2']
$2: ARRAY[ARRAY['tag1'], ARRAY['tag2']]
I tried this query:
INSERT INTO table (id, tags)
...
- 11
0
votes
1
answer
96
views
In Postgresql, can I create a column of a one-dimension array type with foreign key constraint? If not, how to simulate it?
In essence I have to design todo table, and an item table. I need to preserve item order, as well as allowing client to rearrange the order. If postgres allow an array of foreign key that would be ...
- 5
0
votes
1
answer
218
views
psql function: append one dimensional array to a two dimensional array using array_append
I tried to append an one dimensional array to a two dimensional array using array_append in psql function. I defined count_values as output by out count_values integer[][], cout_value as variable in ...
1
vote
1
answer
73
views
How do I split an array between GROUP BY groups?
I'm trying to create a query which will put some rows in multiple groups. I'm doing this by generating an array that contains both categories I want to group by.
Here's my query so far (slightly ...
- 111
1
vote
2
answers
59
views
Converting results to array
I have many entities like:
user
id | name
-----------
1 | Joe
2 | David
3 | Jane
cars
id| name
------------
1 | cars1
2 | cars2
3 | cars3
4 | cars4
5 | cars5
6 | cars6
7 | cars7
8 | cars8
...
- 15
1
vote
2
answers
86
views
Does the PostgreSQL array inTOAST storage have random access times?
This question is related to PostgreSQL TOAST storage and the GIS.SE question: Should TOAST compression be disabled for PostGIS?
Basically, I was wondering if there is any guarantee of constant time ...
- 2,797
0
votes
1
answer
38
views
Postgres outputting an array in PSQL
I have the following function. It is something I am building and want to show the contents of stuff_list so I can prove it is working correctly. I can't however work out what output type I should be ...
- 27
0
votes
1
answer
489
views
Unnest array values from multiple columns with dynamic unpivot
I have been given several tables in wide form that I would like to convert to long form. Unfortunately, they have about 60 columns.
Example input:
CREATE TABLE tbl_wide (
p_key integer PRIMARY KEY,...
- 1
0
votes
1
answer
88
views
How to loop an array and sum values until condition is met?
I have a relational table where I need to loop through and sum amount values until a specified amount criteria is met. The data looks like this:
Company
Account
Amount
Coastal
A384
500
Coastal
C940
...
- 1
1
vote
1
answer
50
views
PLPGSQL store columns of a 2D query output into array variables
In some step of a plpgsql function I need to store a 2D query result into array variables.
The following code does the job for scalars (but fails with arrays):
SELECT col_a, col_b
FROM my_table
WHERE ...
- 181
1
vote
1
answer
88
views
Using the array result of a select statement returns ERROR: pq: operator does not exist: integer = integer[]
The query in question is the following:
SELECT * FROM options WHERE id = any(SELECT option_ids FROM workshops WHERE id=3)
ERROR: pq: operator does not exist: integer = integer[]
Basically the ...
0
votes
3
answers
499
views
How can I return multiple columns from an array of ids?
I have a two tables like the following:
workshops table
+----+--------------+------------+
| id | name | option_ids |
+----+--------------+------------+
| 1 | Conversation | {2,3} |
+----...
4
votes
1
answer
89
views
Optimize query matching first N items of an array
I'm working with a Postgres DB full of chess game data right now, where each game is a row in a 'records' table. The players' moves and the (optional) computer evaluations of those moves each have ...
- 43
0
votes
0
answers
21
views
Multiple tokens per account
In the context of user account management, I'm using Firebase. So for each login, the front-end sends an Auth ID, a FCM token (for sending notifications to the user), and the usual user details. Each ...
- 669
5
votes
1
answer
102
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
0
votes
1
answer
272
views
Calculating Average Value of JSONB array in Postgres
I have a column called "value" in my "answers" table.
| value |
|---------|
| [1,2] |
| [1] |
| [1,2,3] |
The type of "value" is "jsonb".
I want to get ...
user236222
0
votes
0
answers
197
views
CROSSTAB / cannot extract elements from a scalar HELP PLEASE!
I'm kind of lost here and I really need some wisdom from a Data Expert:
cf.item ->> 'name' AS "custom_field" they want everything as a column and not create them as a row and cf.item -...
3
votes
1
answer
656
views
How can I match the items in an array column, with a different table in Postgres?
I have a table in a Postgres 12 database with that has an array column with a bunch of dog breed guids. I want to lookup these values and basically return the lookup values instead of the guids.
...
- 231
0
votes
0
answers
129
views
Postgres jsonb field: remove an element from an array by the element id
Figure a Postgres 12.5 jsonb field containing the following:
[
{id:1, name: 'one'},
{id:2, name: 'two'},
{id:3, name: 'three'}
]
How to write the request that will match: "drop the object ...
- 197
2
votes
1
answer
2k
views
Is it possible to remove a JSON array element by value in MySQL?
In MySQL, it's possible to remove an array element by index:
SELECT JSON_REMOVE('["a", "b", "c"]', '$[1]') `result`;
+------------+
| result |
+------------+
| ["...
- 327
0
votes
1
answer
33
views
How to case text array elements automatically?
According to PostgreSQL documentation on the ARRAY constructor:
By default, the array element type is the common type of the member
expressions, determined using the same rules as for UNION or CASE
...
- 2,797
1
vote
1
answer
384
views
How to keep array fields in PostgreSQL jsonb
It seems that PostgreSQL (12+) cannot keep the PostgreSQL array type in jsonb objects. If a PostgreSQL array is stored in a jsonb, it seems that it's converted to a jsonb array.
For example, given the ...
- 2,797
0
votes
1
answer
988
views
Postgres Query Array Column with Array
I have a postgres view "people_codes" that has a column that is an ARRAY_AGG of intergers. I am trying to query on the view with an array of integers for all rows that may have one of these ...
- 217
0
votes
1
answer
105
views
Join two Json arrays to one with key and value
I have two jsonb columns(keys,values).
Eg: keys colum value = ["key1","key2","key3","key4"]
values column = ["val1","val2","val3",&...
- 103
0
votes
1
answer
130
views
Postgresql: sort by value position in array column, then by secondary order
I'm not quite sure what the best way to phrase this is...
So in my DB there is pillars text array which is basically an enum where providers ordered what values meant the most to their business, from ...
- 245
1
vote
3
answers
1k
views
Postgresql: How to pass an array to trigger function in TG_ARGV?
I've got some fairly generic Postgres trigger functions that I'd like to re-use across a few different tables. To that end, I'm passing arguments as part of the CREATE TRIGGER statement so they'll be ...
- 113
0
votes
1
answer
190
views
Uniqueness constraint for elements of arrays in the entire table
In the following table:
CREATE TABLE user (
email varchar[],
...
);
I'd like to ensure that no two users have the same e-mail. I'm not sure how the UNIQUE constraint interacts with ...
- 125
1
vote
2
answers
161
views
Zip array with values from JSON
Say I have '{1, 2, 3, 4, 5}' (just an array with the numbers 1-5), and {"1": 0, "2": 3, "5": 0} (comes from a column). With only SQL, is it possible to output this? {&...
- 205
1
vote
2
answers
743
views
Postgres 11 JSONB - How to get all distinct values of a field across all elements
I have a table called Shipments:
CREATE TABLE shipments
(
shipmentId numeric,
metadata jsonb
);
Inside the metaData column there can exist an array of JSON objects named stops which ...
- 321
-2
votes
1
answer
46
views
Are unstructured databases faster using arrays or many binary variables? [closed]
Is it faster (or otherwise better) to structure unstructured data like this:
{ label:"many_variables_example",
"a":0,
"b":1,
"c":1,
"d":1,
&...
1
vote
1
answer
2k
views
How to get a JSON array without JSON_ARRAYAGG in MySQL
I can't use a JSON_ARRAYAGG function introduced in MySQL 5.7.22 because I'm using MySQL 5.7.20. I would like to get a JSON array of a column values like I would get if I could use the function: SELECT ...
4
votes
2
answers
67
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] |
...
- 209
1
vote
1
answer
55
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 ...
- 209
1
vote
1
answer
31
views
Having regex_matches exclude cases where both capturing groups match a 0-length string
I'll trying to write a regex pattern that will help me create an array of length 2 arrays such that each inner array is matched like so:
I'm looking to split sequences that begin with a positive or ...
- 115
0
votes
1
answer
36
views
Conditionally insert a value in an array when creating a new column
I have some data in a jsonb col that I need to clean and insert into another text array column.
What I have:
[
{
"v": "birthday"
},
{
"v": "reference&...
- 101
0
votes
1
answer
70
views
Concurrent Update of Array (Postgres)
I have an array that I can update from multiple workers as follows:
UPDATE my_table SET arr = array_append(my_table.arr, element).
The problem pops up when two workers try to update the array at the ...
0
votes
1
answer
831
views
Postgres 12: Inserting array of composite types, composite type has an array field. Malformed array literal, unexpected end of input
I'm having an issue with inserting a simple array of integers into my table in Postgres via a procedure. The array of integers is declared within a composite type and I'm trying to insert an array of ...
- 5
5
votes
2
answers
589
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 |
----------...
- 209
0
votes
1
answer
646
views
(PostgreSQL 11) Reading in an array of JSON objects (or composite types) from a CSV file into Postgres
So I am trying to import rows into a table using a CSV file, but have been running into problems I haven't been able to solve. I would like one column of a row to be an array of JSON objects.
How I ...
- 5
2
votes
1
answer
787
views
Dynamically update JSONB rows - POSTGRESQL
I am currently trying to update some JSONB fields that we have on our database, we are moving nodes from the JSON a level up for an specific field.
The node we are trying to move, however, is not ...
0
votes
1
answer
19
views
Join static file xml with ID field with Database table ID field [closed]
I am using php to display a table with different columns. The table is build from a static xml file with several hundreds of entries. Each entry has also an ID field.
I have to look up that ID field ...
- 109
1
vote
1
answer
767
views
Mysql JSON update by value of a key
I have a table like:
CREATE TABLE `campus_tb` (
`campus_id` int(11) NOT NULL AUTO_INCREMENT,
`campus_dataJSON` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`...
- 113
9
votes
3
answers
6k
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, ...
- 193