Thread: ...

Hello.

Before  SERIAL  type appeared I used INTEGER, so it was easy
to add instance which is stored not in one table. Now when I
have  a  primary key of SERIAL type I do not know how to add
data to multiple tables (and even to one table too :)

For instance I have:
create table companies(
 id serial primary key,
 ...
);
create table ccats(
 cid integer not null references companies(id),
 catid integer not null references categories(id)
);

What do I have to put instead of ??:
insert into companies values (??, ...);
insert into ccats (??, 5);
...
insert into ccats (??, 7);

Andrey.

At 12:52 +0200 on 27/01/1999, You wrote:


> For instance I have:
> create table companies(
>  id serial primary key,
>  ...
> );
> create table ccats(
>  cid integer not null references companies(id),
>  catid integer not null references categories(id)
> );
>
> What do I have to put instead of ??:
> insert into companies values (??, ...);
> insert into ccats (??, 5);
> ...
> insert into ccats (??, 7);

Serial is in fact an integer, but it gets its default value from a sequence
generator.

As a general rule, don't use INSERT statement without saying each of the
fields to which you want to enter a value. So, instead of using

INSERT INTO companies VALUES (....); -- Bad

Use

INSERT INTO companies (id, field2, field3...) VALUES (....); -- Better

Now it's very simple. All you have to do is not insert anything into the ID
field:

INSERT INTO companies (field2, field3...) VALUES (...); -- Best.

Now, once you have inserted a row into the companies table, a number was
generated for it automatically. As long as you are still connected to the
database in the same connection, you can access this number via the
function currval(); This function needs the name of the sequence generator
as a parameter. To know the name of the sequence generator, do a \d after
you create the companies table. For example, currval( 'id_seq' ) will give
you the last number generated in the current session for the sequence
generator 'id_seq'.

So, the inserts for the ccats table become:

INSERT INTO ccats (cid, catid) VALUES (currval( 'name of seq' ), 5 );


Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

On Wed, 27 Jan 1999, ������ ������� wrote:

> Hello.
>
> Before  SERIAL  type appeared I used INTEGER, so it was easy
> to add instance which is stored not in one table. Now when I
> have  a  primary key of SERIAL type I do not know how to add
> data to multiple tables (and even to one table too :)

If you have a column of SERIAL type, you have the option of not
specifying it in the column-list part of your INSERT statement.
PostgreSQL will add the next available integer to the serial type.

So if you create a table like so:

create table companies (
    co_id serial,
    co_name text);

You can INSERT like so:

insert into companies (co_name) values ('Microsoft');
insert into companies (co_name) values ('Netscape');

and assume that PostgreSQL will assign the lowest available integer
to your co_id field.

Note that you CAN specify a co_id when you do an insert, if you want to.

Brian