How can I insert a "infinity" value into a date column in PostgreSQL?
Page 1 / 1
What do you mean by infinity value?
I'm not sure infinity is supported by FME out of the box, but you can write a temporary timestamp, e.g. '99991231' and then substitute it with infinity in the "SQL Statement to execute after translation:
update my_table set my_timestamp = 'infinity' where my_timestamp = date'99991231'
What do you mean by infinity value?
See table 8-13, it's a supported datetime value in Postgresql.
https://www.postgresql.org/docs/9.0/static/datatype-datetime.html
See table 8-13, it's a supported datetime value in Postgresql.
I think its not supported - I created an attribute with the value infinity and connected it to the postgres date column. Then I got the error: 'ERROR: date/time field value out of range: "0000-01-01"
Actually i am having a default value on this column, but somehow its not inserted, if I insert data via FME. I actually dont understand why - I think the default value would be the most elegant solution. So if somebody has an answer to this, it would be very helpful!
Anyhow the solution from @david_r could be a workaround...
I think its not supported - I created an attribute with the value infinity and connected it to the postgres date column. Then I got the error: 'ERROR: date/time field value out of range: "0000-01-01"
Actually i am having a default value on this column, but somehow its not inserted, if I insert data via FME. I actually dont understand why - I think the default value would be the most elegant solution. So if somebody has an answer to this, it would be very helpful!
Anyhow the solution from @david_r could be a workaround...
I suspect you will have to try @david_r workaround, since FME probably (cant test it myself) does not support the value.
and why are the default values ignored?
and why are the default values ignored?
You can try the AttributeRemover on the date field and see if that triggers the default value.
The default values will only be applied if you insert a record where no value has been defined in the INSERT statement. A NULL value is still a value, which will override the default.
You can try the AttributeRemover on the date field and see if that triggers the default value.
The default values will only be applied if you insert a record where no value has been defined in the INSERT statement. A NULL value is still a value, which will override the default.
You can try the AttributeRemover on the date field and see if that triggers the default value.
You may want to read up a bit on how FME handles NULL and empty values, as it can be a bit confusing and/or surprising sometimes: https://knowledge.safe.com/articles/21423/how-does-fme-handle-null-attribute-values.html
I'm just checking with our developers. The Readers and Writers manual states "Floating point types also have several special values such as Infinity, -Infinity, and NaN" - implying that infinity is a valid value for at least float fields (didn't work for me though)
Looks like this has caused a flurry of discussion among our database team. I think there are two outcomes that will occur (although I can't say when)...
- Attributes can be <null>, <missing>, or <empty>. <null> values will continue to be null (ie won't switch to default) but <missing> values will use the default when writing to postgres
- We'll add infinity (and -infinity) support for dates (and, I hope, floats)
Hope David's workaround will suffice for now. Let us know (via safe.com/support) if you need further assistance.
Looks like this has caused a flurry of discussion among our database team. I think there are two outcomes that will occur (although I can't say when)...
- Attributes can be <null>, <missing>, or <empty>. <null> values will continue to be null (ie won't switch to default) but <missing> values will use the default when writing to postgres
- We'll add infinity (and -infinity) support for dates (and, I hope, floats)
Hope David's workaround will suffice for now. Let us know (via safe.com/support) if you need further assistance.
One more question about the default values. I couldnt find a solution in which the default values are inserted - is there any way to have those default values inserted?
Thank you mark, I already tried davids workaround and its working fine! Anyhow - I am looking forward for the infinity support!
One more question about the default values. I couldnt find a solution in which the default values are inserted - is there any way to have those default values inserted?
Did you try removing the attribute with e.g. an AttributeRemover?
Thank you mark, I already tried davids workaround and its working fine! Anyhow - I am looking forward for the infinity support!
One more question about the default values. I couldnt find a solution in which the default values are inserted - is there any way to have those default values inserted?
Currently you would have to use a NullAttributeMapper to turn missing into a temporary value and then replace it afterwards, much the same way as David is suggesting.
Hopefully we'll get the fixes in soon so those workarounds aren't necessary.