On 10/8/05, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
> I use PostgeSQL less than year. Before I worked with MS SQL Server
> 2000, MySQL 3&4, Oracle8i and Interbase. Also, I studied standards
> SQL:1999 and SQL:2003. So, after switching to PostgreSQL I've
> encountered with several things that seem strange to me. Of course,
> several of them are simply not implemented yet and are in the list of
> unsopported features:
> http://www.postgresql.org/docs/8.0/i...-standard.html.
> But some seem to be 'old diseases'.
>
> Here is my list of the issues:
> http://chernowiki.ru/Dev/PostgreSQLC...ServerDB2 Etc
>
> Perhaps I'm wrong with some issues - any comments are welcome.
>
From the referenced page...

"We can insert into column of type TIME the value such as '12:15' and
then obtain '12:15:00' making select, but we couldn't do the same with
dates."

Huh? Minutes and seconds have a valid value of zero. Months and days
don't. Date types need to contain a valid date, and time types need
to contain a valid time. Anything else seems like, well, MySQL.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Nikolay Samokhvalov wrote:

>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/PostgreSQLC...ServerDB2 Etc
>>>
>>>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).
>
>
Who do you mean by "people?" Users? DBA's?

Now, obviously the DBA will always be able to distinguish between them.
Otherwise he/she wouldn't be much of a DBA would he/she?

>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).
>
>
Ok... Your complaint is that PostgreSQL doesn't support automatically
generating insert/update/delete rules for views. Ultimately, however,
this may not be done simply because there are other priorities which
people are devoting their time and energy to instead. I know some
people have put in some work on this item but I don't know what the
status is.

Also, what SQL Server 2003 calles a trigger, we call a rule. What we
call a trigger is different and I am not sure SQL Server 2003 has such
an equivalent.

>
>
>>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...
>
>

Again, this is likely doable. There are hidden fields that I suppose
could be extended in a view to reference unique rows (maybe ctid since
the whole thing is expanded in a single SQL statement, but I haven't
tried it).

Best Wishes,
Chris Travers
Metatron Technology Consulting

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match