[SQL] Tuning complicated query
Hi: Attached to the e-mail is the body of the query and the result of the EXPLAIN (Sorry for not placing the query and EXPLAIN in the e-mail body . The query is rather complicated and the EXPLAIN result is rather long ). The file demo.out.3 is the result of the EXPLAIN The file demo.sql is the sql statement. I would like your opinion on how to tune the query as posted in the attachment Note that I have indexes on the all the column customer_id on both sc_customer_attr and sc_add_points. I am wondering why sequential scan was used the on the clause a.customer_id = b.customer_id since the previous join condition has an "exist" subquery with LIMIT with filters out unneccesary customer_id before performing the join (a_customer_id = b.customer_id). Also I was wondering why the number of rows in the last sequential scan is still 7 million plus (most of the should already have been elimated by the subquery). Note that before the executing the query, the database has been VACUUMed and ANALYZEd. The result of EXPLAIN ANALYZE is almost similar to one produce by issuing the EXPLAIN. Any hints on tuning the query? thank you ludwig __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com psql:demo.sql5:31: NOTICE: QUERY PLAN: Limit (cost=602630531.21..602630531.21 rows=10 width=69) -> Sort (cost=602630531.21..602630531.21 rows=218145 width=69) -> Aggregate (cost=602583597.46..602609774.87 rows=218145 width=69) -> Group (cost=602583597.46..602596686.16 rows=2181451 width=69) -> Sort (cost=602583597.46..602583597.46 rows=2181451 width=69) -> Merge Join (cost=602162862.44..602184219.45 rows=2181451 width=69) -> Sort (cost=600998172.01..600998172.01 rows=52125 width=49) -> Merge Join (cost=600994410.42..600994904.87 rows=52125 width=49) -> Sort (cost=13.25..13.25 rows=302 width=25) -> Seq Scan on sc_attr c (cost=0.00..3.30 rows=302 width=25) -> Sort (cost=600994397.17..600994397.17 rows=64485 width=24) -> Seq Scan on sc_customer_attr a (cost=0.00..600990276.11 rows=64485 width=24) SubPlan -> Limit (cost=736.25..736.25 rows=1 width=20) -> Subquery Scan z (cost=736.25..736.25 rows=1 width=20) -> Limit (cost=736.25..736.25 rows=1 width=20) -> Sort (cost=736.25..736.25 rows=1 width=20) -> Aggregate (cost=0.00..736.24 rows=1 width=20) -> Group (cost=0.00..736.24 rows=1 width=20) -> Index Scan using xie2sc_add_points on sc_add_points d (cost=0.00..736.24 rows=1 width=20) -> Sort (cost=1164690.44..1164690.44 rows=7354200 width=20) -> Seq Scan on sc_add_points b (cost=0.00..138679.20 rows=7354200 width=20) explain select count(distinct(b.customer_id)) as members, sum(b.total_loyalty) as sales, count(b.customer_id) as visits, c.attr_cd, c.attr_type_cd, c.description as description fromsc_customer_attr a, sc_add_points b, sc_attr c whereexists (select z.customer_id from (select d.customer_id, sum(d.total_loyalty) as points from sc_add_points d where d.transdate >= 19980100.00 and d.transdate <= 20020931.00 and d.company_cd = 1 and d.branch_cd = 13 and a.customer_id = d.customer_id group by d.customer_id order by points desc limit 100 ) as z ) and a.attr_cd = c.attr_cd and a.attr_type_cd = c.attr_type_cd and a.attr_type_cd = 2 and a.company_cd = c.company_cd and
[SQL] start and end of the week
How do I get the start and end date of the present week? Is this possible? For example this week Start = Sept. 22 End = Sept. 28 Thank you very much. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] start and end of the week
On Thu, Sep 26, 2002 at 18:56:46 +0800, "John Sebastian N. Mayordomo" <[EMAIL PROTECTED]> wrote: > > > How do I get the start and end date of the present week? > Is this possible? > > For example this week > Start = Sept. 22 > End = Sept. 28 The following advice will work on 7.3. For 7.2.2 and earlier it might not work during a week with a timezone change depending at what time of day you switch between DST and ST. To fix this you need to cast current_date to a timestamp without timezone, and I haven't been able to figure out how to do that and have to run off to a meeting now. For the first day of the current week use something like: area=> select current_date - extract(dow from current_date) * area-> '1 day'::interval; ?column? - 2002-09-22 00:00:00 (1 row) For the last day of the week use something like: area=> select current_date + (6 - extract(dow from current_date)) * area-> '1 day'::interval; ?column? - 2002-09-28 00:00:00 (1 row) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] start and end of the week
How about: select now() - date_part( 'DOW', now()) as starts_on, now() -date_part( 'DOW', now()) + 6 as ends_on; "John Sebastian N. Mayordomo" wrote: > > How do I get the start and end date of the present week? > Is this possible? > > For example this week > Start = Sept. 22 > End = Sept. 28 > > Thank you very much. > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL formatter?
Unfortunately it is Windows based. The emacs mode for SQL is pretty primitive too. Oh well - maybe I'll write one someday. Thanks, Andy -- Andrew J Perrin - http://www.unc.edu/~aperrin Assistant Professor of Sociology, U of North Carolina, Chapel Hill [EMAIL PROTECTED] * andrew_perrin (at) unc.edu On Wed, 25 Sep 2002, Philip Hallstrom wrote: > Looks to be windows based, but... > > >http://www.techno-kitten.com/PBL_Peeper/Online_Manual/SQL_Formatter/sql_formatter.html > > first hit when searching on google for "sql formatter". there were a lot > of other options... > > You might look at how some of those C code indenter's work. Seems like > some of them support multiple languages which means they maybe have some > sort of "language definition" so maybe you could just write a sql one and > it would just work. Of course I've never used one and don't know anything > about it really so I could be wrong :) > > -philip > > On Wed, 25 Sep 2002, Andrew Perrin wrote: > > > Does anyone know of a routine for formatting SQL statements in a > > structured way? Standalone or for emacs would be fine. I'm thinking of > > something that could take a long SQL text statement and format it, e.g.: > > > > select foo from bar where baz and bop and not boo; > > > > becomes > > > > SELECT foo > > FROM bar > > WHERE baz > >AND bop > >AND NOT boo > > ; > > > > Thanks, > > Andy > > > > -- > > Andrew J Perrin - http://www.unc.edu/~aperrin > > Assistant Professor of Sociology, U of North Carolina, Chapel Hill > > [EMAIL PROTECTED] * andrew_perrin (at) unc.edu > > > > > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] start and end of the week
On Thu, Sep 26, 2002 at 11:55:48 -0400, Jean-Luc Lachance <[EMAIL PROTECTED]> wrote: > How about: > > select now() - date_part( 'DOW', now()) as starts_on, > now() -date_part( 'DOW', now()) + 6 as ends_on; That won't work in 7.3. The following works in both 7.2 and 7.3: area=> select current_date - extract(dow from current_date)::int as start_date, area-> current_date - extract(dow from current_date)::int + 6 as end_date; start_date | end_date + 2002-09-22 | 2002-09-28 (1 row) Extract returns double precision and so needs a cast to int to work. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] start and end of the week
Does any one know what is the reason not to put this logic into date_trunc () function? It seems to work with pretty much *any* unit imaginable, *except* for 'week'... Dima Bruno Wolff III wrote: > On Thu, Sep 26, 2002 at 11:55:48 -0400, > Jean-Luc Lachance <[EMAIL PROTECTED]> wrote: > >>How about: >> >>select now() - date_part( 'DOW', now()) as starts_on, >> now() -date_part( 'DOW', now()) + 6 as ends_on; >> > > That won't work in 7.3. > > The following works in both 7.2 and 7.3: > area=> select current_date - extract(dow from current_date)::int as start_date, > area-> current_date - extract(dow from current_date)::int + 6 as end_date; > start_date | end_date > + > 2002-09-22 | 2002-09-28 > (1 row) > > Extract returns double precision and so needs a cast to int to work. > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] check source of trigger
Hi, In case you doing all this to replicate tables conside contrib/dbmirror it does it fairly elegantly. regds mallah. On Friday 20 September 2002 13:55, wit wrote: > Hello, > > I have a question about trigger. I have tables with the following > structure: > > create table A ( >e_codeA char(5) default '' not null, >n_codeA varchar(20) default '' not null, >constraint A_pkey primary key ( e_codeA ) > ); > > create table B ( >e_codeB char(5) default '' not null, >e_codeA char(5) default '' not null > constraint e_codeA_ref references A( e_codeA ) > on delete cascade on update cascade, >n_codeB varchar(20) default '' not null, >constraint B_pkey primary key ( e_tranB, e_codeA ) > ); > > I have trigger and procedure on table B to capture any change and insert > into table logB: > create trigger trigger_b before insert or update or delete on B for > each row execute procedure log_change(); > > When I update e_codeA in table A, the constrain trigger will update e_codeA > in B. My trigger, trigger_b, also was trigged and procedure will record > change into table logB too. > How to write a code in my db procedure to check whether the procedure was > called by normal SQL or was called by cascade trigger. > > Regards, > wit > > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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
[SQL] None
-- Jordan Reiter mailto:[EMAIL PROTECTED] Breezing.com http://breezing.com 1106 West Main St phone:434.295.2050 Charlottesville, VA 22903 fax:603.843.6931 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Case Sensitive "WHERE" Clauses?
Are string comparisons in postgresql case sensitive? I keep on having this response: SELECT * FROM People WHERE first_name='jordan' Result: 0 records SELECT * FROM People WHERE first_name='Jordan' Result: 1 record I though that string matching in SQL was case-insensitive. Isn't this correct? If not, what workarounds have been used successfully before? Obviously, formatting the search string for the query is not a solution... -- Jordan Reiter mailto:[EMAIL PROTECTED] Breezing.com http://breezing.com 1106 West Main St phone:434.295.2050 Charlottesville, VA 22903 fax:603.843.6931 ---(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] Case Sensitive "WHERE" Clauses?
No, I don't think it's supposed to be case-sensitive. In any case, whether it's supposed to be or not, it certainly isn't in practice. Solutions include: SELECT * FROM People WHERE lower(first_name)='jordan'; and: SELECT * FROM People WHERE first_name ~* 'Jordan'; ap -- Andrew J Perrin - http://www.unc.edu/~aperrin Assistant Professor of Sociology, U of North Carolina, Chapel Hill [EMAIL PROTECTED] * andrew_perrin (at) unc.edu On Thu, 26 Sep 2002, Jordan Reiter wrote: > Are string comparisons in postgresql case sensitive? > > I keep on having this response: > > SELECT * > FROM People > WHERE first_name='jordan' > > Result: 0 records > > SELECT * > FROM People > WHERE first_name='Jordan' > > Result: 1 record > > I though that string matching in SQL was case-insensitive. Isn't this correct? If >not, what workarounds have been used successfully before? Obviously, formatting the >search string for the query is not a solution... > -- > > Jordan Reiter mailto:[EMAIL PROTECTED] > Breezing.com http://breezing.com > 1106 West Main St phone:434.295.2050 > Charlottesville, VA 22903 fax:603.843.6931 > > ---(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 > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Preventing DELETEs
Hi , I have a created a database and a table in it, I want to prevent "DELETES" on the table in this database by everyone except superuser postgres. even by me (the creator of this database and table) I have tried in many ways (ALTER TABLE REVOKE DELETE .. etc etc) but i always end up with having the permission can any one tell me how the prevention can be accomplished? thanks in advance. regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] Preventing DELETEs
> I have a created a database and a table in it, > > I want to prevent "DELETES" on the table in this > database by everyone except superuser postgres. > even by me (the creator of this database and table) make superuser the database owner & grant the rights needed to the users ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Preventing DELETEs
In psuedo-code : create rule on mytable on delete return null Robert Treat On Thu, 2002-09-26 at 15:00, Rajesh Kumar Mallah. wrote: > Hi , > > I have a created a database and a table in it, > > I want to prevent "DELETES" on the table in this > database by everyone except superuser postgres. > even by me (the creator of this database and table) > > > I have tried in many ways (ALTER TABLE REVOKE DELETE .. etc etc) > > but i always end up with having the permission > > > can any one tell me how the prevention can be accomplished? > > thanks in advance. > > regds > mallah. > > > > -- > Rajesh Kumar Mallah, > Project Manager (Development) > Infocom Network Limited, New Delhi > phone: +91(11)6152172 (221) (L) ,9811255597 (M) > > Visit http://www.trade-india.com , > India's Leading B2B eMarketplace. > > > > ---(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 ---(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] Preventing DELETEs
Hi Dima, I currently have only one user in the system its me and superuser postgres. every thing belongs to me currently. and the programs connect as me. if make transfer the database ownership to postgres will all the tables also get transfered to him? it that case all programs will stop working. can i transefer database ownership to postgres and allow myself ALL the PREVILEGES and selectively REVOKE the DELETE permission from myself on the concerned table? If that is possible could you kindly tell me the commands Current state is: tradein_clients=> \l List of databases Name | Owner -+-- template0 | postgres template1 | postgres tradein_clients | tradein (this is me) (3 rows) tradein_clients=> regds Mallah. On Friday 27 September 2002 00:30, dima wrote: > > I have a created a database and a table in it, > > > > I want to prevent "DELETES" on the table in this > > database by everyone except superuser postgres. > > even by me (the creator of this database and table) > > make superuser the database owner & grant the rights needed to the users -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Preventing DELETEs
Hi Robert, I will be obliged to receive the "real" code , if its feasible for you. I am not used RULEs before. regds mallah. On Friday 27 September 2002 00:39, Robert Treat wrote: > In psuedo-code : create rule on mytable on delete return null > > Robert Treat > > On Thu, 2002-09-26 at 15:00, Rajesh Kumar Mallah. wrote: > > Hi , > > > > I have a created a database and a table in it, > > > > I want to prevent "DELETES" on the table in this > > database by everyone except superuser postgres. > > even by me (the creator of this database and table) > > > > > > I have tried in many ways (ALTER TABLE REVOKE DELETE .. etc etc) > > > > but i always end up with having the permission > > > > > > can any one tell me how the prevention can be accomplished? > > > > thanks in advance. > > > > regds > > mallah. > > > > > > > > -- > > Rajesh Kumar Mallah, > > Project Manager (Development) > > Infocom Network Limited, New Delhi > > phone: +91(11)6152172 (221) (L) ,9811255597 (M) > > > > Visit http://www.trade-india.com , > > India's Leading B2B eMarketplace. > > > > > > > > ---(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 -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Help tuning query
First of all, try replacing the username/foldername indexes on operator_messages with a single combined index on, say (username,foldername)... It is still not clear to me why it decides not to use one of those indexes you have (it would be less efficient than a combined index, but still better than a seq. scan) - let's see if having a combined index helps... If it doesn't, we'll need to look deeper into what exactly it is that makes it choose seqscan over an index... I hope, it helps... Dima Kevin Traub wrote: > All; > > Can anyone please help with the tuning of this query? > With 77000 rows in the operator_messages database the query is taking almost > 15 seconds to return. Preference woul dbe under 5 seconds if possible. > System load on a dual processor P3 with 1.5GB of memory remains under .4 > during the query. > The query and explain are noted below as well as description of the tables; > Note both ANALYZE and VACUUM have been run numerous times. > any help would be appreciated. -Kev > > > virgin=# explain SELECT opr.msg_id, opr.status, opc.std_time > virgin-# FROM op_msg_folder opc, operator_messages opr > virgin-# WHERE opr.username = 'khp' > virgin-# AND opr.foldername = 'inbox' > virgin-# and opr.msg_id = opc.msg_id; > NOTICE: QUERY PLAN: > > Merge Join (cost=25037.29..27675.47 rows=47958 width=54) > -> Index Scan using opmf_i on op_msg_folder opc (cost=0.00..1797.37 > rows=48579 width=32) > -> Sort (cost=25037.29..25037.29 rows=47958 width=22) > -> Seq Scan on operator_messages opr (cost=0.00..20722.26 > rows=47958 width=22) > > virgin=# \d operator_messages >Table "operator_messages" >Column | Type | Modifiers > +--+--- > msg_id | numeric | > username | text | > foldername | text | > status | character(1) | > Indexes: op_msgs_i, > opr_msgs_foldername_i, > opr_msgs_username_i > > virgin=# \d op_msgs_i > Index "op_msgs_i" > Column | Type > +- > msg_id | numeric > btree > > virgin=# \d opr_msgs_foldername_i > Index "opr_msgs_foldername_i" >Column | Type > +-- > foldername | text > btree > > virgin=# \d opr_msgs_username_i > Index "opr_msgs_username_i" > Column | Type > --+-- > username | text > btree > > virgin=# \d op_msg_folder >Table "op_msg_folder" > Column | Type | Modifiers > +--+--- > msg_id | numeric | > status | character(1) | > std_time | text | > julian_time| text | > smi| character(3) | > description| text | > type | text | > flight | text | > tail | text | > dep_station| text | > dest_station | text | > op_description | text | > Unique keys: opmf_i > > virgin=# \d opmf_i; > Index "opmf_i" > Column | Type > +- > msg_id | numeric > unique btree > > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Preventing DELETEs
I think this should work: alter table mytable owner to postgres; grant all on my table to public; revoke delete on my table from public; I hope, it helps... Dima Rajesh Kumar Mallah. wrote: > Hi , > > I have a created a database and a table in it, > > I want to prevent "DELETES" on the table in this > database by everyone except superuser postgres. > even by me (the creator of this database and table) > > > I have tried in many ways (ALTER TABLE REVOKE DELETE .. etc etc) > > but i always end up with having the permission > > > can any one tell me how the prevention can be accomplished? > > thanks in advance. > > regds > mallah. > > > > --=20 > Rajesh Kumar Mallah, > Project Manager (Development) > Infocom Network Limited, New Delhi > phone: +91(11)6152172 (221) (L) ,9811255597 (M) > > Visit http://www.trade-india.com , > India's Leading B2B eMarketplace. > > > > ---(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 > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Preventing DELETEs
Hi Dmitry, Got it working i made a small change. On Friday 27 September 2002 00:47, you wrote: > I think this should work: > > alter table mytable owner to postgres; > grant all on my table to public; instead of > revoke delete on my table from public; i did : revoke delete on my table from tradein (which is me) ; tradein_clients=> BEGIN WORK; delete from users where userid=34866; BEGIN ERROR: users: Permission denied. tradein_clients=> ROLLBACK ; ROLLBACK tradein_clients=> UPDATE users set password='mallah' where userid=34866; UPDATE 1 tradein_clients=> does public not include me?? regds mallah. > > I hope, it helps... > > Dima > > Rajesh Kumar Mallah. wrote: > > Hi , > > > > I have a created a database and a table in it, > > > > I want to prevent "DELETES" on the table in this > > database by everyone except superuser postgres. > > even by me (the creator of this database and table) > > > > > > I have tried in many ways (ALTER TABLE REVOKE DELETE .. etc etc) > > > > but i always end up with having the permission > > > > > > can any one tell me how the prevention can be accomplished? > > > > thanks in advance. > > > > regds > > mallah. > > > > > > > > --=20 > > Rajesh Kumar Mallah, > > Project Manager (Development) > > Infocom Network Limited, New Delhi > > phone: +91(11)6152172 (221) (L) ,9811255597 (M) > > > > Visit http://www.trade-india.com , > > India's Leading B2B eMarketplace. > > > > > > > > ---(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 -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Case Sensitive "WHERE" Clauses?
On Thu, 26 Sep 2002, Andrew Perrin wrote: > No, I don't think it's supposed to be case-sensitive. In any case, whether > it's supposed to be or not, it certainly isn't in practice. AFAIK, they are case sensitive by design. It is the right thing to do. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Case Sensitive "WHERE" Clauses?
On Thu, 26 Sep 2002, Jordan Reiter wrote: > Are string comparisons in postgresql case sensitive? Yes, AFAIK. I disagree with your comments and recommendations posted at http://www.postgresql.org/idocs/index.php?datatype-character.html because my testing shows that varying text and fixed test comparisons are both case sensitive. testing=# \d casetest Table "casetest" Column | Type | Modifiers +---+--- name | text | city | character(10) | testing=# select * from casetest; name |city --+ Dan | Ottawa (1 row) testing=# select * from casetest where name = 'Dan'; name -- Dan (1 row) testing=# select * from casetest where name = 'dan'; name -- (0 rows) testing=# select * from casetest where city = 'ottawa'; name | city --+-- (0 rows) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Passing array to PL/SQL and looping
All,
I'm a newbie to PL/SQL and need help badly. I'm trying to pass my array of
id's to the function and then loop through until the array is empty. I know
there must be atleast five things I'm doing wrong.
Please help!
Cheers,
-p
Call to Procedure and Array:
$myArray = array(15, 6, 23);
select generateInvoice($myArray);
Procedure:
CREATE FUNCTION generateInvoice (VARRAY) RETURNS int4 AS '
DECLARE
-- local variables
temppk INT4;
v_pids := $1;
v_count BINARY_INTEGER := 1;
id INT4;
BEGIN
SELECT INTO temppk nextval(''t_task_task_id_seq'');
LOOP
IF v_pids.EXISTS(v_count) THEN
id := v_pids.NEXT(v_count);
UPDATE t_project SET task_id=temppk WHERE project_id=id;
v_count := v_count + 1;
ELSE
EXIT;
END IF;
END LOOP;
-- Everything has passed, return id as pk
RETURN temppk;
END;
' LANGUAGE 'plpgsql';
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] FW: query problem "server sent binary data ... without prior row description ..."
Not sure if this is a repeat request, sorry if you receive it twice.
Thanks,
Bill Jones
Systems Architect
Middleware Services
Wells Fargo Services Company
Office --415.222.5226
PCS -- 415.254.3831 ([EMAIL PROTECTED])
Views expressed are mine. Only in unusual circumstances are they shared by
my employer.
I'm having a problem with postgres on HPUX. My version is:
VERSION = 'PostgreSQL 7.2.2 on hppa2.0w-hp-hpux11.11,
compiled by aCC -Ae'
I'm trying to do a query and it consistently gives the
following errors:
wily=# \a
Output format is unaligned.
wily=# \f ';'
Field separator is ';'.
wily=# \t
Showing only tuples.
wily=# select * from wt_metric_backup where intended_end_ts
< '2002-08-16 00:00:00.000-7';
the query runs for 10 minutes or so, then outputs:
server sent binary data ("B" message) without prior row
description ("T" message)
server sent binary data ("B" message) without prior row
description ("T" message)
server sent binary data ("B" message) without prior row
description ("T" message)
unexpected character n following empty query response ("I"
message)
server sent data ("D" message) without prior row description
("T" message)
server sent data ("D" message) without prior row description
("T" message)
server sent binary data ("B" message) without prior row
description ("T" message)
then it prompts me for some input:
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> \.
server sent binary data ("B" message) without prior row
description ("T" message)
unexpected response from server; first received character
was "0"
lost synchronization with server, resetting connection
Asynchronous NOTIFY 'ntsTask|perform:Response Time (mccoust
r@ ' from backend with pid 1667460981 received.
I've tried this several times. After I got the error the
first time I ran a vacuum analysis on the table and added an index on the
query field (wt_metric_backup.intended_end_ts) but I get the same results.
The error log doesn't show anything unusual until after I kill the query.
Looks like it's losing packets/messages (server sent binary
data ("B" message) without prior row description ("T" message)), but the
errors are consistent between attempts - I would expect packet loss to be
random. Simpler queries such as:
select min(intended_end_ts) from wt_metric_backup;
work okay.
Any ideas?
Thanks,
Bill Jones
Systems Architect
Middleware Services
Wells Fargo Services Company
Office --415.222.5226
PCS -- 415.254.3831 ([EMAIL PROTECTED])
Views expressed are mine. Only in unusual circumstances are they shared by
my employer.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Case Sensitive "WHERE" Clauses?
On Thursday 26 September 2002 19:54, Jordan Reiter wrote:
> Are string comparisons in postgresql case sensitive?
Yes, unless you specify otherwise.
Are you sure you are using the right database? I can
reproduce similar results, but only like this:
mysql> create temporary table foo (ch char(2), vc varchar(2));
Query OK, 0 rows affected (0.12 sec)
mysql> insert into foo values ('aa','AA');
Query OK, 1 row affected (0.02 sec)
mysql> select * from foo where ch = 'aa';
+--+--+
| ch | vc |
+--+--+
| aa | AA |
+--+--+
1 row in set (0.01 sec)
mysql> select * from foo where ch = 'AA';
+--+--+
| ch | vc |
+--+--+
| aa | AA |
+--+--+
1 row in set (0.00 sec)
mysql> select * from foo where vc = 'aa';
+--+--+
| ch | vc |
+--+--+
| aa | AA |
+--+--+
1 row in set (0.00 sec)
Regards
Ian Barwick
[EMAIL PROTECTED]
---(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] FW: query problem "server sent binary data ... without
On Thu, 26 Sep 2002 [EMAIL PROTECTED] wrote: > I'm having a problem with postgres on HPUX. My version is: > > VERSION = 'PostgreSQL 7.2.2 on hppa2.0w-hp-hpux11.11, > compiled by aCC -Ae' > > I'm trying to do a query and it consistently gives the > following errors: > > wily=# \a > Output format is unaligned. > wily=# \f ';' > Field separator is ';'. > wily=# \t > Showing only tuples. > > wily=# select * from wt_metric_backup where intended_end_ts > < '2002-08-16 00:00:00.000-7'; > > the query runs for 10 minutes or so, then outputs: How much data is that sending? The client library is going to try to buffer the entire result set. ---(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] Case Sensitive "WHERE" Clauses?
>On Thursday 26 September 2002 19:54, Jordan Reiter wrote:
> > Are string comparisons in postgresql case sensitive?
>
>Yes, unless you specify otherwise.
>
>Are you sure you are using the right database? I can
>reproduce similar results, but only like this:
You're using MySQL in these examples .. not Postgres :)
(FYI - Just tried this with 7.3beta and I got the same results as everyone
else .. it is case sensitive).
Chris.
>mysql> create temporary table foo (ch char(2), vc varchar(2));
>Query OK, 0 rows affected (0.12 sec)
>
>mysql> insert into foo values ('aa','AA');
>Query OK, 1 row affected (0.02 sec)
>
>mysql> select * from foo where ch = 'aa';
>+--+--+
>| ch | vc |
>+--+--+
>| aa | AA |
>+--+--+
>1 row in set (0.01 sec)
>
>mysql> select * from foo where ch = 'AA';
>+--+--+
>| ch | vc |
>+--+--+
>| aa | AA |
>+--+--+
>1 row in set (0.00 sec)
>
>mysql> select * from foo where vc = 'aa';
>+--+--+
>| ch | vc |
>+--+--+
>| aa | AA |
>+--+--+
>1 row in set (0.00 sec)
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Case Sensitive "WHERE" Clauses?
On Friday 27 September 2002 01:14, Chris wrote: > >On Thursday 26 September 2002 19:54, Jordan Reiter wrote: > > > Are string comparisons in postgresql case sensitive? > > > >Yes, unless you specify otherwise. > > > >Are you sure you are using the right database? I can > >reproduce similar results, but only like this: > > You're using MySQL in these examples .. not Postgres :) Full points for paying attention ;-) This, erm, characteristic of the former caused me a lot of grief once... Anyone know what the ANSI standard is? I don`t recall any other database apart from MySQL which default to case-insensitive CHAR or VARCHAR columns. Ian Barwick [EMAIL PROTECTED] ---(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] Case Sensitive "WHERE" Clauses?
> > > > Are string comparisons in postgresql case sensitive? >> > >> >Yes, unless you specify otherwise. >> > >> >Are you sure you are using the right database? I can >> >reproduce similar results, but only like this: >> >> You're using MySQL in these examples .. not Postgres :) > >Full points for paying attention ;-) > >This, erm, characteristic of the former caused me a lot of grief once... > >Anyone know what the ANSI standard is? I don`t recall any other >database apart from MySQL which default to case-insensitive >CHAR or VARCHAR columns. Microsoft Products (SQL Server, Access) are case-insensitive. I find it hard to understand why it's advantageous that column names are NOT case sensitive, while field content is. You have a *lot* more control over the database columns than you do over the content that goes into the fields. In my opinion, allowing someone to refer to a column as first_name, First_Name, or FIRST_NAME just encourages bad programming. -- Jordan Reiter mailto:[EMAIL PROTECTED] Breezing.com http://breezing.com 1106 West Main St phone:434.295.2050 Charlottesville, VA 22903 fax:603.843.6931 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Passing array to PL/SQL and looping
Peter, > I'm a newbie to PL/SQL and need help badly. I'm trying to pass my array of > id's to the function and then loop through until the array is empty. I know > there must be atleast five things I'm doing wrong. Simplified example: CREATE FUNCTION test_array ( INT[] ) RETURNS INT AS ' DECLARE id_array ALIAS for $1; count_it INT; BEGIN count_it := 1; WHILE id_array[count_it] LOOP count_it := count_it + 1; END LOOP; RETURN (count_it - 1); END;' LANGUAGE 'plpgsql'; returns the number of elements in the supplied array. -- Josh Berkus [EMAIL PROTECTED] Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] FW: query problem "server sent binary data ... without
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Thu, 26 Sep 2002 [EMAIL PROTECTED] wrote: >> the query runs for 10 minutes or so, then outputs: > How much data is that sending? The client library is going to try to > buffer the entire result set. And, in fact, this is the typical behavior when it runs out of memory for the result set :-( ... it loses track of the fact that it was receiving a result set at all, and starts spitting out complaints that it's not in the right state as it receives subsequent rows. (That should be fixed someday, but no one's got round to it.) Consider using a cursor so you can FETCH a reasonable number of rows at a time. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Case Sensitive "WHERE" Clauses?
Ian Barwick <[EMAIL PROTECTED]> writes: > Anyone know what the ANSI standard is? I don`t recall any other > database apart from MySQL which default to case-insensitive > CHAR or VARCHAR columns. I believe the spec has a notion of a "collation attribute" attached to character-type columns. You could define a collation that makes comparisons case insensitive and then mark selected columns that way. We don't have anything like that yet, though Tatsuo has been heard muttering about how to make it happen ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Case Sensitive "WHERE" Clauses?
On Friday 27 September 2002 05:19, Tom Lane wrote: > Ian Barwick <[EMAIL PROTECTED]> writes: > > Anyone know what the ANSI standard is? I don`t recall any other > > database apart from MySQL which default to case-insensitive > > CHAR or VARCHAR columns. > > I believe the spec has a notion of a "collation attribute" attached > to character-type columns. You could define a collation that makes > comparisons case insensitive and then mark selected columns that way. > We don't have anything like that yet, though Tatsuo has been heard > muttering about how to make it happen ... For reference, MySQL treats CHAR and VARCHAR columns as case insensitive by default; to be treated as case sensitive, fields must be defined or redefined as CHAR BINARY / VARCHAR BINARY. Personally I prefer handling case (in)sensitivity explicitly in the WHERE clause or at application level, though if the standard allows it and it's optional, enabling specific columns to be case insensitive in comparisions can only be a Good Thing (TM). Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
