All Questions
Tagged with array postgresql-9.3
8 questions
2
votes
1
answer
10k
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
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
13
votes
2
answers
30k
views
Efficient merging (removing duplicates) of arrays
I have two tables, left2 and right2. Both tables will be large (1-10M rows).
CREATE TABLE left2(id INTEGER, t1 INTEGER, d INTEGER);
ALTER TABLE left2 ADD PRIMARY KEY (id,t1);
CREATE TABLE right2( t1 ...
- 1,022
34
votes
1
answer
40k
views
Why does PostgreSQL allow querying for array[0] even though it uses 1-based arrays?
I have been playing with arrays in one of my PostgreSQL databases.
I have created a table with a geometry array with at least one element:
CREATE TABLE test_arrays (
polygons geometry(Polygon,...
- 12.1k
4
votes
2
answers
501
views
Use PostgreSQL builtin operator <@ after including extension intarray
I added the intarray extension to my PostgreSQL database in order to make use of the - operator, but I still want to use the builtin <@ operator, not intarray's <@ operator. Is there a way to do ...
- 200
1
vote
1
answer
1k
views
Easy way to replace an YAML array of IDs with strings from a joined table
I have a database full of camp data. Each camp has many campers and has many camp_weeks.
In the table of campers, there is a YAML encoded array of IDs for the camper's camp weeks. The project ...
- 147
8
votes
1
answer
5k
views
Fastest way to get the length of the intersection of two text arrays in PostgreSQL
This is what I am currently using:
CREATE FUNCTION array_intersect(a1 anyarray, a2 anyarray)
RETURNS anyarray AS
$$
SELECT ARRAY(
SELECT unnest($1)
INTERSECT SELECT unnest($2)
ORDER BY ...
- 8,653
146
votes
8
answers
476k
views
How to turn JSON array into Postgres array?
I have a column data of type json that holds JSON documents like this:
{
"name": "foo",
"tags": ["foo", "bar"]
}
I would like to turn the nested tags array into a concatenated string ('foo, ...
- 1,653