0

I've been searching for this and it seems like it should be something simple, but apparently not so much. I want to return a resultSet within PostgreSQL 9.4.x using an array parameter so:

| id | count |
--------------
| 1  |   22  |
--------------
| 2  |   14  |
--------------
| 14 |   3   |

where I'm submitting a parameter of {'1','2','14'}.

Using something (clearly not) like:

SELECT id, count(a.*) 
FROM tablename a
WHERE a.id::int IN array('{1,2,14}'::int);

I want to test it first of course, and then write it as a storedProc (function) to make this simple.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
James Van Leuven
  • 31
  • 3
  • 9

2 Answers2

0

Forget it, here is the answer:

SELECT a.id,
       COUNT(a.id)
FROM tableName a
WHERE a.id IN
    (SELECT b.id 
     FROM tableName b
     WHERE b.id = ANY('{1,2,14}'::int[])
    )
GROUP BY a.id;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
James Van Leuven
  • 31
  • 3
  • 9
0

You can simplify to:

SELECT id, count(*) AS ct
FROM   tbl
WHERE  id = ANY('{1,2,14}'::int[])
GROUP  BY 1;

More:

To include IDs from the input array that are not found I suggest unnest() followed by a LEFT JOIN:

SELECT id, count(t.id) AS ct
FROM   unnest('{1,2,14}'::int[]) id
LEFT   JOIN tbl t USING (id)
GROUP  BY 1;

Related:

If there can be NULL values in the array parameter as well as in the id column (which would be an odd design), you'd need (slower!) NULL-safe comparison:

SELECT id, count(t.id) AS ct
FROM   unnest('{1,2,14}'::int[]) id
LEFT   JOIN tbl t ON t.id IS NOT DISTINCT FROM id.id
GROUP  BY 1;
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • thnx but it seems I'm having an odd array post from ``$.ajax();`` – James Van Leuven Dec 02 '15 at 09:41
  • interesting, i want it to return 0 (zero) for any id's that are in the array which aren't found in the table... any idea's on that? – James Van Leuven Dec 02 '15 at 11:05
  • hmmm. so it seems that adding the array to the node-postgres query as ``select * from storedProc_func(Array[1,2,14]::int[]);`` only returns whichever return a result but what I really want is to return a result for everything, even if it's null or zero, such as ``select * from storedProc_func('{"1","2","14"}');`` – James Van Leuven Dec 02 '15 at 11:11
  • @JamesVanLeuven: Consider the added solutions. – Erwin Brandstetter Dec 02 '15 at 11:19
  • so yup - that worked... interesting that there is a left join of itself... can you explain to me why self joining (more or less) is the method which worked for this? I have to admit it's a bit perplexing... – James Van Leuven Dec 02 '15 at 19:07
  • so how do i add other filter criteria? say another integer for `` SELECT id, count(t.id) AS ct FROM unnest('{1,2,14}'::int[]) id LEFT JOIN tbl t ON t.id IS NOT DISTINCT FROM id.id WHERE location::int = '1'::int GROUP BY 1; `` – James Van Leuven Dec 02 '15 at 21:20
  • @JamesVanLeuven: There are simple solutions, depending on what you need exactly. Start a new question for a new question. Include the table definition, sample data, the desired result and clearly defined requirements. Comments are not the place. You can always link to this one for context. – Erwin Brandstetter Dec 02 '15 at 21:42
  • Ok I've run your examples a few times and I notice that both versions omit the ```id``` if there is a null value within the count result - I still need to return that id number whether there is a count of 0 or null or an actual value. – James Van Leuven Dec 07 '15 at 11:23