Moving from MySQL to PGSQL....some questions

#11

Quote:

"Karam Chand" <karam_chand03 (AT) yahoo (DOT) com> wrote in message
news:20040225145704.40397.qmail (AT) web60804 (DOT) mail.yahoo.com...

5.) In MySQL, there are many command like show tables,
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?

One easy way is to use the psql command line program
and the \d command. It lists all your tables,
lists all the columns in a table, etc.

Karl <kop (AT) meme (DOT) com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#12

Shachar,

This is a very helpful tidbit that I hadn't realized and it will save me a
significant amount of time figuring out such queries in the coming weeks.
Thank you.

Would it be worthwhile to move many of these \d queries into the system
schema, as views on various system tables? I've thought that it would be
very useful to be able to access these things through the web or other
clients. I could see the benefit of providing users with a consistent
interface
to such "database metadata", no matter what client one is using. (OTOH, one
could argue, learning to do that is a pgsql rite-of-passage. ;-> ).

Shawn Harrison

----- Original Message -----

Quote:

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/

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

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#13

"Shawn Harrison" <harrison (AT) tbc (DOT) net> writes:

Quote:

Would it be worthwhile to move many of these \d queries into the system
schema, as views on various system tables?

There's been talk of that in the past, but no one's gotten around to
doing much about it. If you are interested in working on it, look
into the mail list archives for past discussions.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#14

On Mon, Mar 01, 2004 at 11:09:32 -0600,
Shawn Harrison <harrison (AT) tbc (DOT) net> wrote:

Quote:

Would it be worthwhile to move many of these \d queries into the system
schema, as views on various system tables? I've thought that it would be
very useful to be able to access these things through the web or other
clients. I could see the benefit of providing users with a consistent
interface
to such "database metadata", no matter what client one is using. (OTOH, one
could argue, learning to do that is a pgsql rite-of-passage. ;-> ).

If you are using 7.4.x look at the information_schema schema. This is going
to provide a stable way to get meta data.

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

#15

Michael Chaney wrote:

Quote:

One other note, for those converting a database from MySQL to
PostgreSQL, I have a table creation conversion script here:

http://www.michaelchaney.com/downloads/m2p.pl

I know that two come with PostgreSQL in the contrib directory, but I
wrote this because those two didn't do what I needed. With this, you
should be able to take the MySQL table creation scripts (as created by
mysqldump --tab=x) and directly build the tables and load the data into
a PostgreSQL db with little effort.

Please share what yours does that the /contrib doesn't, and ideally,
send in a patch or let us add your version to /contrib.

--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Moving from MySQL to PGSQL....some questions

comp.databases.postgresql.general comp.databases.postgresql.general

Re: Moving from MySQL to PGSQL....some questions - 02-28-2004 , 03:54 PM

Database metadata queries (WAS Re: Moving from MySQL to PGSQL....some questions) - 03-01-2004 , 11:09 AM

Re: Database metadata queries (WAS Re: Moving from MySQL to PGSQL....some questions) - 03-01-2004 , 10:54 PM

Re: Database metadata queries (WAS Re: Moving from MySQL to PGSQL....some questions) - 03-02-2004 , 11:02 AM

Re: Moving from MySQL to PGSQL....some questions - 03-02-2004 , 12:57 PM