Hi,
First I'm trying to move a MySQL database to Postgres. I have to emulate a
MySQL sql statement - ''Describe tablename' which in general is '\d
tablename' from psql. If I use '-E' my 7.3.x provides three sql statements
and by 7.4.x produces four statements. But what I want is a single SQL
statement that produces the following:
------------------------------
fieldname | field type | isPK
-----------------------------------
clientid int true
last char false
first char false
The following will give me columns 1 and 2 but not 3
SELECT c.oid,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
And this sort of gets the PK (does not provide the actual field name) where
the oid is the one from the above SQL statement.
SELECT c2.relname, i.indisprimary, i.indisunique,
pg_catalog.pg_get_constraintdef(i.indexrelid) \
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i \
WHERE c.oid = %s AND c.oid = i.indrelid AND i.indexrelid = c2.oid \
AND i.indisprimary =TRUE \
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
How can I get this done??????? Is it possible?????
John