Thread: how do I to generate a sequence Range or Set of integer constants
dear SQL friends, What I want to do might be done differantly. Right now I can't think of another solution other than a select statement I would like to create a sequence range of integer constants. Join this sequence against a ID Range in a database and look for missing Id's. Another application for this would be to simply populate a database with say 1000..9999 Records.... Now: Is there a syntax that allows for the following..... create table XX (id int); insert into XX (select xx from "1 to 1000" of integers) or... select IntSeq.MissingValues, x.UniqIntId,x.A,x.B,x.C, from MyDataTable x left outer join (select MissingValues from "1 to 1000" of integers ) IntSeq on MissingValues=x.UniqIntId I'm hoping that someone has done this and might be able to point to some function or methode to do this Thanks, Stefan Becker -- email: stefan@yukonho.de tel : +49 (0)6232-497631 http://www.yukonho.de
am Fri, dem 23.02.2007, um 19:25:35 +0100 mailte Stefan Becker folgendes: > Now: Is there a syntax that allows for the following..... > > create table XX (id int); > insert into XX (select xx from "1 to 1000" of integers) Perhaps you are searching for generate_series(): test=*# select generate_series(1,10);generate_series ----------------- 1 2 3 4 5 6 7 8 9 10 (10 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Fri, 2007-02-23 at 12:25, Stefan Becker wrote: > dear SQL friends, > > What I want to do might be done differantly. Right now I can't > think of another solution other than a select statement > > I would like to create a sequence range of integer constants. Join > this sequence against a ID Range in a database and look for missing > Id's. > > Another application for this would be to simply populate a database with > say 1000..9999 Records.... > > Now: Is there a syntax that allows for the following..... > > create table XX (id int); > insert into XX (select xx from "1 to 1000" of integers) > > or... > > select IntSeq.MissingValues, x.UniqIntId,x.A,x.B,x.C, > from MyDataTable x > left outer join > ( > select MissingValues from "1 to 1000" of integers > ) IntSeq on MissingValues=x.UniqIntId select * from generate_series(1,1000);
On Fri, 2007-02-23 at 19:25 +0100, Stefan Becker wrote: > dear SQL friends, > > What I want to do might be done differantly. Right now I can't > think of another solution other than a select statement > > I would like to create a sequence range of integer constants. Join > this sequence against a ID Range in a database and look for missing > Id's. > > Another application for this would be to simply populate a database with > say 1000..9999 Records.... > > Now: Is there a syntax that allows for the following..... > > create table XX (id int); > insert into XX (select xx from "1 to 1000" of integers) > > or... > > select IntSeq.MissingValues, x.UniqIntId,x.A,x.B,x.C, > from MyDataTable x > left outer join > ( > select MissingValues from "1 to 1000" of integers > ) IntSeq on MissingValues=x.UniqIntId > > > I'm hoping that someone has done this and might be able to > point to some function or methode to do this Maybe something like this will help: SELECT id FROM generate_series(1, (SELECT last_value FROM id_seq)) AS s(id) EXCEPT SELECT UniqIntId FROM MyDataTable ORDER BY id; The id_seq is the sequence on your ID column, assuming it has one, or you can replace the (SELECT ... FROM id_seq) by 1000. Joe