Re: Generate a dynamic sequence within a query

[DM <dm.aeqa@xxxxxxxxx>]

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@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
- > To make changes to your subscription:
- > http://www.postgresql.org/mailpref/pgsql-general

- >