> On 10/6/05, Aly S.P Dharshi <aly ( dot ) dharshi (
at ) telus ( dot ) net> wrote:
>
>
http://sql-info.de/postgresql/postgres-gotchas.html
>
> Any comments from folks on the list ?

- It's a lot shorter than MySQL's gotchas list.
- 8 of the 13 are for versions of PostgreSQL <= 8.1
- Of the remaining, I consider "select as" to be
really trivial (and it appears a work-around can be
hacked).
- lowercase folding. I DO sometimes wish I could use
fieldID, etc. without quoting it.
- I've never found count(*) to be slow.
- I don't know enough about the "UNICODE means
"UTF-8"" and "RANDOM() failures" to comment.

CSN



__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

CSN wrote:

>- I don't know enough about the "UNICODE means
>"UTF-8"" and "RANDOM() failures" to comment.
>
>
I'm hardly an expert, but I've done enough with unicode to know that you
can easily convert utf-8 to any other flavor of unicode you might want
to use. Though, why you'd want to use something other than utf-8 in the
first place I don't understand.

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

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

On Thu, Oct 06, 2005 at 12:54:43PM -0700, CSN wrote:
> - lowercase folding. I DO sometimes wish I could use
> fieldID, etc. without quoting it.

I believe that may be against ANSI SQL. In any case, the only databases
I can think of that don't fold-case in some form are MySQL and Access.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Yep, I think the SQL spec says fold to uppercase. I'm
not sure why PostgreSQL folds to lowercase instead,
but if folding has to occur, I prefer lowercase.

CSN


--- "Jim C. Nasby" <jnasby@pervasive.com> wrote:

> On Thu, Oct 06, 2005 at 12:54:43PM -0700, CSN wrote:
> > - lowercase folding. I DO sometimes wish I could
> use
> > fieldID, etc. without quoting it.
>
> I believe that may be against ANSI SQL. In any case,
> the only databases
> I can think of that don't fold-case in some form are
> MySQL and Access.
> --
> Jim C. Nasby, Sr. Engineering Consultant
> jnasby@pervasive.com
> Pervasive Software http://pervasive.com
> work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf
> cell: 512-569-9461
>




__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

CSN wrote:
> Yep, I think the SQL spec says fold to uppercase. I'm
> not sure why PostgreSQL folds to lowercase instead,
> but if folding has to occur, I prefer lowercase.

I think preference was why lowercase was chosen many moons ago. It's
stayed that way because otherwise existing users would be storming the
-hackers list with pitchforks and flaming torches.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Fri, Oct 07, 2005 at 08:44:34AM +0100, Richard Huxton wrote:
> CSN wrote:
> >Yep, I think the SQL spec says fold to uppercase. I'm
> >not sure why PostgreSQL folds to lowercase instead,
> >but if folding has to occur, I prefer lowercase.
>
> I think preference was why lowercase was chosen many moons ago. It's
> stayed that way because otherwise existing users would be storming the
> -hackers list with pitchforks and flaming torches.

If any change was made I'm sure it would be to allow the user to decide
which way case was folded. But IMHO, anyone messing around with object
names that won't fold is asking for trouble anyway.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

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

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.

On 08/10/05, Jim C. Nasby <jnasby@pervasive.com> wrote:
> On Fri, Oct 07, 2005 at 08:44:34AM +0100, Richard Huxton wrote:
> > CSN wrote:
> > >Yep, I think the SQL spec says fold to uppercase. I'm
> > >not sure why PostgreSQL folds to lowercase instead,
> > >but if folding has to occur, I prefer lowercase.
> >
> > I think preference was why lowercase was chosen many moons ago. It's
> > stayed that way because otherwise existing users would be storming the
> > -hackers list with pitchforks and flaming torches.
>
> If any change was made I'm sure it would be to allow the user to decide
> which way case was folded. But IMHO, anyone messing around with object
> names that won't fold is asking for trouble anyway.
> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
> ---------------------------(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
>


--
Best regards,
Nikolay

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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.

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

Case-insensetive text comparisons can be acheived using the citext
module on gborg.

http://gborg.postgresql.org/project/...rojdisplay.php

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFDR760IB7bNG8LQkwRAldbAJ9tDltFlDIIa9H+RKxzHw TbQLb9ogCdHQVN
2OLTjc2naGnD0R9SrsThNgU=
=oRI4
-----END PGP SIGNATURE-----

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).
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/...rojdisplay.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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Sat, Oct 08, 2005 at 06:05:29PM +0400, Nikolay Samokhvalov wrote:
> On 08/10/05, Martijn van Oosterhout <kleptog@svana.org> wrote:
> > 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).

You've got me confused. What are INSTEAD OF triggers?

PostgreSQL does support views that look exactly like tables. You can
decide on INSERT what to do whith fields not in the view, which columns
you allow UPDATE and what the semantics should be for DELETE if the
view is a join on multiple tables. All PostgreSQL doesn't do is create
these rules for you.

For an example see here, all the way at the bottom. RULEs are not
TRIGGERs:

http://www.varlena.com/varlena/GeneralBits/82.php

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

PostgreSQL allows any view to be updatable, no matter how complex it
is. You just have to create the rules yourself.

There have been attempts to automate the process, they just havn't been
clean enough to pass muster. And people who really want updateable
views can make them already.

Hope this clarifies things for you,
--
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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFDSAUCIB7bNG8LQkwRAlnRAJ9hgP7t8s5pZApEDTsxq5 6JdCy9OQCcD+ES
+ZzS6lqlzvm6LKbPZ0/5L3Q=
=/nXD
-----END PGP SIGNATURE-----