PostgreSQL – SELECT with IF Statement Error ‘Column Does Not Exist’

postgresqlpostgresql-11select

Database, application.
PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg19.04+1),
Dbeaver 6.1.1.

What I need?
Return right values.

What I do.
Here is part of code.

select
if(available_count < 5, "neni skladem", "na sklade") as availability
from
...

Return this.

SQL Error [42703]: ERROR: column "neni skladem" does not exist

Where is the catch?

Thanks.

Best Answer

You have two errors in your statement:

There is no IF in SQL, you need to use a CASE expression
String constants are defined using single quotes in SQL ('), double quotes are for identifiers

select case 
          when available_count < 5 then 'neni skladem'
          else 'na sklade'
       end as availability
from ...

Related Solutions

Postgresql – Error “column does not exist” in a SELECT with JOIN and GROUP BY query

You probably want something like this:

SELECT h_v_charges.*, 
       last_v.last_version
FROM hist_versions_charges h_v_charges 
  JOIN (select proj_charge_id, 
               max(version) as last_version
        from hist_versions_charges 
        where proj_sous_projet_id = 2  
        group by proj_charge_id
  ) last_v  
  ON h_v_charges.version = last_v.last_version
 AND h_v_charges.proj_charge_id = last_v.proj_charge_id 
ORDER BY h_v_charges.proj_charge_id ASC;

A possibly (because no join is required) faster solution would be:

select *
from (
   select hvc.*, 
          row_number() over (partition by proj_charge_id order by version desc) as rn
   from hist_versions_charges as hvc
   where proj_sous_projet_id = 2  
) as hv
where rn = 1
order by hv.proj_charge_id ASC;

As Colin has pointed out, this can also be written as:

with hv as (
  select hvc.*, 
         row_number() over (partition by proj_charge_id order by version desc) as rn
  from hist_versions_charges as hvc
  where proj_sous_projet_id = 2  
) 
select *
from hv
where rn = 1
order by hv.proj_charge_id ASC;

PostgreSQL – Reference Column Alias in Same SELECT List

It's inconvenient sometimes, but it's SQL standard behavior, and it prevents ambiguities. You cannot reference column aliases in the same SELECT list.

There are shorter syntax options:

SELECT s.*, s.percent_water * 100 AS percent_water_100
FROM  (
   SELECT id, wet_weight / NULLIF(dry_weight - 1, 0) AS percent_water
   FROM   samples
   ) s;

And you can use a LATERAL join in Postgres 9.3+:

SELECT s.id, s1.percent_water
     , s1.percent_water * 100 AS percent_water_100
FROM   samples s
     , LATERAL (SELECT s.wet_weight / NULLIF(s.dry_weight - 1, 0) AS percent_water) s1;

I added NULLIF() to defend against division-by-zero errors.

Best Answer

Related Solutions

Postgresql – Error “column does not exist” in a SELECT with JOIN and GROUP BY query

PostgreSQL – Reference Column Alias in Same SELECT List

Related Question