All we need is an easy explanation of the problem, so here it is.
I need some help in converting or equivalent of below in postgreSQL..
From MSSQL code to postgreSQL:-
CONVERT(bit,CASE WHEN column_a & 1 > 0 THEN 1 ELSE 0 END) as ABC
CONVERT(tinyint,CASE WHEN column_a IS NOT NULL THEN 0 END) as ABC
CONVERT(smallint,CASE WHEN col_year = (SELECT year(col_date) FROM tab A WHERE A.a = 192) THEN 1 ELSE 0 END) as ABC
TO_CHAR(TO_CHAR(YEAR(Date))||'Y') as ABC
CASE WHEN COL_A = 1 THEN TO_CHAR(YEAR(Date))||'Y'
WHEN COL_A = 4 THEN TO_CHAR (YEAR(Date) ) || 'Q' || TO_CHAR(Month(Date)/3 ) ELSE NULL END as ABC
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
I have no idea what the
convert() parts are supposed to do, but I am trying to guess(!) what the intention
to_char() (that can’t be valid SQL Server code) is trying to achieve.
As documented in the manual
to_char() needs two arguments: the value to be formatted (e.g. a
timestamp value) and the "format mask".
To extract the year as a string from a
timestamp) value and append the letter
to_char(current_date, 'YYYY"Y"') returns
'2021Y'. Alternatively use `to_char("date", ‘YYYY’)||’Y’
The format mask to get the quarter is
'MM' for the month. So to get a string with a date’s quarter use
To extract the month as a number, use the extract() function, e.g.
extract(month from some_date) / 3
To extract the year use
extract(year from some_date)
CONVERT(tinyint,CASE WHEN column_a IS NOT NULL THEN 0 END) looks like you just want a flag if the column is null. This can be e.g. written as
column_a is null which will return a
boolean value with
false (slightly different than you original expression that returned
null if the column is
null). If you really prefer numbers instead of
case when column_a is not null then 0 end. I don’t exactly know what that
convert() function is doing, but you really don’t like
integers to be used as a flag, then maybe you want to cast that to a
cast(case when column_a is not null then 0 end as smallint)
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂