Skip to main content
Question

Infinity date


Forum|alt.badge.img

How can I insert a "infinity" value into a date column in PostgreSQL?

16 replies

itay
Supporter
Forum|alt.badge.img+17
  • Supporter
  • September 7, 2016

What do you mean by infinity value?


david_r
Celebrity
  • September 7, 2016

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'

0684Q00000ArKI4QAN.png


david_r
Celebrity
  • September 7, 2016
itay wrote:

What do you mean by infinity value?

https://www.postgresql.org/docs/9.0/static/datatype-datetime.html

 

See table 8-13, it's a supported datetime value in Postgresql.

itay
Supporter
Forum|alt.badge.img+17
  • Supporter
  • September 7, 2016
david_r wrote:
https://www.postgresql.org/docs/9.0/static/datatype-datetime.html

 

See table 8-13, it's a supported datetime value in Postgresql.
to infinity and ....

 

 


Forum|alt.badge.img
  • Author
  • September 7, 2016

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...


itay
Supporter
Forum|alt.badge.img+17
  • Supporter
  • September 7, 2016
aleho wrote:

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.

 


Forum|alt.badge.img
  • Author
  • September 7, 2016
itay wrote:
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?

david_r
Celebrity
  • September 7, 2016
aleho wrote:

 

and why are the default values ignored?
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.

 

 


Forum|alt.badge.img
  • Author
  • September 7, 2016
david_r wrote:
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.

 

 

Originally I didn't insert any value into the date column. I think then the default value should be inserted then, but it isn't!

david_r
Celebrity
  • September 7, 2016
david_r wrote:
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.

 

 

Unfortunately it isn't always that easy. If the attribute exists (even if the value is empty) when it hits the Writer, that will be enough to prevent the default value from kicking in.

 

 

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

 

 


mark2atsafe
Safer
Forum|alt.badge.img+45
  • Safer
  • September 7, 2016
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)

 


mark2atsafe
Safer
Forum|alt.badge.img+45
  • Safer
  • September 7, 2016

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.


Forum|alt.badge.img
  • Author
  • September 8, 2016
mark2atsafe wrote:

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.

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?

david_r
Celebrity
  • September 8, 2016
aleho wrote:
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?

Forum|alt.badge.img
  • Author
  • September 8, 2016
david_r wrote:
Did you try removing the attribute with e.g. an AttributeRemover?
The attribute is on the writer and there is no equal attribute on the reader. So no value is associated to it. As far as i know I cant remove an attribute with the AttributeRemover from the writer

mark2atsafe
Safer
Forum|alt.badge.img+45
  • Safer
  • September 8, 2016
aleho wrote:
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?
Sadly I don't think it will work right now. We should use the default when the value is missing, but currently we write it as null instead. That's the first of the two updates mentioned above.

 

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.

 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings