Conditionally select a column in postgres SQL

Question

say I have a table like this:

    table messages
id | show_message | message
 3 |   false      | "some message"
 4 |   true       |  "another message"

I want to only select the message column if show_message column is true, but I also want to select the show message column either way. Would a subquery be suitable in the scenario? I feel like I'm overthinking this. I'm using Postgres 9.5

Gareth · Accepted Answer · 2022-03-09 09:52:05Z

35

How about a simple case?

select id, show_message, (case when show_message then message end) as message
from t;

This will put a NULL value in the message column in the result set for messages that are not shown.

SQL queries return a fixed set of columns. So, this is the best you can do with a single query. If you really want a result set that sometimes has two columns and sometimes has three, then some sort of conditional logic or dynamic SQL would be needed.

edited Mar 9, 2022 at 9:52

Gareth

5,2636 gold badges47 silver badges73 bronze badges

answered Dec 28, 2016 at 16:55

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

DigitalDisaster Over a year ago

returning the column as null is totally fine because I can check the value of show_message before trying to do anything with it. Never used case but it looks like exactly what will work for me. I will try it out.

DigitalDisaster Over a year ago

This is perfect. Thank you for your insights.

Swaps Over a year ago

For anyone who wants to dig deeper - postgresql.org/docs/9.4/functions-conditional.html

Collectives™ on Stack Overflow

Conditionally select a column in postgres SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related