admin wrote:
> I'm convinced that PostgreSQL's performance is not an issue (both
> because it's improved and traffic will be relatively low anyway)
It's really rather solid in performance terms anyway, especially for
non-trivial workloads where data consistency and reliability are important.
> 1. Is a SEQUENCE what I use instead of auto_increment?
Yes. It's not quite the same, in that a sequence may have gaps if a
transaction acquires a value from the sequence and then rolls back (due
to disconnect, explicit ROLLBACK, etc).
> 2. Does this work in PostgreSQL:
>
> INSERT INTO table VALUES ('x','y','z')
Yes, but it's not recommended because it'll break if you add fields to
`table' or re-order fields.
> or do I need to do this
>
> INSERT INTO table (fld_x,fld_y,fld_z) VALUES ('x','y','z')
The above is preferable.
> 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. You are trying to insert the empty string into an integer auto
increment field, which is nonsensical and will be rejected.
Use DEFAULT, or omit the field.
INSERT INTO table VALUES (DEFAULT,'y','z')
or
INSERT INTO table (fld_y,fld_z) VALUES ('y','z')
which is really doing:
INSERT INTO table (fld_x, fld_y,fld_z) VALUES (DEFAULT, 'y','z')
You could also explicitly acquire a value from the sequence:
INSERT INTO table (fld_x, fld_y,fld_z) VALUES (nextval('table_id_seq'),
'y','z')
... but there's not really any point. DEFAULT or just omitting the field
are much better options.
--
Craig Ringer