Re: Moving from MySQL to PGSQL....some questions

Shridhar Daithankar wrote:

>> show databases etc. to get object details. I cant see
>> anything similar in PGSQL. After searching the net i
>> find that i have to execute certain queries to fetch
>> those queries. Is this the only way?
>
> 5.) In MySQL, there are many command like show tables,
>
> No. Simplest would be issuing \? on psql prompt. It will tell you
> plethora of options/commands using which you can accomplish many task.
> Just remember that these are not SQL command provided by server. It is
> the psql application which provide these commands. So you cannot use
> them in say php.
>
If you run psql with the "-E" parameter, whenever you execute a psql
command that translates to a query, that query will be displayed on
screen. This allows you to check out what queries you need for certain
operations.

For example - to check all the tables in the current database/schema:
$ psql -E db
Welcome to psql 7.4.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

db=# \dt
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  u.usename as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
      AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

           List of relations
 Schema |     Name     | Type  | Owner
--------+--------------+-------+-------

Check out the rest of the \d* commands for more listings (\? will give
you the list).

Also, it pays to look up the meaning of the above in the documentation.
The system tables are documented in
http://www.postgresql.org/docs/7.4/static/catalogs.html

>> Any help would be appreciated.
>
       Shachar

--
Shachar Shemesh
Lingnu Open Systems Consulting
http://www.lingnu.com/