Table locks

From: Jake Stride <nsuk(at)users(dot)sourceforge(dot)net>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Table locks
Date: 2004-09-09 15:10:23
Message-ID: BD6630EF.3346%nsuk@users.sourceforge.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I have a need to have an auto increasing field, but I don't think I can use
the serial type.

I have a table similar to:

Jobs
====

Id (bigserial/unique)
Name(varchar)
Companyid(bigint)
Jobno(bigint)

Primary Key: name/companyid

Now what I want is to increase the job number by 1 every time I do an
insert. But it must effectively be a sequence for each companyid in the
table for example:

1/a job/1/1
2/another job/1/2
3/a job/2/1
4/something else/1/3
5/yet one more/2/2

There shouldn't be any gaps in the sequence jobno sequence either. Is there
an easy way to do this?

I thought of doing:

SELECT max(jobno) from jobs where companyid=1;

Then use the result as the job number, is there a way I can lock the table
to do this or a better way of achieving the required result.

Thanks

Jake

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Jake Stride 2004-09-09 15:55:18 Re: Table locks
Previous Message Sean Davis 2004-09-09 14:24:34 Re: how to overwrite tuples in a table