Questions tagged [array]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
432 views

Storing arrays of data in a time-series database

I'm building an low utilization time-series database to capture yearly data points for a set of items fewer than 100,000. My question has to do with storing arrays of data in a way that is easily ...
a coder's user avatar
  • 208
2 votes
0 answers
136 views

Copy to file and transform arrays

I'm copying data from a Postgres DB on AWS RDS to a Postgres-like DB on AWS Redshift. Varchar arrays are being exported as {foo,bar} but Redshift wants them as ["foo","bar"]. Redshift doesn't ...
Dennis's user avatar
  • 121
1 vote
1 answer
54 views

extract different number of rows from multiple tables

I have a quick question. I'm looking for the concept behind this, not necessarily a ready-to-use code, but if it's not too complex, I don't mind a quick solution. I have an app that manages accounts. ...
Chris's user avatar
  • 183
1 vote
0 answers
71 views

Postgresql 10 arrays equality failure

I am using AWS RDS postgres instance version 10.3. Completly stuck with the problem. I have a table like: create table social_message( social_profile_id int8 not null, body text, subject ...
den bardadym's user avatar
15 votes
1 answer
46k views

What is the fastest way to verify that a json[] array is not empty?

I want to verify that an json[] column the_array is not empty. For a normal array, I could check on: the_array != '{}' However, that does not work for a json[], as demonstrated below: select '{}'::...
Jarius Hebzo's user avatar
4 votes
1 answer
9k views

Use array_position() function to get the most frequent value for column from pg_stats

I'm trying to execute this simple query in order to check whether some value (1000) belongs to MCV list used by Postgres query optimizer: SELECT array_position(most_common_vals, 1000) FROM pg_stats ...
zer0hedge's user avatar
  • 143
6 votes
1 answer
5k views

How to remove duplicates from any array and preserve ordering in PostgreSQL?

I'm looking for a way to eliminate duplicates from a PostgreSQL array while preserving the ordering of its elements. What I currently have are the following functions: create function array_unique( ...
John Frazer's user avatar
7 votes
2 answers
57k views

POSTGRES declaring variable containing date array in functions

I am trying to declare a date array. I have tried this: DECLARE dateVal DATE[] := ['2018-01-01','2018-02-01']; I get: ERROR: syntax error at or near "[" How do I properly declare it?
user165242's user avatar
12 votes
3 answers
31k views

How to unnest and GROUP BY elements of a JSON array?

Given the band table, with a json column holding an array : id | people ---+------------- 1 | ['John', 'Thomas'] 2 | ['John', 'James'] 3 | ['James', 'George'] How to list the number of bands each ...
Bax's user avatar
  • 293
1 vote
0 answers
2k views

Convert JSON array to native integer array

My function is returning json data which is containing two integer array. Now I need to get this two integer array list and passed it to another function. CREATE OR REPLACE FUNCTION method1(id ...
Somil's user avatar
  • 125
3 votes
1 answer
598 views

Have several 128 elements lists, need to obtain distance between them

Is storing them in a CUBE type field and then getting euclidean distance, (using <->) the only way? I can not save them as a list of floats, since i get: "can't adapt type numpyndarray" Can ...
misterghost's user avatar
4 votes
2 answers
10k views

Testing if an element in a PostgreSQL array?

Is there an operator in PostgreSQL to test whether an element is in an array? Currently, I'm doing such tests in a slightly complex way by first constructing a singleton array for the element and ...
tinlyx's user avatar
  • 3,425
0 votes
1 answer
2k views

how do you output a plsql array via ref cursor

I have a legacy procedure that returns a plsql assoc table to a C++ DLL. Now I need to call it via C# and ODP.NET. I have been unable to find any info on how to call a proc that returns a pl/sql ...
user151263's user avatar
5 votes
1 answer
31k views

Postgres query column with comma-delimited strings for array of strings

I have a table that has data that looks like this: id oe -- --------------------------------------------------- 1 04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ 2 64526915083,64526953474 ...
MB34's user avatar
  • 171
0 votes
0 answers
873 views

MySQL version of split_to_array or split_to_table?

PostgreSQL supports two String Functions, that are incredibly useful regexp_split_to_array regexp_split_to_table Both of these functions essentially take two strings, (a) a pattern and (b) an input ...
Evan Carroll's user avatar
  • 62.1k
2 votes
2 answers
5k views

Loop through slices of 2-dimensional array in plpgsql function?

How to loop through an array of arrays in a function and access its elements? Below is the the function (this is only the relevant part). create or replace function public.whatwhatwhy( ...
Michael's user avatar
  • 275
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: ...
Amanda's user avatar
  • 305
1 vote
1 answer
4k views

Store arrays in table

I'm not sure if this is the best SE section to ask my question. If not, I kindly ask you to help me to find out the best one. Developing a web application in php and MySql I'm in doubt how to store a ...
Mark's user avatar
  • 113
3 votes
0 answers
1k views

Best way to cast array (text[]) to record-type?

PostgreSQL has the ability to create record types. A lot of operations like regexp_match return text[]. What is the most efficent way to go from text[] to a custom record type? Currently, I'm doing ...
Evan Carroll's user avatar
  • 62.1k
1 vote
1 answer
686 views

Reshape array in PostgreSQL

I am using a PostgreSQL 10 server, in which I have some tables containing attributes of type double precision[] intended for storing 1D arrays of data, of length ~1000. My code interacting with the ...
beldaz's user avatar
  • 1,680
-1 votes
1 answer
70 views

SQL Server : refactor schema to avoid repetition

I have an enormous prod SQL Server database : total size = 1.5 TB full backup duration = 4 hrs ( online ) that needs to be slimmed-down because it's causing a variety of issues for database ...
BaltoStar's user avatar
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&...
Kobus's user avatar
  • 141
4 votes
1 answer
7k views

Searching in Array performance?

We have a table of id|school_id|parent_ids where parent_ids is an array of ids. If we don't have the school_id and only parent_id to search for, then the query will search through all the table ...
simo's user avatar
  • 271
3 votes
1 answer
3k views

Multiply elements of equally sized postgresql array

In Postgresql 9.6, is there a clean way to multiply corresponding elements within two equally sized arrays? For example a = [1,2,9,4,5] b = [5,8,4,0,12] function(a, b) would return [5, 16, 36, 0, ...
Noxville's user avatar
  • 170
3 votes
3 answers
596 views

Array to string sometimes concatenates fewer values

array_to_string(array_agg(array_to_string(array[ highlights,exceptions,inter_note,inter_status::text ,comp_note,comp_status::text,hard_note,hard_status::text] ,'|#|'::...
Namburi Akhil's user avatar
5 votes
4 answers
29k views

ERROR: operator does not exist: text[] ~~ text

We have an easy syntax that allows us to look into an array for a single scalar, SELECT 'foo' = ANY(ARRAY['foo', 'bar', 'baz']); We can use the same method to match with LIKE SELECT 'foobar' LIKE ...
Evan Carroll's user avatar
  • 62.1k
2 votes
1 answer
9k views

How can I set a default now() timestamp in a timestamp array?

I want to have a timestamp array which tracks a series of events. The first event always happens when the row is created, so I want the array to be instantiated with an initial default value. I've ...
John Bachir's user avatar
1 vote
2 answers
6k views

Insert Into Array of object MongoDB Ask Question

I have { "_id" : ObjectId("5a25af80d2c4dd065991dccc"), "username" : "[email protected]", "password" : "$2a$10$ptFyLKtyyrL5gMYdXS6wV.HLUA4Py5iudMJDldf5qsHFS4.9TPCyy", "role" : "Admin", ...
Sandeep Ranjan's user avatar
1 vote
1 answer
39 views

Modify all array entries in all records of a table at once

I have a schema that involves an array of timestamps. Due to an import problem all the timestamps are off by one hour (time zone issue). I'd like to update all the timestamps in all the records with ...
nerdinand's user avatar
  • 151
6 votes
2 answers
4k views

How to check multidimensional array of arrays contains array?

I have an array of arrays [ID, TYPE]. All values are integer. Array can be integer[] or jsonb type. SELECT array[[442,2],[443,2]] SELECT to_jsonb(array[[1,2],[3,4]]) How can I check this array(s) ...
Dmitry's user avatar
  • 189
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 '%, %, %', ...
M.Karthika's user avatar
2 votes
0 answers
2k views

PostgreSQL: Storing large volume of photos and attached tags: many-to-many relationship or array of text?

This is my first post here and I am quite new to PostgreSQL - many apologies if my question may appear a bit unclear. We set up a database for storing a large volume of photo-metadata with attached ...
Alex's user avatar
  • 403
1 vote
1 answer
132 views

Ensuring a GROUP has specific values, no more no less with an aggregate

I have a table (a :: text, b :: uuid). I also have a set (not a list, i.e. order doesn’t matter) of UUIDs, bs = {b1, b2, b3}. How can I get such an a that has exactly those three bs in the table, no ...
Michal Rus's user avatar
4 votes
1 answer
720 views

Proper use of varbinary type in MS SQL Server

I am at a new position and I am being told to implement a stored procedure that will accept a list of user ids and update a flag. When I suggested the use of a table value parameter (array emulation),...
bbqchickenrobot's user avatar
0 votes
1 answer
1k views

Querying an array of strings with a string that represents search terms

I have an array of values like: array ('hello world', 'foo', 'bar') -- terms here have only letters and one space at most and some text like: 'foo hello-world hello1world' and I would like to get ...
Randomize's user avatar
  • 1,163
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 --------- {} {} {}...
Evan Carroll's user avatar
  • 62.1k
0 votes
1 answer
1k views

Use ENUM in array of objects

Is it possible to setup an ARRAY column with objects/JSON on a TABLE, forcing a parameter of the objects in the ARRAY to adhere to an ENUM, while keeping that object parameter unique? Data examples: ...
David Gustavsson's user avatar
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[]...
Evan Carroll's user avatar
  • 62.1k
2 votes
2 answers
2k views

Array exceed allowed size in Postgres

I have a function that accepts as parameter an array like: CREATE OR REPLACE FUNCTION my_func(some_data custom_datum[]) RETURNS VOID AS $$ BEGIN create table foo_table as ( ...
Randomize's user avatar
  • 1,163
9 votes
2 answers
8k views

Accumulate values into an array with Postgres

Currently I have this query: select sum(case when my_table.property_type = 'FLAT' then my_table.price else 0 end) as Flat_Current_Asking_Price, sum(case when my_table.property_type_mapped = '...
Randomize's user avatar
  • 1,163
0 votes
1 answer
658 views

Storing a 3D point array representing a flight path?

I need to store an array of 3D points in a column: latitude, longitude, altitude What datatype would be required for this? Also, if I wanted to extend this in the future to include time (ie an array ...
pookie's user avatar
  • 181
2 votes
1 answer
93 views

Match elements of a JSON array

This is a sample table : create table test( id int, json_key json[] ); insert into test (id,json_key) values (1,array['{"key1":"123"}'::json,'{"key2":"456"}'::json]), (2,array['{"...
Salley Evaughn's user avatar
4 votes
2 answers
2k views

How to check if integer array overlaps with int4range?

PostgreSQL version: 9.5 I am trying to write a query that will check if an array of specified decades overlaps in a range. For example, I'd like to find all of the people employed at a company in the ...
Alexander's user avatar
  • 143
4 votes
4 answers
2k views

How to remove an element from a col with a CSV of ints in PostgreSQL 9.2?

I have PostgreSQL 9.2 database and a table: id integer, allowed_types character varying(255)` Sample data as below: id allowed_types 1 3,4,5,13,14 How I can remove 4 and 5 from allowed_types ...
Zhlobopotam's user avatar
1 vote
1 answer
47 views

Select from arrays in a way similar to IN

I have this table and data: CREATE TABLE sal_emp ( name text, pay_by_quarter integer[] ); INSERT INTO sal_emp VALUES ('Bil' , '{1, 2, 3, 4}'), ('Bill2', '{5, 6, 7, 8}'); ...
Martin Perry's user avatar
0 votes
1 answer
421 views

Fill populate arrays from other table

Things go inside other things. More than one can go inside and each one can be inside many different containers. This is for a on-line shopping site where assorted things that we get from our ...
Nadreck's user avatar
  • 145
0 votes
1 answer
755 views

How do I conditionally select elements of an array? [closed]

I have a table (only one row) in my PostgreSQL 9.5 db with two columns i.e., count (bigint) and array (text). count array 6 "112,19.3,142,142,19.3,172,172,20.3,202,202,20.3,232,232,19.3,...
khajlk's user avatar
  • 197
6 votes
5 answers
3k views

PostgreSQL: how can I shorten a multi-word string to few words?

I'm using PostgreSQL 9.5.6. I have space separated multi-word strings that I need to shorten to say 3 words? I've looked at the documentation and in order to use the substring() functions I need to ...
S.Ro's user avatar
  • 95
50 votes
2 answers
67k 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:...
Ced's user avatar
  • 714
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 ...
Evan Carroll's user avatar
  • 62.1k

1 2 3
4
5
7