Re: [SQL] to_char(interval) ?
This is perfect. select extract(epoch from '1 day 1 hour 15 minute'::interval)/3600 || 'hours'; ?column? 25.25hours (1 row) I must have looked at this for hours and not seen the 'interval' part of the epoch... Thanks, Ted --- Bruno Wolff III <[EMAIL PROTECTED]> wrote: > On Sun, Apr 24, 2005 at 13:08:04 -0700, > Theodore Petrosky <[EMAIL PROTECTED]> wrote: > > how do I get an interval '1 day 1 hour' to display > as > > '25 hours'. I am hunting in the docs (and > googling) > > and either I am blind or I need a special > function? > > > > Is there an easy way? > > EXTRACT epoch from the interval and divide by 3600 > and concatenate to ' hours'. > > area=> select extract(epoch from '1 day 1 > hour'::interval)/3600 || ' hours'; > ?column? > -- > 25 hours > (1 row) > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] searching cidr/inet arrays
i'm having trouble figuring out how to search in inet arraysits
been a long time since i used postgres array support, so i may just be
bone-headed..
how can i determine if a given ip address is contained in the subnet
declaration inside an array?
{134.53.25.0/24,134.53.0.0/16}
{134.53.24.0/24}
i'd like to see which rows match an ip of, say, 134.53.24.2.
thanks in advance, and sorry if this is a faqi've googled
site:archives.postgresql.org, but haven't found my solution.
---(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 cidr/inet arrays
On Mon, Apr 25, 2005 at 02:46:37PM -0400, Rob Casson wrote:
>
> i'm having trouble figuring out how to search in inet arraysits
> been a long time since i used postgres array support, so i may just be
> bone-headed..
>
> how can i determine if a given ip address is contained in the subnet
> declaration inside an array?
>
> {134.53.25.0/24,134.53.0.0/16}
> {134.53.24.0/24}
>
> i'd like to see which rows match an ip of, say, 134.53.24.2.
See "Row and Array Comparisons" in the "Functions and Operators"
chapter of the documentation. The following works in 7.4 and later:
CREATE TABLE foo (
idserial PRIMARY KEY,
nets cidr[] NOT NULL
);
INSERT INTO foo (nets) VALUES ('{134.53.25.0/24,134.53.0.0/16}');
INSERT INTO foo (nets) VALUES ('{134.53.24.0/24}');
SELECT * FROM foo WHERE '134.53.24.2' << ANY (nets);
id | nets
+
1 | {134.53.25.0/24,134.53.0.0/16}
2 | {134.53.24.0/24}
(2 rows)
SELECT * FROM foo WHERE '134.53.100.2' << ANY (nets);
id | nets
+
1 | {134.53.25.0/24,134.53.0.0/16}
(1 row)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Coming from Oracle SQL
[Please copy the mailing list on replies so others can contribute to and learn from discussions.] On Mon, Apr 25, 2005 at 09:31:35AM -0600, Veronica L Bounmixay wrote: > > Thank you so much for responding. I did dig around until I was able to > find pg_tables but I must be extremely stupid. I'm using 8.0.2 and I > noticed that my download includes the docs - how the heck do I get to > them? I'm just using the psql interactive terminal. If the doc is a bunch of HTML files then you can view them with a browser, either by serving them via a web server or by using a file URL. For example, if the files are under /path/to/files then you could view the doc with file:///path/to/files/index.html. > Also, what did you mean by "if you're using psql?" Is there any other? I > really would like to find that out. I know I'm stupid on that part! psql is the standard client but third-party clients exist. Some people, for example, like pgAdmin III. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] people who buy A, also buy C, D, E
The goal of my query is: given a book, what did other people who bought this book also buy? I plan the list the 5 most popular such books. In reality, this isn't about books, but that makes it easier to understand I think. We have a table of customer_id (watch_list_id) and book_id (element_id). freshports.org=# \d watch_list_element Table "public.watch_list_element" Column | Type | Modifiers ---+-+--- watch_list_id | integer | not null element_id| integer | not null Indexes: "watch_list_element_pkey" primary key, btree (watch_list_id, element_id) "watch_list_element_element_id" btree (element_id) Foreign-key constraints: "$2" FOREIGN KEY (watch_list_id) REFERENCES watch_list(id) ON UPDATE CASCADE ON DELETE CASCADE "$1" FOREIGN KEY (element_id) REFERENCES element(id) ON UPDATE CASCADE ON DELETE CASCADE freshports.org=# I have a query which returns the needed results: SELECT W.element_id FROM watch_list_element W WHERE w.watch_list_id in (select watch_list_id from watch_list_element where element_id = 54968) GROUP BY W.element_id ORDER BY count(W.watch_list_id) DESC LIMIT 5; But performance is an issue here. So I'm planning to calculate all the possible values and cache them. That is, given each element_id in a watch_list, what are the top 5 element_id values on all the lists on which the original element_id appears? I'm having trouble constructing the query. I'm not even sure I can do this in one select, but that would be nice. Examples and clues are appreciated. Any ideas? Thank you. -- Dan Langille : http://www.langille.org/ BSDCan - The Technical BSD Conference - http://www.bsdcan.org/ NEW brochure available at http://www.bsdcan.org/2005/advocacy/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] UPDATE WITH ORDER BY
Hi Guys!
I need to make an UPDATE on a column reordering it with a sequence
using order by a description.
Confusing??? Well.. Let me give an example...
Today, my table it's organized like this:
Code / Description
9 Orange
15 Apple
1 Pear
3 Tomato
I wanna to reorganize (reordering the code from 1 to ... ordering by
description)
Code / Description
1 Apple
2 Orange
3 Pear
4 Tomato
I created a sequence but I am having no succes to use it because UPDATE
don't accept ORDER BY CLAUSE. The "ideal SQL" is UPDATE table SET code
= nextval('sequence') ORDER BY description
I searched a lot on the NET without ant "tip" for my case.
It's a very simple need but I am not able to solve it...
Anyone knows how I can do it?
Cheers,
--
Rodrigo Carvalhaes
--
Esta mensagem foi verificada pelo sistema de antivírus e
acredita-se estar livre de perigo.
