Re: PostgreSQL Gotchas

On 08/10/05, Martijn van Oosterhout <kleptog@svana.org> wrote:
> On Sat, Oct 08, 2005 at 03:32:00PM +0400, Nikolay Samokhvalov wrote:
> > http://chernowiki.ru/Dev/PostgreSQLComparedWithSQL2003AndOracleSQLServerDB2Etc
> >
> > Perhaps I'm wrong with some issues - any comments are welcome.
>
> The only thing I can comment on is updatable views. You can make
> updatable views using RULEs. The only thing is that they're not
> *automatically* updateable.

OK, I'll make this correction. But for me, updatable views are views
for which DBMS supports  insert/update/delete operations as for
tables. Ideally, people shouldn't distinguish table and view - that's
what theory stands for (see Date's thoutghs about it:
http://www.dbmsmag.com/int9410.html, he also has a cycle of articles
on this theme: http://www.dbdebunk.citymax.com/page/page/622302.htm).
PostgreSQL doesn't support updates even for simple views such as
select-with-restriction. What it does support - not updatable views,
but some kind of INSTEAD OFF triggers (another form of).

>
> In theory, if someone came up will a program that from given <view
> definition> produced the appropriate INSERT, UPDATE and DELETE rules,
> it might be incorporated. Currently you just have to do it manually...

There is a good theory (Date), but it cannot be implemented for any
practical DBMS. The cause lies in differences between theory and
practice. And the major difference is possibility to define tables w/o
PK (in other words, possible duplicate rows). Nevertheless, all major
commercial RDMSs support some subset of views that can be updated..
SQL:2003 defines a quite large subset, but the definition is pretty
mazy...

>
> Case-insensetive text comparisons can be acheived using the citext
> module on gborg.
>
> http://gborg.postgresql.org/project/citext/projdisplay.php

Thanks for the link, I'll try it. However, I suppose that such basic
feature as support collations should be implemented in core.

>
> Have a nice day,
> --
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.
>
>
>


--
Best regards,
Nikolay