PostgreSQL CASE ... END with multiple conditions

Question

Here is an extract of my table:

  gid    |    datepose    |    pvc
---------+----------------+------------
 1       |  1961          | 01
 2       |  1949          |
 3       |  1990          | 02
 1       |  1981          |
 1       |                | 03
 1       |                |

I want to fill the PVC column using a SELECT CASE as bellow:

SELECT

 gid,

 CASE
  WHEN (pvc IS NULL OR pvc = '') AND datpose < 1980) THEN '01'
  WHEN (pvc IS NULL OR pvc = '') AND datpose >= 1980) THEN '02'
  WHEN (pvc IS NULL OR pvc = '') AND (datpose IS NULL OR datpose = 0) THEN '03'
 END AS pvc

FROM my_table ;

The result is the same content as source table, nothing has happened and I get no error message in pg_log files. It might be a syntax error, or a problem with using multiple conditions within WHEN clauses?

Thanks for help and advice!

Can you display a NULL value where it is null (so we can see the difference between NULL and empty string)? As in, a string containing NULL for example — Paco
– Paco, Commented Jan 6, 2015 at 14:03
You also have an issue with brackets. Why is there a closing bracket after 1980 ? (in both places) — Paco
– Paco, Commented Jan 6, 2015 at 14:05

Soni Harriz · Accepted Answer · 2015-01-06 14:24:15Z

324

This kind of code perhaps should work for You

SELECT
 *,
 CASE
  WHEN (pvc IS NULL OR pvc = '') AND (datepose < 1980) THEN '01'
  WHEN (pvc IS NULL OR pvc = '') AND (datepose >= 1980) THEN '02'
  WHEN (pvc IS NULL OR pvc = '') AND (datepose IS NULL OR datepose = 0) THEN '03'
  ELSE '00'
 END AS modifiedpvc
FROM my_table;


 gid | datepose | pvc | modifiedpvc 
-----+----------+-----+-------------
   1 |     1961 | 01  | 00
   2 |     1949 |     | 01
   3 |     1990 | 02  | 00
   1 |     1981 |     | 02
   1 |          | 03  | 00
   1 |          |     | 03
(6 rows)

answered Jan 6, 2015 at 14:24

Soni Harriz

3,6481 gold badge15 silver badges8 bronze badges

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

3 Comments

wiltomap Over a year ago

Nearly perfect ;-) ! I would just replace ELSE '00' with ELSE pvc so that I can keep the existing values in pvc column, otherwise they are scratched with '00' (case pvc IS NOT NULL). Thanks a lot!

Zon Over a year ago

Can I skip ELSE?

Klaws Over a year ago

The ELSE is optional. Without an ELSE, the expression will return NULL when none of ten WHEN clauses match.

Francislei Souza · Accepted Answer · 2022-09-06 18:12:57Z

-1

You must CAST before the value passed as parameter and insert after the parameter the VARCHAR(50) Example: WHEN (pvc IS NULL OR pvc = '') AND (datepose < 1980) THEN cast ('01' as varchar(50))

answered Sep 6, 2022 at 18:12

Francislei Souza

1

Collectives™ on Stack Overflow

PostgreSQL CASE ... END with multiple conditions

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related