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)

Hello,

El 13/08/2004 10:50 AM, adburne@asocmedrosario.com.ar en su mensaje
escribio:[color=blue]
> 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???[/color]

Check the serial data type.


--
Sinceramente,
Josué Maldonado.

"Y conoceréis la verdad, y la verdad os hará libres." San Juan 8-32.

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

Yep, use the serial datatype. And then use the helpful documention. :)

http://www.postgresql.org/docs/7.4/s...ATATYPE-SERIAL
http://www.postgresql.org/docs/faqs/FAQ.html#4.15.2

On Fri, 13 Aug 2004 adburne@asocmedrosario.com.ar wrote:
[color=blue]
> 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)
>[/color]



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Fri, Aug 13, 2004 at 13:50:48 -0300,
adburne@asocmedrosario.com.ar wrote:[color=blue]
> 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:[/color]

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

adburne@asocmedrosario.com.ar wrote:
[color=blue]
>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
>------+-------
>
>[/color]
Do you mean:


field1| field2
------+-------
1 | 1
1 | 2
2 | 3
------+-------

You need the serial type.

Mage

--
http://mage.hu


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

"Josué Maldonado" <josue@lamundial.hn> wrote:[color=blue]
>
> El 13/08/2004 10:50 AM, adburne@asocmedrosario.com.ar en su mensaje
> escribio:[color=green]
> > ...
> > 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???[/color]
>
> Check the serial data type.[/color]

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



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

adburne@asocmedrosario.com.ar writes
[color=blue]
> 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???[/color]

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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

From: <adburne@asocmedrosario.com.ar>:[color=blue]
> 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???[/color]

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





---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

gnari wrote:
[color=blue]
>From: <adburne@asocmedrosario.com.ar>:
>
>[color=green]
>>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???
>>
>>[/color]
>
>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;
>
>
>[/color]
....

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

"Brendan Jurd" <blakjak@blakjak.sytes.net> wrote:
[color=blue]
>
> gnari wrote:
>[color=green]
> >From: <adburne@asocmedrosario.com.ar>:
> >
> >[color=darkred]
> >>[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
> >>------+-------[/color]
> >
> >[trigger implementation using max]
> > ...[/color][/color]
[color=blue]
>
> 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();
>[/color]

how is this any different than a regular serial ?
[color=blue]
>
> 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.[/color]

I agree that the use of MAX is weak, but the point was that the OP
wanted the mysql behaviour.

gnari



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

gnari wrote:
[color=blue]
>"Brendan Jurd" <blakjak@blakjak.sytes.net> wrote:
>
>
>[color=green]
>>gnari wrote:
>>
>>
>>[color=darkred]
>>>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]
>>>...
>>>
>>>[/color][/color]
>
>
>[color=green]
>>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();
>>
>>
>>[/color]
>
>how is this any different than a regular serial ?
>
>
>[/color]
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.
[color=blue][color=green]
>>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.
>>
>>[/color]
>
>I agree that the use of MAX is weak, but the point was that the OP
>wanted the mysql behaviour.
>
>
>[/color]
gnari
[color=blue]
>
>
>[/color]

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

In article <14610181509.20040813135048@asocmedrosario.com.ar> ,
adburne@asocmedrosario.com.ar writes:
[color=blue]
> 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:[/color]
[color=blue]
> create table table1
> (field1 integer,
> field2 integer autoincrement,
> primary key (field1,field2))[/color]
[color=blue]
> when insert rows:[/color]
[color=blue]
> insert into table1 (field1) values (1);
> insert into table1 (field1) values (1);
> insert into table1 (field1) values (2);[/color]
[color=blue]
> and then select * from table1, you get:
> field1| field2
> ------+-------
> 1 | 1
> 1 | 2
> 2 | 1
> ------+-------[/color]

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.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Estimado Mage,

Con fecha viernes 13 de agosto de 2004, 16.54.09, escribió:

M> adburne@asocmedrosario.com.ar wrote:
[color=blue][color=green]
>>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
>>------+-------
>>
>>[/color][/color]
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


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

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


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

adburne@asocmedrosario.com.ar wrote:
[color=blue]
> Estimado Mage,
>
> Con fecha viernes 13 de agosto de 2004, 16.54.09, escribió:
>
> M> adburne@asocmedrosario.com.ar wrote:
>
>[color=green][color=darkred]
>>>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
>>>------+-------
>>>
>>>[/color][/color]
>
> 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.[/color]

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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 !


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Tue, 2004-08-17 at 12:22, Pierre-Frédéric Caillaud wrote:[color=blue]
> 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 ![/color]

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.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster