how to coalesce timestamp with not null constraint postgres

Question

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

Solution

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.

Comments

[Tim Biegeleisen]

Inserting dd-mm-yyyy as a timestamp doesn't seem right to me.

[TheDragonWarrior]

I dont want to insert values if it is null but want column to b empty

[smn_onrocks]

@TimBiegeleisen I was about to write him but then think if I am not wrong from the code style he is from oracle background so I let it as it is.

[smn_onrocks]

@DragonWarrior you can't keep the column blank incase of time stamp with not null constraint

[TheDragonWarrior]

what could be an alternative to that

[smn_onrocks]

You can do one thing if this is the requirement then my solution would be change the data type of the column from timestamp to text and keep the data at the time of insertion or representation cast it accordingly.

[TheDragonWarrior]

Im dealing with sensitive data!!! i cant manipulate it using now(),that is the reason i want it to be empty or something else.

[smn_onrocks]

so then give some default past date like I mentioned and make an exception in your procedure whenever that date appears just ignore it.

[a_horse_with_no_name]

@DragonWarrior: null is "empty" for a timestamp column

[TheDragonWarrior]

The employee from select query is in other database and insertion is into other database,both tables have got same name and structure

[Tim Biegeleisen]

@DragonWarrior Thanks for the clarification.

[TheDragonWarrior]

Newbie bro!!! function to_timestamp(date, unknown) does not exist Line: 1

[TheDragonWarrior]

im getting this error when im trying to run ur query

[Tim Biegeleisen]

What version of Postgres are you using?

[TheDragonWarrior]

PostgreSQL 9.3.13 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit