Re: PostgreSQL Gotchas

* Aly S.P Dharshi (aly(dot)dharshi(at)telus(dot)net) wrote:
> - What is the status of those items listed on the PostgreSQL gotchas
> - Are they bugs ?
> - Are they valid statements ?
> - If they are bugs are they resolved ?
> - What does the PG community thing of this list of gotchas ?
>
> http://sql-info.de/postgresql/postgres-gotchas.html

There's only 6 things on there which apply to the current version of
PostgreSQL, and one of those will be fixed in 8.1.

The remaining items are:

SELECT column alias,...
This issue looks like it has probably been hashed over a few times and
at the end of the day the answer is "Best to use AS for clarity
anyway, and it is documented to be necessary, and Postgres upstream
has decided to require it instead of making alot of things reserved
which wouldn't otherwise need to be."

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. I've had problems with this when people were building things
against a MySQL database w/o proper quoting and assuming it was
case-sensitive when under Postgres it wasn't.

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.

COUNT(*) very slow
As someone else has pointed out, it's only slow if you've got a large
dataset. There's certainly workarounds for this issue (generally
involving a couple of functions for keeping track of the number of
rows). In this way, Postgres actually gives the user the flexibility
to choose the performance loss on insert/deletes which is necessary to
track the number of rows seperately, or not to.

UNICODE means "UTF-8"
This is an interesting point. To be perfectly honest, it seems like
Postgres' UNICODE/UTF-8/etc support could stand to be somewhat better.
I've not used it much myself but I do see comments about it on the
lists from time to time. I think there was some work done on this
for 8.1 and I expect support will only continue to improve in these
areas.

Just my 2c.

Thanks,

Stephen