[SQL] select query optimization

Hi all! i run this query select f1,f2 from t1,t2 where t1.url_id=t2.entry_id and (t1.entry_stamp::date>=(now()::date-interval '14 days')) and the problem is that t1 is a very big table so the query is too slow. what if i move the second condition to where-clause. Can this reduce the select

[SQL] ERROR: "TZ"/"tz" not supported

When I execute query, I've got error message. test=> SELECT to_timestamp('00:00:05.601 SAMST Tue Jun 28 2005', 'HH24:MI:SS.MS TZ Dy Mon DD '); ERROR: "TZ"/"tz" not supported How can I convert '00:00:05.601 SAMST Tue Jun 28 2005' (varchar type) to timestamp with time zone? -

[SQL] on select rule

Hi If I can not do insert/update action with on select rule, how can I execute some procedure or any sql statement on select event? WBR ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an i

Re: [SQL] on select rule

http://www.postgresql.org/docs/8.1/interactive/sql-createtrigger.html SELECT does not modify any rows so you can not create SELECT triggers. Rules and views are more appropriate in such cases. On 4/14/06, A. Kretschmer <[EMAIL PROTECTED]> wrote: > am 13.04.2006, um 18:46:02 +0300 mail

[SQL] how to replace 0xe28093 char with another one?

hi. When I execute "SELECT specification FROM armature WHERE id = 96;" query I get WARNING: ignoring unconvertible UTF-8 character 0xe28093. How can I replace this (0xe28093) char with another one? ---(end of broadcast)--- TIP 9: In versions below

Re: [SQL] Show CAS, USD first; the left ordered by currency name

...order by currency not in('USD', 'AND', 'CAD'); this condition will be avaluated as FALSE for USD, AND and CAD, and as TRUE for all other currencies. When the records are sorted the "false" are placed on the top because false wrote: > Good morning, > > I have a currency table (code, description

Re: [SQL] Stalled post to pgsql-sql

Hello, I need a type that will store date and time, but without seconds and microseconds (like timestamp does). At least without microseconds. I also need all the operators to work. Is this possible with one of existing date/time types or i need to create my own? Thank you!

Re: [SQL] Week to date function

gt;  2010-03-25 >  2010-03-26 >  2010-03-27 >  2010-03-28 > (7 rows) > > SELECT week2date(2009, 53); >  week2date > ---- >  2009-12-28 >  2009-12-29 >  2009-12-30 >  2009-12-31 >  2010-01-01 >  2010-01-02 >  2010-01-03 > (7 rows) > > SELECT wee

Re: [SQL] Week to date function

hink it is better to use date_part('year', day) instead of to_char(...). And may be it is worth to do raise exception when incorrect week specified. -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Sky

Re: [SQL] Table Design for Hierarchical Data

description of ltree: "This module implements a data type ltree for representing labels of data stored in a hierarchical tree-like structure". http://www.postgresql.org/docs/8.4/interactive/ltree.html -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linked

Re: [SQL] Difference between these two queries ?

ql.org/docs/8.4/interactive/queries-table-expressions.html#QUERIES-FROM > > What is the first type of join called ? CROSS JOIN > > and is it possible that they have different execution times ? AFAIK planner would choose the same execution plan in your situation so it is not. -- Sergey

Re: [SQL] How to store Byte strings into a table.

----- > Trinath Somanchi, > -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to

Re: [SQL] plpgsql out parameter with select into

   status integer; > $BODY$ >        select into status _status from testfunc1(); > $BODY$ > language plpgsql; > > testfunc2 works, testfunc3 not. > > Thanks in advance: > Imre Horvath > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To m

Re: [SQL] Question about POSIX Regular Expressions performance on large dataset.

of generally indexing to match regexp :( >> , so, tablescans for this huge dataset. >> >> What do you think of this? >> >> Sincerely, >> >> Ildefonso Camargo >> > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) &g

Re: [SQL] plpgsql out parameter with select into

What is your PG version? 2010/8/18 Horváth Imre : > It don't work... > > Only select into status * from... works. > > Imre Horvath > > 2010. 08. 18, szerda keltezéssel 09.42-kor Sergey Konoplev ezt írta: >> Hi, >> >> SELECT column_name >> INTO

Re: [SQL] plpgsql out parameter with select into

lane > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gr

Re: [SQL] Use "CREATE USER" in plpgsql function

brace" this parameter (i mean creds."userName")? Show the whole function please. > > Thanks > -- > -- > Alexander > -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype:

Re: [SQL] Use "CREATE USER" in plpgsql function

uot;inUse"=TRUE WHERE > "credId"=creds."credId"; >     CREATE USER creds."userName" WITH PASSWORD creds."userPassword" > IN GROUP ta_users; - there is error occured >             cred:=N'pass'; >     else >         cre

Re: [SQL] unique fields

sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp /

Re: [SQL] Using PL/pgSQL text argument in 'IN (INT,INT,...)' clause [re-post]

99DD6  +49 151 2300 9283  computing @ chaos > claudius > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sergey Konoplev Blog: http://gray-hemp.blogspot.c

Re: [SQL] How to use hstore

nt to ensure that they are virus free and no responsibility > is accepted by Jan Eskilsson  for any loss or damage arising in any > way from receipt or use thereof. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http:

Re: [SQL] DELETE using an outer join

ter than one with the sub query and NOT IN. ps. May be this example is worth to append to the documentation? -- Sergey Konoplev a database architect, software developer at PostgreSQL-Consulting.com http://www.postgresql-consulting.com Jabber: gray...@gmail.com Skype: gray-hemp Phone: +791606

Re: [SQL] DELETE using an outer join

are complications. However it works. DELETE FROM some_table USING some_table AS s WHERE some_table.col1 = s.col1 AND some_table.col2 = s.col2 AND some_table.id < s.id; -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray..

Re: [SQL] DELETE using an outer join

>> table name to be respecified in USING, but there are complications. >> >> However it works. >> >> DELETE FROM some_table USING some_table AS s >> WHERE >> some_table.col1 = s.col1 AND >> some_table.col2 = s.col2 AND >> some_table.id <

Re: [SQL] DELETE using an outer join

JOIN other_table ... > > where the respecification of the table in USING would be understood > to mean the target table. Right now this is an error case because > of duplicate table aliases. Yes, the OP has already pointed me to it. Thank you for your explanation. -- Sergey

Re: [SQL] Need to Iterate the record in plpgsql

t; LOOP > > END LOOP > END LOOP > > As per the example, How can I iterate pre stored record. > > Please let me know if you have any suggestions. > > Thanks & Regards, > Ramkumar > > > > > -- Sergey Konoplev a database architect, soft

Re: [SQL] Question regarding modelling for time series

It will be. You will probably need some more optimization/partitioning depending on the data distribution however it can be done later. > > > > Thank you very much! > > > --Alex > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes t

Re: [SQL] Question regarding modelling for time series

3 > Unemployment; release: 2011/09/01; reporting: 2011/08/01; value: 4 > > That are the series I want to get from the db. > > > --Alex -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray...@gmail.com Skype: gray-hemp P

Re: [SQL] returning values from dynamic SQL to a variable

CUTE like this: sql := 'select * from public.elt_set_locking(...'; EXECUTE sql INTO v_retcode; Here you will find more info about EXECUTE in plpgsql. http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -- Sergey Konoplev a database a

Re: [SQL] generate_series() with TSTZRANGE

tamp, '2012-09-17 > 12:00:00', '1 hour') > AS a OFFSET 1; > > Thanks! > > -- > Wolfe Whalen > wo...@quios.net > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.post

Re: [SQL] select on many-to-many relationship

gt; > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russi

Re: [SQL] upsert doesn't seem to work..

_hotsales, > loadoltp.st_item_insert.item_showplan, > loadoltp.st_item_insert.item_sms_code, loadoltp.st_item_insert.datetyp_id, > loadoltp.st_item_insert.item_start, loadoltp.st_item_insert.item_end, > loadoltp.st_item_insert.item_validfrom, > loadoltp.st_item_insert.item_validuntil, &g

Re: [SQL] Volatile functions in WITH

add_item(...) as before > ) > SELECT DISTINCT e.msg > FROM "exp" e > LEFT JOIN "subst" s ON FALSE > > WITH "exp" AS ( -- as before > ) > SELECT DISTINCT s.msg > FROM ( > SELECT e.msg, CAS

Re: [SQL] Volatile functions in WITH

n value can change even within a single table scan, so no optimizations can be made". So they are guaranteed to behave as you need in your last example. What about optimizing it out in WITH - I would like to listen to hackers' opinion, because for me it looks like a bug. -- Ser

Re: [SQL] Volatile functions in WITH

ed". > I don't know if this happens, or may sometimes happen, or might happen > in the future, for rows eliminated because of DISTINCT. It is a good point. Nothing guarantees it in a perspective. Optimizer guarantees a stable result but not the way it is reached. -- Sergey Konoplev Datab

Re: [SQL] execute

On Thu, Apr 25, 2013 at 9:17 AM, Mauricio Cruz wrote: > execute 'aCad:=aCad array[['||var1||','||var2||']]' What are you going to do using this construction aCad:=aCad array[['||var1||','||var2||']] ? I can not understand the meaning. -

Re: [SQL] Unique index and unique constraint

defer the uniqueness check until the end of transaction if it is constraint, and can not it it is unique index. Correct? http://www.postgresql.org/docs/9.2/static/sql-set-constraints.html -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhe

Re: [SQL] Can I simplify this somehow?

e a look at this module: http://www.postgresql.org/docs/9.3/static/tablefunc.html Particularly look at the crostab(text, text) function. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gr

Re: [SQL] Unique index VS unique constraint

erform its function. This index will be created automatically when you add the constraint. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-sql mailing

[SQL] Helper aggregate function

Hello! Can anyone tell me how i do this properly? create or replace function agg(varchar,varchar) returns void as $func$ select $1,count(*) from $2 group by $1 order by $1; $func$ Language SQL; Right now this wives me "ERROR: syntax error at or near "$2" at character 97 select $1,count(*

[SQL] EXECUTE with a prepared plan and NULL

Hello! I'm trying to run this code in one of my stored procedures s := 'execute prepared_plan( ' || id_engine || ',' || id_search || ',' || id_rsite || ')'; execute s; where "prepared_plan" is a statement defined before with PREPARE. The thing is, that if i one of the parameters is NULL, then

[SQL] Same question about PostgreSql

S database. Excuse me for long entering. Finally questions. How stable is PostgreSql ? I know, PostgreSql doesn't support 'prepare ' operation, is it successful to use one for OLTP databases ? Thanks for responds. Good luck. Sergey. ---

[SQL] Re: Informix->PostgreSQL database convertion

I think this isn't so difficult. Use "unload to ..." on Informix side ( from dbaccess ) and "copy ... from ..." om Postgres side. "Sylte" <[EMAIL PROTECTED]> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ: 9d8r7v$1to$[EMAIL PROTECTED]">news:9d8r7v$1to$[EMAIL PROTECTED]... > Howto? Are there tools or is i

[GENERAL] Re: Return cursor

cuserbase; Please read documentation for more detail. Good luck. Sergey. "Alla" <[EMAIL PROTECTED]> ???/ ? ?: news:[EMAIL PROTECTED]... > Alexander Dederer <[EMAIL PROTECTED]> wrote in message news:<9ehged$k4f$[EMAIL PROTECTED]>... > &

[SQL] Re: SELECT * INTO TABLE is not working for me.

"Roy Souther" <[EMAIL PROTECTED]> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ: 0105301102010Q.28903@OlympusMons">news:0105301102010Q.28903@OlympusMons... > I am testing my SQL commands in pgaccess before I put them into my C++ code. > Trying to copy a table using... > SELECT * INTO TABLE copy_stuff FROM