Re: inserting values like in mySQL

Hi :)

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

Yeh, it is a little bit different there.

>In mySQL it was like this:
>insert into users values ('','peter','my_pass');

This should've given you errors actually, I just tried it with mysql and it
came up with an error.
Anyway...

>In PostGreSQL this does not work. The only thing that works is
>insert into users (name,pass) values ('peter','my_pass');

This is the easiest way to write it, it's not the only thing that'll work
though :)

>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.

The only thing you have to change is your SQL, your basic table structure
won't change. The main difference you have to worry about is the
auto_increment to serial datatype conversion, PostgreSQL has everything
else (text,int,bool, etc) that mysql does...

>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?

You could use NULL instead of ''. I'm not sure if this will help much
though, especially for those columns with 'NOT NULL' defined.

Use the column names in your SQL, it makes sense (both logically and for
debugging), then if you add things in later, you don't have to worry about
the values being put in the wrong places if you change your table structure.

HTH
----------------------
Chris Smith
http://www.squiz.net/