Postgres, return default row if id doesn't exist in table

Question

I'm selecting rows from a table where the id is in an array. This works, but I also want to return default values for rows that do not exist in the table.

I currently have

  SELECT
    id,
    column1
  FROM
    table_name
  WHERE
    id = ANY(ids_array_variable)

but, if some of the ids in the array do not exist in the table, then my result is short a few rows. I need it to still return a default record of {id, default_value} so that the result always has the same number of entries as the ids_array_variable

user330315 · Accepted Answer · 2020-08-28 21:55:23Z

5

Use a left join:

select a.id, 
       coalesce(t.column1, 'some default') as column1
from unnest(ids_array_variable) as a(id)
  left join table_name t on t.id = a.id;

answered Aug 28, 2020 at 21:55

user330315

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

Comments

madjardi · Accepted Answer · 2023-01-10 14:00:47Z

0

if table serviceconfig have n’t key - ‘my_key’ - use default value

thank @a-horse-with-no-name

SELECT COALESCE(KV.value, def.val) as value
FROM (
    select def.val from unnest(ARRAY ['default']) as def(val)) as def
LEFT JOIN
     (SELECT *
      FROM serviceconfig
      WHERE key = 'my_key') KV on true;

answered Jan 10, 2023 at 14:00

madjardi

6,0192 gold badges39 silver badges40 bronze badges

Collectives™ on Stack Overflow

Postgres, return default row if id doesn't exist in table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related