SELECT statement using columns based on a subquery (postgres)

Question

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.

Kurt Kline · Accepted Answer · 2020-05-17 17:04:30Z

I think the most proper way of handling this is to change the format of the food table

create table food ( id serial primary key, pet_id varchar, foodtype varchar, amount int );

This way, you would be able to do a regular join on food.foodtype and foodtypes.name columns. And this would future proof your queries because any new food types would automatically be handled.

Recommended Table Set-up

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

Recommended Query

select * from food f join foodtypes ft on f.foodtype = ft.name where ft.meat = true

You can play around with an example of the recommended set-up here.

These tables are just minimal examples, I can't change the real schema in production. Needless to say, the actual problem doesn't involve a pet shop
I see, I'll see if there's something else I can come up with.
Thanks, this is for a report from quite an extensive schema. I want to be able to store and reuse columns in a temporary table. I made up the example to try to demonstrate the simplest possible case. There remains, of course, a chance that I'm asking the wrong question

Collectives™ on Stack Overflow

SELECT statement using columns based on a subquery (postgres)

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related