Re: [SQL] SQL query
PC> Hello, PC> I would like to retrieve all the records from table A which have given PC> lang_id and its modification date is later then modification date of PC> the same id record with lang_id='pl'. PC> Example: PC> table A - data example PC> == PC> id | modification_date | lang_id PC> +-+-- PC> abc | 2002-10-11 10:12:11 | en PC> abc | 2002-11-12 11:12:11 | pl PC> abc | 2002-11-11 18:12:00 | de PC> sample | 2003-04-15 22:43:14 | pl PC> sample | 2003-05-16 11:10:15 | en PC> sample | 2003-11-11 18:11:10 | de select * from a where lang_id='en' and modification_date>(select modification_date from a as a_alias where a_alias.id=a.id and a_alias.lang_id='pl') Try this! DAQ ---(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] Update from same table
Hello, I want to update columns in a table that match a fixed key from the same column of the same table matching another fixed key. There can be several tuples per key distinguished by a secondary id. Tuples are unique with the combined keys. Maybe a query could look something like this: UPDATE a.mytable from b.mytable SET a.mycolumn = b.mycolumn WHERE a.firstid = some_key AND b.firstid = some_other_key AND a.secondaryid = b.secondaryid; But it's a syntax error. I tried a subselect but i'm failing to connect the subselect's 2nd ID with the update's 2nd ID. Any help ? Thanks, Jürgen ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Update from same table
Jurgen, > UPDATE a.mytable from b.mytable > SET a.mycolumn = b.mycolumn > WHERE a.firstid = some_key > AND b.firstid = some_other_key > AND a.secondaryid = b.secondaryid; Very close, actually; you just need to fix the table alias: UPDATE mytable FROM mytable as b SET mytable.mycolumn = b.mycolumn WHERE mytable.firstid = some_key AND b.firstid = some_other_key AND mytable.secondaryid = b.secondaryid; AFAIK, one can't alias a table name in an UPDATE clause. So for that instance of the table, you need to use the full name. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] TIME ZONE SQL
Hi Pls see this Query I have following fields in my table "customer_events" a) time_difference (which has values like -05:00 , +05:30, +00:00 etc) b) start_time (has value like 11:05, 10:00 etc) c) send_before_time (has value like 00:05, 00:10 etc) select ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN (start_time::time - send_before_time::time) and start_time::time) as yesno, current_time(0), CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference), (start_time::time - send_before_time::time) as difference, time_difference from customer_events MY PROBLEM: This query runs fine when i have time_difference value like +5:30 +5:00 i.e. works fine for positive values but failes for negative values i.e. -7:00, -6:00 etc I don't know WHY WHY... pls help I am helpless. do reply back Regards, Raman Garg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] TIME ZONE SQL
On Wednesday 04 February 2004 17:57, Raman wrote: > > This query runs fine when i have > time_difference value like +5:30 +5:00 i.e. works fine for positive values > but failes for negative values i.e. -7:00, -6:00 etc > > I don't know WHY WHY... pls help > I am helpless. Can you give example outputs? It's difficult to decide otherwise. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Slow sub-selects, max and count(*)
I have a procedure ( included below with table
definition) to import end of day quotes into a table fsechist.
The issue is with the speed ( or lackof it) that
the procedure proceeds. Apparently it is the subselects that are the worst issue
and I have tried to replace these. Also max() and count() refuse to use indexes.
I have replaced max() with (select ... as mx from ... order by ... desc limit 1)
and this is much faster but appear to have to avoid count() as I can not find a
way to point this at an index.
Please, I am sure that there is a better way to do
this. And 5 to 7 minutes to insert a day is really too slow. Each days import
table contains about 3200 records. Total table size for fsechist is about 2.5
million records.
If you could point me in the right direction here
it would be much appreciated.
thanks
Richard Sydney-Smith
CREATE OR REPLACE FUNCTION
public.import_sharedata(varchar) RETURNS bool AS'
declare filnam alias for
$1; alldone bool := true; /* cnt integer := 0;
*/ /* count of number inserted not used */
begin/* Imports a text file
containing end of day price quotes filnam should be in double
backslash format e.g. c:\\data\\sharequotes\\quotes.dat
Ensures that it only brings in quotes for companies we have
listed in our database and ensures only the latest copy of the
quote is kept for each day
Import table has 7 columns in CSV tab
delimited ascii format. EzyChart format has
prices in cents, METASTOCK in
dollars MetaStock +100 has the volume divided
by 100. Depending on your import data source
you may need to process the temp_shareprices so that the values
are in Dollars , not cents and the volume is per unit not per 100
units.
Present calculation is for EzyChart
format.
Both tables have to have a 4
digit year mmdd Warning : If you have the wrong
format this procedure will import garbage into your database !!!
*/
perform
drop_table(\'temp_shareprices\'); create table
temp_shareprices( ticker char(10)
null, dte char(8), v1 numeric(19,4)
not null default 0, v2 numeric(19,4) not null default
0, v3 numeric(19,4) not null default
0, v4 numeric(19,4) not null default
0, v5 numeric(19,4) not null default
0 ); execute \'copy temp_shareprices from
\'||quote_literal(filnam)||\' delimiter \'||quote_literal(\',\');
-- extend yr to 4 digits, alter quotes to dollars
update temp_shareprices set dte =
\'20\'||dte,v1=v1/100,v2=v2/100,v3=v3/100,v4=v4/100;
-- add in any new company tickersinsert into fsecurity (sec_cde) select
ticker from temp_shareprices where length(trim(ticker))=3 and ticker not in (
select sec_cde from fsecurity);
--
-- remove rows from temp_shareprices where we do not have a corresponding
security
-- using sub-select is slow-- delete from temp_shareprices where ticker
not in (select sec_cde from fsecurity);
-- can just delete ones where length<>3 as insert above ensures all
length 3 are inserted. All ASX codes are 3char longdelete from
temp_shareprices where length(trim(ticker))<>3;
-- Attempt to remove without limiting to length and not using sub select
does not work--update temp_shareprices set ticker = s.sec_cde from
temp_shareprices t left outer join fsecurity s on t.ticker =
s.sec_cde;--delete from temp_shareprices where ticker is null;
--
-- put indexes on temp tableCREATE INDEX temphist_tick ON
temp_shareprices USING btree (ticker);CREATE INDEX
temphist_dte ON temp_shareprices USING btree (dte);
-- -- Scan the input table and delete
as appropriate from fsechist
-- Using sub select to remove any pre-existing records in fsechist very
slow-- delete from fsechist where hist_q in (select hist_q from fsechist,
temp_shareprices where hist_tick = ticker and dte = hist_date);
-- Tried to replace subselect update fsechist set hist_tick = \'@@\'
from fsechist h, temp_shareprices t where t.ticker = h.hist_tick and h.hist_date
= t.dte;delete from fsechist where hist_tick = \'@@\';
--
-- insert newvalues insert into
fsechist(hist_tick,hist_date,hist_v1,hist_v2,hist_v3,hist_v4,hist_vol)
select ticker,dte,v1,v2,v3,v4,v5 from temp_shareprices;
-- count the number inserted-- select count(*) into cnt
from temp_shareprices;
-- perform drop_table(\'temp_shareprices\');
return alldone;
end;' LANGUAGE 'plpgsql' VOLATILE;
Note : drop_table is a simple procedure that drops
a procedure by name iff it exists.
==
Definition of Fsechist
CREATE TABLE public.fsechist( hist_q
int4 NOT NULL DEFAULT nextval('hist_q'::text), hist_v1
numeric(19,4), hist_v2 numeric(19,4), hist_v3
numeric(19,4), hist_v4 numeric(19,4), hist_vol
numeric(19,4), h
Re: [SQL] Slow sub-selects, max and count(*)
Richard, > The issue is with the speed ( or lackof it) that the procedure proceeds. Apparently it is the subselects that are the worst issue and I have tried to replace these. Also max() and count() refuse to use indexes. Max() and Count() cannot use indexes for technical reasons. Browse through the archives of SQL, PERFORM, and HACKERS for about 12,000 discussions on the subject. > Please, I am sure that there is a better way to do this. And 5 to 7 minutes to insert a day is really too slow. Each days import table contains about 3200 records. Total table size for fsechist is about 2.5 million records. Standard advice: 1) Where possible, use COPY and not INSERT for bulk imports. 2) Where COPY is not possible, group inserts into 1000-statement blocks and wrap them in a transaction. 3) Where safe, suspend all triggers, foriegn keys, and constraints on the table while inserting and re-apply them afterward. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Slow sub-selects, max and count(*)
Hi, I don't think thatyou gave enough information to get any direct help, for example, what are these "sub-selects"? I often see performance problems arising from procedural code that puts selects that don't use indexes inside loops. That's a plain programming issue, and understanding your system. Another situation I encountered recently was using "in" sub selects. Recoding them as "exists" or "not exists" as the case may be turned a query that ran for 18hours with no sign of ending (before I killed it) into a query that ran in a couple of minutes. I always try to avoid procedural code in such batch updates. If it can be encoded into one insert then it is usually better, It just depends on what you are doing. - Original Message - From: Richard Sydney-Smith To: [EMAIL PROTECTED] Sent: Thursday, February 05, 2004 10:48 AM Subject: [SQL] Slow sub-selects, max and count(*) I have a procedure ( included below with table definition) to import end of day quotes into a table fsechist. The issue is with the speed ( or lackof it) that the procedure proceeds. Apparently it is the subselects that are the worst issue and I have tried to replace these. Also max() and count() refuse to use indexes. I have replaced max() with (select ... as mx from ... order by ... desc limit 1) and this is much faster but appear to have to avoid count() as I can not find a way to point this at an index. Please, I am sure that there is a better way to do this. And 5 to 7 minutes to insert a day is really too slow. Each days import table contains about 3200 records. Total table size for fsechist is about 2.5 million records. If you could point me in the right direction here it would be much appreciated. thanks Richard Sydney-Smith CREATE OR REPLACE FUNCTION public.import_sharedata(varchar) RETURNS bool AS' declare filnam alias for $1; alldone bool := true; /* cnt integer := 0; */ /* count of number inserted not used */ begin/* Imports a text file containing end of day price quotes filnam should be in double backslash format e.g. c:\\data\\sharequotes\\quotes.dat Ensures that it only brings in quotes for companies we have listed in our database and ensures only the latest copy of the quote is kept for each day Import table has 7 columns in CSV tab delimited ascii format. EzyChart format has prices in cents, METASTOCK in dollars MetaStock +100 has the volume divided by 100. Depending on your import data source you may need to process the temp_shareprices so that the values are in Dollars , not cents and the volume is per unit not per 100 units. Present calculation is for EzyChart format. Both tables have to have a 4 digit year mmdd Warning : If you have the wrong format this procedure will import garbage into your database !!! */ perform drop_table(\'temp_shareprices\'); create table temp_shareprices( ticker char(10) null, dte char(8), v1 numeric(19,4) not null default 0, v2 numeric(19,4) not null default 0, v3 numeric(19,4) not null default 0, v4 numeric(19,4) not null default 0, v5 numeric(19,4) not null default 0 ); execute \'copy temp_shareprices from \'||quote_literal(filnam)||\' delimiter \'||quote_literal(\',\'); -- extend yr to 4 digits, alter quotes to dollars update temp_shareprices set dte = \'20\'||dte,v1=v1/100,v2=v2/100,v3=v3/100,v4=v4/100; -- add in any new company tickersinsert into fsecurity (sec_cde) select ticker from temp_shareprices where length(trim(ticker))=3 and ticker not in ( select sec_cde from fsecurity); -- -- remove rows from temp_shareprices where we do not have a corresponding security -- using sub-select is slow-- delete from temp_shareprices where ticker not in (select sec_cde from fsecurity); -- can just delete ones where length<>3 as insert above ensures all length 3 are inserted. All ASX codes are 3char longdelete from temp_shareprices where length(trim(ticker))<>3; -- Attempt to remove without limiting to length and not using sub select does not work--update temp_shareprices set ticker = s.sec_cde from temp_shareprices t left outer join fsecurity s on t.ticker = s.sec_cde;--delete from temp_shareprices where ticker is null; -- -- put indexes on temp tableCREATE INDEX temphist_tick ON temp_shareprices USING btree (ticker);CREATE INDEX temphist_dte ON temp_shareprices USING btree (dte); -- -- Scan the input table and delete as appropriate from fsechist -- Using sub select to remov
