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)*
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 |