create a cte of manually entered values

January 29, 2022

I’d like to create a lookup table within my query using a cte e.g.

food, category
apple, fruit
carrot, vegetable
grape, fruit

How can I just type values directly in this way? Tried:

with
    
lookup_table as (
  select 
    'apple', 'carrot', 'grape' as fruits,
    'fruit', 'vegetable', 'fruit' as category
)

select * from lookup_table;

Gives:

"?column?"  "?column?"  fruits  "?column?"  "?column?"  category
apple   carrot  grape   fruit   vegetable   fruit

How can I create a manual lookup directly in this way with 2 fields, fruit and category, as well as the 3 corresponding values for each?

>Solution :

One elegant way in Postgres is to use a VALUES clause.

WITH
lookup
AS
(
SELECT *
       FROM (VALUES ('apple',
                     'fruit'),
                    ...
                    ('grape',
                     'fruit')) AS v
                                  (fruit,
                                   category)
)
SELECT *
       FROM lookup;