Re: moving from MySQL to Postgres

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(at)bluetwanger(dot)de>