Thread: Autoincremental value
Hi I'm a newbie in postgresql, I came from MSSQL, MySQL and now I'm testing postgres. In mysql there is a way to make a second autoincrement field, just: create table table1 (field1 integer, field2 integer autoincrement, primary key (field1,field2)) when insert rows: insert into table1 (field1) values (1); insert into table1 (field1) values (1); insert into table1 (field1) values (2); and then select * from table1, you get: field1| field2 ------+------- 1 | 1 1 | 2 2 | 1 ------+------- there is a way to do this with postgres??? thnx!!!! -- Saludos, adburne mailto:adburne@asocmedrosario.com.ar
Hello, El 13/08/2004 10:50 AM, adburne@asocmedrosario.com.ar en su mensaje escribio: > Hi I'm a newbie in postgresql, I came from MSSQL, MySQL and now > I'm testing postgres. > In mysql there is a way to make a second autoincrement field, just: > > create table table1 > (field1 integer, > field2 integer autoincrement, > primary key (field1,field2)) > > when insert rows: > > insert into table1 (field1) values (1); > insert into table1 (field1) values (1); > insert into table1 (field1) values (2); > > and then select * from table1, you get: > field1| field2 > ------+------- > 1 | 1 > 1 | 2 > 2 | 1 > ------+------- > > there is a way to do this with postgres??? Check the serial data type. -- Sinceramente, Josué Maldonado. "Y conoceréis la verdad, y la verdad os hará libres." San Juan 8-32.
On Fri, Aug 13, 2004 at 13:50:48 -0300, adburne@asocmedrosario.com.ar wrote: > Hi I'm a newbie in postgresql, I came from MSSQL, MySQL and now > I'm testing postgres. > In mysql there is a way to make a second autoincrement field, just: Use serial for the type. You probably want to read up on sequences and the serial type (which is really a macro for an int plus a default based on a sequence) as the semantics are not identical with autoincrement.
adburne@asocmedrosario.com.ar wrote:
>when insert rows:
>
>insert into table1 (field1) values (1);
>insert into table1 (field1) values (1);
>insert into table1 (field1) values (2);
>
>and then select * from table1, you get:
>field1| field2
>------+-------
> 1 | 1
> 1 | 2
> 2 | 1
>------+-------
>
>
Do you mean:
field1| field2
------+-------
1 | 1
1 | 2
2 | 3
------+-------
You need the serial type.
Mage
--
http://mage.hu
Yep, use the serial datatype. And then use the helpful documention. :) http://www.postgresql.org/docs/7.4/static/datatype.html#DATATYPE-SERIAL http://www.postgresql.org/docs/faqs/FAQ.html#4.15.2 On Fri, 13 Aug 2004 adburne@asocmedrosario.com.ar wrote: > Hi I'm a newbie in postgresql, I came from MSSQL, MySQL and now > I'm testing postgres. > In mysql there is a way to make a second autoincrement field, just: > > create table table1 > (field1 integer, > field2 integer autoincrement, > primary key (field1,field2)) > > when insert rows: > > insert into table1 (field1) values (1); > insert into table1 (field1) values (1); > insert into table1 (field1) values (2); > > and then select * from table1, you get: > field1| field2 > ------+------- > 1 | 1 > 1 | 2 > 2 | 1 > ------+------- > > there is a way to do this with postgres??? > > thnx!!!! > > -- > Saludos, > adburne > mailto:adburne@asocmedrosario.com.ar > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
"Josué Maldonado" <josue@lamundial.hn> wrote: > > El 13/08/2004 10:50 AM, adburne@asocmedrosario.com.ar en su mensaje > escribio: > > ... > > insert into table1 (field1) values (1); > > insert into table1 (field1) values (1); > > insert into table1 (field1) values (2); > > > > and then select * from table1, you get: > > field1| field2 > > ------+------- > > 1 | 1 > > 1 | 2 > > 2 | 1 > > ------+------- > > > > there is a way to do this with postgres??? > > Check the serial data type. I do not think this is possible with the serial data type in postgres. notice how the field2 is reset to 1 for each new value of field1 I did not know that this was possible in mysql. you would think that this is tricky to implement. gnari
From: <adburne@asocmedrosario.com.ar>:
> Hi I'm a newbie in postgresql, I came from MSSQL, MySQL and now
> I'm testing postgres.
> In mysql there is a way to make a second autoincrement field, just:
>
> create table table1
> (field1 integer,
> field2 integer autoincrement,
> primary key (field1,field2))
>
> when insert rows:
>
> insert into table1 (field1) values (1);
> insert into table1 (field1) values (1);
> insert into table1 (field1) values (2);
>
> and then select * from table1, you get:
> field1| field2
> ------+-------
> 1 | 1
> 1 | 2
> 2 | 1
> ------+-------
>
> there is a way to do this with postgres???
it seems it is possible with triggers:
create table table1 (
field1 integer,
field2 integer,
primary key (field1,field2)
);
create or replace function table1_subcnt()
returns trigger as '
begin
select coalesce(max(field2),0)+1 from table1
where field1=NEW.field1
into NEW.field2;
return NEW;
end;
' language plpgsql;
create trigger table1_subcnt before insert on table1
for each row execute procedure table1_subcnt();
insert into table1 (field1) values (1);
insert into table1 (field1) values (1);
insert into table1 (field1) values (2);
gnari=# select * from table1;
field1 | field2
--------+-------
1 | 1
1 | 2
2 | 1
(3 rows)
gnari
adburne@asocmedrosario.com.ar writes
> Hi I'm a newbie in postgresql, I came from MSSQL, MySQL and now
> I'm testing postgres.
> In mysql there is a way to make a second autoincrement field, just:
>
> create table table1
> (field1 integer,
> field2 integer autoincrement,
> primary key (field1,field2))
>
> when insert rows:
>
> insert into table1 (field1) values (1);
> insert into table1 (field1) values (1);
> insert into table1 (field1) values (2);
>
> and then select * from table1, you get:
> field1| field2
> ------+-------
> 1 | 1
> 1 | 2
> 2 | 1
> ------+-------
>
> there is a way to do this with postgres???
It looks like a weird feature. Anyway to achieve the same result with
postgresql, a trigger can compute a value for field2 when needed. I believe
this would do:
CREATE OR REPLACE FUNCTION fill_field2() RETURNS trigger AS '
BEGIN
IF new.field2 IS NULL THEN
SELECT 1+coalesce(max(field2),0) INTO new.field2 FROM table1
WHERE field1=new.field1;
END IF;
RETURN new;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER insert_table1 BEFORE INSERT on table1 FOR EACH ROW
EXECUTE PROCEDURE fill_field2();
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
gnari wrote:
>From: <adburne@asocmedrosario.com.ar>:
>
>
>>Hi I'm a newbie in postgresql, I came from MSSQL, MySQL and now
>>I'm testing postgres.
>>In mysql there is a way to make a second autoincrement field, just:
>>
>>create table table1
>>(field1 integer,
>>field2 integer autoincrement,
>>primary key (field1,field2))
>>
>>when insert rows:
>>
>>insert into table1 (field1) values (1);
>>insert into table1 (field1) values (1);
>>insert into table1 (field1) values (2);
>>
>>and then select * from table1, you get:
>>field1| field2
>>------+-------
>> 1 | 1
>> 1 | 2
>> 2 | 1
>>------+-------
>>
>>there is a way to do this with postgres???
>>
>>
>
>it seems it is possible with triggers:
>
>create table table1 (
> field1 integer,
> field2 integer,
> primary key (field1,field2)
>);
>
>create or replace function table1_subcnt()
> returns trigger as '
> begin
> select coalesce(max(field2),0)+1 from table1
> where field1=NEW.field1
> into NEW.field2;
> return NEW;
> end;
>' language plpgsql;
>
>
>
...
Rather than using an aggregate function ( max() ) on the table, which
could be expensive over a very great number of rows, why not use a
sequence? If it's good enough for a true serial, then it should be good
enough for this value-dependant one. You'd still use the trigger, but
simplify it. Like so:
CREATE SEQUENCE table1_field2_seq;
CREATE OR REPLACE FUNCTION fill_field2() RETURNS trigger AS '
BEGIN
IF new.field2 IS NULL THEN
SELECT nextval( ''table1_field2_seq'' ) INTO new.field2
END IF;
RETURN new;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER insert_table1 BEFORE INSERT on table1 FOR EACH ROW
EXECUTE PROCEDURE fill_field2();
This gives the same result, without the added burden of running MAX for
every insert, and because it's a sequence, the results will work even if
multiple inserts are trying to run at very similar times.
HTH
BJ
"Brendan Jurd" <blakjak@blakjak.sytes.net> wrote: > > gnari wrote: > > >From: <adburne@asocmedrosario.com.ar>: > > > > > >>[mysql implementation of autoincrement as second field in primary key] > >> ... > >>and then select * from table1, you get: > >>field1| field2 > >>------+------- > >> 1 | 1 > >> 1 | 2 > >> 2 | 1 > >>------+------- > > > >[trigger implementation using max] > > ... > > Rather than using an aggregate function ( max() ) on the table, which > could be expensive over a very great number of rows, why not use a > sequence? If it's good enough for a true serial, then it should be good > enough for this value-dependant one. You'd still use the trigger, but > simplify it. Like so: > > CREATE SEQUENCE table1_field2_seq; > > CREATE OR REPLACE FUNCTION fill_field2() RETURNS trigger AS ' > BEGIN > IF new.field2 IS NULL THEN > SELECT nextval( ''table1_field2_seq'' ) INTO new.field2 > END IF; > RETURN new; > END; > ' LANGUAGE 'plpgsql'; > > CREATE TRIGGER insert_table1 BEFORE INSERT on table1 FOR EACH ROW > EXECUTE PROCEDURE fill_field2(); > how is this any different than a regular serial ? > > This gives the same result, without the added burden of running MAX for > every insert, and because it's a sequence, the results will work even if > multiple inserts are trying to run at very similar times. I agree that the use of MAX is weak, but the point was that the OP wanted the mysql behaviour. gnari
gnari wrote: >"Brendan Jurd" <blakjak@blakjak.sytes.net> wrote: > > > >>gnari wrote: >> >> >> >>>From: <adburne@asocmedrosario.com.ar>: >>> >>> >>> >>> >>>>[mysql implementation of autoincrement as second field in primary key] >>>>... >>>>and then select * from table1, you get: >>>>field1| field2 >>>>------+------- >>>> 1 | 1 >>>> 1 | 2 >>>> 2 | 1 >>>>------+------- >>>> >>>> >>>[trigger implementation using max] >>>... >>> >>> > > > >>Rather than using an aggregate function ( max() ) on the table, which >>could be expensive over a very great number of rows, why not use a >>sequence? If it's good enough for a true serial, then it should be good >>enough for this value-dependant one. You'd still use the trigger, but >>simplify it. Like so: >> >>CREATE SEQUENCE table1_field2_seq; >> >>CREATE OR REPLACE FUNCTION fill_field2() RETURNS trigger AS ' >>BEGIN >> IF new.field2 IS NULL THEN >> SELECT nextval( ''table1_field2_seq'' ) INTO new.field2 >> END IF; >> RETURN new; >>END; >>' LANGUAGE 'plpgsql'; >> >>CREATE TRIGGER insert_table1 BEFORE INSERT on table1 FOR EACH ROW >> EXECUTE PROCEDURE fill_field2(); >> >> >> > >how is this any different than a regular serial ? > > > It isn't. My post was a marvellous example of why I shouldn't try to solve pgsql problems first thing after waking up, and *especially* why I shouldn't post my solutions without testing them out first. Don't know what I was thinking. >>This gives the same result, without the added burden of running MAX for >>every insert, and because it's a sequence, the results will work even if >>multiple inserts are trying to run at very similar times. >> >> > >I agree that the use of MAX is weak, but the point was that the OP >wanted the mysql behaviour. > > > gnari > > >
In article <14610181509.20040813135048@asocmedrosario.com.ar>, adburne@asocmedrosario.com.ar writes: > Hi I'm a newbie in postgresql, I came from MSSQL, MySQL and now > I'm testing postgres. > In mysql there is a way to make a second autoincrement field, just: > create table table1 > (field1 integer, > field2 integer autoincrement, > primary key (field1,field2)) > when insert rows: > insert into table1 (field1) values (1); > insert into table1 (field1) values (1); > insert into table1 (field1) values (2); > and then select * from table1, you get: > field1| field2 > ------+------- > 1 | 1 > 1 | 2 > 2 | 1 > ------+------- Even MySQL disallows that - unless you use the MyISAM backend. The only time when I used this ugly hack was when I looked for a workaround to the missing subselects. Perhaps you should describe what you're trying to achieve. I'm pretty sure PostgreSQL has an elegant solution for that.
Estimado Mage, Con fecha viernes 13 de agosto de 2004, 16.54.09, escribió: M> adburne@asocmedrosario.com.ar wrote: >>when insert rows: >> >>insert into table1 (field1) values (1); >>insert into table1 (field1) values (1); >>insert into table1 (field1) values (2); >> >>and then select * from table1, you get: >>field1| field2 >>------+------- >> 1 | 1 >> 1 | 2 >> 2 | 1 >>------+------- >> >> M> Do you mean: M> field1| field2 M> ------+------- M> 1 | 1 M> 1 | 2 M> 2 | 3 M> ------+------- M> You need the serial type. M> Mage Thnx to everybody for your answers; you put the finger on the key =), in mysql you can create a mixed autoincremental on 2 fields, then the first act as key and the second field is autoincremental based on the first field. Like I wrote in the prev msg. field1| field2 ------+------- 1 | 1 1 | 2 2 | 1 ------+------- I was reading about serial type but I didn't find anything to do this like mysql. -- Saludos, adburne mailto:adburne@asocmedrosario.com.ar
Estimado Harald, Con fecha sábado 14 de agosto de 2004, 10.26.25, escribió: ... HF> Even MySQL disallows that - unless you use the MyISAM backend. The HF> only time when I used this ugly hack was when I looked for a HF> workaround to the missing subselects. HF> Perhaps you should describe what you're trying to achieve. I'm pretty HF> sure PostgreSQL has an elegant solution for that. First, thanks for all your answers again; second my natural language is spanish maybe some 'mistakes expressions'. It's true mixed autoincremental only works on mysql with myisam tables, but it's a simple way to do what I need at this moment. This is my situation: I've a header and a details tables. There some users who create header records and about 75 who fill the details table. The header has around 50,000 records and details around 50,000,000. Often the clients need edit details records, the header record it's in the app's memory; the client just insert the sequence like '517', edit this, save and type '124'; edit the other, save.... If I use a serial the user must write '49167842', edit, save; enter '49160564', save, etc. Seems to be very simple, but for it's not the same to write 3 digits than 8. --- Greetings, adburne mailto:adburne@asocmedrosario.com.ar
adburne@asocmedrosario.com.ar wrote: > Estimado Mage, > > Con fecha viernes 13 de agosto de 2004, 16.54.09, escribió: > > M> adburne@asocmedrosario.com.ar wrote: > > >>>when insert rows: >>> >>>insert into table1 (field1) values (1); >>>insert into table1 (field1) values (1); >>>insert into table1 (field1) values (2); >>> >>>and then select * from table1, you get: >>>field1| field2 >>>------+------- >>> 1 | 1 >>> 1 | 2 >>> 2 | 1 >>>------+------- >>> >>> > > M> Do you mean: > > > M> field1| field2 > M> ------+------- > M> 1 | 1 > M> 1 | 2 > M> 2 | 3 > M> ------+------- > > M> You need the serial type. > > M> Mage > > > Thnx to everybody for your answers; you put the finger on the key =), in > mysql you can create a mixed autoincremental on 2 fields, then the first > act as key and the second field is autoincremental based on the first > field. Like I wrote in the prev msg. > > field1| field2 > ------+------- > 1 | 1 > 1 | 2 > 2 | 1 > ------+------- > > I was reading about serial type but I didn't find anything to do this > like mysql. Did you see the other posts in this threads ? What you can do with MySQL surelly you can do with Postgres, don't forget features that MySQL doesn't have that in Postgresq can help you to obtain the same result. Of course in Postgresql you have to write you own trigger, this means however to have a for example a unique serial for values: 1,2,3,4 and another serial for value: 4,5,6,7 ( note the 4 repeated twice just to made it more difficult ). The solutions proposed can easily solve this problem, could you do this in MySQL ? Regards Gaetano Mendola
Estimado Gaetano, Con fecha martes 17 de agosto de 2004, 9.40.45, escribió: GM> Did you see the other posts in this threads ? What you can do with MySQL GM> surelly you can do with Postgres, don't forget features that MySQL GM> doesn't have that in Postgresq can help you to obtain the same result. GM> Of course in Postgresql you have to write you own trigger, this means GM> however to have a for example a unique serial for values: 1,2,3,4 and GM> another serial for value: 4,5,6,7 ( note the 4 repeated twice just to GM> made it more difficult ). The solutions proposed can easily solve this problem, GM> could you do this in MySQL ? GM> Regards GM> Gaetano Mendola Yeap, I saw the others answers and I'm trying it now. Because I want another features that mysql don't have just I'm testing postgres right now. Thanks, Alejandro. mailto:adburne@asocmedrosario.com.ar
Suppose your table is like :
key1 key2
1 1
1 2
2 1
To get the next value to insert for key1=1 you can do this :
SELECT key2 FROM ... WHERE key1=1 ORDER BY key2 DESC LIMIT 1
Of course a UNIQUE INDEX on key1, key2 helps.
You won't be protected from two transactions adding the same value at the
same time, though. The unique index will catch them and one of them will
fail (constraint violation etc). Just retry the transaction until it
works... or, be a warrior and lock the table... but if you do that, please
do it in a function/trigger so that it's not kept locked for long !
On Tue, 2004-08-17 at 12:22, Pierre-Frédéric Caillaud wrote: > Suppose your table is like : > > key1 key2 > 1 1 > 1 2 > 2 1 > > To get the next value to insert for key1=1 you can do this : > > SELECT key2 FROM ... WHERE key1=1 ORDER BY key2 DESC LIMIT 1 > > Of course a UNIQUE INDEX on key1, key2 helps. > > You won't be protected from two transactions adding the same value at the > same time, though. The unique index will catch them and one of them will > fail (constraint violation etc). Just retry the transaction until it > works... or, be a warrior and lock the table... but if you do that, please > do it in a function/trigger so that it's not kept locked for long ! Actually, it should be transactionally safe to put this in a trigger and use select ... for update ... which should lock the key1/key2 combo you're operating on for the short period of the trigger running, and block other triggers from running at the same time on those data.