Postgis datastore generates null value in sql statement which overrides table's "DEFAULT now()" declaration
Description
CREATE TABLE user_location ( user_name character varying(50) NOT NULL, user_bbox geometry NOT NULL, user_enter_ts timestamp without time zone DEFAULT now(), user_location_id serial NOT NULL, CONSTRAINT user_location_id PRIMARY KEY (user_location_id) )
Using javascript to generate timestamp(ISO-8601 format) : //generated from user's mouse moves var feature = new OpenLayers.Feature.Vector(
new OpenLayers.Geometry.MultiPolygon(bounds.toGeometry())); // Set feature attributes. user_bbox is generated from var d = new Date(); feature.attributes = {'user_name':USER, 'user_enter_ts':d.toISO8601String(6)} ; // Add it to the layer. map.layers); // Post. map.layers
---------------------------------------------------------------------------------------------------------------------------------------------------
Now we remove the javascript generated timestamp and allow postgres to generate the timestamp with the DEFAULT now() declaration in CREATE TABLE: //feature attribute with timestamp removed. feature.attributes = {'user_name':USER} ;
Fails. No now() value inserted into user_enter_ts as a NULL value is inserted by postgis. INSERT INTO "public"."user_location" ("user_name","user_bbox","user_enter_ts") VALUES ('test01',setSRID('...'::geometry,4326),null)
Thanks.
Environment
None
Activity
codehaus
April 10, 2015 at 4:44 PM
CodeHaus Comment From: aaime - Time: Tue, 2 Jun 2009 10:00:50 -0500 ---------------------
<p>The current postgis datastore won't be changed as we're about to replace it with a newer version (a complete rewrite). I've opened an issue against the new datastore to see if we can handle this case in a meaningful way.</p>
codehaus
April 10, 2015 at 4:44 PM
CodeHaus Comment From: ahayes - Time: Thu, 26 Mar 2009 13:11:42 -0500 ---------------------
<p>This appears to be related to <a href="https://jira.codehaus.org/browse/GEOT-1806" title="WFS Datastore submits a default value on insert for null attributes which are nillable" class="issue-link" data-issue-key="GEOT-1806">GEOT-1806</a></p>
CREATE TABLE user_location
(
user_name character varying(50) NOT NULL,
user_bbox geometry NOT NULL,
user_enter_ts timestamp without time zone DEFAULT now(),
user_location_id serial NOT NULL,
CONSTRAINT user_location_id PRIMARY KEY (user_location_id)
)
Using javascript to generate timestamp(ISO-8601 format) :
//generated from user's mouse moves
var feature = new OpenLayers.Feature.Vector(
new OpenLayers.Geometry.MultiPolygon(bounds.toGeometry()));
// Set feature attributes. user_bbox is generated from
var d = new Date();
feature.attributes =
{'user_name':USER,
'user_enter_ts':d.toISO8601String(6)}
;
// Add it to the layer.
map.layers);
// Post.
map.layers
Resultant POST body:
<wfs:Transaction xmlns:wfs="http://www.opengis.net/wfs" version="1.0.0" service="WFS"><wfs:Insert>
<feature:user_location xmlns:feature="https://www.djc2.org/schemas"><feature:user_bbox>
<gml:MultiPolygon xmlns:gml="http://www.opengis.net/gml"><gmlolygonMember><gmlolygon><gml:outerBoundaryIs><gml:LinearRing>
<gml:coordinates decimal="." cs="," ts=" ">-159.43359375,-17.05078125 42.36328125,-17.05078125 42.36328125
,50.44921875 -159.43359375,50.44921875 -159.43359375,-17.05078125</gml:coordinates></gml:LinearRing>
</gml:outerBoundaryIs></gmlolygon></gmlolygonMember></gml:MultiPolygon>
</feature:user_bbox><feature:user_name>test01</feature:user_name>
<feature:user_enter_ts>2009-03-18T15:32:47.024Z</feature:user_enter_ts></feature:user_location></wfs:Insert></wfs:Transaction>
---------------------------------------------------------------------------------------------------------------------------------------------------
Now we remove the javascript generated timestamp and allow postgres to generate the timestamp with the DEFAULT now() declaration in CREATE TABLE:
//feature attribute with timestamp removed.
feature.attributes =
{'user_name':USER}
;
Resultant POST body:
<wfs:Transaction xmlns:wfs="http://www.opengis.net/wfs" version="1.0.0" service="WFS"><wfs:Insert>
<feature:user_location xmlns:feature="https://www.djc2.org/schemas">
<feature:user_bbox><gml:MultiPolygon xmlns:gml="http://www.opengis.net/gml">
<gmlolygonMember><gmlolygon><gml:outerBoundaryIs>
<gml:LinearRing><gml:coordinates decimal="." cs="," ts=" ">-273.1640625,-50.9765625 130.4296875,-50.9765625 130.4296875
,84.0234375 -273.1640625,84.0234375 -273.1640625,-50.9765625</gml:coordinates>
</gml:LinearRing>
</gml:outerBoundaryIs></gmlolygon></gmlolygonMember></gml:MultiPolygon>
</feature:user_bbox><feature:user_name>test01</feature:user_name></feature:user_location></wfs:Insert></wfs:Transaction>
Fails. No now() value inserted into user_enter_ts as a NULL value is inserted by postgis.
INSERT INTO "public"."user_location"
("user_name","user_bbox","user_enter_ts")
VALUES
('test01',setSRID('...'::geometry,4326),null)
Thanks.