[Geoserver-users] Problem inserting point into postgis

GeoServer GeoServer User
1

Hallo,

I'm using Geoserver 1.7 and I do have the following problem:

I have created the following table in a postgis database:

CREATE TABLE cams
(
  ...
  zeit timestamp without time zone NOT NULL DEFAULT now(),
  ...
)

It is my intention that everytime I insert a new cam the date is being saved
too.

schema.xml:

<xs:complexType name = "cams_Type" >
  <xs:complexContent >
    <xs:extension base = "gml:AbstractFeatureType" >
      <xs:sequence >
        <xs:element nillable = "false" name = "zeit" maxOccurs = "1" type =
"gml:AbstractFeatureType" minOccurs = "1" />
      ...
      </xs:sequence>
    </xs:extension>
  </xs:complexContent>
</xs:complexType>

My insert request without the "zeit"-attribute:

<wfs:Transaction xmlns:wfs="http://www.opengis.net/wfs&quot; version="1.0.0"
service="WFS">
  <wfs:Insert>
    <feature:cams xmlns:feature="http://localhost:8080/cctv&quot;&gt;
      <feature:the_geom>
        ...
      </feature:the_geom>
    </feature:cams>
  </wfs:Insert>
</wfs:Transaction>

And geoserver's anwser:

2008-11-20 16:46:51,500 ERROR [data.jdbc] - SQL Exception writing geometry
columnERROR: null value in column "zeit" violates not-null constraint
org.postgresql.util.PSQLException: ERROR: null value in column "zeit"
violates not-null constraint
  ...
2008-11-20 16:46:51,515 WARN [data.jdbc] - Feature writer calling close when
queryData is already closed
2008-11-20 16:46:51,515 ERROR [geoserver.wfs] - Transaction failed
org.geoserver.wfs.WFSTransactionException: Error performing insert
  ...
Caused by: org.geotools.data.DataSourceException: SQL Exception writing
geometry columnERROR: null value in column "zeit" violates not-null
constraint
  ...
Caused by: org.postgresql.util.PSQLException: ERROR: null value in column
"zeit" violates not-null constraint

I have deleted the "not null"-constrain in the database but than I get empty
fields and not the default value.

Can anybody please help me?
Thanks.

Regards, Florian

--
View this message in context: http://www.nabble.com/Problem-inserting-point-into-postgis-tp20604354p20604354.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

2

Geoflo ha scritto:

Hallo,

I'm using Geoserver 1.7 and I do have the following problem:

I have created the following table in a postgis database:

CREATE TABLE cams
(
  ...
  zeit timestamp without time zone NOT NULL DEFAULT now(),
  ...
)

It is my intention that everytime I insert a new cam the date is being saved
too.

schema.xml:

<xs:complexType name = "cams_Type" >
  <xs:complexContent >
    <xs:extension base = "gml:AbstractFeatureType" >
      <xs:sequence >
        <xs:element nillable = "false" name = "zeit" maxOccurs = "1" type =
"gml:AbstractFeatureType" minOccurs = "1" />
      ...
      </xs:sequence>
    </xs:extension>
  </xs:complexContent>
</xs:complexType>

My insert request without the "zeit"-attribute:

<wfs:Transaction xmlns:wfs="http://www.opengis.net/wfs&quot; version="1.0.0"
service="WFS">
  <wfs:Insert>
    <feature:cams xmlns:feature="http://localhost:8080/cctv&quot;&gt;
      <feature:the_geom>
        ...
      </feature:the_geom>
    </feature:cams>
  </wfs:Insert>
</wfs:Transaction>

And geoserver's anwser:

2008-11-20 16:46:51,500 ERROR [data.jdbc] - SQL Exception writing geometry
columnERROR: null value in column "zeit" violates not-null constraint
org.postgresql.util.PSQLException: ERROR: null value in column "zeit"
violates not-null constraint
  ...
2008-11-20 16:46:51,515 WARN [data.jdbc] - Feature writer calling close when
queryData is already closed
2008-11-20 16:46:51,515 ERROR [geoserver.wfs] - Transaction failed
org.geoserver.wfs.WFSTransactionException: Error performing insert
  ...
Caused by: org.geotools.data.DataSourceException: SQL Exception writing
geometry columnERROR: null value in column "zeit" violates not-null
constraint
  ...
Caused by: org.postgresql.util.PSQLException: ERROR: null value in column
"zeit" violates not-null constraint

I have deleted the "not null"-constrain in the database but than I get empty
fields and not the default value.

Can anybody please help me?

GeoServer does not know about the default value and just specifies a NULL, in that case the DEFAULT rule you specified does not work anymore.
I don't know if it would be possible to recognize those attributes
with a default and avoid specifying them into the insert in case
there is no value, but the change in the code would not be trivial.
As a workaround you can turn your DEFAULT into a trigger that forces
in the current time on transaction commit.

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.