Обсуждение: serial

I created this table:

create table se (n  serial);

 Column |  Type      |                   Modifiers
---------+---------+------------------------------------------------
 n           | integer    | not null default nextval('se_n_seq'::regclass)

I inserted two record, later select, but column n (serial) no
auto-incremented
banco=# select * from se;
 n
---
 0
 0
(2 rows)

Why?
Thanks
Gustavo

On Thu, Dec 4, 2008 at 2:42 PM, Gustavo Rosso <grosso@sadaic.org.ar> wrote:
> I created this table:
>
> create table se (n  serial);
>
in case you did just insert into se(n) values(0); twice, it won't work
if you want to add series of numbers, don't define it as serial.
Serial is for a different purpose.
if you want a series of generated numbers, please use generate_series();

so :

create table se(n int not null);
insert into se(n) select generate_series(1,100);

serial is used for different purposes. Say, you need an auto
incremented id on a row:
create table foo(
  id serial,
  name varchar(128)
);

and than:

insert into foo(name) values('Gustavo'), ('Grzegorz') returning id;

;]



hth

--
GJ

2008/12/4 Gustavo Rosso <grosso@sadaic.org.ar>:
> I created this table:
>
> create table se (n  serial);
>
> Column |  Type      |                   Modifiers
> ---------+---------+------------------------------------------------
> n           | integer    | not null default nextval('se_n_seq'::regclass)
>
> I inserted two record, later select, but column n (serial) no
> auto-incremented
> banco=# select * from se;
> n
> ---
> 0
> 0
> (2 rows)
>

what is your insert statement?

postgres=#
postgres=# create table se(n serial);
NOTICE:  CREATE TABLE will create implicit sequence "se_n_seq" for
serial column "se.n"
CREATE TABLE
postgres=# insert into se values(default);
INSERT 0 1
postgres=# insert into se values(default);
INSERT 0 1
postgres=# select * from se;
 n
---
 1
 2
(2 rows)

regards
Pavel Stehule

> Why?
> Thanks
> Gustavo
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Thu, Dec 4, 2008 at 7:42 AM, Gustavo Rosso <grosso@sadaic.org.ar> wrote:
> I created this table:
>
> create table se (n  serial);
>
> Column |  Type      |                   Modifiers
> ---------+---------+------------------------------------------------
> n           | integer    | not null default nextval('se_n_seq'::regclass)
>
> I inserted two record, later select, but column n (serial) no
> auto-incremented
> banco=# select * from se;
> n
> ---
> 0
> 0
> (2 rows)
>
> Why?

Because MySQL taught you bad habits?  You told the db to insert a 0,
so it inserted a 0.  If you told it to insert a NULL, it would proceed
to do that too.  There are several ways to have it use the serial /
sequence properly...  You can use the default keyword, or leave out
the field altogether (assuming you have > 1 field I guess), or you can
insert from the sequence yourself:

smarlowe=# create table test (i serial primary key, t text);
NOTICE:  CREATE TABLE will create implicit sequence "test_i_seq" for
serial column "test.i"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
smarlowe=# insert into test (t) values ('this is text');
INSERT 0 1
smarlowe=# insert into test (i,t) values (DEFAULT,'this is more text');
INSERT 0 1
smarlowe=# insert into test (i,t) values (nextval('test_i_seq'),'even
more text');
INSERT 0 1
smarlowe=# select nextval('test_i_seq');
 nextval
---------
       4
(1 row)

smarlowe=# insert into test (i,t) values (4,'last bit of text');
INSERT 0 1
smarlowe=# select * from test;
 i |         t
---+-------------------
 1 | this is text
 2 | this is more text
 3 | even more text
 4 | last bit of text
(4 rows)

Hope that helps.

On Thu, Dec 4, 2008 at 7:06 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Thu, Dec 4, 2008 at 7:42 AM, Gustavo Rosso <grosso@sadaic.org.ar> wrote:
>> I created this table:
>>
>> create table se (n  serial);
>>
>> Column |  Type      |                   Modifiers
>> ---------+---------+------------------------------------------------
>> n           | integer    | not null default nextval('se_n_seq'::regclass)
>>
>> I inserted two record, later select, but column n (serial) no
>> auto-incremented
>> banco=# select * from se;
>> n
>> ---
>> 0
>> 0
>> (2 rows)
>>
>> Why?
>
> Because MySQL taught you bad habits?  You told the db to insert a 0,
> so it inserted a 0.  If you told it to insert a NULL, it would proceed
> to do that too.  There are several ways to have it use the serial /
> sequence properly...  You can use the default keyword, or leave out
> the field altogether (assuming you have > 1 field I guess), or you can
> insert from the sequence yourself:
>
> smarlowe=# create table test (i serial primary key, t text);
> NOTICE:  CREATE TABLE will create implicit sequence "test_i_seq" for
> serial column "test.i"
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "test_pkey" for table "test"
> CREATE TABLE
> smarlowe=# insert into test (t) values ('this is text');
> INSERT 0 1
> smarlowe=# insert into test (i,t) values (DEFAULT,'this is more text');
> INSERT 0 1
> smarlowe=# insert into test (i,t) values (nextval('test_i_seq'),'even
> more text');
> INSERT 0 1
> smarlowe=# select nextval('test_i_seq');
>  nextval
> ---------
>       4
> (1 row)
>
> smarlowe=# insert into test (i,t) values (4,'last bit of text');
> INSERT 0 1
> smarlowe=# select * from test;
>  i |         t
> ---+-------------------
>  1 | this is text
>  2 | this is more text
>  3 | even more text
>  4 | last bit of text
> (4 rows)
>
> Hope that helps.
>

Last way, forgot about it:


smarlowe=# select nextval('test_i_seq');
 nextval
---------
       5
smarlowe=# insert into test (i,t) values (currval('test_i_seq'),'last
bit of text');
INSERT 0 1


--
When fascism comes to America, it will be draped in a flag and
carrying a cross - Sinclair Lewis

The esteemed Scott Marlowe said:

>
> On Thu, Dec 4, 2008 at 7:42 AM, Gustavo Rosso <grosso@sadaic.org.ar> wrote:
> > I created this table:
> >
<...>
>
> Because MySQL taught you bad habits?  You told the db to insert a 0,
> so it inserted a 0. 

With respect, sir, let me point out that Informix usage says to insert a zero to trigger a serial column, so don't be indicting Gustavo on MySQL use -- he might be coming from a real database where usage is different.

Those of us who came to postgres from Informix-land made [perhaps] the same mistakes. Not a bad *habit* unless you can prove to me that the spec says otherwise. (Maybe you can -- the SQL formal definitions seem to be deliberately opaque and often do not define _how_ a given behavior should be implemented). Just a matter of indoctrination into the local ways of doing serials.

Greg Williamson
Senior DBA
DigitalGlobe

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)

On Thu, Dec 4, 2008 at 7:47 AM, Gregory Williamson
<Gregory.Williamson@digitalglobe.com> wrote:
> The esteemed Scott Marlowe said:
>
>>
>> On Thu, Dec 4, 2008 at 7:42 AM, Gustavo Rosso <grosso@sadaic.org.ar>
>> wrote:
>> > I created this table:
>> >
> <...>
>>
>> Because MySQL taught you bad habits?  You told the db to insert a 0,
>> so it inserted a 0.
>
> With respect, sir, let me point out that Informix usage says to insert a
> zero to trigger a serial column, so don't be indicting Gustavo on MySQL use
> -- he might be coming from a real database where usage is different.

I really shoulda had a smiley up there with my comment.  However, mea
cupla, mea maxima culpa.

Note that it is quite possible to learn bad habits from many
commercial databases, not just MySQL.  Like an Oracle DBA who told me
pgsql was broken because the output of group by wasn't properly
ordered like it was on Oracle 9.  Admittedly, MySQL has a much larger
set of bad habits to teach the average user than any other DB, but it
ain't the only one.

> Those of us who came to postgres from Informix-land made [perhaps] the same
> mistakes. Not a bad *habit* unless you can prove to me that the spec says
> otherwise.

I'm pretty sure that if you say to insert a value, the spec says that
the value should be inserted unaltered.  0 is a value.  DEFAULT, or
leaving it out of the column list is a different thing entirely.

> (Maybe you can -- the SQL formal definitions seem to be
> deliberately opaque and often do not define _how_ a given behavior should be
> implemented). Just a matter of indoctrination into the local ways of doing
> serials.

True++.  The sql spec is usually quite simple, but in some areas it's
worse than the tax code.

On 04/12/2008 14:47, Gregory Williamson wrote:

> With respect, sir, let me point out that Informix usage says to insert a
> zero to trigger a serial column, so don't be indicting Gustavo on MySQL

So what do you do if you just want to insert a zero? - just curious...

In fairness, I suppose you wouldn't often be doing this on an
auto-incrementing column....but just say you did - how do you do it?

Ray.


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

Scott Marlowe, 04.12.2008 16:34:
> Like an Oracle DBA who told me
> pgsql was broken because the output of group by wasn't properly
> ordered like it was on Oracle 9.

Then he did not know Oracle very well :)

Even Oracle 9 does not guarantee that the output of an GROUP BY is actually sorted, with Oracle 10 this has just gotton
more"obvious" because the grouping strategies were enhanced. 

Thomas

On Fri, Dec 5, 2008 at 8:24 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:
> Scott Marlowe, 04.12.2008 16:34:
>>
>> Like an Oracle DBA who told me
>> pgsql was broken because the output of group by wasn't properly
>> ordered like it was on Oracle 9.
>
> Then he did not know Oracle very well :)
> Even Oracle 9 does not guarantee that the output of an GROUP BY is actually
> sorted, with Oracle 10 this has just gotton more "obvious" because the
> grouping strategies were enhanced.
>
yeah, that sounds merely like his short experience, or by-product of
the way GROUP BY was implemented in some particular version of oracle
9, not a guaranteed outcome. Poor guy... he probably still lives on
thinking that oracle is so good because of those little 'things' :)


--
GJ