From krisallsop@btinternet.com Sat Jun 22 10:36:12 2002 Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 26192475F24 for ; Sat, 22 Jun 2002 06:36:12 -0400 (EDT) Received: from rhenium.btinternet.com (rhenium.btinternet.com [194.73.73.93]) by postgresql.org (Postfix) with ESMTP id C1E544758A2 for ; Sat, 22 Jun 2002 06:34:41 -0400 (EDT) Received: from host213-123-38-48.in-addr.btopenworld.com ([213.123.38.48] helo=graveland) by rhenium.btinternet.com with smtp (Exim 3.22 #8) id 17LiE7-0000rZ-00 for pgsql-sql@postgresql.org; Sat, 22 Jun 2002 11:34:43 +0100 Message-ID: <009201c219d7$c6605420$30267bd5@graveland> Reply-To: "Kris" From: "Kris" To: Subject: Obtaining primary key information from pg system tables Date: Sat, 22 Jun 2002 11:30:04 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_008F_01C219E0.27921910" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200206/309 X-Sequence-Number: 8274 This is a multi-part message in MIME format. ------=_NextPart_000_008F_01C219E0.27921910 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I am trying to generate some xml metadata from the pg system tables for my = java code generation utility. I have can get most information, but i would = like to be able to tell which column is a primary key or which one is a for= eign key (i dont need to know which table the foreign key references at thi= s point). Which tables do i need reference, could anyone provide an example query? Kris ------=_NextPart_000_008F_01C219E0.27921910 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
I am trying to generate some xml metadata = from the=20 pg system tables for my java code generation utility. I have can get most= =20 information, but i would like to be able to tell which column is a primary = key=20 or which one is a foreign key (i dont need to know which table the foreign = key=20 references at this point).
 
Which tables do i need reference, could an= yone=20 provide an example query?
 
Kris
------=_NextPart_000_008F_01C219E0.27921910-- From dim@bigpond.net.au Sat Jun 22 10:43:50 2002 Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id A19CC475A38 for ; Sat, 22 Jun 2002 06:43:50 -0400 (EDT) Received: from mta05ps.bigpond.com (mta05ps.bigpond.com [144.135.25.137]) by postgresql.org (Postfix) with ESMTP id 1A1DE475987 for ; Sat, 22 Jun 2002 06:43:48 -0400 (EDT) Received: from hobbes ([144.135.25.72]) by mta05ps.bigpond.com (Netscape Messaging Server 4.15 mta05ps Apr 29 2002 13:22:02) with SMTP id GY3SH000.DNR; Sat, 22 Jun 2002 20:43:48 +1000 Received: from CPE-144-136-0-9.vic.bigpond.net.au ([144.136.0.9]) by PSMAM02.mailsvc.email.bigpond.com(MailRouter V3.0n 74/15324790); 22 Jun 2002 20:43:48 Message-ID: <006801c219d9$ba0b3710$fe00a8c0@hobbes> From: "Dmitri Colebatch" To: "Kris" , References: <009201c219d7$c6605420$30267bd5@graveland> Subject: Re: Obtaining primary key information from pg system tables Date: Sat, 22 Jun 2002 20:44:03 +1000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0065_01C21A2D.8B5D4030" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2462.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2462.0000 X-Archive-Number: 200206/310 X-Sequence-Number: 8275 This is a multi-part message in MIME format. ------=_NextPart_000_0065_01C21A2D.8B5D4030 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Kris, I'm assuming you dont want to do this, but just in case you're not aware of= the option - the JDBC API exposes this information.=20=20 hth dim ----- Original Message -----=20 From: Kris=20 To: pgsql-sql@postgresql.org=20 Sent: Saturday, June 22, 2002 8:30 PM Subject: [SQL] Obtaining primary key information from pg system tables Hi, I am trying to generate some xml metadata from the pg system tables for m= y java code generation utility. I have can get most information, but i woul= d like to be able to tell which column is a primary key or which one is a f= oreign key (i dont need to know which table the foreign key references at t= his point). Which tables do i need reference, could anyone provide an example query? Kris ------=_NextPart_000_0065_01C21A2D.8B5D4030 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Kris,
 
I'm assuming you dont want to do t= his, but=20 just in case you're not aware of the option - the JDBC API exposes this=20 information. 
 
hth
dim
----- Original Message -----
Fro= m:=20 Kris
To: pgsql-sql@postgresql.org
Sent: Saturday, June 22, 2002 8:30= =20 PM
Subject: [SQL] Obtaining primary k= ey=20 information from pg system tables

Hi,
 
I am trying to generate some xml metadat= a from=20 the pg system tables for my java code generation utility. I have can get = most=20 information, but i would like to be able to tell which column is a primar= y key=20 or which one is a foreign key (i dont need to know which table the foreig= n key=20 references at this point).
 
Which tables do i need reference, could = anyone=20 provide an example query?
 
Kris
------=_NextPart_000_0065_01C21A2D.8B5D4030-- From krisallsop@btinternet.com Sat Jun 22 11:13:33 2002 Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id AE827475A70 for ; Sat, 22 Jun 2002 07:13:33 -0400 (EDT) Received: from tungsten.btinternet.com (tungsten.btinternet.com [194.73.73.81]) by postgresql.org (Postfix) with ESMTP id C22AF47597F for ; Sat, 22 Jun 2002 07:13:30 -0400 (EDT) Received: from host213-123-38-48.in-addr.btopenworld.com ([213.123.38.48] helo=graveland) by tungsten.btinternet.com with smtp (Exim 3.22 #8) id 17Lipc-0005k8-00; Sat, 22 Jun 2002 12:13:29 +0100 Message-ID: <00cd01c219dd$30c26bf0$30267bd5@graveland> Reply-To: "Kris" From: "Kris" To: "Dmitri Colebatch" , References: <009201c219d7$c6605420$30267bd5@graveland> <006801c219d9$ba0b3710$fe00a8c0@hobbes> Subject: Re: Obtaining primary key information from pg system tables Date: Sat, 22 Jun 2002 12:08:50 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00CA_01C219E5.91D89290" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200206/311 X-Sequence-Number: 8276 This is a multi-part message in MIME format. ------=_NextPart_000_00CA_01C219E5.91D89290 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thanx for replying Dmitri, I am aware of the JDBC API exposing information, it might be an idea to use= both the system tables and JDBC information in the this case. The reason w= hy i want to use the system tables is for example , (in MS SQL Server and O= racle) the NVARCHAR and NVARCHAR2 datatypes are not part of the JDBC spec a= nd when using JDBC there is no way of telling these part from a VARCHAR or = VARCHAR2. I needed to do this with when writing a java based insert script = generator so i could cast the strings with the N. I was thinking the system= tables were more reliable but i dont need to use just those i guess. Kris ----- Original Message -----=20 From: Dmitri Colebatch=20 To: Kris ; pgsql-sql@postgresql.org=20 Sent: Saturday, June 22, 2002 11:44 AM Subject: Re: [SQL] Obtaining primary key information from pg system tables Kris, I'm assuming you dont want to do this, but just in case you're not aware = of the option - the JDBC API exposes this information.=20=20 hth dim ----- Original Message -----=20 From: Kris=20 To: pgsql-sql@postgresql.org=20 Sent: Saturday, June 22, 2002 8:30 PM Subject: [SQL] Obtaining primary key information from pg system tables Hi, I am trying to generate some xml metadata from the pg system tables for= my java code generation utility. I have can get most information, but i wo= uld like to be able to tell which column is a primary key or which one is a= foreign key (i dont need to know which table the foreign key references at= this point). Which tables do i need reference, could anyone provide an example query? Kris ------=_NextPart_000_00CA_01C219E5.91D89290 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Thanx for replying Dmitri,
 
I am aware of the JDBC API exposing inform= ation, it=20 might be an idea to use both the system tables and JDBC information in the = this=20 case. The reason why i want to use the system tables is for example , (in M= S SQL=20 Server and Oracle) the NVARCHAR and NVARCHAR2 datatypes are not part of the= JDBC=20 spec and when using JDBC there is no way of telling these part from a VARCH= AR or=20 VARCHAR2. I needed to do this with when writing a java based insert script= =20 generator so i could cast the strings with the N. I was thinking the system= =20 tables were more reliable but i dont need to use just those i=20 guess.
 
Kris
 
----- Original Message -----
Fro= m:=20 Dmitri= =20 Colebatch
To: Kris ; pgsql-sql@postgresql.org
Sent: Saturday, June 22, 2002 11:4= 4=20 AM
Subject: Re: [SQL] Obtaining prima= ry key=20 information from pg system tables

Kris,
 
I'm assuming you dont want to do= this,=20 but just in case you're not aware of the option - the JDBC API exposes th= is=20 information. 
 
hth
dim
----- Original Message -----
F= rom:=20 Kris
To: pgsql-sql@postgresql.org <= /DIV>
Sent: Saturday, June 22, 2002 8:= 30=20 PM
Subject: [SQL] Obtaining primary= key=20 information from pg system tables

Hi,
 
I am trying to generate some xml metad= ata from=20 the pg system tables for my java code generation utility. I have can ge= t=20 most information, but i would like to be able to tell which column is a= =20 primary key or which one is a foreign key (i dont need to know which ta= ble=20 the foreign key references at this point).
 
Which tables do i need reference, coul= d anyone=20 provide an example query?
 
Kris
------=_NextPart_000_00CA_01C219E5.91D89290--