Error when using SQL DELETE on archive view

1373
3
01-03-2017 02:47 PM
Highlighted
New Contributor III

I get the following error when I try to delete from an archive enabled feature class view (DELETE FROM myschema.mytable_evw WHERE objectid = 3;):

ERROR:  date/time field value out of range: "12.31.9999 23:59:59.999"

LINE 2: ...  WHERE objectid = old.objectid AND gdb_to_date = '12.31.999...  

HINT:  Perhaps you need a different "datestyle" setting.

QUERY:  UPDATE myschema.mytable SET gdb_to_date  = current_timestamp(3) AT TIME ZONE 'UTC'

    WHERE objectid = old.objectid AND gdb_to_date = '12.31.9999 23:59:59.999'

CONTEXT:  PL/pgSQL function myschema.nvv_update_11() line 15 at SQL statement

********** Error **********  

ERROR: date/time field value out of range: "12.31.9999 23:59:59.999"

SQL state: 22008

Hint: Perhaps you need a different "datestyle" setting.

Context: PL/pgSQL function myschema.nvv_update_11() line 15 at SQL statement

Updating and inserting records to the view works, but not deleting. Is this deliberate, and if so, why?

I'm using a 10.3.1 geodatabase in postgres 9.3.15 on linux.

Thanks

Simon

Reply
0 Kudos
3 Replies
Highlighted
Frequent Contributor

Have you tried setting the datestyle for for your transaction/session?

Reply
0 Kudos
Highlighted
New Contributor III

Thanks for the suggestion Chris, but it doesn't seem to make a difference as the datestyle that causes the problem - "12.31.9999 23:59:59.999" - is written in the trigger function created by SDE for an archive view. Seems odd that it should be written in this way when all other dates in the function are written as "9999-12-31 23:59:59".

Reply
0 Kudos
Highlighted
New Contributor III

I take it back - the datestyle setting was the issue  It needed to be set on the database (or in postgres.confto 'ISO MDY' before enabling archiving on the feature class. If you make the change after the trigger function has already been created, the SQL DELETE returns successfully but the WHERE gdb_to_date = "12.31.9999 23:59:59.999" clause still fails to delete the feature out of the base table. With the datestyle set to MDY before archiving, the trigger function changes slightly to use "12.31.9999 23:59:59" (without the .999) and the SQL DELETE works.

Reply
0 Kudos