By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,178 Members | 987 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,178 IT Pros & Developers. It's quick & easy.

Calling on all SQL guru's

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.