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

It shows me that error when i try to insert, i know coz timestamp doesnt allow '' — TheDragonWarrior, Jan 10 '17 at 07:03
Please show us data from the `dojo` column. You're currently trying to insert character data as a timestamp. — Tim Biegeleisen, Jan 10 '17 at 07:05
im inserting timestamp into dojo unless its null i want it to be empty but not using now() — TheDragonWarrior, Jan 10 '17 at 07:11
`''` is not a valid timestamp value. And if you want the value to be "empty" the only choice you have is `null` — , Jan 10 '17 at 07:21
but bro it has a not null constraint and i cannot enter any other date — TheDragonWarrior, Jan 10 '17 at 07:55
Wanting them to be empty contradicts the fact that you have not null constraint on that field. It just won't work that way. Remove the not null constraint, it is the only way (and don't use `coalesce` ofc). — pozs, Jan 10 '17 at 09:22

score 3 · Answer 1 · answered Jan 10 '17 at 07:21

3

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.

answered Jan 10 '17 at 07:21

Tim Biegeleisen

502,043
27
286
360

1

The employee from select query is in other database and insertion is into other database,both tables have got same name and structure – TheDragonWarrior Jan 10 '17 at 07:26
@DragonWarrior Thanks for the clarification. – Tim Biegeleisen Jan 10 '17 at 07:27
Newbie bro!!! function to_timestamp(date, unknown) does not exist Line: 1 – TheDragonWarrior Jan 10 '17 at 07:36
im getting this error when im trying to run ur query – TheDragonWarrior Jan 10 '17 at 07:37
What version of Postgres are you using? – Tim Biegeleisen Jan 10 '17 at 07:42
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 – TheDragonWarrior Jan 10 '17 at 07:44

score 0 · Answer 2 · answered Jan 10 '17 at 07:04

0

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;

answered Jan 10 '17 at 07:04

smn_onrocks

1,282
1
18
33

Inserting `dd-mm-yyyy` as a timestamp doesn't seem right to me. – Tim Biegeleisen Jan 10 '17 at 07:05
I dont want to insert values if it is null but want column to b empty – TheDragonWarrior Jan 10 '17 at 07:06
@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 Jan 10 '17 at 07:07
@DragonWarrior you can't keep the column blank incase of time stamp with not null constraint – smn_onrocks Jan 10 '17 at 07:09
what could be an alternative to that – TheDragonWarrior Jan 10 '17 at 07:13
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. – smn_onrocks Jan 10 '17 at 07:15
Im dealing with sensitive data!!! i cant manipulate it using now(),that is the reason i want it to be empty or something else. – TheDragonWarrior Jan 10 '17 at 07:18
so then give some default past date like I mentioned and make an exception in your procedure whenever that date appears just ignore it. – smn_onrocks Jan 10 '17 at 07:19
@DragonWarrior: `null` **is** "empty" for a timestamp column – Jan 10 '17 at 07:22

score -1 · Answer 3 · answered Dec 04 '20 at 04:31

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

https://en.wikipedia.org/wiki/Null_(SQL)

how to coalesce timestamp with not null constraint postgres

3 Answers3