Re: [SQL] improvements to query with hierarchical elements
At 07:24 PM 1/22/2008, you wrote: Hi all, I have created a little test database to help illustrate my situation. CREATE TABLE categories ( id integer NOT NULL, name character varying(255) NOT NULL, description character varying(255), vocabulary_id integer, derived boolean ); CREATE TABLE category_descendants ( id integer NOT NULL, ancestor_id integer, descendant_id integer, distance integer, derived boolean ); CREATE TABLE category_links ( id integer NOT NULL, parent_id integer, child_id integer, derived boolean ); [snip..] As stated in my last post, any help you can give on how to improve queries of this type would be very much appreciated. Thanks! Ryan Hi Ryan, I've been toying with your sample data for a bit and I apologize but your query has me baffled. Not that it's wrong - it actually looks very sophisticated, but it seems super complex to me - kind of like how I usually feel reading perl.. :) I'm sure real sql-heads would get it right away but I'm not able to. If you're looking to optimize the use-case you provided in your first email, the best thing I can suggest from what I understand would make an assumption: Are the data in your tables are slowly changing? So could you build some analytic/pre-calculated data into these tables or related supporting ones to guide your searches/queries? For example, if you want to find only records which are immediate children of other records, why not make a table which stores just that information? Your current tables are fully hierarchical which is great, but you want to look things up quickly based on a specific relationship: records who are direct children of a particular record.. So if you made a calculated table that stores this information, you could keep it up to date either by running the calculation script periodically or by attaching updates to relevant triggers / rules. I'm sorry I'm not able to get into the SQL / example you sent further. I got lost in the code, which I'm a little embarrassed to admit but there you are. If you're interested in this idea of precalculating values to optimize your search, I'd be happy to discuss further. Also, Ralph Kimball's Data Warehousing books are excellent on this subject (one of the few authors who truly changed the way I think about data). Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] This SQL works under Mysql, not Postgresql.
On Jan 25, 2008 10:11 AM, acec acec <[EMAIL PROTECTED]> wrote: > I have the following sql, which works fine under mysql > database: > SELECT sa.ID, suv.TOTAL as VOICE_TOTAL, sus.TOTAL as > SMS_TOTAL FROM SUB_ACCOUNT sa INNER JOIN SUBSCRIBER s > ON (sa.ID = s.SUB_ACCOUNT_ID) LEFT JOIN (SERVICE suv, > SERVICE sus) ON (sa.ID = suv.SUB_ACC_ID AND > suv.SERVICE_ID = 0 AND sa.ID = sus.SUB_ACC_ID AND > sus.SERVICE_ID = 1) WHERE s.TELEPHONE = '1'; > When I ran it under postgresql, which gave me "ERROR: > syntax error at or near" > It looks like I could not put two table on LEFT JOIN: > LEFT JOIN (SERVICE suv, SERVICE sus) > > Do you have any suggestion for this problem? Is that legal SQL? I've never seen anything like that before... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] regex_replace problem -additional
Sorry, should have added that I tried type-casting to see if that fixed it. It didn't. Gary goole=# select distinct regex_replace(ud_rfl::text,' *= *'::text,'+'::text) from used_diary where ud_rfl ~ ' *= *'; ERROR: function regex_replace(text, text, text) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. goole=# -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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
[SQL] regex_replace problem
Hi folks. I've got a problem with regex_replace. As you can see below psql displays the error saying the function does not exist. Can anyone see why, and what I need todo to fix it. Cheers goole=# select distinct ud_rfl from used_diary where ud_rfl ~ ' *= *'; ud_rfl --- 12 = 1wk 12=1WK 12m = 1wk 6=1WK (4 rows) goole=# select distinct regex_replace(ud_rfl,' *= *','+') from used_diary where ud_rfl ~ ' *= *'; ERROR: function regex_replace(character varying, "unknown", "unknown") does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. goole=# select distinct replace(ud_rfl,'=','+') from used_diary where ud_rfl ~ ' *= *'; replace --- 12 + 1wk 12+1WK 12m + 1wk 6+1WK (4 rows) [EMAIL PROTECTED] ~]# rpm -qa|grep postgresql postgresql-jdbc-8.1.9-1.fc5 postgresql-libs-8.1.9-1.fc5 postgresql-pl-8.1.9-1.fc5 postgresql-contrib-8.1.9-1.fc5 postgresql-server-8.1.9-1.fc5 postgresql-docs-8.1.9-1.fc5 postgresql-python-8.1.9-1.fc5 postgresql-8.1.9-1.fc5 postgresql-test-8.1.9-1.fc5 postgresql-tcl-8.1.9-1.fc5 [EMAIL PROTECTED] ~]# -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] regex_replace problem
Gary Stainburn wrote: Hi folks. I've got a problem with regex_replace. The function is regexp_replace - you misspelled it. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] age() function usage
On Jan 25, 2008 1:06 PM, Marcin Krawczyk <[EMAIL PROTECTED]> wrote: > Hi all. I am trying to determine the way to pass a variable/field value to > an age() function, query looks something like: > > SELECT age(timestamp data_zakonczenia_fakt) FROM kip_pracownicy_umowy WHERE > id_pracownika = 8 > > data_zakonczenia_fakt being char column equal to say '1993-11-30'. > Such approach won't work, can anyone tell me the way to do it? > > SELECT age(timestamp '1993-11-30') works great. > Same story with age(timestamp '2008-01-01', timestamp '1993-11-30'). You need an explicit cast: SELECT age(data_zakonczenia_fakt::timestamp) FROM kip_pracownicy_umowy WHERE id_pracownika = 8 ---(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] Extract interdependent info from one table
On 25/01/2008, Phillip Smith <[EMAIL PROTECTED]> wrote:
> Absolutely clear as mud :P
sorry ... I'm not too good with expressing my SQL needs, I'm afraid,
Thanks for trying :}
In the end I got it sorted with the following
select
dm.docnum,
dm.alias1,
dm.docname,
dm.alias2 as "Status",
dm2.alias2 as "Reply"
from docmaster as dm
left outer join
( select
alias1,
subclass_alias,
alias2,
entrywhen
from docmaster
where subclass_alias in ('Reply','MinTR')
and alias2 in ('PENDING','COMPLETED')
) as dm2
on dm2.alias1 = dm.alias1
where dm.subclass_alias='Post'
and alias2 in ('PENDING','REDRAFT');
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
http://www.american.edu/econ/notes/htmlmail.htm
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] age() function usage
Hi all. I am trying to determine the way to pass a variable/field value to an age() function, query looks something like: SELECT age(timestamp data_zakonczenia_fakt) FROM kip_pracownicy_umowy WHERE id_pracownika = 8 data_zakonczenia_fakt being char column equal to say '1993-11-30'. Such approach won't work, can anyone tell me the way to do it? SELECT age(timestamp '1993-11-30') works great. Same story with age(timestamp '2008-01-01', timestamp '1993-11-30'). Thanks in advance mk
Re: [SQL] regex_replace problem -additional
On Jan 25, 2008 10:19 AM, Gary Stainburn <[EMAIL PROTECTED]> wrote: > Sorry, > > should have added that I tried type-casting to see if that fixed it. It > didn't. > > Gary > > goole=# select distinct regex_replace(ud_rfl::text,' *= *'::text,'+'::text) \df regexp* List of functions Schema | Name | Result data type | Argument data types ++--+ pg_catalog | regexp_replace | text | text, text, text pg_catalog | regexp_replace | text | text, text, text, text I think you lost a p in there. ---(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
Re: [SQL] date format
iuri de araujo sampaio wrote:
hi,
how to change the default format for type date?
I have created a field on a table:
##
create table tbl_inventory (
item_id integer constraint c_pk primary key,
I. purchase_date date,
II. fabrication_date date,
III. expiration_date date
);
##
the error i got is:
##
Database operation "dml" failed
(exception ERROR, "ERROR: invalid input syntax for type date: "2008 7 22 {} {} {}
{DD MONTH }"
")
##
Assuming that your import code has something similar to:
insert into tbl_inventory(item_id,purchase_date)
values(1,"2008 7 22 {} {} {} {DD MONTH }")
I would change it to something like:
insert into tbl_inventory(item_id,purchase_date)
values(1,regexp_replace('2008 7 22 {} {} {} {DD MONTH }','{.+}
*','','g')::date);
Frank
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] This SQL works under Mysql, not Postgresql.
Maybe it is not legal sql according to SQL standard, but it works under mysql, I try to port it into Postgresql. --- Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Jan 25, 2008 10:11 AM, acec acec > <[EMAIL PROTECTED]> wrote: > > I have the following sql, which works fine under > mysql > > database: > > SELECT sa.ID, suv.TOTAL as VOICE_TOTAL, sus.TOTAL > as > > SMS_TOTAL FROM SUB_ACCOUNT sa INNER JOIN > SUBSCRIBER s > > ON (sa.ID = s.SUB_ACCOUNT_ID) LEFT JOIN (SERVICE > suv, > > SERVICE sus) ON (sa.ID = suv.SUB_ACC_ID AND > > suv.SERVICE_ID = 0 AND sa.ID = sus.SUB_ACC_ID AND > > sus.SERVICE_ID = 1) WHERE s.TELEPHONE = > '1'; > > When I ran it under postgresql, which gave me > "ERROR: > > syntax error at or near" > > It looks like I could not put two table on LEFT > JOIN: > > LEFT JOIN (SERVICE suv, SERVICE sus) > > > > Do you have any suggestion for this problem? > > Is that legal SQL? I've never seen anything like > that before... > Connect with friends from any web browser - no download required. Try the new Yahoo! Canada Messenger for the Web BETA at http://ca.messenger.yahoo.com/webmessengerpromo.php ---(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] This SQL works under Mysql, not Postgresql.
Please keep replies on list, others may have answers I do not. On Jan 25, 2008 10:29 AM, acec acec <[EMAIL PROTECTED]> wrote: > --- Scott Marlowe <[EMAIL PROTECTED]> wrote: > > On Jan 25, 2008 10:11 AM, acec acec > > <[EMAIL PROTECTED]> wrote: > > > I have the following sql, which works fine under > > mysql > > > database: > > > SELECT sa.ID, suv.TOTAL as VOICE_TOTAL, sus.TOTAL > > as > > > SMS_TOTAL FROM SUB_ACCOUNT sa INNER JOIN > > SUBSCRIBER s > > > ON (sa.ID = s.SUB_ACCOUNT_ID) LEFT JOIN (SERVICE > > suv, > > > SERVICE sus) ON (sa.ID = suv.SUB_ACC_ID AND > > > suv.SERVICE_ID = 0 AND sa.ID = sus.SUB_ACC_ID AND > > > sus.SERVICE_ID = 1) WHERE s.TELEPHONE = > > '1'; > > > When I ran it under postgresql, which gave me > > "ERROR: > > > syntax error at or near" > > > It looks like I could not put two table on LEFT > > JOIN: > > > LEFT JOIN (SERVICE suv, SERVICE sus) > > > > > > Do you have any suggestion for this problem? > > > > Is that legal SQL? I've never seen anything like > > that before... > > > Maybe it is not legal sql according to SQL standard, > but it works under mysql, I try to port it into > Postgresql. Well, I'd say just breaking each part of the "LEFT JOIN (SERVICE suv, SERVICE sus)" and the on () clause should work. ---(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
[SQL] This SQL works under Mysql, not Postgresql.
I have the following sql, which works fine under mysql database: SELECT sa.ID, suv.TOTAL as VOICE_TOTAL, sus.TOTAL as SMS_TOTAL FROM SUB_ACCOUNT sa INNER JOIN SUBSCRIBER s ON (sa.ID = s.SUB_ACCOUNT_ID) LEFT JOIN (SERVICE suv, SERVICE sus) ON (sa.ID = suv.SUB_ACC_ID AND suv.SERVICE_ID = 0 AND sa.ID = sus.SUB_ACC_ID AND sus.SERVICE_ID = 1) WHERE s.TELEPHONE = '1'; When I ran it under postgresql, which gave me "ERROR: syntax error at or near" It looks like I could not put two table on LEFT JOIN: LEFT JOIN (SERVICE suv, SERVICE sus) Do you have any suggestion for this problem? Thanks in advance. Looking for the perfect gift? Give the gift of Flickr! http://www.flickr.com/gift/ ---(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
[SQL] Unclosed connections
We are using this bad piece of the software that does not close connections to the postgres server. Is there some setting for closing dead connections? And not TCP/IP keep alive does not work. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Unclosed connections
On Jan 25, 2008 10:02 AM, PostgreSQL Admin <[EMAIL PROTECTED]> wrote: > We are using this bad piece of the software that does not close > connections to the postgres server. Is there some setting for closing > dead connections? And not TCP/IP keep alive does not work. If the TCP keepalive can't detect them as dead, how is something else supposed to? I.e. if a keepalive packet gets answered, then the connection isn't dead, something is still connected to it. ---(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] This SQL works under Mysql, not Postgresql.
Neither have I. The LEFT JOIN I know is something like SELECT ... FROM table1 LEFT OUTER JOIN table2 ON Try using this construct Best, Oliveiros - Original Message - From: "Scott Marlowe" <[EMAIL PROTECTED]> To: "acec acec" <[EMAIL PROTECTED]> Cc: Sent: Friday, January 25, 2008 4:23 PM Subject: Re: [SQL] This SQL works under Mysql, not Postgresql. > On Jan 25, 2008 10:11 AM, acec acec <[EMAIL PROTECTED]> wrote: >> I have the following sql, which works fine under mysql >> database: >> SELECT sa.ID, suv.TOTAL as VOICE_TOTAL, sus.TOTAL as >> SMS_TOTAL FROM SUB_ACCOUNT sa INNER JOIN SUBSCRIBER s >> ON (sa.ID = s.SUB_ACCOUNT_ID) LEFT JOIN (SERVICE suv, >> SERVICE sus) ON (sa.ID = suv.SUB_ACC_ID AND >> suv.SERVICE_ID = 0 AND sa.ID = sus.SUB_ACC_ID AND >> sus.SERVICE_ID = 1) WHERE s.TELEPHONE = '1'; >> When I ran it under postgresql, which gave me "ERROR: >> syntax error at or near" >> It looks like I could not put two table on LEFT JOIN: >> LEFT JOIN (SERVICE suv, SERVICE sus) >> >> Do you have any suggestion for this problem? > > Is that legal SQL? I've never seen anything like that before... > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
Re: [SQL] This SQL works under Mysql, not Postgresql.
"Scott Marlowe" <[EMAIL PROTECTED]> writes: > On Jan 25, 2008 10:11 AM, acec acec <[EMAIL PROTECTED]> wrote: >> I have the following sql, which works fine under mysql >> database: >> SELECT sa.ID, suv.TOTAL as VOICE_TOTAL, sus.TOTAL as >> SMS_TOTAL FROM SUB_ACCOUNT sa INNER JOIN SUBSCRIBER s >> ON (sa.ID = s.SUB_ACCOUNT_ID) LEFT JOIN (SERVICE suv, >> SERVICE sus) ON (sa.ID = suv.SUB_ACC_ID AND >> suv.SERVICE_ID = 0 AND sa.ID = sus.SUB_ACC_ID AND >> sus.SERVICE_ID = 1) WHERE s.TELEPHONE = '1'; > Is that legal SQL? It is not, though given mysql's historical inability to implement the JOIN syntax per-spec, it's not too surprising that they'd show such a weak grasp of correct syntax. Maybe replace the comma with CROSS JOIN? ... (SERVICE suv CROSS JOIN SERVICE sus) ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] age() function usage
Heh, that was easy, I must have been working for too long... :) Thanks 2008/1/25, Scott Marlowe <[EMAIL PROTECTED]>: > > On Jan 25, 2008 1:06 PM, Marcin Krawczyk <[EMAIL PROTECTED]> wrote: > > Hi all. I am trying to determine the way to pass a variable/field value > to > > an age() function, query looks something like: > > > > SELECT age(timestamp data_zakonczenia_fakt) FROM kip_pracownicy_umowy > WHERE > > id_pracownika = 8 > > > > data_zakonczenia_fakt being char column equal to say '1993-11-30'. > > Such approach won't work, can anyone tell me the way to do it? > > > > SELECT age(timestamp '1993-11-30') works great. > > Same story with age(timestamp '2008-01-01', timestamp '1993-11-30'). > > You need an explicit cast: > > SELECT age(data_zakonczenia_fakt::timestamp) FROM kip_pracownicy_umowy > WHERE id_pracownika = 8 >
