Re: [GENERAL] (redefinition) alter table, add foreign key constraint

[Matthew Kennedy]

[EMAIL PROTECTED] wrote:
 On Tue, 17 Oct 2000, Matthew Kennedy wrote:
  Is it possible to perform an alter table with add foreign key constraint
  in PostgreSQL? We are trying to support multiple DBMS in our project by
  keeping to a common, compatible denominator. Oracle, supports such a
  construct. Does (or might) PostgreSQL support this feature?
 Yes...
 ALTER TABLE table ADD constraint definition
 Brett W. McCoy

Firstly...

A little redefinition of my question is in order. If I try doing this:

create table things (name varchar(20), state(2));
create table states (abbreviation varchar(2));
alter table things 
add foreign key (state) 
references states (abbreviation);

Postgres responds with:

NOTICE:  ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s)
for 
FOREIGN KEY check(s)
CREATE

I am wondering what the implications of postgres using implicit triggers
for foreign key constraints are? I guess if there were no implications
(ie. implicit triggers were a perfect implimentation of foreign keyt
constraints), then there would be no warning message?


Secondly...

Postgres doesn't seem to take the name of a foreign key (it's a parse
error to include it). This is slightly different to what SQL92 and
Oracle allow. I beleive I should have been able to write:

alter table things 
add foreign key things2states (state) 
references states (abbreviation);


Postgres responds with:

ERROR:  parser: parse error at or near "things2states"

I don't really need postgres to do anything with the name of the foreign
key. I would like it to ignore it, rather than bawk at it. (I'm trying
to feed the output of a relational design tool to PostgreSQL without
having to write too many filters to strip out things postgres doesn't
like). Perhaps I just have the syntax wrong?

Thanks and much appreciated,

Matt

[GENERAL] Ignore when using COPY FROM

[Matthew Kennedy]

I have a ton of data in a text delimited file from an old legacy system.
When uploading it into postgres, I'd do something like this:

COPY stuff FROM 'stuff.txt' USING DELIMITERS = '|';

The problem is some of the rows in stuff.txt may not conform to the
stuff table attributes (duplicate keys, for example). The command above
terminates with no change to the table stuff when it encounters an error
in stuff.txt. Is there a way to have postgres ignore erroneous fields
but keep information about which fields weren't processed. I believe
Oracle has some support for this through an IGNORE clause.