Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more

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
1 vote
1 answer
31 views

MySQL Trigger updating second table based on History and computed values

The Goal: Update an secondary table called Lottery_Cycle when an INSERT occours on table Lottery_History with some computed values and checking previous data. Let's say a lottery with 25 numbers of ...
Wisdom's user avatar
  • 11
0 votes
1 answer
14 views

Select rows where an item in a list of items is in a jsonb array of items

As an example, here with completely fictional logic: select * from materials where anyOfTheseValues('aluminum','steel','plastic') in valid_names_column_jsonb_array So, in the ...
Shawn Crocker's user avatar
0 votes
0 answers
105 views

An alternative to PostgreSQL union?

In the following example variables in single quotes '' are arguments passed to the PostgreSQL query. If 'tag_args' is an empty array, the first row of result table is a row of three nulls and I use ...
jvkloc's user avatar
  • 133
-1 votes
1 answer
117 views

Replacing substrings in array elements

I have a table with an array of URLs in column urls. Now there's a need to update some URLs changing the hostname. E.g.: {https://storage.host1.com/file.jpg, https://storage.host1.com/file2.jpg} ...
minicooper's user avatar
0 votes
1 answer
75 views

Performance issues with @> operator in postgres 14

CREATE TABLE public.article ( id uuid NOT NULL, tags _text NULL, CONSTRAINT article_pkey PRIMARY KEY (id) ); I have the requirement to load articles that have a tag which is in a large ...
user1117605's user avatar
0 votes
1 answer
7 views

How can I get a linear forecast in BigQuery?

BigQuery has no native support for linear forecasting outside of machine learning, but it does support user-defined functions, and these can accept arrays as inputs. However, (as of 2023/10 anyway), ...
Jon of All Trades's user avatar
0 votes
1 answer
216 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 ...
gerardw's user avatar
  • 103
0 votes
0 answers
44 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 ...
mslot's user avatar
  • 427
0 votes
1 answer
52 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 ...
Khuzema's user avatar
  • 13
0 votes
1 answer
170 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 ...
user45867's user avatar
  • 1,683
1 vote
2 answers
559 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 ...
Chris Stryczynski's user avatar
1 vote
0 answers
155 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,...
Leehan's user avatar
  • 207
1 vote
1 answer
2k 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 ...
Pantea's user avatar
  • 1,432
0 votes
1 answer
414 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),...
Pantea's user avatar
  • 1,432
1 vote
0 answers
28 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 | ...
L777's user avatar
  • 111
1 vote
1 answer
412 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, &...
glmn's user avatar
  • 11
0 votes
2 answers
381 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 ...
xqzkio's user avatar
  • 1
0 votes
1 answer
158 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 --------+-------------------------------...
PythonScrub's user avatar
0 votes
1 answer
842 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 ...
Biller Builder's user avatar
0 votes
1 answer
469 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 ...
Ron Piggott's user avatar
0 votes
1 answer
991 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( ...
Daniel Kniaz's user avatar
1 vote
1 answer
618 views

Multi dimensional JSON Array SQL Query

I'm struggling to write an appropriate query for my data -> "schools":[ { "org_symbol":"School 1", "criteria":[ [ ...
chris's user avatar
  • 11
1 vote
1 answer
837 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),'&...
Ömer Faruk Demirel's user avatar
0 votes
1 answer
988 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 ...
ryanzidago's user avatar
2 votes
1 answer
894 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), ...
Ron Piggott's user avatar
1 vote
1 answer
345 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 ...
Henrik N's user avatar
  • 197
5 votes
2 answers
2k 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 ...
Joe's user avatar
  • 161
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 | {...
user1117605's user avatar
1 vote
0 answers
113 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 ...
pjk_ok's user avatar
  • 111
7 votes
1 answer
8k 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
  • 181
1 vote
1 answer
7k 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], [...
kerniebc's user avatar
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 ...
Mihael's user avatar
  • 3
3 votes
1 answer
3k 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) ...
pietrushka's user avatar
1 vote
1 answer
907 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 ...
Qi luo's user avatar
  • 25
0 votes
1 answer
2k 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 ...
Erwin Zangwill's user avatar
1 vote
1 answer
581 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 ...
iconoclast's user avatar
1 vote
2 answers
2k 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 ...
majid's user avatar
  • 15
1 vote
2 answers
314 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 ...
tinlyx's user avatar
  • 3,580
0 votes
1 answer
2k 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 ...
user973347's user avatar
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,...
domhinch's user avatar
0 votes
1 answer
1k 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 ...
ncmc100's user avatar
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 ...
Héctor's user avatar
  • 297
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 ...
Gonzalo Rocha's user avatar
0 votes
3 answers
3k 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} | +----...
Gonzalo Rocha's user avatar
6 votes
1 answer
1k 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 ...
randompawn's user avatar
0 votes
0 answers
232 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 ...
ahron's user avatar
  • 803
5 votes
1 answer
189 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
0 votes
1 answer
1k 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 ...
user avatar
3 votes
1 answer
5k 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. ...
Kamilski81's user avatar
3 votes
3 answers
13k 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 | +------------+ | ["...
Marcus's user avatar
  • 378

1
2 3 4 5
7