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?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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?
@user973238: There is practically no limit to the length of the array.
That mean no limit to the length of query string. Regarding your experience is this function acceptable fast if array have few thousand elements?
@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.
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.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.