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

2

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.

edited May 17, 2020 at 17:04

answered May 17, 2020 at 16:54

Kurt Kline

2,1191 gold badge15 silver badges24 bronze badges

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

3 Comments

moo Over a year ago

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

Kurt Kline Over a year ago

I see, I'll see if there's something else I can come up with.

moo Over a year ago

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

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