Tagged Questions
9
votes
8answers
13k views
How can I pass an “array” of values to my stored procedure?
I want to be able to pass an "array" of values to my stored procedure, instead of calling "Add value" procedure serially.
Can anyone suggest a way to do it? am I missing something here?
Edit: I will ...
9
votes
5answers
439 views
Aggregate functions over arrays
I have a table like this:
+-----+----------------+
| ID | array300 |
+-----+----------------+
| 100 | {110,25,53,..} |
| 101 | {56,75,59,...} |
| 102 | {65,93,82,...} |
| 103 | {75,70,80,...} ...
8
votes
2answers
5k views
mapping a postgres array with hibernate
has anyone successfully mapped a numeric array in postgres to a numeric array in java via hibernate?
sql:
CREATE TABLE sal_emp (name text, pay_by_quarter integer[]);
INSERT INTO sal_emp VALUES ...
6
votes
2answers
196 views
Query on value of array elements (PostgreSQL)
The following query:
select unnest(Table2.L) as X, unnest(Table1.O)
from Table1, Table2
where Table1.code = Table2.code
order by X ;
produces the desired results. I would, however, like to ...
6
votes
1answer
1k views
Postgres Query of an Array using LIKE
I am querying a database in Postgres using psql. I have used the following query to search a field called tags that has an array of text as it's data type:
select count(*) from planet_osm_ways where ...
5
votes
3answers
581 views
Postgres UNIQUE CONSTRAINT for array
How to create a constraint on the uniqueness of all the values in the array like:
CREATE TABLE mytable
(
interface integer[2],
CONSTRAINT link_check UNIQUE (sort(interface))
)
my sort ...
5
votes
3answers
1k views
Postgresql aggregate array
Hi I have a two tables
Student
--------
Id Name
1 John
2 David
3 Will
Grade
---------
Student_id Mark
1 A
2 B
2 B+
3 C
3 A
Is it ...
5
votes
3answers
4k views
Postgres - How to check for an empty array
I'm using Postgres and I'm trying to write a query like this:
select count(*) from table where datasets = ARRAY[]
i.e. I want to know how many rows have an empty array for a certain column, but ...
5
votes
2answers
4k views
Equivalent to PostgreSQL array() / array_to_string() functions in Oracle 9i
I'm hoping to return a single row with a comma separated list of values from a query that returns multiple rows in Oracle, essentially flattening the returned rows into a single row.
In PostgreSQL ...
5
votes
1answer
681 views
Java and PostgreSQL Arrays
Question is quite simple:
is there any JDO/JPA/anything else "object-to-DB" mapping tools that can handle PG arrays? Multi-dimensional arrays? Mostly of strings and integers/longs.
Second one: can ...
4
votes
2answers
4k views
How to store array or multiple values in one column
Running Postgres 7.4 (Yeah we are in the midst of upgrading)
I need to store from 1 to 100 selected items into one field in a database. 98% of the time it's just going to be 1 item entered, and 2% of ...
4
votes
6answers
3k views
Remove array values in pgSQL
Is there a way to remove a value from an array in pgSQL? Or to be more precise, to pop the last value? Judging by this list the answer seems to be no. I can get the result I want with an additional ...
4
votes
2answers
5k views
Postgres integer arrays as parameters?
I understand that in Postgres pure, you can pass an integer array into a function but that this isn't supported in the .NET data provider Npgsql.
I currently have a DbCommand into which I load a ...
4
votes
1answer
2k views
unwrap postgresql array into rows
What is a fastest way to unwrap array into rows in PostgreSQL? i.e.
We have:
a
-
{1,2}
{2,3,4}
And we need:
b
-
1
2
2
3
4
I'm use:
select explode_array(a) as a from a_table;
where ...
4
votes
4answers
4k views
Getting the last element of a Postgres array, declaratively
How to obtain the last element of the array in Postgres?
I need to do it declaratively as I want to use it as a ORDER BY criteria. I wouldn't want to create a special PGSQL function for it, the less ...
