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