Re: [GENERAL] Re:PostgreSQL and PHP

sheila bel wrote:
>
> but I'm having a problem when I want to insert data into
> a table that has type serial in it.
>
> agency(id serial, agency_name text, street_addr text, ...)
>
> $result = pg_Exec($conn, "INSERT INTO agency VALUES
> ('nextval(\'agency_agencyid_seq\'),','$agency_name','$street_addr')");
>
> Warning: PostgresSQL query failed: ERROR: Cannot insert a duplicate key into
> a unique index in
> /home/httpd/html/tests/T2.php3 on line 57

This from a non-PHP user:

1) If you have triggers firing on the server, and one of *those*
tries to insert a duplicate key, it can appear from the client side as
if you'd inserted a dup into agency.

2) The name of your sequence looks fishy. I thought it would've been
named 'agency_id_seq' if it came from a serial...

% psql -d emsdb -c "create table agency(id serial, agency_name text,
street_addr text)"
NOTICE: CREATE TABLE will create implicit sequence 'agency_id_seq'
for SERIAL column 'agency.id'
NOTICE: CREATE TABLE/UNIQUE will create implicit index
'agency_id_key' for table 'agency'
CREATE

3) The nicest thing about serials is that you really don't need to
specify the serial column at all if you don't need the value
immediately in the client as a foreign key...

% psql -d emsdb -c "insert into agency (agency_name, street_addr)
values ('MyAgency1', 'MyAddr1')"
INSERT 3878944 1

% psql -d emsdb -c "insert into agency (agency_name, street_addr)
values ('MyAgency2', 'MyAddr2')"
INSERT 3878976 1

% psql -d emsdb -c "select * from agency"
id|agency_name|street_addr
--+-----------+-----------
1|MyAgency1 |MyAddr1
2|MyAgency2 |MyAddr2
(2 rows)

Cheers,
Ed Loehr