Menu
I want to select a date (my column is a timestamp type). But when in column is a NULL date, I want to return an empty string. How to do this? I wrote this:
SELECT
CASE WHEN to_char(last_post, 'MM-DD-YYYY HH24:MI:SS') IS NULL THEN ''
ELSE to_char(last_post, 'MM-DD-YYYY HH24:MI:SS') AS last_post END
to_char(last_post, 'MM-DD-YYYY HH24:MI:SS') AS last_post, content
FROM topic;
But it shows me some errors, dont really know why:
ERROR: syntax error at or near "as"
LINE 1: ...ELSE to_char(last_post, 'MM-DD-YYYY HH24:MI:SS') AS last_po...
^
999
The error message indicates that your "date" column (which apparently isn't actually a date column) contains the string constant 'NULL' rather than a real null value (because there is no problem casting a real null value to date value). But the correct fix for this problem is to store date values in DATE columns.
Postgresql date to string yyymmdd In Postgresql, dates can be converted in specific string formats like yyymmdd, where yyyy for a year, mm for a month, and dd for the date. For that conversion, we will use the to_char function to format the date as a string. Syntax: TO_CHAR(date_value, string_format);
nullif also used with the coalesce function to handle the null values. PostgreSQL nullif function returns a null value if provided expressions are equal. If two expressions provided are equal, then it provides a null value; as a result, otherwise, it will return the first expression as a result.
Postgres DATE data type Postgres uses the DATE data type for storing different dates in YYYY-MM-DD format. It uses 4 bytes for storing a date value in a column. You can design a Postgres table with a DATE column and use the keyword DEFAULT CURRENT_DATE to use the current system date as the default value in this column.
Using the COALESCE() function is the nicest approach, as it simply swaps in a substitute value in the case of a NULL. Readability is improved greatly too. :)
SELECT COALESCE(to_char(last_post, 'MM-DD-YYYY HH24:MI:SS'), '') AS last_post, content FROM topic;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
