Re: PostgreSQL Gotchas

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> Unquoted object names fold to lower case
> I don't really see this as too much of an issue, personally, but I do
> know some people have run into it. The example they give seems a bit
> off tho, as I thought Oracle just folded to upper-case (in which case
> "table" != table in Oracle, but "table" = table in Postgres, kind of
> depends on how you look at it). I'm not sure the spec says one way or
> another.

Yeah, it does, and the spec sides with Oracle. This is explained
exactly in the manual:

: Quoting an identifier also makes it case-sensitive, whereas unquoted
: names are always folded to lower case. For example, the identifiers FOO,
: foo, and "foo" are considered the same by PostgreSQL, but "Foo" and
: "FOO" are different from these three and each other. (The folding of
: unquoted names to lower case in PostgreSQL is incompatible with the SQL
: standard, which says that unquoted names should be folded to upper
: case. Thus, foo should be equivalent to "FOO" not "foo" according to the
: standard. If you want to write portable applications you are advised to
: always quote a particular name or never quote it.)

> RANDOM() failures
> This looks like an interesting problem, and might be a bug to be
> fixed. I think it depends on the what the SQL specification says for
> this case, if it says anything at all, as to if it should be
> considered a bug or not. On the surface it certainly does look like
> an oddity though.

Well, it's surely legitimate material as a "gotcha". The example is
taken from
http://archives.postgresql.org/pgsql-general/2004-11/msg01375.php
and the "previous discussion" referred to is this thread:
http://archives.postgresql.org/pgsql-hackers/2004-10/msg00082.php

As you can see, there wasn't a lot of agreement that we ought to change
it. Arguably, if we did change it we'd get ripped for the "gotcha" of
poor optimization when the user forgets to mark nonvolatile functions
properly. (Personally, though, I'm in favor of tightening it up.)

regards, tom lane