Insertion of geometric type column with column[0], column[1] and etc.

From: Marcelo Zabani <mzabani(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Insertion of geometric type column with column[0], column[1] and etc.
Date: 2010-10-05 02:39:59
Message-ID: AANLkTi=1q1ye_yHb-1-CbEN34chJyjzvM4G=UiXHDRkC@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I've been using postgresql with geometric types and I've been using
NHibernate as an ORM solution.
The problem is that NHibernate can't map geometric data types to any kind of
property of a class (not for insertions).
I've (with partial success, as will be explained) mapped the desired column
(in my case, a POINT pgsql type) by mapping from column[0] and column[1].
I know I can do updates and selects using column[0] and column[1], but I
can't do INSERTS (at least not if the column has a not-null constraint).
The documentation doesn't say that insertions would work (
http://www.postgresql.org/docs/9.0/interactive/functions-geometry.html), so
I'm not filing this as a bug report.

However, with the huge popularity of ORM solutions and the complexity of
db-specific datatypes (geometric types in other dbs probably work
differently), we can't really expect ORM software to do all the mapping
successfully (although projects such as Hibernate Spatial exist for java
solutions, NHibernate Spatial seems to be abandoned, and there are, of
course, other ORM solutions for other platforms).

While I have emphasized the POINT data type, it would be desirable that all
types in pgsql could be accessed/updated/inserted with the array-analogy (or
other analogy), so that we could easily map ANYTHING with ORM software these
days.

Also, just to note, if there isn't a not null constraint on the column,
inserting with column[0] and column[1] will insert a null value in that
column.

*The SQL to show what I mean:*
postgres=# create table test (coordinates POINT NOT NULL);
CREATE TABLE
postgres=# insert into test (coordinates[0], coordinates[1]) values (1,2);
ERROR: null value in column "coordinates" violates not-null constraint

*And then:*
postgres=# alter table test alter column coordinates drop not null;
ALTER TABLE
postgres=# insert into test (coordinates[0], coordinates[1]) values (1,2);
INSERT 0 1
postgres=# select * from test where coordinates is null;
coordinates
-------------

(1 row)

** In the results above, the blank line shows the null value (obviously)*

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2010-10-05 02:50:50 Re: Insertion of geometric type column with column[0], column[1] and etc.
Previous Message Joseph Adams 2010-10-05 02:29:52 Re: Basic JSON support