Calling on all SQL guru's

[John Fabiani]

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

[Ian Barwick]

On Mon, 1 Nov 2004 09:59:44 -0800, John Fabiani <jf******@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
ba*****@gmail.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Ian Barwick]

On Mon, 1 Nov 2004 09:59:44 -0800, John Fabiani <jf******@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
ba*****@gmail.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[John Fabiani]

On Monday 01 November 2004 16:13, Ian Barwick wrote:

On Mon, 1 Nov 2004 09:59:44 -0800, John Fabiani <jf******@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[John Fabiani]

On Monday 01 November 2004 16:13, Ian Barwick wrote:

On Mon, 1 Nov 2004 09:59:44 -0800, John Fabiani <jf******@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Alvaro Herrera]

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/)
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Alvaro Herrera]

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/)
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster