Thread: moving from MySQL to Postgres
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
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 -- Markus Bertheau <twanger@bluetwanger.de>
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: > 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
В Вск, 31.10.2004, в 17:47, John Fabiani пишет: > Thanks for the quick response. Except I don't know what you are referring > too. Is there something other than the system tables (pg_*)? Indeed: http://www.postgresql.org/docs/current/static/information-schema.html in particular http://www.postgresql.org/docs/current/static/infoschema-columns.html -- Markus Bertheau <twanger@bluetwanger.de>
I guess I'm just behind the times. The schema information you refer to is in the 7.4.x of Postgres - right? I of course do not have 7.4.x. I'll have to do a little more research on the issue. More importantly do the old pg_* table still apply to the 7.4.x versions? This is reason I post to the novice area - I'm less than a novice. BTW thanks for the helping. John On Sunday 31 October 2004 09:00, Markus Bertheau wrote: > В Вск, 31.10.2004, в 17:47, John Fabiani пишет: > > Thanks for the quick response. Except I don't know what you are > > referring too. Is there something other than the system tables (pg_*)? > > Indeed: > > http://www.postgresql.org/docs/current/static/information-schema.html > > in particular > > http://www.postgresql.org/docs/current/static/infoschema-columns.html
В Вск, 31.10.2004, в 18:18, John Fabiani пишет: > I guess I'm just behind the times. The schema information you refer to is in > the 7.4.x of Postgres - right? Yes. > I of course do not have 7.4.x. Upgrade then. :) How they say, for your own good. Especially when you're starting something new, there's not much reason for starting with something with known shortcomings. > I'll have to > do a little more research on the issue. More importantly do the old pg_* > table still apply to the 7.4.x versions? They do. -- Markus Bertheau <twanger@bluetwanger.de>