Question 202462 in PostgreSQL Question posted in
The official documentation can be found here.

POSTGRESQL: Return default record when specific record not found

Objective: I want a single query to return either the default value OR a configured value based on the input parameter.

Example: I have a default email I want to send out for all my sources. However, I want to allow users to override the default email by configuring an email for a specific source.

Sample Table:

CREATE TABLE test_content (
    content_context character varying(10),
    content_value   character varying(10),
    content_source  character varying(10)
);

INSERT INTO test_content (content_context, content_value, content_source) VALUES ('SMS', 'DEFAULT', NULL);
INSERT INTO test_content (content_context, content_value, content_source) VALUES ('EMAIL', 'DEFAULT', NULL);
INSERT INTO test_content (content_context, content_value, content_source) VALUES ('EMAIL', 'MY_VALUE', 'MY_SOURCE');

Using the above example: When the context is ‘EMAIL’ and the source is ‘MY_SOURCE’, I want to return ‘MY_VALUE’. If the context is ‘EMAIL’ but source is anything else, I want to return ‘DEFAULT’.

Source will always have a value.

This query is great when the source is not found but it returns 2 records when the source is present:

SELECT content_value 
  FROM test_content
 WHERE content_context = 'EMAIL'
   AND (content_source IS NULL or content_source = 'ABC');

Currently I am using 2 queries. First, I check if there is an email for the specific source. If the query does not return a value, I call the second to get the default email. I feel (and hope) that I should be able to do this in a single query.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The query below will always return 2 records. By adding an ORDER BY and LIMIT will return the correct record:

    SELECT content_value, content_source 
  FROM test_content
 WHERE content_context = 'EMAIL'
   AND (content_source IS NULL or content_source = 'MY_SOURCE')
 ORDER BY content_source
 LIMIT 1;

  2. 0

    HI you can order and use limit,
    let me show you an example:

    SELECT
  id AS id,
  email AS email,
  (CASE WHEN email LIKE '%first_email' THEN TRUE ELSE FALSE END) AS was_found
FROM
  users
WHERE
  email LIKE 'first_email'
  OR email LIKE 'second_email'
ORDER BY
  (email LIKE 'first_email') DESC
LIMIT 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search