From: Thomas Lockhart <lockhart@alumni.caltech.edu>
To: Rex Byrns <rexb@testengeer.com>
Cc: pgsql-interfaces@postgreSQL.org <pgsql-interfaces@postgreSQL.org>
Subject: Re: [INTERFACES] Auto incrementing Field
Date: Tue, 13 Apr 1999 15:15:36 +0000
Message-ID: <37135F98.C608B4F0@alumni.caltech.edu> (raw)
References: <37135900.A54047B3@testengeer.com>
> I have been porting some scripts that were written for mySQL and there
> is extensive use of an autoincrementing field. I gather that I must
> write a function to do this. Anyone have an example or better method?
Look for the serial type, or Vadim's CREATE SEQUENCE feature.
> Another question I have is about the timespan data type. I am in need
> of a way to allow users to enter time worked on specific jobs in the
> form of say 8 am thru 12:30 pm (Date inclusion is ok). I would like
> to prevent overlapping time entries. The term timespan sounds like it
> might be useful for this .
You could put a constraint on the table to get some data consistancy:
create table worksheet (
start datetime,
stop datetime,
check ((stop-start) > '0 sec')
);
postgres=> insert into worksheet values ('now', 'tomorrow');
INSERT 409771 1
postgres=> insert into worksheet values ('now', 'yesterday');
ERROR: ExecAppend: rejected due to CHECK constraint $1
But that doesn't help you with the problem of the same worker trying
to charge the same time of day to two jobs (which is probably what you
were asking in the first place).
istm that you could use triggers and the SPI interface to do a query
on the table before doing the insert. Or you could enforce this rule
in your application, doing an explicit query before trying the insert.
One nice thing about doing the explicit check in your app is that you
can be more helpful or specific when telling the user about the error.
- Tom
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: lockhart@alumni.caltech.edu, rexb@testengeer.com
Subject: Re: [INTERFACES] Auto incrementing Field
In-Reply-To: <37135F98.C608B4F0@alumni.caltech.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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