inserting table into postgres

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
forgodsake8
Forum Newbie
Posts: 9
Joined: Fri Feb 27, 2004 8:30 pm

inserting table into postgres

Post 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
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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...
Post Reply