Re: inserting values like in mySQL

Peter Asemann wrote:
>
> Hi there!
>
> We (me and others from my group) have to convert mySQL syntax to
> PostGreSQL, and incidentally we had some problems.
>
> We have a table named users with ID, name, pass as columns.
>
> In mySQL we had the column "ID" set to auto-increment. It took us some
> time to find out how to use the "serial" feature ;-)
>
> In mySQL it was like this:
>
> insert into users values ('','peter','my_pass');
>
> In PostGreSQL this does not work. The only thing that works is
>
> insert into users (name,pass) values ('peter','my_pass');
>
> Apparently this is longer, and we'll have tables with much more columns,
> so we'll have to write much more than in mySQL, and as we're lazy people
> (all programmers are, Larry Wall says), we don't want to write a single
> character more than necessary.
>
> Is there a way to set all columns without explicitly giving their
> names? Isn't there something to indicate that the value we give to the
> database is only a dummy like the '' in mySQL?

Having created the column with 'SERIAL', PostgreSQL will actually create a sequence
called table_column_seq, and will define the column as "DEFAULT
nextval('table_column_seq')" so you can fake what it does by entering the same
default into your insert as PostgreSQL has put on the column, viz:

insert into users values( nextval('users_ID_seq'), 'peter','my_pass');

Hope this helps,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew(at)catalyst(dot)net(dot)nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64(21)635-694, Fax: +64(4)499-5596, Office: +64(4)499-2267xtn709