Skip site navigation (1) Skip section navigation (2)

moving from MySQL to Postgres

From: John Fabiani <jfabiani(at)yolo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: moving from MySQL to Postgres
Date: 2004-10-31 16:26:51
Message-ID: 200410310826.51929.jfabiani@yolo.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


Responses

pgsql-novice by date

Next:From: Markus BertheauDate: 2004-10-31 16:35:08
Subject: Re: moving from MySQL to Postgres
Previous:From: Michael FuhrDate: 2004-10-31 14:04:47
Subject: Re: using palloc in extension functions in c

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group