Обсуждение: 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...
>


*****> Sorry I didn't type the table correctly, it is :
agency(agency_id serial,....) thats why i get the fishy sequence name.


>% 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)

*****> Well, I did try doing this but for some reason its expecting
a value for the agency_id and I get an error message. Maybe it has
something to do with PHP ?

by the way what does the -d emsdb -c mean ?

I just connect to my database using >psql mydatabase
then I do inserts or updates. If I want to connect to another
database I do >-c anotherdb.

-Sheila


______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

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
> >
> >2)  The name of your sequence looks fishy.  I thought it would've been
> >named 'agency_id_seq' if it came from a serial...
>
> *****> Sorry I didn't type the table correctly, it is :
> agency(agency_id serial,....) thats why i get the fishy sequence name.

Still fishy.  Looks like you must have used 'agencyid'? (no
underscore)

> >% 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'
> >
> >3)  ...you really don't need to specify the serial column...
> >
> >insert into agency (agency_name, street_addr) values ('MyAgency1', 'MyAddr1')
> >insert into agency (agency_name, street_addr) values ('MyAgency2', 'MyAddr2')
> >
> >% psql -d emsdb -c "select * from agency"
> >id|agency_name|street_addr
> >--+-----------+-----------
> >  1|MyAgency1  |MyAddr1
> >  2|MyAgency2  |MyAddr2
> >(2 rows)
>
> *****> Well, I did try doing this but for some reason its expecting
> a value for the agency_id and I get an error message. Maybe it has
> something to do with PHP ?

Could be.  Might also come from not naming the insert columns, which
breaks miserably if you add new columns with default values in the
future...

    insert into agency values ('MyAgency2', 'MyAddr2')"
vs.
    insert into agency (agency_name, street_addr) values ('MyAgency2',
'MyAddr2')"

> by the way what does the -d emsdb -c mean ?

% man psql

Cheers,
Ed Loehr