PGSQL 7.4 tips, was Re: [GENERAL] Is my MySQL Gaining ?

Hi Dante;
From: "D. Dante Lorenso" <dante(at)lorenso(dot)com>

> I started with MySQL and it WAS easier to use. It was easier because
> the manual essentially reads:
>
> -- we didn't implement anything complicated that's why
> -- we are fast.
>
> The only SQL customizations that MySQL has that I really miss in
PostgreSQL
> are the commands:
>
> SHOW DATABASES;
> SHOW TABLES;
> DESC table;
>
With 7.4, PostgreSQL implements the standard information_schema so that one
can essentially get all this information in a standard way with will
presumably not be brokent too much in future versions. Prior to this
release, you have to dig the information out of the system catelogs which
would periodically change.

Here are some examples (see the docs on the information schema ;-)

SELECT table_name FROM information_schema.tables WHERE table_schema =
'public';
(lists all tables in the public schema)

SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'pg_class';
(lists all columns from table pg_class, part of the system catelogs)

One area where you may need to use the catalogs is in listing the databases
in the cluster.
To do this, use
SELECT datname FROM pg_catalog.pg_database;

> That was ubber simple to do in MySQL. To this day, I have trouble with
> that in PostgreSQL. I'm constantly doing:
>
> psql> \?
> psql> help;
> ERROR: syntax error at or near "help" at character 1
> psql> \h
> ...
> * damnit, that's not it...*
> psql> \?
> psql> \d
> * ok, now which flag do I use for tables vs functions..etc?*
>
Ok. Hope the tips above are helpful :-)

> I finally figure it out, I just end up forgetting again later. I still
> have no clue how I'd find the same data without using psql. In MySQL
> I can run those queries from PHP, PERL...etc. I know you can find that
> data in system tables in PostgreSQL, but I don't wanna muck around with
> all that. I just wanna do something as simple as MySQL.
>
Another hint-- run psql -E to echo the queries to the screen, so that you
can see how the information is being requested from the system catalogs.

WARNING: Using the system catalogs is NOT supported across versions, as
they tend to change from time to time. Use the information_schema instead
wherever possible :-)

> Course, with that said... I've been building ALL my database apps with
> PostgreSQL because it just simply works even if it doesn't always work
> simple-ly.
>
> As a plug, though ... I'm hooked on EMS PostgreSQL Manager 2.0. I'd have
> to say that I'd not be as much of a PostgreSQL supporter if it weren't for
> this client tool. I think EMS did the 'making it friendly to the
developer'
> that was sorely lacking in stock PostgreSQL client tools. Kudos.
>
> Dante
>
> ----------
> D. Dante Lorenso
> dante(at)lorenso(dot)com
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>