foreign keys - script

What version are you using? It wasn't implemented until 7.0
and the script you gave appeared to basically work on my 7.0.2
machine. (At least the fk triggers were in pg_trigger after
running it.)

Stephan Szabo
sszabo@bigpanda.com

On Tue, 29 Aug 2000, Adam Lang wrote:

Nope, I'm running 6.5. Besides upgrading to 7 (I tried once and it was a
pain in the ass. I'm running RedHat6.2 and I only had about a million
dependencies to update also... one which I never got working...), what can I
do? Technically I really don't need the references, just a nice addition.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Adam Lang" <aalang@rutgersinsurance.com>
Cc: "PGSQL General" <pgsql-general@postgresql.org>
Sent: Tuesday, August 29, 2000 1:50 PM
Subject: Re: [GENERAL] foreign keys - script

On Tue, Aug 29, 2000 at 01:12:05PM -0400, Adam Lang wrote:

I'm also having a problem with my references.

In the script below, I get this error after each references statement:
NOTICE: CREATE TABLE/FOREIGN KEY clause ignored; not yet implemented

Any ideas?

Upgrade to the current PostgreSQL:

test=# create table category (
test(# category_id char(5) primary key,
test(# name varchar(20));
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
'category_pkey' for table 'category'
CREATE
test=#
test=# create table parts (
test(# part_id char(10) primary key,
test(# category_id char(5) references category
test(# on delete cascade,
test(# name varchar(20),
test(# price numeric(7,2));
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'parts_pkey'
for table 'parts'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
test=#

As to your other question, regarding LEFT JOIN: that's 'not yet
implemented'. IF you don't have too many of them, or their not too complicated,
you can work around it with a UNION, such as:

SELECT a,b from foo,bar where foo.b = bar.b
UNION
SELECT a,NULL from foo where foo.b is null;

That gives you a LEFT JOIN on foo and bar on field 'b'.

There's currently some discussion going on on HACKERS concerning what
will be needed to make OUTER JOINs happen for 7.1 (current talk is
release sometime in November)

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

i created my foreign keys afterwards using a statement like this:

Alter table web_hits add constraint fk_webh_wu_id
foreign key (wu_id) references web_users(wu_id);

HTH
mikeo

At 01:12 PM 8/29/00 -0400, Adam Lang wrote:

This question was me actually, and thanks for the info! Is the
development list archived somewhere publicly accessible?

thanks,
-emile

On Tue, 29 Aug 2000, Ross J. Reedstrom wrote:

Hmm... if you have (can get your hands on) the contrib directory from 6.5,
there was a refint package you could work with (although you have to make
triggers manually i think). I remember people saying there were bugs in
it, but I never played with it so I don't know any details, but there's
probably something in the archives about it.

Stephan Szabo
sszabo@bigpanda.com

On Tue, 29 Aug 2000, Adam Lang wrote:

Sorry for the off topic question, but what is a "contrib". I see it a lot
in the rpm directories. (Yes, relatively new to Linux also :P)

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Adam Lang" <aalang@rutgersinsurance.com>
Cc: "PGSQL General" <pgsql-general@postgresql.org>
Sent: Tuesday, August 29, 2000 3:17 PM
Subject: Re: [GENERAL] foreign keys - script

Hmm... if you have (can get your hands on) the contrib directory from 6.5,
there was a refint package you could work with (although you have to make
triggers manually i think). I remember people saying there were bugs in
it, but I never played with it so I don't know any details, but there's
probably something in the archives about it.

Stephan Szabo
sszabo@bigpanda.com

On Tue, 29 Aug 2000, Adam Lang wrote:

Nope, I'm running 6.5. Besides upgrading to 7 (I tried once and it was

a

pain in the ass. I'm running RedHat6.2 and I only had about a million
dependencies to update also... one which I never got working...), what

can I

do? Technically I really don't need the references, just a nice

addition.

As for using 7.02, I had tried installing it from scratch, but had
dependency problems. I attempted an "upgrade" just now, and here is the
list of dependencies failures I had:

For Server:
postgresql = 7.0.2 is needed by postgresql-server-7.0.2-10
libncurses.so.5 is needed by postgresql-server-7.0.2-10
libpq.so.2.1 is needed by postgresql-server-7.0.2-10
libreadline.so.4.1 is needed by postgresql-server-7.0.2-10
libc.so.6(GLIBC_2.2) is needed by postgresql-server-7.0.2-10

And for base postgresql:
libncurses.so.5 is needed by postgresql-7.0.2-10
libreadline.so.4.1 is needed by postgresql-7.0.2-10
postgresql = 6.5.3 is needed by postgresql-devel-6.5.3-6
postgresql = 6.5.3 is needed by postgresql-server-6.5.3-6
libpq.so.2.0 is needed by postgresql-server-6.5.3-6

And this is with 6.5.3 installed.
Weird part is, I am sure libc.so.6 is installed.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Adam Lang" <aalang@rutgersinsurance.com>
Sent: Tuesday, August 29, 2000 4:29 PM
Subject: Re: [GENERAL] foreign keys - script

Generally outside things that have been contributed, but aren't
sanctioned as being part of the the "core" distribution. It could
just be untested by whoever decides what is and isn't core, or
in postgres' case, often functionality they felt wasn't necessary
to most people (like extra array operators).

On Tue, 29 Aug 2000, Adam Lang wrote:

Sorry for the off topic question, but what is a "contrib". I see it a

lot

"Adam Lang" <aalang@rutgersinsurance.com> writes:

As for using 7.02, I had tried installing it from scratch, but had
dependency problems. I attempted an "upgrade" just now, and here is the
list of dependencies failures I had:

Seems like grabbing the source distribution and doing "configure; make;
make install" would be a lot easier ;-)

regards, tom lane

But wouldn't the dependencies be there whether I compile from scratch or use
an RPM?

What the main issue that stumped was the libc.6.so dependency. According to
epm -a -q, it is installed.

I guess I can blow out 6.5 and download the 7.0 source and reinstall a
compiled version.

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Adam Lang" <aalang@rutgersinsurance.com>
Cc: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>; "PGSQL General"
<pgsql-general@postgresql.org>
Sent: Tuesday, August 29, 2000 6:47 PM
Subject: Re: [GENERAL] foreign keys - script

"Adam Lang" <aalang@rutgersinsurance.com> writes:

But wouldn't the dependencies be there whether I compile from scratch or use
an RPM?

But if you compile from source, it will use whatever libc you have
installed.

regards, tom lane