1

After I get excellent results with converting data "to_timestamp" and "to_number" from VB.NET I am wondering if PostgreSQL have possibility to query table indexes by array of integers from .NET?

Say, I have array filled with (1, 3, 5, 6, 9). Is here any possibility that PostgreSQL return rows with data from those indexes to "odbc.reader"?
That would be much faster than looping and querying 5 times like I do now.

Something like this:

SELECT myindexes, myname, myadress from mytable WHERE myindexes IS IN ARRAY

If this is possible how a simple query should look like?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Wine Too
  • 4,515
  • 22
  • 83
  • 137

1 Answers1

2

That's possible.

ANY

SELECT myindex, myname, myadress
FROM   mytable
WHERE  myindex = ANY ($my_array)

Example with integer-array:

...
WHERE  myindex = ANY ('{1,3,5,6,9}'::int[])

Details about ANY in the manual.

IN

There is also the SQL IN() expression for the same purpose.
PostgreSQl in its current implementation transforms that to = ANY (array) internally prior to execution, so it's conceivably a bit slower.

Examples for joining to a long list (as per comment):

JOIN to VALUES expression

WITH x(myindex) AS (
    VALUES
    (1),(3),(5),(6),(9)
    )
SELECT myindex, myname, myadress
FROM   mytable
JOIN   x USING (myindex)

I am using a CTE in the example (which is optional, could be a sub-query as well). You need PostgreSQL 8.4 of later for that.
The manual about VALUES.

JOIN to unnested array

Or you could unnest() an array and JOIN to it:

SELECT myindex, myname, myadress
FROM   mytable
JOIN  (SELECT unnest('{1,3,5,6,9}'::int[]) AS myindex) x USING (myindex)

Each of these methods is far superior in performance to running a separate query per value.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you very much. This probably mean that I should "write" my array to string with comas to be able to put it in a query. But I didn't find in the manual how long that array could be in bytes or elements? – Wine Too Jan 05 '13 at 14:49
  • @user973238: There is practically no limit to the length of the array. – Erwin Brandstetter Jan 05 '13 at 15:02
  • That mean no limit to the length of query string. Regarding your experience is this function acceptable fast if array have few thousand elements? – Wine Too Jan 05 '13 at 15:05
  • @user973238: It's pretty fast. Whether performance is acceptable is for you to decide. For huge lists if may be faster to create a temporary table and `JOIN` to it, or use a `VALUES` expression for the same purpose. Test performance with `EXPLAIN ANALYZE`. I'll add a example queries to my answer. – Erwin Brandstetter Jan 05 '13 at 15:09
  • I am a bit limited with that because I uses postgre through visual basic net and ODBC and where I should everything convert to string first, but I'll try all what I can to test those, new options for me. – Wine Too Jan 05 '13 at 15:40
  • ["Postgres" or "PostgreSQL", no such thing as "Postgre".](http://wiki.postgresql.org/wiki/Identity_Guidelines) – Erwin Brandstetter Jan 05 '13 at 16:22