Skip to main content
AI Assist is now on Stack Overflow. Start a chat to get instant answers from across the network. Sign up to save and share your chats.
edited tags
Link
GMB
  • 223.8k
  • 25
  • 103
  • 151
Source Link
moo
  • 2.3k
  • 2
  • 23
  • 37

SELECT statement using columns based on a subquery (postgres)

What is the most elegant (i.e. readable and easy to maintain) way to select columns based on the results of a subquery (using Postgres 9.3)?

Here's some example data:

create temporary table if not exists food ( id serial primary key, pet_id varchar, steak integer, chicken integer, fish integer, veg integer ); insert into food (pet_id, steak, chicken, fish, veg) values (1, 100, 10, 1, 78), (2, 140, 100, 1100, 7), (3, 10, 10, 10, 7); create temporary table if not exists foodtypes ( id serial primary key, name varchar, meat boolean ); insert into foodtypes (name, meat) values ('steak', true), ('chicken', true), ('fish', true), ('veg', false); 

I would like to be able to generate some output for all the pets in the database, with all meaty columns. At the moment, I'm doing that using:

select id, pet_id, steak, chicken, fish from food;

 id pet_id steak chicken fish 0 1 1 100 10 1 1 2 2 140 100 1100 2 3 3 10 10 10 3 7 1 100 10 1 4 8 2 140 100 1100 5 9 3 10 10 10 

However, what happens if another pet store operator adds a new meaty food type to the foodtypes table? It won't be included in the results

Ideally, for maintainability and consistency, I would like to be able to choose my columns based on a subquery - something like:

select name, (select distinct name from foodtypes where meat = true) from food;

This doesn't work. I've read that a lateral join may be useful but I am not sure how it would help achieve this.