If Statement Postgres

Question

I can't to solve my problem anymore, please , I am looking to make a selection but only if the date is lower than 11/05/1990 must take 01/01/2000 and make the selection

select (date, champ1 , champ2, champ3, 
CASE
when (a.date <'1990-05-11') then (a.date ='2000-01-01') 
else a.date 
END as a.date 
from table a

Don't use localized strings. Use a date-typed field, date-typed parameters or the ISO8601 format, ie YYYY-MM-DD. BTW what's the type of a.date ? YOu won't be able to perform range queries at all if it contains localized strings — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 24, 2020 at 9:28
Show us some sample table data and the expected result. (As formatted text, not images.) — jarlh
– jarlh, Commented Jan 24, 2020 at 9:29
@ Panagiotis Kanavos I just want to make the selection on the date field of the table at, I don't want to add another field, should I leave the word CASE? , I just want to do the research if the date is lower than 11/05/1990 it takes 01/01/2000 — JAVAINMYDNA
– JAVAINMYDNA, Commented Jan 24, 2020 at 9:31
The reason localized strings always fail is that strings are always sorted alphabetically. This means that '11/05/1990' comes after '01/01/2000' - never mind the ambiguity between eg November 5th vs May 11th. You can only perform range queries if all strings follow a sortable format like YYYY-MM-DD. It's far faster, easier and safer though to use date — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 24, 2020 at 9:32
Your logic does not seem to make much sense. Sample data would greatly help your question. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 24, 2020 at 9:33

score 2 · Accepted Answer · 2020-01-24 09:56:24Z

2

You can't use CASE like that. If you want to replace certain values dynamically with something else, you need to return that value in the THEN part, not a comparison (which returns a boolean).

The following assumes that a.date is defined with the data type date

select date, champ1, champ2, champ3,
       case 
           when a.date < DATE '1990-05-11'  
               then DATE '2000-01-01'
           else a.date
       end as "date"
from table a

edited Jan 24, 2020 at 9:56

answered Jan 24, 2020 at 9:39

user330315

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

5 Comments

JAVAINMYDNA Over a year ago

Thanq All , just i have erreur ERROR: syntax error at or near ";" in ligne FROM

Panagiotis Kanavos Over a year ago

@JAVAINMYDNA not with this answer's query. There's no ; here. What did you actually try?

JAVAINMYDNA Over a year ago

@Panagiotis Kanavos ,no no , that near of inner join ,because there is jointure :)

user330315 Over a year ago

@JAVAINMYDNA: there is no join in my answer.

Panagiotis Kanavos Over a year ago

@JAVAINMYDNA not in the query you posted. There's no JOIN in the question. People can't guess what the problem is with code you haven't even posted.

Collectives™ on Stack Overflow

If Statement Postgres

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related