[SQL] Convert INT to INTERVAL?
Is it possible within pl/pgsql, to convert an integer to an interval in months? IE, if num_months is an INT set to 48, can it be converted to an interval? Thanks, Brian ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Convert INT to INTERVAL?
On Fri, 27 Feb 2004, Brian Knox wrote: > Is it possible within pl/pgsql, to convert an integer to an interval in > months? IE, if num_months is an INT set to 48, can it be converted to an > interval? You should be able to say something like num_months * INTERVAL '1 month' I believe. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] searching polygons
Hi elin thanks for advice, i looked at your website but i didnt really understand the information given, sorry. However i looked at the postgres documentation and realised what i had to do select * from species where location[1] @ polygon '((0,0), (1000,0), (1000,1000), (0,1000))': This works fine for just the one location ([1]), but when i tried to search the entire array of polygons using the query: select * from species where location @ polygon '((0,0), (1000,0), (1000,1000), (0,1000))': i got this: Error: ' and 'polygon' You will have to retype this query using an explicit cast (State:S1000, Native Code: 7) I am not sure how to do this, any suggestions Many thanks Dave - Original Message - From: "elein" <[EMAIL PROTECTED]> To: "David" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, February 22, 2004 2:12 AM Subject: Re: [SQL] searching polygons > You should use some variation of overlaps or > contains within. There is some discussion and > a list of operators in Issue #61 of General Bits. > ( http://www.varlena.com/GeneralBits/61 ) > > I would also suggest looking at the geometric > operators in the documentation. You may have > to cast the polygon to a circle to use the operators, > but it will still tell you whether the smaller polys > are contained within or overlap the larger. > > elein > > On Tue, Feb 17, 2004 at 07:01:51PM -, David wrote: > > What query would i have to use to search for an item using a polygon as a > > parameter? (i.e a very large polygon that would identify smaller polygons > > within it) ideally i would like to give postgresq a series of co-ordinates > > and then have it return all those results whose polygons fall into that set > > of co-ordinates, is this possible? > > > > at the moment all i can think of is > > > > > > select * from species where location between '(0,0)' and '(1000,0)' and > > '(0, 1000)' and '(1000; 1000)'; > > > > I think im way off, any suggestions? > > > > Cheers Dave > > > > > > ---(end of broadcast)--- > > TIP 6: Have you searched our list archives? > > > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Convert INT to INTERVAL?
On Fri, Feb 27, 2004 at 11:54:04 -0500, Brian Knox <[EMAIL PROTECTED]> wrote: > Is it possible within pl/pgsql, to convert an integer to an interval in > months? IE, if num_months is an INT set to 48, can it be converted to an > interval? Can you use num_months * '1 month'::interval ? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Convert INT to INTERVAL?
Brian Knox <[EMAIL PROTECTED]> writes: > Is it possible within pl/pgsql, to convert an integer to an interval in > months? IE, if num_months is an INT set to 48, can it be converted to an > interval? Sure, just multiply by the appropriate interval value: regression=# select 48 * '1 month'::interval; ?column? -- 4 years (1 row) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] BUG #1083: Insert query reordering interacts badly with
I am going to try to move this over the sql list, since it doesn't belong
on bugs.
On Tue, Feb 24, 2004 at 23:47:48 +1300,
Martin Langhoff <[EMAIL PROTECTED]> wrote:
> Tom Lane wrote:
>
> >How about
> >
> >SELECT nextval('seq'); -- ignore result
> >
> >INSERT INTO ... VALUES (currval('seq'), currval('seq'));
> >
> >
>
> Well, it works for my sample case, I have to agree. Maybe I should
> mention that I tried to boil down the bugreport to the simplest repro
> case I could.
>
> My actual SQL looks roughly like
>
>INSERT INTO destination (record_id, page, row)
>SELECT
>(SELECT record_id FROM record ORDERED BY name),
>(NEXTVAL('seq') / 200),
>(CURRVAL('seq') % 200)
>
> While I have a workaround, I am definitely curious as to whether there
> is actually a way to do it. Thanks for your patience.
I think the following will do what you want:
INSERT INTO destination (record_id, page, row)
SELECT record_id, seq/200, seq%200 FROM
(SELECT record_id, nextval('seq') as seq FROM record ORDERED BY name);
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
