Re: problem inserting with sequence

On Thu, Jul 28, 2005 at 05:32:03AM -0700, germ germ wrote:
> I have been trying to figure out why I can't insert
> into a table and I think it has something to do with
> the sequnce.
>
> I am able to use able to properly insert into the
> table using the the shell, but I am not able to insert
> using a php script:
>
> INSERT INTO requests (time_stamp, req_num,
> recommended_by) VALUES (now(),
> nextval('requests_req_num_seq'), 'foo');

What happens when you try the insert? We need more details than
just "it doesn't work." If there's an error then it should be in
the postmaster logs, and it should also be available to the PHP
script.

What's different between the situation that works and the one that
doesn't? Are you connecting as different users? In the code you
posted I don't see any permissions being granted on the sequence,
so nextval() might be failing with "permission denied for sequence";
another possibility is that the sequence name is wrong (see below).

> Here is the schema I'm using:
> DROP SEQUENCE requests_req_num_seq;
> DROP TABLE requests;
>
> CREATE SEQUENCE requests_req_num_seq INCREMENT BY 1
> START WITH 1000;
>
> CREATE TABLE requests (
> time_stamp timestamp PRIMARY KEY DEFAULT 'now',

A timestamp is a poor choice for a primary key because it's not
unique; aside from that you've defined the default to be a constant --
run "\d requests" in psql and you'll see what I mean. See the
following section of the documentation for more info:

http://www.postgresql.org/docs/7.4/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

> req_num integer DEFAULT
> nextval('acq_requests_req_num_seq') NOT NULL,

This sequence name doesn't match the name of the sequence you created,
at least not the one you showed. Is there an acq_requests_req_num_seq
sequence?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/