Re: [GENERAL] Am I using the SERIAL type properly?

2003-07-12 Thread Jeffrey Melloy
If you use a serial datatype, it simply says "the *default* behavior is 
to use the next value of sequence a".  So you can insert 1, 2, 
10,204,492 into the column just fine.  However, by inserting data into 
something you want to auto-increment, you can run into non-uniqueness.  
(Like you were).   So most people tend to leave the id field as 
completely null.  Setting the value is only for loading data, or data 
that already has something as a PK.

The command to set it is "select pg_catalog.setval('sequence_name', 
value)".  Check the docs on sequences for more info.
On Saturday, July 12, 2003, at 02:36  AM, Chad N. Tindel wrote:


drop table A;
create table A (
   id SERIAL PRIMARY KEY,
   foo int default 5,
   bar int default 10
);
insert into A (id, foo, bar) values (1, 1, 1);
insert into A (id, foo, bar) values (2, 2, 2);
insert into A (id, foo, bar) values (3, 3, 3);
insert into A (id, foo, bar) values (4, 4, 4);
  A serial data type will allow you to input values into it, but the
counter is still at 0.  That's why your first update statement's
nextval outputs "1".  It's not showing what was already inserted, it's
showing what would have been.  So at this point you need to set the
current value of id at 4.
I wasn't quite able to figure out what the solution to my problem 
should be...
Are you saying that a serial type isn't smart enough to realize that I 
just
inserted the values 1, 2, 3, 4, and to figure out that if I ask it to
self-generate the next one that it should return a 5?  I have to tell 
it that
the next value it should return is 5?

Maybe I'm just using the wrong feature of postgres... is there some 
way to
get the same behavior as a mysql auto_increment primary key column?


Nextval *sets* the sequence at N+1.
OK... didn't know that.  How do I set the sequence to an arbitrary 
value X?

Thanks for the help!  Hopefully I'll get the hang of this soon.

Chad



---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [GENERAL] Am I using the SERIAL type properly?

2003-07-11 Thread Jeffrey Melloy

drop table A;
create table A (
id SERIAL PRIMARY KEY,
foo int default 5,
bar int default 10
);
insert into A (id, foo, bar) values (1, 1, 1);
insert into A (id, foo, bar) values (2, 2, 2);
insert into A (id, foo, bar) values (3, 3, 3);
insert into A (id, foo, bar) values (4, 4, 4);
  A serial data type will allow you to input values into it, but the 
counter is still at 0.  That's why your first update statement's 
nextval outputs "1".  It's not showing what was already inserted, it's 
showing what would have been.  So at this point you need to set the 
current value of id at 4.

insert into A (foo, bar) values (5, 5);
insert into A (foo, bar) values (6, 6);
--
The output that I get is:

[EMAIL PROTECTED] Setup]$ p < a.sql
DROP TABLE
NOTICE:  CREATE TABLE will create implicit sequence 'a_id_seq' for 
SERIAL
column 'a.id'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'a_pkey'
for table 'a'
CREATE TABLE
INSERT 44289 1
INSERT 44290 1
INSERT 44291 1
INSERT 44292 1
ERROR:  Cannot insert a duplicate key into unique index a_pkey
Here it's trying to insert "1"
ERROR:  Cannot insert a duplicate key into unique index a_pkey
Here "2"

Nextval *sets* the sequence at N+1.

INSERT 44319 1
 id | foo | bar
+-+-
  1 |   1 |   1
(1 row)
 nextval
-
   1
  === These are unrelated.

Nextval is at 4 now, so the insert (foo,bar) works correctly.
 id | foo | bar
+-+-
  1 |   1 |   1
  2 |   2 |   2
  3 |   3 |   3
  4 |   4 |   4
  5 |   5 |   5
(5 rows)
 nextval
-
   6
  < == This increases it again, showing the skipping behavior you were 
seeing.

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match


Re: [GENERAL] Am I using the SERIAL type properly?

2003-07-11 Thread Martijn van Oosterhout
On Thu, Jul 10, 2003 at 12:10:31AM -0700, Chad N. Tindel wrote:
> I tried doing numerous searches to see if this has been discussed before,
> but I couldn't find anything.  If it has, perhaps my question can be
> answered simply by a pointer to a previous thread.
> 
> I'm trying to convert a program from mysql to postgres (yes, I've seen the
> light).  I'm running into some problems though.  From everything I've
> read, the SERIAL type is supposed to be like using an auto_increment in
> mysql.  However, when I try to mix and match user assigned ids (my serial
> column) with database generated ids things break.

The serial fields is only a default so the counter is only advanced when you
don't specify a specific value in the insert. So in your first example, the
first four insert did not move the serial counter from 1 so the last tried
to insert keys 1 and 2 which failed because it's a primary key.

You second example works because you're explicitly calling nextval(). The
insert calls it too so you skip values.

Solutions are:
- Don't include the serial field in the insert statement
- Specify the value DEFAULT instead of an actual value in the inserts
- Specify nextval() explicitly in your inserts
- Create a trigger so it overrides any value you put in with the next serial

Note, you can't really avoid holes in the sequence. This is a FAQ somewhere.

-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington


pgp0.pgp
Description: PGP signature