Обсуждение: Calling on all SQL guru's
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
On Mon, 1 Nov 2004 09:59:44 -0800, John Fabiani <jfabiani@yolo.com> 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 ;-)
HTH
Ian Barwick
barwick@gmail.com
On Monday 01 November 2004 16:13, Ian Barwick wrote: > On Mon, 1 Nov 2004 09:59:44 -0800, John Fabiani <jfabiani@yolo.com> 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
On Mon, Nov 01, 2004 at 05:34:21PM -0800, John Fabiani wrote: > 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??? Yes, see the "System Catalogs" section in the "Internals" chapter of the documentation. http://www.postgresql.org/docs/7.4/static/catalogs.html -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "On the other flipper, one wrong move and we're Fatal Exceptions" (T.U.X.: Term Unit X - http://www.thelinuxreview.com/TUX/)