Re: [GENERAL] Am I using the SERIAL type properly?
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?
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?
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