pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAdding foreign key constraint post table creation
2+ messages / 2 participants
[nested] [flat]
* Adding foreign key constraint post table creation
@ 2002-12-09 16:11 Charles Hauser <chauser@duke.edu>
0 siblings, 1 reply; 2+ messages in thread
From: Charles Hauser @ 2002-12-09 16:11 UTC (permalink / raw)
To: pgsql-sql
All,
A couple of novice questions:
I would like to modify an existing TABLE by addinga new column (FOREIGN
KEY):
type_id int not null,
foreign key (type_id) references cvterm (cvterm_id),
Will this work ( running PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled
by GCC 2.96):
ALTER TABLE contig ADD COLUMN type_id int;
ALTER TABLE contig ADD CONSTRAINT cvtermfk FOREIGN KEY (type_id)
references cvterm (cvterm_id);
********
I would like to load data into the table below from a file lacking the
timestamp fields, where the file structure is:
COPY table FROM STDIN;
1 feature_type types of features \N
2 3'-exon \N 1
.
.
.
\.
This fails as the timestamp fields are 'not null'. Othere than
generating INSERT stmts for the data how else could I enter the data?
create table cvterm (
cvterm_id serial not null,
primary key (cvterm_id),
termname varchar(255) not null,
termdefinition text,
termtype_id int,
foreign key (termtype_id) references cvterm (cvterm_id),
timeentered timestamp not null default current_timestamp,
timelastmod timestamp not null default current_timestamp,
unique(termname, termtype_id)
);
regards,
Charles
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Adding foreign key constraint post table creation
@ 2002-12-11 09:58 Richard Huxton <dev@archonet.com>
parent: Charles Hauser <chauser@duke.edu>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2002-12-11 09:58 UTC (permalink / raw)
To: Charles Hauser <chauser@duke.edu>; pgsql-sql
On Monday 09 Dec 2002 4:11 pm, Charles Hauser wrote:
> All,
>
> A couple of novice questions:
>
>
> I would like to modify an existing TABLE by addinga new column (FOREIGN
> KEY):
>
> type_id int not null,
> foreign key (type_id) references cvterm (cvterm_id),
>
>
> Will this work ( running PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled
> by GCC 2.96):
You'll need to check your manuals for 7.1.3 - look in the SQL command
reference under ALTER TABLE. It should work with current versions but I don't
have v7.1.3 to hand.
> I would like to load data into the table below from a file lacking the
> timestamp fields, where the file structure is:
>
> COPY table FROM STDIN;
> 1 feature_type types of features \N
> 2 3'-exon \N 1
>
> This fails as the timestamp fields are 'not null'. Othere than
> generating INSERT stmts for the data how else could I enter the data?
You could process the file with perl/awk etc. and add the required timestamps.
I don't think COPY substitutes default values for you.
--
Richard Huxton
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-12-11 09:58 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-09 16:11 Adding foreign key constraint post table creation Charles Hauser <chauser@duke.edu>
2002-12-11 09:58 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox