Questions tagged [array]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
-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/...
jave.web's user avatar
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 ...
gerardw's user avatar
  • 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 ...
mslot's user avatar
  • 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 ...
Khuzema's user avatar
  • 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 ...
user45867's user avatar
  • 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 ...
Chris Stryczynski's user avatar
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] => ...
Matteo's user avatar
  • 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,...
Leehan's user avatar
  • 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 ...
Pantea's user avatar
  • 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),...
Pantea's user avatar
  • 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 | ...
L777's user avatar
  • 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, &...
glmn's user avatar
  • 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 ...
1upkd's user avatar
  • 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 --------+-------------------------------...
PythonScrub's user avatar
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 ...
Biller Builder's user avatar
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 ...
Ron Piggott's user avatar
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( ...
Daniel Kniaz's user avatar
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":[ [ ...
chris's user avatar
  • 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),'&...
Ömer Faruk Demirel's user avatar
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 ...
ryanzidago's user avatar
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), ...
Ron Piggott's user avatar
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 ...
Henrik N's user avatar
  • 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 ...
Joe's user avatar
  • 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 | {...
user1117605's user avatar
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 ...
pjk_ok's user avatar
  • 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 ...
Eoin's user avatar
  • 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], [...
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
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) ...
pietrushka's user avatar
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 ...
Qi luo's user avatar
  • 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 ...
Erwin Zangwill's user avatar
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 ...
iconoclast's user avatar
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 ...
majid's user avatar
  • 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 ...
tinlyx's user avatar
  • 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 ...
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
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 ...
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
  • 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 ...
Gonzalo Rocha's user avatar
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} | +----...
Gonzalo Rocha's user avatar
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 ...
randompawn's user avatar
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 ...
ahron's user avatar
  • 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,...
user3050153's user avatar
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 ...
user avatar
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. ...
Kamilski81's user avatar
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 | +------------+ | ["...
Marcus's user avatar
  • 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 ...
tinlyx's user avatar
  • 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 ...
tinlyx's user avatar
  • 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 ...
as.beaulieu's user avatar
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",&...
REMITH's user avatar
  • 103

1
2 3 4 5
7