7
\$\begingroup\$

I haven't had much experience with postgresql (none) and I am wondering/hoping that there is a better way for me to do this query.

SELECT * FROM member_copy WHERE id = 17579 OR id = 17580 OR id = 17582 ect.

There are about 800 where clauses in total so this will take a while and I need to run it on a fairly regular basis.

\$\endgroup\$

closed as off-topic by Toby Speight, yuri, pacmaninbw, t3chb0t, esote Jun 17 at 2:42

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Lacks concrete context: Code Review requires concrete code from a project, with sufficient context for reviewers to understand how that code is used. Pseudocode, stub code, hypothetical code, obfuscated code, and generic best practices are outside the scope of this site." – Toby Speight, yuri, pacmaninbw, t3chb0t, esote
If this question can be reworded to fit the rules in the help center, please edit the question.

  • \$\begingroup\$ If there are really 800 where clauses I think you should work on your application \$\endgroup\$ – Thomas Junk Dec 6 '13 at 22:56
12
\$\begingroup\$

The way to write that query is:

SELECT * FROM member_copy WHERE id IN (17579, 17580, 17582);

However, the real question is, where did that list of ids come from? If the list of ids is the result of another database query, then you should be doing either a subselect or a join instead.

\$\endgroup\$
  • \$\begingroup\$ Worked perfectly. It was not from a database query, somebody actually went through and picked out every number. I do not envy them. \$\endgroup\$ – inimrepus Dec 6 '13 at 20:57
6
\$\begingroup\$

A test with EXPLAIN ANALYZE VERBOSE will show you that the form with id IN (...) in the answer of @200_success is transformed internally into:

SELECT * FROM member_copy WHERE id = ANY ('{17579, 17580, 17582}');

.. which therefore performs slightly faster to begin with (no conversion needed).

Also, the form in your question will effectively perform very similar.

With big lists, unnesting an array, followed by a JOIN will generally be faster:

SELECT m.*
FROM   unnest('{17579, 17580, 17582}'::int[]) id
JOIN   member_copy m USING (id);

Since the list is is the result of another database query, it will be fastest to combine both in one query with a JOIN.

More detailed explanation:

\$\endgroup\$
0
\$\begingroup\$

For a single id the following query worked for me:

SELECT * FROM member_copy WHERE id IN ('17579');
\$\endgroup\$
  • 1
    \$\begingroup\$ Could you explain how this answers the OP? \$\endgroup\$ – dfhwze Jun 14 at 9:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.