On Monday 01 November 2004 16:13, Ian Barwick wrote: > On Mon, 1 Nov 2004 09:59:44 -0800, John Fabiani <[EMAIL PROTECTED]> wrote: > > 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 > > Unfortunately the guru certificate is still "in the post", but below > is a nasty kludge which might be going in the general direction you > want: > > SELECT c.column_name AS fieldname, > c.data_type AS fieldtype, > COALESCE(i.indisprimary,FALSE) AS is_pkey > FROM information_schema.columns c > LEFT JOIN information_schema.key_column_usage cu > ON (c.table_name=cu.table_name AND c.column_name=cu.column_name) > LEFT JOIN pg_class cl ON(cl.relname=cu.table_name) > LEFT JOIN pg_index i ON(cl.oid= i.indrelid) > WHERE c.table_name='insert_tablename_here' > > Caveats: > - this is _not_ schema-aware. > - requires the information schema, e.g. 7.4 and later > - might just be horribly wrong anyway, but you get the general idea ;-) > God bless you! It works as expected. But is it possible to create a SQL statement using only the pg files. This will allow it to be used with 7.3.x and later. I have been trying for a full day. Actually, I really need to understand the relationship between the pg files. Is there a description somewhere??? From the bottom of my heart thanks. John John
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
