SQL Server to PostgreSQL equivalent

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:-

  1. CONVERT(bit,CASE WHEN column_a & 1 > 0 THEN 1 ELSE 0 END) as ABC

  2. CONVERT(tinyint,CASE WHEN column_a IS NOT NULL THEN 0 END) as ABC

  3. CONVERT(smallint,CASE WHEN col_year = (SELECT year(col_date) FROM tab A WHERE A.a = 192) THEN 1 ELSE 0 END) as ABC

  4. TO_CHAR(TO_CHAR(YEAR(Date))||'Y') as ABC

  5. CASE WHEN COL_A = 1 THEN TO_CHAR(YEAR(Date))||'Y'

  6. WHEN COL_A = 4 THEN TO_CHAR (YEAR(Date) ) || 'Q' || TO_CHAR(Month(Date)/3 ) ELSE NULL END as ABC

Please help..

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.

Method 1

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 date or timestamp value) and the "format mask".

To extract the year as a string from a date (or timestamp) value and append the letter Y use:

to_char("date", 'YYYY"Y"')

e.g. to_char(current_date, 'YYYY"Y"') returns '2021Y'. Alternatively use `to_char("date", ‘YYYY’)||’Y’

The format mask to get the quarter is 'Q' and 'MM' for the month. So to get a string with a date’s quarter use to_char("date", 'Q').

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 true or false (slightly different than you original expression that returned null if the column is null). If you really prefer numbers instead of booleans use 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 booleans or integers to be used as a flag, then maybe you want to cast that to a smallint: 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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply