Обсуждение: 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.<br />The problem is thatNHibernate can't map geometric data types to any kind of property of a class (not for insertions).<br /> I've (with partialsuccess, as will be explained) mapped the desired column (in my case, a POINT pgsql type) by mapping from column[0]and column[1].<br />I know I can do updates and selects using column[0] and column[1], but I can't do INSERTS (atleast not if the column has a not-null constraint).<br /> The documentation doesn't say that insertions would work (<a href="http://www.postgresql.org/docs/9.0/interactive/functions-geometry.html">http://www.postgresql.org/docs/9.0/interactive/functions-geometry.html</a>), soI'm not filing this as a bug report.<br /><br />However, with the huge popularity of ORM solutions and the complexity ofdb-specific datatypes (geometric types in other dbs probably work differently), we can't really expect ORM software todo all the mapping successfully (although projects such as Hibernate Spatial exist for java solutions, NHibernate Spatialseems to be abandoned, and there are, of course, other ORM solutions for other platforms).<br /><br />While I haveemphasized the POINT data type, it would be desirable that all types in pgsql could be accessed/updated/inserted withthe array-analogy (or other analogy), so that we could easily map ANYTHING with ORM software these days.<br /><br />Also,just to note, if there isn't a not null constraint on the column, inserting with column[0] and column[1] will inserta null value in that column.<br /><br /><b>The SQL to show what I mean:</b><br />postgres=# create table test (coordinatesPOINT NOT NULL);<br /> CREATE TABLE<br />postgres=# insert into test (coordinates[0], coordinates[1]) values(1,2);<br />ERROR: null value in column "coordinates" violates not-null constraint<br /><br /><b>And then:</b><br/>postgres=# alter table test alter column coordinates drop not null;<br /> ALTER TABLE<br />postgres=# insertinto test (coordinates[0], coordinates[1]) values (1,2);<br />INSERT 0 1<br />postgres=# select * from test where coordinatesis null;<br /> coordinates<br />-------------<br /><br />(1 row)<br /><br /><i>* In the results above, the blankline shows the null value (obviously)</i><br />
Marcelo Zabani <mzabani@gmail.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@sss.pgh.pa.us> wrote: > Marcelo Zabani <mzabani@gmail.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@gmail.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@gmail.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@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.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