Re: [SQL] Interval subtracting
Thank you all for suggestions and links. Currently, I am working on PostgreSQL 8.0.4., so I cannot use justify_*() functions. Regards, Milorad Poluga [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SELECT on a to-be-determined table
garhone wrote: Hi, I'm a new at this. So please forgive if I mess up. Also, if there is already a reference/tutorial somewhere, feel free to point me to it. Here's my situation: db=# select * from projects; projid | projname +-- 1 | cars 2 | houses 3 | pets (3 rows) db=# select * from cars; carid | carname ---+- 1 | ford 2 | mazda (2 rows) db=# select * from houses; houseid | housename -+--- 1 | semi 2 | trailer 3 | mansion (3 rows) db=# select * from pets; petid | petname ---+- 1 | dog 2 | cat 3 | bird (3 rows) Is it possible to do this: Give me all the rows of the table whose project id is 2 (or whatever projid). Thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Your way of thinking leads to the need of comparing a field to a table name. Such a request requires two steps 1 - retrieve the name of the table to search in, store it in a variable 2 - use execute to issue a request to that table. Instead, I think it would be better to use only two tables: 1 - projects (projid, projname) 2 - items (itemid, projid, itemproperty1,itemidproperty2,...) You would have in the second table, to take your example: projid | itemid | itemname | 1 | 1 | ford | 1 | 2 | mazda | 2 | 1 | semi | 2 | 2 | trailer| 2 | 3 | mansion| 3 | 1 | dog| 3 | 2 | cat| 3 | 3 | bird | Your request would become : SELECT itemid, itemname FROM items where projid=2 The problem of having a different set of properties for the items of differents projects could be solved with three tables: project(projid, projname) itempropertymeaning(projid, propid, propmeaning) itemproperty(projid, itemid, propid, propvalue) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Need help: Find dirty rows, Update, Delete SQL
Janning Vygen wrote:
Am Samstag, 18. Februar 2006 18:41 schrieb [EMAIL PROTECTED]:
Hello,
I need a bit of help with some SQL.
I have two tables, call them Page and Bookmark.
Each row in Page can have many Bookmarks pointing to it, and
they are joined via a FK (Page.id = Bookmark.page_id).
Page has a 'url' column: Page.url, which has a unique index on it.
My Page.url column got a little dirty, and I need to clean it up,
and that's what I need help with.
Here is an example of dirtiness:
Page:
id=1 url = 'http://example.com/'
id=2 url = 'http://example.com/#' -- dirty
id=3 url = 'http://example.com/#foo' -- dirty
The last two rows are dirty. Normally I normalize URLs before
inserting them, but these got in, and now I need to clean them.
The problem is that rows in Bookmark table may point to dirty
rows in Page, so I can't just remove the dirty rows, and I can't
just update 'url' column in Page to 'http://example.com/',
because that column is unique.
Is there some fancy SQL that I can use them to find the dirty
rows in page (... where url like '%#%') and then find rows in
Bookmark table that point to them, then point those rows to
good rows in Page (e.g. id=1 row above), and finally remove the
dirty rows from Page?
try this. But please check if it really does its job. I just wrote it down in
a minute or two. There will be an easier way or nicer written SQL but a sit
is just a one time operation you shoudn't care too much. One more hint: you
should add a CHECK clause to your page_url like "page_url text NOT NULL
UNIQUE CHECK (page_url !~ '#')"
here is my test code
CREATE TABLE pages (
page_id SERIAL PRIMARY KEY,
page_url text NOT NULL UNIQUE
);
CREATE TABLE bookmarks (
bm_id SERIAL PRIMARY KEY,
bm_text text not null,
page_id int4 NOT NULL REFERENCES pages (page_id)
);
INSERT INTO pages (page_url) VALUES ('http://example.com/');
INSERT INTO pages (page_url) VALUES ('http://example.com/#');
INSERT INTO pages (page_url) VALUES ('http://example.com/#foo');
INSERT INTO pages (page_url) VALUES ('http://example2.com/#foo');
INSERT INTO pages (page_url) VALUES ('http://example3.com/#foobar');
insert into bookmarks (bm_text, page_id) values ('test1', 1);
insert into bookmarks (bm_text, page_id) values ('test2', 1);
insert into bookmarks (bm_text, page_id) values ('test3', 2);
insert into bookmarks (bm_text, page_id) values ('test4', 2);
insert into bookmarks (bm_text, page_id) values ('test5', 3);
insert into bookmarks (bm_text, page_id) values ('test6', 3);
insert into bookmarks (bm_text, page_id) values ('test7', 4);
BEGIN;
UPDATE bookmarks set page_id = pages2.page_id
FROM
pages AS pages1,
pages AS pages2
WHERE
pages1.page_id = bookmarks.page_id
AND pages2.page_url = split_part(pages1.page_url, '#', 1)
;
DELETE FROM pages WHERE page_id IN (
SELECT
pages1.page_id
FROM
pages AS pages1
JOIN pages AS pages2 ON (
pages1.page_id != pages2.page_id
AND pages2.page_url = split_part(pages1.page_url, '#', 1)
)
WHERE position('#' in pages1.page_url) > 0
AND pages1.page_id NOT IN (SELECT page_id FROM bookmarks)
);
;
UPDATE pages SET page_url = split_part(page_url, '#', 1)
WHERE position('#' in pages.page_url) > 0
;
select * from bookmarks;
select * from pages;
COMMIT;
kind regards,
janning
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
why do you consider as dirty perfectly honest URLs as
http://example.com#foo ?
Such a construct points to a specific part (foo) of a specific document
(http://example.com)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] ORDER BY CASE ...
On Mon, Feb 13, 2006 at 22:28:38 +0100, Mario Splivalo <[EMAIL PROTECTED]> wrote: > Can't do so, because receiving_time is timestamptz, and "from" is > varchar. There: > > pulitzer2=# select id, "from", receiving_time from messages order by > case when 2=3 then "from" else receiving_time end desc limit 5; > ERROR: CASE types timestamp with time zone and character varying cannot > be matched > > I need to explicitly cast receiving_time into varchar. > > What I would like to include ASC/DESC into CASE, but I guess that's not > possible. If you describe what order you are trying to get we might be able to give you a more specific suggestion. It looks like you are trying to sort on different column numbers based on whether or not two other column numbers (that weren't shown) are equal. (The syntax you are using for doing this isn't correct, but it is the only thing that seems to make sense.) Since columns 2 and 3 aren't really comparable, how were you expecting the rows for the two cases to be interspersed? Just converting a timestamp to a string seem like it wouldn't be the right answer. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
Hello.
I'm having difficulties on my first incursion through generate_series.
The details:
SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS date,
COUNT (o."04-sms") as totalcause98
FROM generate_series(11,19) AS s(d)
LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) = (DATE_TRUNC('month',
timestamp'2006-02-01' )::DATE + s.d) and o.cause01=98)
GROUP BY s.d ORDER BY 1;
This query (although quite messed up on the date parameters), does exactly
what i want:
"sum column 'cause01=98' for a specified date range, including 0's"
date| totalcause98
+--
2006-02-12 |0
2006-02-13 |0
2006-02-14 |0
2006-02-15 |0
2006-02-16 | 68
2006-02-17 | 256
2006-02-18 | 104
2006-02-19 | 34
2006-02-20 | 20
I'm using a left join because i really need the =0 sums.
The use of substr() is due to the fact the "26-insertTime" on the 'netopia'
table has a default of 'default (now())::timestamp(2) without time zone'.
So, i can make generate_series work with the left join using the substr.
I was getting ready to optimize this query, when i remembered i also have the
need for another column, 'totalcause99', almost the same as this query, but
with 'cause01=99' as condition.
The maximum i was able to do without syntax errors was:
SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS date,
COUNT (o."04-sms") as totalcause98,
COUNT (p."04-sms") as totalcause99
FROM generate_series(11,19) AS s(d)
LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) = (DATE_TRUNC('month',
timestamp'2006-02-01' )::DATE + s.d) and o.cause01=98)
LEFT JOIN netopia p ON (substr(p."26-insertTime",1,10) = (DATE_TRUNC('month',
timestamp'2006-02-01' )::DATE + s.d) and p.cause01=99)
GROUP BY s.d ORDER BY 1;
Reading this one aloud, i feel the "logic" of what i'm trying to do, but the
values of its output are.. scary to say the least, and the sums are exactly
the same on the 2 columns, and that should never happen with the data i have
on the table.
I'm starting to wonder if this is actually possible to be done on one single
query...
Ideas, anyone?
Sorry for the long email.
Any and all help is deeply appreciated.
Regards,
--
\\pb
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
Pedro,
Would something such as this suffice?
Mark
create function get_date_range(date, date) returns setof date as '
DECLARE
cur date;
BEGIN
cur := $1;
while cur <= $2 LOOP
return next cur;
cur := cur + interval ''1 day'';
end LOOP;
return;
END;' language 'plpgsql';
dev=# select * from get_date_range('2/1/2006'::date, '2/28/2006'::date);
get_date_range
2006-02-01
2006-02-02
2006-02-03
2006-02-04
2006-02-05
2006-02-06
2006-02-07
2006-02-08
2006-02-09
2006-02-10
2006-02-11
2006-02-12
2006-02-13
2006-02-14
2006-02-15
2006-02-16
2006-02-17
2006-02-18
2006-02-19
2006-02-20
2006-02-21
2006-02-22
2006-02-23
2006-02-24
2006-02-25
2006-02-26
2006-02-27
2006-02-28
(28 rows)
On Monday 20 February 2006 15:30, Pedro B. wrote:
> Hello.
> I'm having difficulties on my first incursion through generate_series.
>
> The details:
>
> SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS date,
> COUNT (o."04-sms") as totalcause98
> FROM generate_series(11,19) AS s(d)
> LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) =
> (DATE_TRUNC('month', timestamp'2006-02-01' )::DATE + s.d) andcreate
function get_date_range(date, date) returns setof date as '
DECLARE
cur date;
BEGIN
cur := $1;
while cur <= $2 LOOP
return next cur;
cur := cur + interval ''1 day'';
end LOOP;
return;
END;' language 'plpgsql';
dev=# select * from get_date_range('2/1/2006'::date, '2/28/2006'::date);
get_date_range
2006-02-01
2006-02-02
2006-02-03
2006-02-04
2006-02-05
2006-02-06
2006-02-07
2006-02-08
2006-02-09
2006-02-10
2006-02-11
2006-02-12
2006-02-13
2006-02-14
2006-02-15
2006-02-16
2006-02-17
2006-02-18
2006-02-19
2006-02-20
2006-02-21
2006-02-22
2006-02-23
2006-02-24
2006-02-25
2006-02-26
2006-02-27
2006-02-28
(28 rows)
> o.cause01=98)
> GROUP BY s.d ORDER BY 1;
>
>
> This query (although quite messed up on the date parameters), does exactly
> what i want:
> "sum column 'cause01=98' for a specified date range, including 0's"
>
> date| totalcause98
> +--
> 2006-02-12 |0
> 2006-02-13 |0
> 2006-02-14 |0
> 2006-02-15 |0
> 2006-02-16 | 68
> 2006-02-17 | 256
> 2006-02-18 | 104
> 2006-02-19 | 34
> 2006-02-20 | 20
>
> I'm using a left join because i really need the =0 sums.
> The use of substr() is due to the fact the "26-insertTime" on the 'netopia'
> table has a default of 'default (now())::timestamp(2) without time zone'.
> So, i can make generate_series work with the left join using the substr.
> I was getting ready to optimize this query, when i remembered i also have
> the need for another column, 'totalcause99', almost the same as this query,
> but with 'cause01=99' as condition.
>
> The maximum i was able to do without syntax errors was:
>
> SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS date,
> COUNT (o."04-sms") as totalcause98,
> COUNT (p."04-sms") as totalcause99
> FROM generate_series(11,19) AS s(d)
> LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) =
> (DATE_TRUNC('month', timestamp'2006-02-01' )::DATE + s.d) and
> o.cause01=98)
> LEFT JOIN netopia p ON (substr(p."26-insertTime",1,10) =
> (DATE_TRUNC('month', timestamp'2006-02-01' )::DATE + s.d) and
> p.cause01=99)
> GROUP BY s.d ORDER BY 1;
>
> Reading this one aloud, i feel the "logic" of what i'm trying to do, but
> the values of its output are.. scary to say the least, and the sums are
> exactly the same on the 2 columns, and that should never happen with the
> data i have on the table.
>
> I'm starting to wonder if this is actually possible to be done on one
> single query...
> Ideas, anyone?
>
> Sorry for the long email.
> Any and all help is deeply appreciated.
>
> Regards,
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] ... more than one count with left join
On Monday 20 February 2006 20:39, Mark R. Dingee Pedro wrote:
|> Pedro,
|>
|> Would something such as this suffice?
Hello Mark,
It's far superior to what i was doing, serialization wise. Thank you.
However, it still leaves me with the big headache of the left joins with the
"count ... where..."...
Thanks,
\\pb
|>
|> Mark
|>
|> create function get_date_range(date, date) returns setof date as '
|> DECLARE
|> cur date;
|> BEGIN
|> cur := $1;
|>
|> while cur <= $2 LOOP
|> return next cur;
|> cur := cur + interval ''1 day'';
|> end LOOP;
|> return;
|> END;' language 'plpgsql';
|>
|> dev=# select * from get_date_range('2/1/2006'::date, '2/28/2006'::date);
|> get_date_range
|>
|> 2006-02-01
|> 2006-02-02
|> 2006-02-03
|> 2006-02-04
|> 2006-02-05
|> 2006-02-06
|> 2006-02-07
|> 2006-02-08
|> 2006-02-09
|> 2006-02-10
|> 2006-02-11
|> 2006-02-12
|> 2006-02-13
|> 2006-02-14
|> 2006-02-15
|> 2006-02-16
|> 2006-02-17
|> 2006-02-18
|> 2006-02-19
|> 2006-02-20
|> 2006-02-21
|> 2006-02-22
|> 2006-02-23
|> 2006-02-24
|> 2006-02-25
|> 2006-02-26
|> 2006-02-27
|> 2006-02-28
|> (28 rows)
|>
|> On Monday 20 February 2006 15:30, Pedro B. wrote:
|> > Hello.
|> > I'm having difficulties on my first incursion through generate_series.
|> >
|> > The details:
|> >
|> > SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS
|> > date, COUNT (o."04-sms") as totalcause98
|> > FROM generate_series(11,19) AS s(d)
|> > LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) =
|> > (DATE_TRUNC('month', timestamp'2006-02-01' )::DATE + s.d) andcreate
|>
|> function get_date_range(date, date) returns setof date as '
|> DECLARE
|> cur date;
|> BEGIN
|> cur := $1;
|>
|> while cur <= $2 LOOP
|> return next cur;
|> cur := cur + interval ''1 day'';
|> end LOOP;
|> return;
|> END;' language 'plpgsql';
|>
|> dev=# select * from get_date_range('2/1/2006'::date, '2/28/2006'::date);
|> get_date_range
|>
|> 2006-02-01
|> 2006-02-02
|> 2006-02-03
|> 2006-02-04
|> 2006-02-05
|> 2006-02-06
|> 2006-02-07
|> 2006-02-08
|> 2006-02-09
|> 2006-02-10
|> 2006-02-11
|> 2006-02-12
|> 2006-02-13
|> 2006-02-14
|> 2006-02-15
|> 2006-02-16
|> 2006-02-17
|> 2006-02-18
|> 2006-02-19
|> 2006-02-20
|> 2006-02-21
|> 2006-02-22
|> 2006-02-23
|> 2006-02-24
|> 2006-02-25
|> 2006-02-26
|> 2006-02-27
|> 2006-02-28
|> (28 rows)
|>
|> > o.cause01=98)
|> > GROUP BY s.d ORDER BY 1;
|> >
|> >
|> > This query (although quite messed up on the date parameters), does
|> > exactly what i want:
|> > "sum column 'cause01=98' for a specified date range, including 0's"
|> >
|> > date | totalcause98
|> > +--
|> > 2006-02-12 | 0
|> > 2006-02-13 | 0
|> > 2006-02-14 | 0
|> > 2006-02-15 | 0
|> > 2006-02-16 | 68
|> > 2006-02-17 | 256
|> > 2006-02-18 | 104
|> > 2006-02-19 | 34
|> > 2006-02-20 | 20
|> >
|> > I'm using a left join because i really need the =0 sums.
|> > The use of substr() is due to the fact the "26-insertTime" on the
|> > 'netopia' table has a default of 'default (now())::timestamp(2) without
|> > time zone'. So, i can make generate_series work with the left join
|> > using the substr. I was getting ready to optimize this query, when i
|> > remembered i also have the need for another column, 'totalcause99',
|> > almost the same as this query, but with 'cause01=99' as condition.
|> >
|> > The maximum i was able to do without syntax errors was:
|> >
|> > SELECT DATE_TRUNC('month', timestamp '2006-02-01' )::DATE + s.d AS
|> > date, COUNT (o."04-sms") as totalcause98,
|> > COUNT (p."04-sms") as totalcause99
|> > FROM generate_series(11,19) AS s(d)
|> > LEFT JOIN netopia o ON (substr(o."26-insertTime",1,10) =
|> > (DATE_TRUNC('month', timestamp'2006-02-01' )::DATE + s.d) and
|> > o.cause01=98)
|> > LEFT JOIN netopia p ON (substr(p."26-insertTime",1,10) =
|> > (DATE_TRUNC('month', timestamp'2006-02-01' )::DATE + s.d) and
|> > p.cause01=99)
|> > GROUP BY s.d ORDER BY 1;
|> >
|> > Reading this one aloud, i feel the "logic" of what i'm trying to do,
|> > but the values of its output are.. scary to say the least, and the sums
|> > are exactly the same on the 2 columns, and that should never happen
|> > with the data i have on the table.
|> >
|> > I'm starting to wonder if this is actually possible to be done on one
|> > single query...
|> > Ideas, anyone?
|> >
|> > Sorry for the long email.
|> > Any and all help is deeply appreciated.
|> >
|> > Regards,
|>
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.po
Re: [SQL] ORDER BY with LTREE
On Mon, Feb 20, 2006 at 11:15:43AM +0100, Axel Straschil wrote:
> I have a table with a ltree and acaption "CREATE TABLE (tree LTREE,
> caption TEXT)", I can't store the data of caption in the ltree, becouse of
> the limitation of the ltree labels. I use the ltree to give the captions
> a direcory like hirachy, wenn a new caption is createt, the customer can
> decide which is the parent caption.
>
> My Problem now ist, that I want to show in a list-box all the captions
> ordered by ltree, but in the same hirachy by the caption.
Maybe somebody can think of a better way, but as I mentioned in my
previous post you could create a custom operator to use in the ORDER
BY clause. The operator's function would receive two rows: if the
rows have a common parent in the hierarchy then the function could
compare the rows' labels; otherwise it could query for other rows
and perform appropriate comparisons. With a large table those queries
could make ordering expensive; if anybody has a better suggestion
then hopefully they'll mention it.
Below is a simple example using the data from your original message
and giving the results you requested. It's intended to be a starting
point, not a final solution. I have only a little experience with
ltree and with using custom operators in this manner, so corrections
and improvements are welcome.
CREATE TABLE foo (tree ltree, caption text);
INSERT INTO foo VALUES ('root.1', 'z');
INSERT INTO foo VALUES ('root.2', 'c');
INSERT INTO foo VALUES ('root.2.1', 'a');
INSERT INTO foo VALUES ('root.2.2', 'b');
INSERT INTO foo VALUES ('root.3', 'i');
INSERT INTO foo VALUES ('root.4', 'f');
INSERT INTO foo VALUES ('root.4.1', 'k');
INSERT INTO foo VALUES ('root.4.2', 'c');
CREATE FUNCTION foo_lt(foo, foo) RETURNS boolean AS $$
DECLARE
level integer;
row1 foo%ROWTYPE;
row2 foo%ROWTYPE;
BEGIN
IF subpath($1.tree, 0, -1) = subpath($2.tree, 0, -1) THEN
RETURN $1.caption < $2.caption;
ELSIF $1.tree @> $2.tree THEN
RETURN true;
ELSE
level := nlevel(lca($1.tree, $2.tree)) + 1;
SELECT INTO row1 * FROM foo WHERE tree = subpath($1.tree, 0, level);
SELECT INTO row2 * FROM foo WHERE tree = subpath($2.tree, 0, level);
RETURN row1.caption < row2.caption;
END IF;
END;
$$ LANGUAGE plpgsql STABLE STRICT;
CREATE OPERATOR < (
PROCEDURE = foo_lt,
LEFTARG = foo,
RIGHTARG = foo
);
SELECT * FROM foo ORDER BY foo USING <;
tree | caption
--+-
root.2 | c
root.2.1 | a
root.2.2 | b
root.4 | f
root.4.2 | c
root.4.1 | k
root.3 | i
root.1 | z
(8 rows)
--
Michael Fuhr
---(end of broadcast)---
TIP 1: 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
