Re: Generate a dynamic sequence within a query

create temp table dup_test (nm1 varchar(2),nm2 varchar(3));
insert into dup_test values ('A','A'),('A','B'),('A','C'),('B','A'),('B',
'B'),('B','C');

CREATE SEQUENCE
dup_test_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

alter table dup_test add column dup_id integer;

alter table dup_test alter column dup_id SET DEFAULT
nextval('dup_test_seq'::regclass);

update dup_test set dup_id = nextval('dup_test_seq'::regclass);

select * from dup_test;
nm1 | nm2 | dup_id
-----+-----+--------
A | A | 1
A | B | 2
A | C | 3
B | A | 4
B | B | 5
B | C | 6
(6 rows)

Hope this helps

On Wed, Oct 20, 2010 at 4:07 PM, David Kerr <dmk(at)mr-paradox(dot)net> wrote:

> On Wed, Oct 20, 2010 at 03:47:19PM -0700, DM wrote:
> - select generate_series(1,(select count(*) from tax)), country from tax;
> -
> - you should use braces around the sub select.
> -
> - Thanks
> - Deepak
>
> Table "public.test"
> Column | Type | Modifiers
> --------+----------------------+-----------
> col1 | character varying(2) |
> col2 | character varying(2) |
>
>
> select * from test;
> col1 | col2
> ------+------
> A | A
> A | B
> A | C
> B | A
> B | B
> B | C
> (6 rows)
>
> select generate_series(1,(select count(*) from test)), col1, col2 from
> test;
> generate_series | col1 | col2
> -----------------+------+------
> 1 | A | A
> 2 | A | A
> 3 | A | A
> 4 | A | A
> 5 | A | A
> 6 | A | A
> 1 | A | B
> 2 | A | B
> 3 | A | B
> 4 | A | B
> 5 | A | B
> 6 | A | B
> 1 | A | C
> 2 | A | C
> 3 | A | C
> 4 | A | C
> 5 | A | C
> 6 | A | C
> 1 | B | A
> 2 | B | A
> 3 | B | A
> 4 | B | A
> 5 | B | A
> 6 | B | A
> 1 | B | B
> 2 | B | B
> 3 | B | B
> 4 | B | B
> 5 | B | B
> 6 | B | B
> 1 | B | C
> 2 | B | C
> 3 | B | C
> 4 | B | C
> 5 | B | C
> 6 | B | C
> (36 rows)
>
> when what i want is:
> 1 | A | A
> 2 | A | B
> 3 | A | C
> 4 | B | A
> 5 | B | B
> 6 | B | C
>
>
> thanks
>
> Dave
>
> -
> - On Wed, Oct 20, 2010 at 3:30 PM, David Kerr <dmk(at)mr-paradox(dot)net> wrote:
> -
> - > On Wed, Oct 20, 2010 at 11:28:18PM +0100, Raymond O'Donnell wrote:
> - > - On 20/10/2010 23:22, David Kerr wrote:
> - > - >I know I've seen posts on how to do this, but i can't seem to find
> them.
> - > - >
> - > - >I've got a data set
> - > - >
> - > - >A, B
> - > - >A, C
> - > - >A, D
> - > - >[...]
> - > - >
> - > - >and so on
> - > - >
> - > - >and i'd like to be able to wite a query that would result in
> - > - >
> - > - >1,A,B
> - > - >2,A,C
> - > - >3,A,D
> - > - >[...]
> - > - >
> - > - >PG version is 8.3.
> - > - >
> - > - >Any ideas?
> - > -
> - > - You probably want generate_series():
> - > -
> - > - http://www.postgresql.org/docs/8.3/static/functions-srf.html
> - > -
> - > - Ray.
> - >
> - > I thought, so. what would that look like?
> - >
> - > select generate_series(1,select count(*) from table), field1, field2
> from
> - > table
> - > doesn't work..
> - >
> - >
> - > thanks
> - >
> - > Dave
> - >
> - > --
> - > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> - > To make changes to your subscription:
> - > http://www.postgresql.org/mailpref/pgsql-general
> - >
>