Re: A couple of newbie questions ...

On 23/07/2008 10:48, admin wrote:
> So anyway, life story aside, I have a couple of very newbie questions
> after tinkering with PostgreSQL 8.1.9 for a day converting some
> PHP/MySQL code:

Hi there,

You should consider upgrading to 8.3 if you can - there are significant
performance improvements.

> 1. Is a SEQUENCE what I use instead of auto_increment?

Yes. The easiest thing is to define the column as type SERIAL - this
will create the sequence for you and associate it with the column.
Alternatively, you can create the sequence by hand, create the column as
an integer type, and then set the default for the column as
nextval('<sequence name>'). Have a look in the docs for the gory details:

http://www.postgresql.org/docs/8.3/static/datatype-numeric.html#DATATYPE-SERIAL

> 2. Does this work in PostgreSQL:
>
> INSERT INTO table VALUES ('x','y','z')
>
> or do I need to do this
>
> INSERT INTO table (fld_x,fld_y,fld_z) VALUES ('x','y','z')

I personally tend to use the latter for safety, but the former will work
too as long as the columns have been defined in the order you're expecting.

> 3. Does this work in PostgreSQL:
>
> INSERT INTO table VALUES ('','y','z')
>
> where the empty first item is intended for an auto_increment/SEQUENCE id
> field?

No - this will try to stuff a string value (here, an empty string) into
an integer column, which will cause an error.

If you've defined the first column to take a default value from a
sequence (as I described above), then just leave it out of the INSERT
statement altogether - this will mean you have to use a column list:

   INSERT INTO table (fld_y, fld_z) VALUES ('y', 'z');

HTH,

Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------