Questions tagged [array]
The array tag has no usage guidance.
315
questions
1
vote
0
answers
1k
views
Table structure to store a an array of tags per entry
Lets say one wants to create a DB to store items, each having a Set<String> tags, associated with it. A straightforward way would be a flat table with ItemID, ItemName, DateCreated,... and a Set&...
- 213
0
votes
1
answer
2k
views
PostgreSQL multidimensional array - ok to use or misdesign in this case?
I have a table called listings. For each listing, the user can upload 5 images. In the table listings, I have a multidimensional array images text[][]. Each multidimensional element will hold [ a ...
- 135
2
votes
2
answers
2k
views
How to get lexemes in original sort order from ts_vector?
Postgres' full text search to_tsvector() function returns a sorted array of lexemes. I need exactly that, just with lexemes in original sort order. Is there any such Postgres function, or do I need to ...
- 175
3
votes
2
answers
6k
views
SQL Arrays are returned as strings in PHP
I have a column with type Array, I need to get all the elements in the array, I tried:
SELECT managers_ids::INTEGER[] FROM teams WHERE id = 14
the result is '{8,9,10}'. How can I iterate on the ...
9
votes
1
answer
5k
views
Is there any sense in using Postgres array of JSONB values instead of only JSONB?
Is there any reason to use array of JSONB values instead of just raw JSONB?
My use case is that I need to store custom forms definition in list of JSON objects. And my question is whether it would be ...
- 205
6
votes
2
answers
11k
views
How does postgres store array values?
Postgres seems to do some sort of compression on array values. I have a table with 2.1 million rows, each has 2 columns of smallint arrays, all populated with 1440 values. most of these values are -...
4
votes
2
answers
2k
views
How can I implement full-text search on a single value in a text[] column?
I am using Postgresql 9.3 on an Ubuntu 12.4 machine.
I've only recently started studying database and database design, and I am to create a database that includes text that will later be searched ...
1
vote
3
answers
2k
views
UPDATE BIGINT[] column from a SELECT
I have to update a bigint[] column named permissao_ver.
Example value: '{1,2,3,4,5,11,44,56,75,11}'.
Pseudo code:
UPDATE callcenter.pausa
SET permissao_ver = '{"(SELECT cod_grupo FROM crm....
- 239
3
votes
1
answer
2k
views
Why does jsonb use more space for storing integers?
I did some small tests to try these data types, found some puzzling results.
Summary:
jsonb uses more space for integers than strings:
select pg_column_size('{"a":"1"}'::jsonb) as json_size
18
...
- 699
14
votes
2
answers
32k
views
Creating an array from multiple columns without NULL elements
I'm trying to build a query to aggregate together multiple columns in a legacy table stored in a similar structure as below:
CREATE TEMPORARY TABLE foo AS
SELECT * FROM ( VALUES
(1,'Router','...
- 243
2
votes
1
answer
11k
views
Array stringifies to using `{}` in Postgres output
I have an array column but when I output it using SQL SELECT, it looks like
{{"a":"b","c":"d"},{"e":"f","g":"h"}}
But why does it start and end with {{ and }}? Shouldn't it be [{ and }] for arrays? ...
- 133
5
votes
3
answers
5k
views
Element of an array in a daterange?
I have an array of dates, and I want to find out if any of the elements in the array appear within a given daterange.
For example (pseudocode - doesn't work, wrapped for readability):
SELECT ARRAY['...
- 175
19
votes
2
answers
18k
views
Why is array_agg() slower than the non-aggregate ARRAY() constructor?
I was just reviewing some old code written for pre-8.4 PostgreSQL, and I saw something really nifty. I remember having a custom function do some of this back in the day, but I forgot what pre-...
- 62.2k
11
votes
1
answer
10k
views
How to use an array as argument to a VARIADIC function in PostgreSQL?
I am trying to make a case-insensitive version of json_extract_path_text(), using the citext module.
I would like this to be a simple wrapper around the built-in function, the only difference being ...
- 607
4
votes
0
answers
2k
views
Does PostgreSQL support `unnest` as a column?
Does PostgreSQL document and support this behavior of the SRF unnest() as a computer/virtual column,
SELECT
unnest(array_agg(id)) AS id
FROM foo
GROUP BY CUBE (a,b,c,d)
HAVING count(*) > 1
If ...
- 62.2k
8
votes
7
answers
1k
views
How can I generate all trailing substrings following a delimeter?
Given a string that may contain multiple instances of a delimiter, I want to generate all substrings starting after that character.
For example, given a string like 'a.b.c.d.e' (or array {a,b,c,d,e}, ...
- 353
7
votes
2
answers
2k
views
What is the overhead for an array in Postgres?
What is the additional overhead of an array compared to a normal column of that same datatype? In other words, if an array will almost always have one value in it, how much space would I be "wasting" ...
- 699
1
vote
1
answer
248
views
PostgreSQL - PostGIS ST_SetValues - int array cast to double and back
I am using ST_SetValues with array defined as ARRAY[[9, 9], [9, 9]]::double precision[][].
If I store 32bit integer value in this array can I retrieve this value exactly after casting from double to ...
- 231
0
votes
1
answer
2k
views
how to search this field in Postgres for an array of characters
I have a users table which is an id, name, and tags. I'd like to search tags for a specific piece of text. How would I do do this?
id | integer | not null ...
- 635
11
votes
1
answer
23k
views
PostgreSQL filtering on array length inside JSON
I have a table table with a JSONB field data, which contains a variable-length array, e.g.
{"label": "xyz", "items": [ ... ]}
I created an index on the length of the "items" element:
CREATE INDEX ...
- 225
2
votes
1
answer
702
views
Filter by list of values pairs
I have Postgresql 9.4 database and products table. For each product there must be list of pairs:
price; quantity for which this price is acutal
Products table may contain millions and billions of ...
- 21
1
vote
1
answer
4k
views
POSTGRES 9.4: Function parameter as empty array
I'm trying to create an empty array as a parameter so it's optional. I want to be able to call it without having the need to give a 2nd and 3rd parameter. Can somebody help?
CREATE OR REPLACE ...
- 11
2
votes
1
answer
70
views
Simplified table using more disk space than its original (bigger) version
Using PostgreSQL 9.3 on Debian 7.
I have a system that searches for text tokens inside PDF files and store where it was found for indexation.
Basically the table structure is:
...
0
votes
2
answers
1k
views
Optimal way(performance wise) to store array like data in MySQL [closed]
I am working on a website(PHP), and one of the features that the site should have is ordering items from the website, and the order will be like an array with an architecture similar to this:
order(
...
2
votes
1
answer
9k
views
Selecting only overlapping elements from array of ranges
The table I need to search contains an array of numrange values illustrated by:
CREATE TABLE data ( sensor varchar(25), ranges numrange[] );
INSERT INTO data VALUES
('sensor0','{"[872985609.0,...
- 123
3
votes
1
answer
2k
views
Why is postgreSQL using 1-based arrays?
Why is PostgreSQL using 1-based arrays (first element being at index 1) and not 0-based arrays (first element being at index 0)?
- 223
11
votes
5
answers
34k
views
Build JSON object from one-to-many relationship data in a single query?
I have a PostgreSQL 9.5.3 DB with tables like this:
container
id: uuid (pk)
... other data
thing
id: uuid (pk)
... other data
container_thing
container_id: uuid (fk)
...
- 255
13
votes
2
answers
6k
views
Does it make sense to store a couple of Boolean values as array?
I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.)
Instead of having Boolean columns, I could have a single array ...
- 233
1
vote
1
answer
2k
views
What is the best way to save a large array in database?
Currently, I'm working on a simple CBIR (Content-based Image Retrieval) and I have a question.
What is the best way to save a large array on the database? For example, I want to save the results of ...
- 11
2
votes
2
answers
2k
views
Fastest query for selecting arrays that contain duplicates
I'm using Postgres 9.5 and I have a column, named phones, of type text[].
I need to find all rows where this column contains duplicates.
I've found this extremely useful set of functions https://...
- 123
10
votes
2
answers
13k
views
Customize jsonb key sort order involving arrays
I have a table in PostgreSQL with some data:
create table t2 (
key jsonb,
value jsonb
);
INSERT INTO t2(key, value)
VALUES
('1', '"test 1"')
,('2', '"test 2"')
,('3', '"test 3"')
,('[]', '"...
- 201
8
votes
1
answer
20k
views
array_length if array is empty behaviour
Please explain to me this behavior.
This SQL finds elements where ids (type is array::bigint) is empty.
SELECT * FROM rises WHERE ids = '{}'
-- finds, e.g., 9 rows
This SQL finds no rows:
SELECT * ...
- 181
2
votes
1
answer
236
views
Postgres Array into Geoserver
I'm working with OS Highways data, which when loaded into Postgres using ogr2ogr has a few columns that are set as arrays. Geoserver does not like this, so I need to transform the arrays into a simple ...
- 455
1
vote
1
answer
3k
views
Is there a way to find out if value exists with addToSet?
addToSet adds values to an array if the value doesn't exist.
If the value already exists MongoDB will not return anything saying that it exsits.
Is there a way to find out if the value exists? (...
- 443
0
votes
1
answer
110
views
Query Last Value in Fixed Length Array With Empty Values
I have a table that generally looks like:
Name varchar | Location varchar[]
---------------------------------------------
Lemoney | {Home,Work,Home,Stack Exchange,,,,,,}
Other Dude | {Home,,,...
- 5
1
vote
0
answers
633
views
Inner join on array columns from 3 tables
I have 2 tables that are related between them using integer data which is stored in arrays.
Data table
telemetria_data.historico_354677050164513
CREATE TABLE historico_354677050164513 (
imei ...
- 999
1
vote
1
answer
2k
views
Constraint to prevent overlapping ranges in an array field in PostgreSQL
If I have a tztrange field in a table, I can make sure that no two rows have values that overlap using an exclusion constraint. To make sure that no two appointments can be scheduled at the same time, ...
7
votes
2
answers
22k
views
Use array of composite type as function parameter and access it
I have created a type Books in Postgres which has 2 numeric fields and 2 varchar fields. I want to send an array of Books to a function to INSERT those values in a matching table.
This is my type:
...
34
votes
1
answer
75k
views
Query JSON array of obejcts against multiple values
I want to write a query against a jsonb type table-column in Postgres that, given an array of customers IDs, will find corresponding groups.
Given this example table:
CREATE TABLE grp(d jsonb NOT NULL)...
- 443
3
votes
1
answer
2k
views
Array of template type in PL/pgSQL function using %TYPE
I have a PostgreSQL database and a PL/pgSQL function that currently has arguments with copied types (for example IN arg_1 table_1.column_1%TYPE).
I want to update it so one of those arguments is ...
- 31
3
votes
1
answer
2k
views
Use specific array column in the where clause
Having a function that returns an array and in a query we use the function to return results such as:
{"TUNDUMA GOING (BORDER)","ARRIVAL DATE",09/01/2016,22,"AT CHECKPOINT"}
{"TUNDUMA RETURN (BORDER)"...
- 1,940
25
votes
2
answers
82k
views
How to get particular object from jsonb array in PostgreSQL?
I have a field called 'user' that holds a json array that roughly looks like this:
"user":
[{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}]
Now I want a query like:
select count from ...
- 251
16
votes
1
answer
38k
views
Postgres query to return JSON object keys as array
Is it possible to return a JSON object keys as an array of values in PostgreSQL?
In JavaScript, this would simply be Object.keys(obj), which returns an array of strings.
For example, if I have a ...
- 1,571
1
vote
2
answers
1k
views
Postgresql: Sortable index on array values
Suppose the following table:
CREATE TABLE foo (id serial, category int, bar int[]);
INSERT INTO foo VALUES (1, 1, '{4,10,20}');
INSERT INTO foo VALUES (2, 1, '{1,8,9}');
We want to query sorted ...
- 365
4
votes
1
answer
12k
views
Foreign key for array column [duplicate]
I have a table:
CREATE TABLE methods
(
method_id serial PRIMARY KEY,
method_name varchar(100)
);
I now want to create a table with the following columns:
CREATE TABLE experiments
(
...
- 141
10
votes
2
answers
14k
views
Array integer[]: how to get all distinct values in a table and count them?
I'm not so good with SQL (PostgreSQL). Here is what I want to do:
I have a table, fields:
id SERIAL
inet INET
ports integer[]
id | inet | ports
----+------------+------------
2 | 1.2.2.1 ...
- 203
4
votes
1
answer
16k
views
PostgreSql : flatten json array data
From my current query, I obtain this jsonb data :
values: "a1", ["b1", "b2"]
And I want to flatten it on one level only, like this :
values: "a1", "b1", "b2"
Here is a simplfied way to get data ...
- 93
7
votes
1
answer
4k
views
Group and count array elements using intarray
I am working on a Postgres 9.4 project with the intarray extension enabled. We have a table that looks like this:
items
-------------------------------------
id name tag_ids
----...
- 635
10
votes
1
answer
11k
views
How to get 0 as array_length() result when there are no elements
I have a query like this:
select id, array_length(users_who_like_ids,1) as ct
from queryables
order by 2 desc;
But empty arrays (with no elements) sort on top. I'd rather have array_length() return 0 ...
- 635
6
votes
1
answer
4k
views
Order result by count of common array elements
Using Postgres 9.4, I'm interested in having an array of integers like user_ids_who_like and provide an array of users (like user_ids_i_am_following) to sort that intersection.
Something like:
...
- 635