Tagged Questions
11
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 ...
10
votes
8answers
14k 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
695 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
179 views
Why is PostgreSQL array access so much faster in C than in PL/pgSQL?
I have a table schema which includes an int array column, and a custom aggregate function which sums the array contents. In other words, given the following:
CREATE TABLE foo (stuff INT[]);
INSERT ...
7
votes
3answers
5k 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 ...
6
votes
3answers
2k 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 ...
6
votes
3answers
5k views
Convert PostgreSQL array to PHP array
I have trouble reading Postgresql arrays in PHP. I have tried explode(), but this breaks arrays containing commas in strings, and str_getcsv() but it's also no good as PostgreSQL doesn't quote the ...
6
votes
2answers
247 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
2k 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 ...
6
votes
1answer
727 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 ...
5
votes
3answers
661 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
7answers
4k 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 ...
5
votes
2answers
6k 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 ...
5
votes
2answers
1k views
Finding the position of a value in PostgreSQL arrays
How can I get the position of a value in PostgreSQL arrays? There's .index() method for Python and array_search() function for PHP, but I cannot find any such function for PostgreSQL. Should I write ...
5
votes
1answer
2k views
PostgresQL SQL: Converting results to array
The query below:
SELECT i_adgroup_id, i_category_id
FROM adgroupcategories_br
WHERE i_adgroup_id IN
(
SELECT i_adgroup_id
FROM adgroupusers_br
WHERE i_user_id ...
