moving from MySQL to Postgres

#1

Hi,

I'm attempting to move some SQL code from a MySQL database to a Postgres
database. First, I bet I'm not the only person ever to this - so I bet there
is a conversion white paper somewhere (if there's not - there should be).
Could someone point out where I might find it on the web - google didn't help
(most likely the wrong search string).

Most of the issues I have are around Metadata and how MySQL uses a simple
statement to produce Metadata:

'SHOW tables'
'describe table' ....

At the moment I'm having trouble with 'describe table' which produces three
columns- column name, column type, column attributes (like PK).
The following works for the first two columns but I can't figure out how to
get the third column.
"SELECT a.attname, t.typname FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = 'tablename' AND a.attnum > 0 AND a.attrelid = c.oid
AND a.atttypid = t.oid ORDER BY a.attnum"

So will someone help out a novice? I need the third column in the statement.
I did find I could use '-E' when starting the psql client which reproduces
what Postgres uses for the '\d tablename' but it uses three statements and
it does not combine the index information until the end.

Which brings up another question. How does the statement 2 know about the
results of statement 1 (same how does statement 3 know results of statement
2). Could it be that internally it created a temporary cursor?

John

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#2

Be sure to look at information_schema for easier access to such data.

В Вск, 31.10.2004, в 17:26, John Fabiani пишет:

Quote:

Hi,

I'm attempting to move some SQL code from a MySQL database to a Postgres
database. First, I bet I'm not the only person ever to this - so I bet there
is a conversion white paper somewhere (if there's not - there should be).
Could someone point out where I might find it on the web - google didn't help
(most likely the wrong search string).

Most of the issues I have are around Metadata and how MySQL uses a simple
statement to produce Metadata:

'SHOW tables'
'describe table' ....

At the moment I'm having trouble with 'describe table' which produces three
columns- column name, column type, column attributes (like PK).
The following works for the first two columns but I can't figure out how to
get the third column.
"SELECT a.attname, t.typname FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = 'tablename' AND a.attnum > 0 AND a.attrelid = c.oid
AND a.atttypid = t.oid ORDER BY a.attnum"

So will someone help out a novice? I need the third column in the statement.
I did find I could use '-E' when starting the psql client which reproduces
what Postgres uses for the '\d tablename' but it uses three statements and
it does not combine the index information until the end.

Which brings up another question. How does the statement 2 know about the
results of statement 1 (same how does statement 3 know results of statement
2). Could it be that internally it created a temporary cursor?

John

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
--

Markus Bertheau <twanger (AT) bluetwanger (DOT) de>

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#3

Thanks for the quick response. Except I don't know what you are referring
too. Is there something other than the system tables (pg_*)?
John
On Sunday 31 October 2004 08:35, Markus Bertheau wrote:

Quote:

Be sure to look at information_schema for easier access to such data.

В Вск, 31.10.2004, в 17:26, John Fabiani пишет:
Hi,

I'm attempting to move some SQL code from a MySQL database to a Postgres
database. First, I bet I'm not the only person ever to this - so I bet
there is a conversion white paper somewhere (if there's not - there
should be). Could someone point out where I might find it on the web -
google didn't help (most likely the wrong search string).

Most of the issues I have are around Metadata and how MySQL uses a simple
statement to produce Metadata:

'SHOW tables'
'describe table' ....

At the moment I'm having trouble with 'describe table' which produces
three columns- column name, column type, column attributes (like PK).
The following works for the first two columns but I can't figure out how
to get the third column.
"SELECT a.attname, t.typname FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = 'tablename' AND a.attnum > 0 AND a.attrelid = c.oid
AND a.atttypid = t.oid ORDER BY a.attnum"

So will someone help out a novice? I need the third column in the
statement. I did find I could use '-E' when starting the psql client
which reproduces what Postgres uses for the '\d tablename' but it uses
three statements and it does not combine the index information until the
end.

Which brings up another question. How does the statement 2 know about
the results of statement 1 (same how does statement 3 know results of
statement 2). Could it be that internally it created a temporary cursor?

John

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your joining column's datatypes do not match

---------------------------(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 Postgres

comp.databases.postgresql.novice comp.databases.postgresql.novice

moving from MySQL to Postgres - 10-31-2004 , 11:26 AM

Re: moving from MySQL to Postgres - 10-31-2004 , 11:35 AM

Re: moving from MySQL to Postgres - 10-31-2004 , 11:47 AM