Page 1 of 1

inserting table into postgres

Posted: Tue Mar 16, 2004 7:48 am
by forgodsake8
Hi,
I'm trying to insert some tables into a postgres database, but i seem to be having some problems.
I've inserted a table called advert, which is laid out as follows:

advert_id - (PK)
username
mod_book_id (this links to the another table called module_books on the tables primary key called "id")
asking_price
condition
date_added

ive made the advert_id the primary key, but do i need to make the mod_book_id the foreign key? If I don't, will this cause problems when retrieving data?
i'm doing this in postgress and have already inserted the table using the following:

CREATE table advert
(advert_id int(8),
username varchar(20),
mod_book_id int(8),
asking_price int(6),
conidition varchar(20),
date_added TIMESTAMP,
PRIMARY KEY(advert_id) );

i think I need to make the advert_id an autonumber, and i want the data_added filed to automatically be entered when someone places an advert.
I'm doing this via linux and its command driven, im not sure how to make these above changes to my already existing tables.

Can anyone help?

thanks.
lou

Posted: Tue Mar 16, 2004 10:03 am
by CoderGoblin
Generally I tend to use the web tool phpPgAdmin
http://phppgadmin.sourceforge.net/ for a lot of my DB admin. It helps to have a user interface :wink:.

If you have no existing data then you can use

DROP SEQUENCE id_seq;
CREATE sequence id_seq;

DROP TABLE orders;
CREATE TABLE orders (
advent_id integer PRIMARY KEY DEFAULT nextval('id_seq'::text),
mod_book_id int4 REFERENCES module_books (id),
username varchar(20),
asking_price int(6),
condition varchar(20),
date_added TIMESTAMP DEFAULT now()
);

While the references are useful and you can ensure database integrity (ON DELETE CASCADE for example) you can still perform information retrieval without it.

If you have data rename the table, create as new and then insert from the old table.

More information if you did not know can be found at http://www.postgresql.org/

Hopes this gives you a starting point...