Questions tagged [array]
The array tag has no usage guidance.
314
questions
-1
votes
0
answers
35
views
Relational DB engine with support for column as a list of foreign keys
Is there some sort of DB engine that is relational (like MySQL's InnoDB/PostgreSQL)
and supports a type of column that is basically a list of foreign keys?
Is something like that in some standard and/...
- 99
0
votes
1
answer
31
views
Convert PostgreSQL array type
Given an enum type (e.g. letter).
What is the syntax to convert an existing array to the enum type? The existing values are valid for the enum. For example, how would I convert letter_data in:
create ...
- 103
0
votes
0
answers
26
views
Create index on array of composite types
I have this example
create type price_schedule_day as enum ('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday');
create type price_element as
(
price_start ...
- 417
0
votes
1
answer
39
views
How to calculate quantity based on a boolean field and retrieve values from a JSON field in MySQL
I'm working with a table called price_options that has the following fields: id, slots, list_slots, and quantity.
Issue Description
My objective is to calculate the quantity for each price option ...
- 13
0
votes
1
answer
61
views
Snowflake - extracting country/ state names - RegEx or Array?
I have a bunch of sloppy Geo text in a field.
However the country/ state names or codes are relatively clean.
It'll say Poland 23 or Illinois Remote or Frogballs, Germany or TX, AL, AK.
I have a ...
- 1,631
1
vote
2
answers
128
views
How to update each value in an array with a function?
I want to trim spaces from each value in a text[].
If this was just a text column I could do:
update server set tags = rtrim(ltrim(tags));
However how do I do this for an array? As otherwise that ...
0
votes
0
answers
131
views
Compare 2 arrays with duplicate values in PostgreSQL
I need to compare 2 arrays but with taking into account duplicate values counting.
Currently:
select array[1,2,3,3] <@ array[1,2,3]
=> true
select array[1,2,3,3] <@ array[1,2,3,3]
=> ...
- 1
1
vote
0
answers
132
views
PostgreSQL - updating a counter outside a loop function
PostgreSQL 14
Here is a function :
CREATE OR REPLACE FUNCTION test(datedebut integer)
returns text
LANGUAGE plpgsql AS
$$
DECLARE
tab integer[];
periodes text;
i integer;
BEGIN
tab := Array[[0,...
- 207
0
votes
1
answer
748
views
Receiving error "can not access rows from a non-nested table item" while working with associative arrays
Below , I'm trying to use the data of the array I've declared,
in the where clause of the query but I receive the following error :
declare
v_input varchar2(400) := '0,1,2,3,4';
type ...
- 1,402
0
votes
1
answer
216
views
Receiving error "Local collection types not allowed" while working with associative arrays
I have an input parameter with this format '0,1,2,3,4' , what I want to do is to declare an array with a variable size (by this I mean the input parameter might be like '0,1' , so the size is not fix),...
- 1,402
1
vote
0
answers
25
views
Best practice to store subitems
I am going to create my first Dynamodb database table. I have 3 options in my mind:
1. Create a long table where every subitem is a new row.
Example:
user John | item 1 | 2021
user John | item 2 | ...
- 111
1
vote
1
answer
179
views
Find integer in array is very slow
PostgreSQL Version PostgreSQL 15.0, compiled by Visual C++ build 1914, 64-bit
This is my "victim" table.
create table if not exists "KeywordsStats" (
id bigserial primary key, &...
- 11
0
votes
2
answers
237
views
PostgreSQL Multicolumn GIN Index with Ordering
I have a large table (~20M records) in a Postgres DB with a "tags" column of ARRAY type, and a "date_inserted" column. I want to be able to query the latest inserted records for a ...
- 1
0
votes
1
answer
96
views
PostgreSQL: Return ENUM values like data_type modifiers
When querying the datatype using format_type(tttypid, atttypmod) from pg_attribute, I would like to return the ENUM values like atttypmod values.
attname | type
--------+-------------------------------...
0
votes
1
answer
617
views
How do you pass the array of composite type to the argument of an SQL function?
Database example: https://dbfiddle.uk/sERgZPiB
Tables and types
CREATE TABLE accounts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
login text NOT NULL,
password text NOT NULL,
email ...
- 276
0
votes
1
answer
343
views
PostgreSQL INSERT Multi-Dimensional Array
I have spent a few hours looking at PostgreSQL INSERT array documentation and examples. But I am just getting more and more confused.
I've created the table word_mash_word_cube_definitions where ...
- 165
0
votes
1
answer
596
views
postgres reverse jsonb array
Postgres 13
I have a table with jsonb array and I need to revert the array. Any idea on how I can do that?
CREATE OR REPLACE FUNCTION array_reverse(anyarray) RETURNS anyarray AS $$
SELECT ARRAY(
...
- 101
1
vote
1
answer
455
views
Multi dimensional JSON Array SQL Query
I'm struggling to write an appropriate query for my data ->
"schools":[
{
"org_symbol":"School 1",
"criteria":[
[
...
- 11
1
vote
1
answer
664
views
Mysql update json if-else exist
I want to update my raw with if-else condition. Here is my sql code;
update posts
set like =
if(json_contains(like,2,'$'),
json_remove(like,replace(json_search(like,'one',2),'&...
0
votes
1
answer
704
views
How to compute the average score for each user, by time interval?
I have a table User that only has an id and a name. A user has many Games, which is a table that contains an id, a score, inserted_at and a user_id referencing the User table. I would like that my ...
- 375
2
votes
1
answer
724
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), ...
- 165
1
vote
1
answer
245
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 ...
- 197
3
votes
2
answers
1k
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 ...
- 141
1
vote
2
answers
4k
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 | {...
- 23
1
vote
0
answers
92
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
6
votes
1
answer
5k
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 ...
- 171
1
vote
1
answer
5k
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
2k
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
2
votes
1
answer
2k
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)
...
- 21
1
vote
1
answer
742
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 ...
- 25
0
votes
1
answer
1k
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
410
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
1k
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
278
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 ...
- 3,325
0
votes
1
answer
1k
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 ...
- 37
0
votes
1
answer
3k
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
767
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
2
votes
1
answer
1k
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 ...
- 277
2
votes
1
answer
2k
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
2k
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} |
+----...
5
votes
1
answer
888
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 ...
- 53
0
votes
0
answers
176
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 ...
- 759
5
votes
1
answer
179
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
996
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
3
votes
1
answer
4k
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
3
votes
3
answers
10k
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 |
+------------+
| ["...
- 356
0
votes
2
answers
899
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
...
- 3,325
1
vote
1
answer
2k
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 ...
- 3,325
0
votes
1
answer
5k
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 ...
- 227
0
votes
1
answer
846
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