insert into employee(eid,dojo) SELECT
14,coalesce(to_char(dojo,'dd-mm-yyyy'),'')
from employee;
I have to insert into table by selecting it from table,my column dojo has not null constraint and timestamp doesn't allow '' to insert please provide an alternate for this if timestamp is null from select query
Your current query has severals problems, two of which I think my answer can resolve. First, you are trying to insert an empty string '' to handle NULL values in the dojo column. This won't work, because empty string is not a valid timestamp. As others have pointed out, one solution would be to use current_timestamp as a placeholder.
Another problem you have is that you are incorrectly using to_char to format your timestamp data. The output of to_char is a string, and the way you are using it would cause Postgres to reject it. Instead, you should be using to_timestamp(), which can parse a string and return a timestamp. Something like the following is what I believe you intend to do:
insert into employee (eid, dojo)
select 14, coalesce(to_timestamp(dojo, 'DD/MM/YYYY HH:MI:SS PM'), current_timestamp)
from employee;
This assumes that your timestamp data is formatted as follows:
DD/MM/YYYY HH:MI:SS PM (e.g. 19/2/1995 12:00:00 PM)
It also is not clear to me why you are inserting back into the employee table which has non usable data, rather than inserting into a new table. If you choose to reuse employee you might want to scrub away the bad data later.
you can use some default date value like 1st jan 1900 or now()
your query should be like
insert into employee(eid,dojo) SELECT
14,coalesce(to_char(dojo,'dd-mm-yyyy'),now())
from employee;
dd-mm-yyyy as a timestamp doesn't seem right to me.
There is no such thing as a non-null yet blank timestamp. NULL = blank.
There is literally nothing you can do but store a valid timestamp or a null. Since you have a non-null constraint your only option is to pick a default timestamp that you consider "blank".
Using a hard coded date to indicate a blank value is a terrible terrible terrible idea btw. If it is blank, remove the not null constraint, make it null and move on.
I am not trying to be condescending but I do not think you understand nulls. See here
dojocolumn. You're currently trying to insert character data as a timestamp.