Questions tagged [array]
The array tag has no usage guidance.
2
votes
2answers
73 views
Get the max value from a slice of a JSON Array?
I would like to get the max value within a slice of a Json object (typicaly [1,2,3,5,6,7,9,10]) which is contained in a field named Data of the table raw.
The limits Start & End of the slice are ...
1
vote
1answer
23 views
Better way to select a matching subset of rows?
DB Fiddle link. I have a many to many table like this:
CREATE TABLE house_to_cats (
id SERIAL PRIMARY KEY,
house_id INTEGER,
cat_id INTEGER
);
-- house with cats 1 and 2: too small
INSERT INTO ...
0
votes
1answer
23 views
Removing duplicate-ish paths generated from recursive CTE
I have some multi-parented data, that I run a recursive CTE against to find common ancestors:
item_id |item_name |path_name |id_path_array |
--------|----------|-------------|--------------|
1 ...
0
votes
1answer
30 views
How can I get a unique array in PostgreSQL?
The following
SELECT ARRAY[a,b,c,d]
FROM ( VALUES
('foo', 'bar', 'foo', 'baz' )
) AS t(a,b,c,d);
Returns {foo,bar,foo,baz} of type text[]. I would like to get {foo,bar,baz} of type text[] with one ...
0
votes
1answer
20 views
Mongodb what is the cost of array queries?
As far as I can tell, find queries in mongodb involving an array appear very expensive. Take as example the queries given in: https://docs.mongodb.com/manual/tutorial/query-array-of-documents/
db....
0
votes
1answer
35 views
How do I modify a JSON object?
I have a table "raw_data" in which I have a field "data" containing a json array like:
{'a' : 1,
'b' : 2,
'c' : 3}
I would like two different things:
Append a new item like {'d' : 4} at the end of ...
1
vote
1answer
32 views
How to pass an array to a plpgsql function with VARIADIC parameter
I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array.
I have already defined the function
CREATE OR REPLACE FUNCTION mix_table_fields(...
0
votes
2answers
32 views
PostgreSQL - how to generate an array containing column name and column data…?
I'd like to create a dictionary - an array - from a simple query, to include column names in output.
IE, I'd like to turn this:
SELECT id, last, first FROM names;
id | last | first
---...
2
votes
3answers
48 views
Pass array of mixed type into stored FUNCTION
I am preparing procedure, that is basically finishing payment. It takes all info as arguments and do all inserts and stuff in one transaction.
the problem here is, that one argument is list of n ...
1
vote
1answer
22 views
How to prepend an element to a JSON array?
I have a plpgsql function in PostgreSQL 9.6, which at some point has a text variable _x (guaranteed to not be NULL) and another variable _y which is a JSONB array of text (which might be empty).
How ...
0
votes
3answers
157 views
How to select multiple values into an array and loop through? (postgres 9.3)
I have simple table for the sake of argument. I have a function that selects ids and loops through them called loop_test. I can select an array of ids and loop through them, causing my changes in a ...
1
vote
2answers
318 views
Casting an array of texts to an array of UUIDs
How can I cast an array of texts into an array of UUIDs?
I need to do a join between two tables: users and projects.
The users table has an array field named project_ids containing the project IDs ...
0
votes
1answer
42 views
Use an array returned from a subquery as argument in WHERE clause with ANY function in outer query
A query like this:
SELECT *
FROM person
WHERE person.favorite_number = ANY(
ARRAY[ 2 , 3 ]
)
;
…runs. The ANY() function tells us if the single value is found within an array of values. So, ...
3
votes
4answers
129 views
Representing lists in SQL
My database setup is modern (My)SQL.
I've got a Content Table, including ids.
Now, users should be able to comment on content.
To achieve this, arrays have come to my mind.
Now I see several options ...
0
votes
1answer
86 views
Index for split string. PostgreSQL 10
I have the following string in db:
A/B/C/
A/B/D/
E/F/C/
It's the uri column.
The table is the following:
create table meta_info (
id bigserial primary key,
uri varchar,
created ...
1
vote
1answer
27 views
How can I index a text[] field in postgresql?
I have two columns:
alternate_names text[]
first_name character varying
I am not sure how I can write an index on this type of query?
SELECT "people".* FROM "people"
WHERE (lower(first_name) = '...
0
votes
1answer
258 views
How can you insert data into a ARRAY column on a Snowflake table using SQL?
I am having difficultly finding documentation on how to insert data into an ARRAY column type using SQL on a Snowflake table.
Snowflake Documentation: https://docs.snowflake.net/manuals/sql-reference/...
1
vote
2answers
43 views
How do You concatenate a value to an array that was retrieved through a query in sql?
I have a table named "mkvtable" that establishes a correspondence between individual strings and arrays of strings:
word | mkvword
---------+-----------------
hello | {world,friend}
...
2
votes
1answer
244 views
Error while inserting into array of timestamps
Im using postgres 10.1 and the Datestyle config is the following
DateStyle
-----------
ISO, MDY
Im having this error
ERROR: date/time field value out of range: "1535673858" Hint: Perhaps
you ...
0
votes
1answer
69 views
Postgresql JSONB Column, how to query for multiple specific elements in a JSON array
Giving the following JSONB data structure of a stopInfo column in a myTable table:
{
"stopInfo": [
{
"stopType": "Origin",
"partnerId": "KR01",
"locationId"...
0
votes
1answer
31 views
What is the best way to manage interdependent indexes
So I have a table of items as so
|firstIndex|subIndex|relatedIndexes|
-----------------------------------
| 1 | 1 | 2 ,6 , 8 |
-----------------------------------
| 1 | 2 |...
0
votes
1answer
32 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 ...
2
votes
0answers
50 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 ...
1
vote
1answer
20 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. ...
1
vote
0answers
42 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 ...
1
vote
1answer
556 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 '{}'::...
3
votes
1answer
380 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
...
0
votes
0answers
38 views
JSONB: function which sorts only JSON arrays
I've just finished SQL code which checks if particular "query" fulfils "conditions" stored in rows. If yes, id-s of that rows are returned.
This is made up example.
CREATE TABLE jsonb_test (
id ...
2
votes
1answer
115 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( ...
1
vote
2answers
2k 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?
3
votes
2answers
2k 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 | ['George', 'John']
2 | ['Thomas', 'James']
3 | ['James', 'John']
How to list the number of bands each ...
0
votes
0answers
383 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 ...
1
vote
1answer
82 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 ...
2
votes
2answers
67 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 ...
0
votes
1answer
363 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 ...
0
votes
0answers
15 views
get an array with a crosstab as multiple join
I have a ad_id from action_states u1 join with the table ad_media on u1.ad_id=ad_media.ad_id
so basically as a return result i must have a set of array ad_media_id , it keeps giving me the results ...
0
votes
0answers
200 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 ...
1
vote
2answers
889 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(
...
5
votes
1answer
494 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:
...
1
vote
1answer
1k 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 ...
3
votes
0answers
238 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 ...
0
votes
1answer
104 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 ...
-1
votes
1answer
45 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 ...
3
votes
2answers
3k 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": "somevalue"},
{"name":"bla2", "...
0
votes
1answer
726 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 ...
1
vote
1answer
272 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, ...
1
vote
3answers
128 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]
,'|#|'::...
2
votes
4answers
2k 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 ...
2
votes
1answer
2k 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 ...
1
vote
2answers
2k views
Insert Into Array of object MongoDB Ask Question
I have
{
"_id" : ObjectId("5a25af80d2c4dd065991dccc"),
"username" : "abc@gmail.com",
"password" : "$2a$10$ptFyLKtyyrL5gMYdXS6wV.HLUA4Py5iudMJDldf5qsHFS4.9TPCyy",
"role" : "Admin",
...
