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

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

Marcelo Zabani <mzabani(at)gmail(dot)com> writes:
> 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.

Surely it would be better to get an ORM that can actually deal with the
data types you wish to use.

regards, tom lane

On Mon, Oct 4, 2010 at 10:50 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Marcelo Zabani <mzabani(at)gmail(dot)com> writes:
>> 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.
>
> Surely it would be better to get an ORM that can actually deal with the
> data types you wish to use.

*scratches head*

Yeah, but isn't the current behavior awfully flaky? ISTM that if you
insert into a subscripted column, you should either get an error, or
your data should end up in the table somewhere. Sending it merrily
off into the void is ... well, words fail me.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Yeah, but isn't the current behavior awfully flaky?

The reason the particular case acts so oddly is there's no such thing as
half a point: you can't have a point with one null and one non-null
component. So there's no way to construct the value incrementally,
which is what that syntax implies doing. What actually happens is that
"foo[0] := something" results in a NULL point if the initial value of
foo was NULL, and then the same again for "foo[1] := something".
I suppose we could have these cases throw an error instead, but that's
not a lot better from the standpoint of functionality ... and I
certainly don't wish to try to introduce partially-null point values.

In general this shows the limitations of trying to pretend that complex
data types are arrays. Even if you can manage to find some kind of
mapping, it's not necessarily one-to-one, nor are all the values that
might be valid from one viewpoint valid from the other. So I've got no
enthusiasm for the OP's proposal.

regards, tom lane

* Marcelo Zabani (mzabani(at)gmail(dot)com) wrote:
> 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).

Erm, there's two failing here. One is that geometric types work
differently in other databases (I'd encourage you to go check out
http://www.opengis.org), the second is that we can't expect ORM software
to work as, well, an ORM.

Thanks,

Stephen

On Tue, Oct 5, 2010 at 12:11 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Yeah, but isn't the current behavior awfully flaky?
>
> The reason the particular case acts so oddly is there's no such thing as
> half a point: you can't have a point with one null and one non-null
> component.  So there's no way to construct the value incrementally,
> which is what that syntax implies doing.  What actually happens is that
> "foo[0] := something" results in a NULL point if the initial value of
> foo was NULL, and then the same again for "foo[1] := something".
> I suppose we could have these cases throw an error instead, but that's
> not a lot better from the standpoint of functionality ... and I
> certainly don't wish to try to introduce partially-null point values.

Well, I think the user might expect foo[0] and foo[1] to get assigned
to simultaneously rather than iteratively.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company