Thread: inserting values like in mySQL

inserting values like in mySQL

From

Peter Asemann

Date:

22 May 2001, 15:32:17

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?

Hope you got the point... maybe this question is really stupid and I
managed to overlook the hints written in 20-pixels height on page one of
the "PostGreSQL manual for complete morons" dealing with this problem ;-)

Thanks for reading,

---------------------------------------------------------
Peter Asemann                 unrzb8@rrze.uni-erlangen.de

Re: inserting values like in mySQL

From

"Giorgio A."

Date:

22 May 2001, 17:32:08

i'll answer only to a part of your question, since i'm not sure of the other
part:

you can create a SEQUENCE with something like:

CREATE SEQUENCE "name_of_the_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1  cache 1

and then add a column like:

"column_name" int4 DEFAULT nextval('name_of_the_seq'::text) NOT NULL

this column will be auto-incrementing, so when you do a INSERT you won't
have to pass any parameter for to this column.

hope this helped you,
Giorgio A.


----- Original Message -----
From: "Peter Asemann" <Peter.Asemann@rrze.uni-erlangen.de>
To: <pgsql-novice@postgresql.org>
Sent: Friday, May 18, 2001 6:03 PM
Subject: [NOVICE] inserting values like in mySQL


> 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?
>
> Hope you got the point... maybe this question is really stupid and I
> managed to overlook the hints written in 20-pixels height on page one of
> the "PostGreSQL manual for complete morons" dealing with this problem ;-)
>
> Thanks for reading,
>
> ---------------------------------------------------------
> Peter Asemann                 unrzb8@rrze.uni-erlangen.de
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl

Re: inserting values like in mySQL

From

Nabil Sayegh

Date:

22 May 2001, 21:09:18

On 18 May 2001 18:03:25 +0200, Peter Asemann wrote:
> 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');


Yes, thats not nice in postgresql. But there is a workaround.
Put the 'default' fields at the end:

---------------8<---------------------
CREATE TEMP TABLE tempo (test bool, id serial);

INSERT INTO tempo values (true);

SELECT * from tempo;
---------------8<---------------------

 test | id
------+----
 t    |  1
(1 row)

Hope it helps.

P.S.: I'd really like postgresql to behave like mysql in this context
P.S.S.: Perhaps also in the GROUP BY context :)

--
 Nabil Sayegh

Re: inserting values like in mySQL

From

Chris Smith

Date:

22 May 2001, 21:34:44

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/

Re: inserting values like in mySQL

From

Chris Smith

Date:

22 May 2001, 22:04:29

Hi,

> > 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');
>
>Yes, thats not nice in postgresql. But there is a workaround.
>Put the 'default' fields at the end:
>
>---------------8<---------------------
>CREATE TEMP TABLE tempo (test bool, id serial);
>
>INSERT INTO tempo values (true);
>
>SELECT * from tempo;
>---------------8<---------------------
>
>  test | id
>------+----
>  t    |  1
>(1 row)
>
>Hope it helps.
>
>P.S.: I'd really like postgresql to behave like mysql in this context

Huh? Mysql doesn't like it either (at least in 3.23). Anyway, it's not
standard.

>P.S.S.: Perhaps also in the GROUP BY context :)

Can't comment on this one, have no idea what you're talking about.

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

Re: inserting values like in mySQL

From

Chris Smith

Date:

22 May 2001, 22:24:08

Me again,

>> > 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');
>>
>>Yes, thats not nice in postgresql. But there is a workaround.
>>Put the 'default' fields at the end:
>>
>>---------------8<---------------------
>>CREATE TEMP TABLE tempo (test bool, id serial);
>>
>>INSERT INTO tempo values (true);
>>
>>SELECT * from tempo;
>>---------------8<---------------------
>>
>>  test | id
>>------+----
>>  t    |  1
>>(1 row)
>>
>>Hope it helps.
>>
>>P.S.: I'd really like postgresql to behave like mysql in this context
>
>Huh? Mysql doesn't like it either (at least in 3.23). Anyway, it's not
>standard.

What I meant was...
You can do it, just not with int or serial values (try it with a text,
varchar, char, bool field), or any field with the UNIQUE constraint. Same
as mysql, you can't insert '' into an int field or an auto_increment field.

Bye again :)

Re: inserting values like in mySQL

From

Nabil Sayegh

Date:

23 May 2001, 00:36:28

On 23 May 2001 09:24:02 +1000, Chris Smith wrote:
> What I meant was...
> You can do it, just not with int or serial values (try it with a text,
> varchar, char, bool field), or any field with the UNIQUE constraint. Same
> as mysql, you can't insert '' into an int field or an auto_increment field.
>
> Bye again :)

And what I meant was: When inserting NULL, or skipping a field =->
default.
But as you stated: sometimes it is desireable to insert NULL

BTW:
What I want, and what is good for me needn't be the same ;)
Using proprietary extensions isn't very portable.

--
 Nabil Sayegh

Re: inserting values like in mySQL

From

Andrew McMillan

Date:

23 May 2001, 10:35:03

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@catalyst.net.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

Re: inserting values like in mySQL

From

Nabil Sayegh

Date:

28 May 2001, 13:49:43

On 18 May 2001 18:03:25 +0200, Peter Asemann wrote:
> 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');


Yes, thats not nice in postgresql. But there is a workaround.
Put the 'default' fields at the end:

---------------8<---------------------
CREATE TEMP TABLE tempo (test bool, id serial);

INSERT INTO tempo values (true);

SELECT * from tempo;
---------------8<---------------------

 test | id
------+----
 t    |  1
(1 row)

Hope it helps.

P.S.: I'd really like postgresql to behave like mysql this context
P.S.S.: Perhaps also in the GROUP BY context :)

--
 Nabil Sayegh