[SQL] Dynamic views/permissions
I'm trying to permit users access to their own records in a database. A sample would be: create table logins ( login char(8), name char(32), primary key (login) ); When a login is added an SQL user is created simultaneously. Now I want the user to be able to view her own data: create function userid() returns text as 'select current_user::text;' language sql; create view loginview as select * from logins where logins.login=userid(); grant select on loginview to public; [OR grant select on loginview to ...] However, a select * from loginview; doesn't return any rows. Is what I'm trying not possible at all, or am I just trying to implement it wrong? Regards, -- Raju -- Raj Mathur[EMAIL PROTECTED] http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F It is the mind that moves ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] DBD::Pg transaction issues
Hi, Am using DBD::Pg with PostgreSQL 7.2.3, Perl 5.8.0. I have a set of updates to a table which has (a) references to another table and (b) possibly duplicates. The data is in a text file, which is read record by record and appended to the database table. A transactions spans the complete reading of the text file. If the cross-reference field in the file doesn't exist in the referenced table I want to ignore the record. If the record already exists in the table I want to perform some updates to the existing data. The problem is that the first record in the text file that has an invalid reference, or one that already exists, causes the transaction to abort and all subsequent updates from the file to fail. Is there any way to tell DBI/PostgreSQL that it should continue the transaction until the program directs it to commit/rollback? Tried the following so far: Set RaiseError to null. No effect. Currently manually checking for duplicates/missing referenced records and taking appropriate action when found. Pretty inelegant. Pseudocode: open text file begin transaction while read text record write into table if write failed due to duplicate read existing record update values in existing record rewrite record else if write failed due to missing reference ignore record else mark file as bad if file not bad commit else rollback Hope this is the right list to be asking on. Regards, -- Raju -- Raj Mathur[EMAIL PROTECTED] http://kandalaya.org/ It is the mind that moves ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Home-brewed table syncronization
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 >>>>> "Michael" == Michael A Nachbaur writes: Michael> On Wednesday 09 July 2003 02:28 pm, Cliff Wells wrote: >> On Wed, 2003-07-09 at 14:14, Michael A Nachbaur wrote: > So, >> I'm looking at syncronizing 4 tables from one master database >> to > several child databases. I'm thinking of doing the >> following with > DBD::Multiplex: >> > >> > DELETE FROM TableA; > INSERT INTO TableA (..) VALUES (...); > >> >> > >> > on all the child databases, but I'm not sure what kind of >> impact this > would have on my servers. My impression is that >> this would hammer the > indexes, and might blow any memory >> optimization out the window. Only a > few records in my >> dataset will change from time-to-time, but just the > process >> of determining what is different may take more effort than >> simply > rebuilding. >> >> Keep a timestamp associated with each record. Only update the >> records with timestamps later than your last sync. Michael> I'm dealing with an existing database structure that, Michael> though I can change it, has a lot of impact on the rest Michael> of my infrastructure. If I can find a way of doing this Michael> without resorting to timestamps, I'd much rather do it Michael> that way. Had the same issue, so I made a separate table to store meta-information about what records have been changed in my master tables. Note that I do not store the actual change, just which record was affected and the nature of the change: delete, insert or update. The deltas table is called, strangely enough, ``delta'', and here's the code that manages it (for a table called pdetail (keys: package, pool, timeslot): - -- Table pdetail create or replace function pdetail_update_delta() returns opaque as ' declare mykeyval varchar(1024) ; upd char(1) ; begin if TG_OP = ''UPDATE'' then upd := ''U'' ; mykeyval := OLD.package || ''|'' || OLD.pool || ''|'' || OLD.timeslot ; end if ; if TG_OP = ''INSERT'' then upd := ''I'' ; mykeyval := NEW.package || ''|'' || NEW.pool || ''|'' || NEW.timeslot ; end if ; if TG_OP = ''DELETE'' then upd := ''D'' ; mykeyval := OLD.package || ''|'' || OLD.pool || ''|'' || OLD.timeslot ; execute ''delete from delta where relation='''''' || TG_RELNAME || '''''' and keyval='''''' || mykeyval || '''''';'' ; end if ; insert into delta ( relation , keyval , timestamp , what ) values ( ''pdetail'' , mykeyval , now () , upd ) ; if TG_OP = ''UPDATE'' or TG_OP = ''INSERT'' then return NEW ; end if ; if TG_OP = ''DELETE'' then return OLD ; end if ; end ; ' language plpgsql ; create trigger pdetail_update_delta_trigger after update on pdetail for each row execute procedure pdetail_update_delta() ; create trigger pdetail_insert_delta_trigger after insert on pdetail for each row execute procedure pdetail_update_delta() ; create trigger pdetail_delete_delta_trigger before delete on pdetail for each row execute procedure pdetail_update_delta() ; Table delta itself looks like this: create table delta ( relationvarchar(32) , -- Table name to which update was made keyval varchar(1024) , -- Key value of the updated record timestamp timestamp without time zone default now() , -- When whatchar(1) check (what = 'U' or what = 'D' or what = 'I') , primary key ( relation , keyval , timestamp ) ) ; Not much experienced with PgSQL, so would appreciate any tips the masters can give for improving the plpgsql code. However, it works for me as it is at the moment. You are free to use this code under the terms of the GNU GPL. Regards, - -- Raju - -- Raj Mathur[EMAIL PROTECTED] http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F It is the mind that moves -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) Comment: Processed by Mailcrypt 3.5.6 and Gnu Privacy Guard <http://www.gnupg.org/> iD8DBQE/DN78yWjQ78xo0X8RAsmXAJ4k1cq7mFiRxUb6EGO0R81MVfAWfgCfdGxN K7g2SsvUAPedg7RH86OZcTY= =JkN/ -END PGP SIGNATURE- ---(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] Substring
On Saturday 05 Sep 2009, bilal ghayyad wrote: > I have an sql script function that take one text parameter "funct > (text)", what I need to do is the following: > > If the parameter name is string and its value was for example "abcd" > then I need to do a query based on ab and then based on the abc, how? > > Example: > > SELECT * from voipdb where prefix like string > > But I need the string to be ab and then to be abc? How I can assign > the string to the first character and then to the first and second? > In other words, how can I can take part of the string to do query on > it? From your example the following brute-force method should work (not tested): select * from voipdb where prefix like substring(string from 1 for 2) || '%' or prefix like substring(string from 1 for 3) || '%'; However, I don't understand why you'd want to search for both 'ab' and 'abc' in the same query, since the first condition is a superset of the second one. Regards, -- Raju -- Raj Mathurr...@kandalaya.org http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Substring
On Saturday 05 Sep 2009, Frank Bax wrote: > Raj Mathur wrote: > > On Saturday 05 Sep 2009, bilal ghayyad wrote: > >> I have an sql script function that take one text parameter "funct > >> (text)", what I need to do is the following: > >> > >> If the parameter name is string and its value was for example > >> "abcd" then I need to do a query based on ab and then based on the > >> abc, how? > >> > >> Example: > >> > >> SELECT * from voipdb where prefix like string > >> > >> But I need the string to be ab and then to be abc? How I can > >> assign the string to the first character and then to the first and > >> second? In other words, how can I can take part of the string to > >> do query on it? > > > > From your example the following brute-force method should work (not > > tested): > > > > select * from voipdb where prefix like substring(string from 1 for > > 2) || '%' or prefix like substring(string from 1 for 3) || '%'; > > > > However, I don't understand why you'd want to search for both 'ab' > > and 'abc' in the same query, since the first condition is a > > superset of the second one. > > Given that tablename is "voipdb"; I wonder if OP really wants to > write a query that finds the row where argument to function matches > the most number of leading characters in "prefix". > > If voipdb table contains: ab, abc, def, defg; then calling function > with "abc" or "abcd" returns "abc" and calling function with "defh" > returns "def". > > If this is the real problem to be solved; then brute force is one > solution; but I'm left wondering if a single query might return > desired result (a single row). Something like this may help in that case (note, we're completely in the realm of creating imaginary problems and solving them now :) select * from voipdb where prefix <= string order by prefix desc limit 1; Regards, -- Raju -- Raj Mathurr...@kandalaya.org http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple (?) join
On Thursday 24 Sep 2009, Gary Stainburn wrote: > Hi folks. > > I have two tables > > create table orders ( > o_id serial primary key > ... > ); > > create table orders_log ( > ol_id serial primary key, > o_id int4 not null references orders(o_id), > ol_timestamp timestamp, > ol_user, > ); > > How can I select all from orders and the last (latest) entry from the > orders_log? Does this do what you want? You would have to do some tricks if you also want orders that don't have any entry in the orders_log table to be displayed. select ol.ol_id,ol.o_id,ol.ol_timestamp from orders o natural join orders_log ol where (ol.o_id,ol.ol_timestamp) in (select o_id,max(ol_timestamp) from orders_log group by o_id); Regards, -- Raju -- Raj Mathurr...@kandalaya.org http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] For each key, find row with highest value of other field
I have some data of the form: Key | Date | Value A | 2008-05-01 | foo* A | 2008-04-01 | bar A | 2008-03-01 | foo* B | 2008-03-04 | baz B | 2008-02-04 | bar C | 2008-06-03 | foo* C | 2008-04-04 | baz C | 2008-03-04 | bar Is there any way to select only the rows marked with a (*) out of these without doing a join? I.e. I wish to find the row with the highest Date for each Key and use the Value from that. Regards, -- Raju -- Raj Mathur[EMAIL PROTECTED] http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Search fields in multiple tables
Hi, I have some information about books spread over multiple tables (title, authors, ISBN, blurb, publisher, etc.) Is there any convenient way in PostgreSQL to allow a user to search these in a single operation? If there is, would there be some way to assign weights to the fields? E.g. a match on title would rate higher than a match on publisher or on blurb. Regards, -- Raju -- Raj Mathur[EMAIL PROTECTED] http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Enc: Help to replace caracter
On Friday 14 Nov 2008, paulo matadr wrote:
> I Need to replace string (" ) in the situation below :
>
> select clie_nmclientefrom cadastro.cliente where clie_nmcliente
> like '%"%';
>
> result:
> JOANA D"ARCALMEIDA"
> EMLURB "P M R."
> CECILIA D"CAGNO"
> HELENA FERREIRA D"FREITAS"
> JOSE M. "BARRACA DO BOLA"
> FORTE" DUNAS BAR"
> JOANA D"ARC R. DE SOUZA
> ASSEMBLEIA DE DEUS"
> USINA SALGADO"SUPRIMENTO
> JOSE MOURA 'BIGODE"
> BEATRIZ MEDEIROS D"EMERY
>
> Any help me to create pgPL/sql or funcion to replace ( " ) to null
> value, I have many table with this. Thanks for help
For a single field, you can use something like this to achieve your
objective (this will delete all " characters in the field):
update cadastro.cliente
set clie_nmcliente = regexp_replace(clie_nmcliente, '"', '', 'g')
where clie_nmcliente like '%"%'; -- delete all ": not tested!
You could replace the '' with some other string to replace all " with
that string.
It may be quicker to edit an ASCII dump of the database and reload it if
you want to do the same replacement in multiple fields in multiple
tables.
Regards,
-- Raju
--
Raj Mathur[EMAIL PROTECTED] http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Array iterator
Hi,
Is there any way to apply a built-in function to all elements of an
array (and get an array as result) without using a procedural language?
Specifically, I'm looking at being able to take a string, tokenise it
into an array and apply soundex to each of the tokens (not particularly
efficient or effective, but serves this specific purpose). Have got as
far as:
select regexp_split_to_array('string with tokens', '[^A-Za-z0-9]');
Can one now process the resulting array in a single shot within SQL
itself?
Regards,
-- Raju
--
Raj Mathurr...@kandalaya.org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Array iterator
On Tuesday 27 Jan 2009, Achilleas Mantzios wrote:
> Στις Tuesday 27 January 2009 14:40:29 ο/η Raj Mathur έγραψε:
> > select regexp_split_to_array('string with tokens', '[^A-Za-z0-9]');
>
> maybe
> select regexp_split_to_table('string with tokens', '[^A-Za-z0-9]');
> would help?
That did the job, thanks!
-- Raju
--
Raj Mathurr...@kandalaya.org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Must I use DISTINCT?
On Friday 06 Feb 2009, Michael B Allen wrote:
> On Thu, Feb 5, 2009 at 10:59 PM, Rajesh Kumar Mallah
>
> wrote:
> > have you tried Join using , eg
> > SELECT e.eid, e.name
> > FROM entry e join access a ON( e.eid = 120
> > AND (e.ownid = 66 OR e.aid = a.aid) ) ;
> >
> > some sample data might also help in understanding the prob
> > more clrearly.
>
> Hi Rajesh,
>
> Unfortunately using JOIN does not seem to change the result.
>
> Here is some real data:
> > select eid, name, ownid, aclid from foo_entry;
>
> +-+---+---+---+
>
> | eid | name | ownid | aclid |
>
> +-+---+---+---+
> | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |66 | 0 |
> +-+---+---+---+
> 15 rows in set (0.01 sec)
>
> > select a, b from foo_link;
>
> +-+-+
>
> | a | b |
>
> +-+-+
> | 71 | 92 |
> +-+-+
> 16 rows in set (0.00 sec)
>
> So there are two tables: foo_entry AS e and foo_link AS a1. I want to
> select the the single row from foo_entry with e.eid = 113 but only if
> the e.ownid = 66 OR if e.aclid is indirectly linked with 66 via the
> foo_link table.
Something like this?
select * from foo_entry where eid = 113 and ownid in (select a from
foo_link where a=66 or b=66);
Regards,
-- Raju
--
Raj Mathurr...@kandalaya.org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Funtion to clean up strings?
On Friday 13 Feb 2009, Andreas wrote:
> now ... lets get more complicated.
> Phone numbers are entered:
> 0123/4567-89 national number
> 0049/123/4567-89 the same number
> +49/123/4567-89 still the same number
>
> should come out as 0123456789 to search in this column.
> "0049" and "+49" --> 0
>
> while international numbers
> +33/123456789
> 0033/123456789
>
> should come as
> +33123456789
TEST=> create table foo(p text);
TEST=> insert into foo (select regexp_split_to_table('0123/4567-89
0049/123/4567-89 +49/123/4567-89 +33/123456789 0033/123456789',' '));
TEST=> select * from foo;
p
--
0123/4567-89
0049/123/4567-89
+49/123/4567-89
+33/123456789
0033/123456789
(5 rows)
TEST=> select
(case
when p ~ E'^(\\+|00)49'
then '0'||regexp_replace(regexp_replace(p, E'[^0-9+]', '', 'g'),
E'^(?:\\+|00)49(.*)', E'\\1')
when p ~ E'^(\\+|00)'
then '+'||regexp_replace(regexp_replace(p, E'[^0-9+]', '', 'g'),
E'^(?:\\+||00)(.*)', E'\\1')
else
regexp_replace(p, E'[^0-9]', '', 'g')
end)
from foo;
regexp_replace
0123456789
0123456789
0123456789
+33123456789
+33123456789
(5 rows)
That do what you want? (Apologies for the wrapped lines.)
Regards,
-- Raju
--
Raj Mathurr...@kandalaya.org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Funtion to clean up strings?
On Tuesday 17 Feb 2009, Andreas wrote: > [snip] > case > when regexp_replace(p, E'[^0-9+]', '', 'g') ~ E'^(\\+|00)49' > then '0'|| >regexp_replace( > regexp_replace( > regexp_replace(p, E'[^0-9+()]', '', 'g') > , '\\(0\\)||\\(||\\)', '', 'g') >, E'^(?:\\+|00)49(.*)', E'\\1') > when regexp_replace(p, E'[^0-9+]', '', 'g') ~ E'^(\\+|00)' > then '+'|| >regexp_replace( > regexp_replace( > regexp_replace(p, E'[^0-9+()]', '', 'g') > , '\\(0\\)||\\(||\\)', '', 'g') >, E'^(?:\\+||00)(.*)', E'\\1') > else > regexp_replace(p, E'[^0-9]', '', 'g') > end > > That would catch the leading spaces in " 00 49 ( 0 ) 1 2 3 456 -0", > too. Creating a sql-function thows a WARNING: nonstandard use of \\ > in a string literal > but it still works. Do you know a better or more correct way to reach > the same? > > Perhaps one could find a way with less calls to regexp_replace ? That is what I would have tried too :) The only improvement I can think of is to replace one instance of regex_replace with a string replace, since the string (0) is fixed. On the other hand, I'm not an expert at Pg functions by any means, so someone else may have a faster or more elegant solution. Regards, -- Raju -- Raj Mathurr...@kandalaya.org http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Generic design: char vs varchar primary keys
Hi, Can you point me to any pages that explain the difference between using, say CHAR(8) vs VARCHAR(8) as the primary key for a table? Is there any impact on the database in terms of: - Speed of indexed retrieval - Speed of join from a referencing table - Storage (I presume VARHAR(8) would have a slight edge, in general) - Any other issue Regards, -- Raj -- Raj Mathurr...@kandalaya.org http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Generic design: char vs varchar primary keys
On Thursday 04 Aug 2011, Josh Kupershmidt wrote: > 2011/8/3 Raj Mathur (राज माथुर) : > > Can you point me to any pages that explain the difference between > > using, say CHAR(8) vs VARCHAR(8) as the primary key for a table? > > Is there any impact on the database in terms of: > > > > - Speed of indexed retrieval > > - Speed of join from a referencing table > > - Storage (I presume VARHAR(8) would have a slight edge, in > > general) - Any other issue > I suspect the tiny size differences between char(8) and varchar(8) > are going to be negligible. In fact, this post talks precisely about > this concern, and more: > > http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varc > har-vs-text/ > > Personally I'd be most worried about the different semantics of the > types (i.e. treatment of trailing spaces), and perhaps the ease of > expanding the length constraint in the future. Thanks, that's useful for benchmarking the various textual data types. Anything specific about using CHAR vs VARCHAR for primary keys that are going to be referenced from multiple tables that comes to mind? Regards, -- Raj -- Raj Mathurr...@kandalaya.org http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Correlating Asterisk CDRs
Hi, I'm trying to correlate Call Data Records (CDRs) from two Asterisk servers, one of which uses the other for telephony. The data is in the tables cdr and cdr2. With some indexes, the query and explain result are: explain analyse select cdr.calldate, cdr2.calldate, (cdr2.calldate-cdr.calldate) as rtdur, cdr.clid, cdr.dst, cdr2.src, cdr2.dst, cdr2.dstchannel, cdr2.lastapp, cdr2.duration, cdr2.disposition from cdr, cdr2 where cdr2.calldate >= cdr.calldate and cdr.clid=cdr2.clid and cdr.dst=substring(cdr2.dst from 4) order by cdr.calldate, cdr2.calldate, cdr.clid limit 100; QUERY PLAN - Limit (cost=46782.15..46782.40 rows=100 width=109) (actual time=4077.866..4078.054 rows=100 loops=1) -> Sort (cost=46782.15..46785.33 rows=1272 width=109) (actual time=4077.863..4077.926 rows=100 loops=1) Sort Key: cdr.calldate, cdr2.calldate, cdr.clid Sort Method: top-N heapsort Memory: 42kB -> Merge Join (cost=2.95..46733.54 rows=1272 width=109) (actual time=0.070..3799.546 rows=168307 loops=1) Merge Cond: (((cdr.clid)::text = (cdr2.clid)::text) AND ((cdr.dst)::text = "substring"((cdr2.dst)::text, 4))) Join Filter: (cdr2.calldate >= cdr.calldate) -> Index Scan using ick1 on cdr (cost=0.00..34667.86 rows=208798 width=43) (actual time=0.022..434.246 rows=208798 loops=1) -> Index Scan using i2k1 on cdr2 (cost=0.00..9960.89 rows=65449 width=88) (actual time=0.011..391.599 rows=240981 loops=1) Total runtime: 4078.184 ms (10 rows) Is there any way to make this query faster? I already have an index i2k1 on substring(cdr2.dst from 4), which is being used. Application --- I'm looking for all caller records in cdr2 that have the same callerid (clid) and destination (dst) and were started on cdr2 after they were started on cdr. cdr2.dst is the same as cdr.dst but with a 3-character prefix. Regards, -- Raj -- Raj Mathur || r...@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Correlating Asterisk CDRs
On Wednesday 07 Dec 2011, Julien Cigar wrote: > Try to raise work_mem Odd, I tried adding work_mem=50MB / 256MB / 1024MB into postgres.conf and the times actually went up to over 12 seconds. Leaving it commented results in the 4-second time originally posted. Regards, -- Raj > On 12/07/2011 15:34, Raj Mathur (राज माथुर) wrote: > > I'm trying to correlate Call Data Records (CDRs) from two Asterisk > > servers, one of which uses the other for telephony. The data is in > > the tables cdr and cdr2. With some indexes, the query and explain > > result are: > > > > explain analyse select cdr.calldate, cdr2.calldate, > > (cdr2.calldate-cdr.calldate) as rtdur, cdr.clid, cdr.dst, cdr2.src, > > cdr2.dst, cdr2.dstchannel, cdr2.lastapp, cdr2.duration, > > cdr2.disposition from cdr, cdr2 where cdr2.calldate>= cdr.calldate > > and cdr.clid=cdr2.clid and cdr.dst=substring(cdr2.dst from 4) order > > by cdr.calldate, cdr2.calldate, cdr.clid limit 100; > > > > QUERY PLAN > > > > --- > > --- > > --- > > > > Limit (cost=46782.15..46782.40 rows=100 width=109) (actual > > time=4077.866..4078.054 > > > > rows=100 loops=1) > > > > -> Sort (cost=46782.15..46785.33 rows=1272 width=109) > > (actual time=4077.863..4077.926 > > > > rows=100 loops=1) > > > > Sort Key: cdr.calldate, cdr2.calldate, cdr.clid > > Sort Method: top-N heapsort Memory: 42kB > > -> Merge Join (cost=2.95..46733.54 rows=1272 > > width=109) (actual > > > > time=0.070..3799.546 rows=168307 loops=1) > > > > Merge Cond: (((cdr.clid)::text = (cdr2.clid)::text) > > AND ((cdr.dst)::text = > > > > "substring"((cdr2.dst)::text, 4))) > > > > Join Filter: (cdr2.calldate>= cdr.calldate) > > -> Index Scan using ick1 on cdr > > (cost=0.00..34667.86 rows=208798 > > > > width=43) (actual time=0.022..434.246 rows=208798 loops=1) > > > > -> Index Scan using i2k1 on cdr2 > > (cost=0.00..9960.89 rows=65449 width=88) > > > > (actual time=0.011..391.599 rows=240981 loops=1) > > > > Total runtime: 4078.184 ms > > > > (10 rows) > > > > Is there any way to make this query faster? I already have an > > index i2k1 on substring(cdr2.dst from 4), which is being used. > > > > Application > > --- > > > > I'm looking for all caller records in cdr2 that have the same > > callerid (clid) and destination (dst) and were started on cdr2 > > after they were started on cdr. cdr2.dst is the same as cdr.dst > > but with a 3-character prefix. -- Raj Mathur || r...@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SOLVED] Re: [SQL] Correlating Asterisk CDRs
On Wednesday 07 Dec 2011, Raj Mathur (राज माथुर) wrote: > On Wednesday 07 Dec 2011, Julien Cigar wrote: > > Try to raise work_mem > > Odd, I tried adding work_mem=50MB / 256MB / 1024MB into postgres.conf > and the times actually went up to over 12 seconds. Leaving it > commented results in the 4-second time originally posted. Seems that the time is constant at around 13 seconds once you go above 200 records or so, which is acceptable. Thanks for your help. Regards, -- Raj > > On 12/07/2011 15:34, Raj Mathur (राज माथुर) wrote: > > > I'm trying to correlate Call Data Records (CDRs) from two > > > Asterisk servers, one of which uses the other for telephony. > > > The data is in the tables cdr and cdr2. With some indexes, the > > > query and explain result are: > > > > > > explain analyse select cdr.calldate, cdr2.calldate, > > > (cdr2.calldate-cdr.calldate) as rtdur, cdr.clid, cdr.dst, > > > cdr2.src, cdr2.dst, cdr2.dstchannel, cdr2.lastapp, > > > cdr2.duration, > > > cdr2.disposition from cdr, cdr2 where cdr2.calldate>= > > > cdr.calldate and cdr.clid=cdr2.clid and > > > cdr.dst=substring(cdr2.dst from 4) order by cdr.calldate, > > > cdr2.calldate, cdr.clid limit 100; > > > > > > QUERY > > PLAN > > > > - > > > -- > > > > > > --- --- > > > > > > Limit (cost=46782.15..46782.40 rows=100 width=109) (actual > > > time=4077.866..4078.054 > > > > > > rows=100 loops=1) > > > > > > -> Sort (cost=46782.15..46785.33 rows=1272 width=109) > > > (actual time=4077.863..4077.926 > > > > > > rows=100 loops=1) > > > > > > Sort Key: cdr.calldate, cdr2.calldate, cdr.clid > > > Sort Method: top-N heapsort Memory: 42kB > > > -> Merge Join (cost=2.95..46733.54 rows=1272 > > > width=109) (actual > > > > > > time=0.070..3799.546 rows=168307 loops=1) > > > > > > Merge Cond: (((cdr.clid)::text = > > > (cdr2.clid)::text) AND ((cdr.dst)::text = > > > > > > "substring"((cdr2.dst)::text, 4))) > > > > > > Join Filter: (cdr2.calldate>= cdr.calldate) > > > -> Index Scan using ick1 on cdr > > > (cost=0.00..34667.86 rows=208798 > > > > > > width=43) (actual time=0.022..434.246 rows=208798 loops=1) > > > > > > -> Index Scan using i2k1 on cdr2 > > > (cost=0.00..9960.89 rows=65449 width=88) > > > > > > (actual time=0.011..391.599 rows=240981 loops=1) > > > > > > Total runtime: 4078.184 ms > > > > > > (10 rows) > > > > > > Is there any way to make this query faster? I already have an > > > index i2k1 on substring(cdr2.dst from 4), which is being used. > > > > > > Application > > > --- > > > > > > I'm looking for all caller records in cdr2 that have the same > > > callerid (clid) and destination (dst) and were started on cdr2 > > > after they were started on cdr. cdr2.dst is the same as cdr.dst > > > but with a 3-character prefix. -- Raj Mathur || r...@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Correlating Asterisk CDRs
On Wednesday 07 Dec 2011, Brent Dombrowski wrote:
> On Dec 7, 2011, at 6:34 AM, Raj Mathur (राज माथुर) wrote:
> > I'm trying to correlate Call Data Records (CDRs) from two Asterisk
> > servers, one of which uses the other for telephony. The data is in
> > the tables cdr and cdr2. With some indexes, the query and explain
> > result are:
> >
> > explain analyse select cdr.calldate, cdr2.calldate,
> > (cdr2.calldate-cdr.calldate) as rtdur, cdr.clid, cdr.dst, cdr2.src,
> > cdr2.dst, cdr2.dstchannel, cdr2.lastapp, cdr2.duration,
> > cdr2.disposition from cdr, cdr2 where cdr2.calldate >= cdr.calldate
> > and cdr.clid=cdr2.clid and cdr.dst=substring(cdr2.dst from 4) order
> > by cdr.calldate, cdr2.calldate, cdr.clid limit 100;
> >
> > QUERY
PLAN
> >
> > ---
> > ---
> > --- Limit (cost=46782.15..46782.40 rows=100 width=109) (actual
> > time=4077.866..4078.054 rows=100 loops=1)
> >
> > -> Sort (cost=46782.15..46785.33 rows=1272 width=109) (actual
> > time=4077.863..4077.926
> >
> > rows=100 loops=1)
> >
> > Sort Key: cdr.calldate, cdr2.calldate, cdr.clid
> > Sort Method: top-N heapsort Memory: 42kB
> > -> Merge Join (cost=2.95..46733.54 rows=1272 width=109)
> > (actual
> >
> > time=0.070..3799.546 rows=168307 loops=1)
> >
> > Merge Cond: (((cdr.clid)::text = (cdr2.clid)::text)
> > AND ((cdr.dst)::text =
> >
> > "substring"((cdr2.dst)::text, 4)))
> >
> > Join Filter: (cdr2.calldate >= cdr.calldate)
> > -> Index Scan using ick1 on cdr
> > (cost=0.00..34667.86 rows=208798
> >
> > width=43) (actual time=0.022..434.246 rows=208798 loops=1)
> >
> > -> Index Scan using i2k1 on cdr2
> > (cost=0.00..9960.89 rows=65449 width=88)
> >
> > (actual time=0.011..391.599 rows=240981 loops=1)
> > Total runtime: 4078.184 ms
> > (10 rows)
> >
> > Is there any way to make this query faster? I already have an
> > index i2k1 on substring(cdr2.dst from 4), which is being used.
>
> You have a non-equi join in there (cdr2.calldate >= cdr.calldate). I
> would try to get rid of that. It's increasing the number of rows in
> the result set and will only get worse as your data set grows.
Seen. At the moment (and in the foreseeable future) the query is going
to be run with a condition of the form "cdr.calldate >=
date_trunc('day', now())", so the set of matches from cdr2 will remain
relatively constant.
However, you're right, the scope of cdr2 calldates also ought to be time
limited. Have added an "and cdr2.calldate < cdr.calldate + interval '1
day'" to the query. Thanks.
Regards,
-- Raj
--
Raj Mathur || r...@kandalaya.org || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves || http://schizoid.in || D17F
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Correlating Asterisk CDRs
On Thursday 08 Dec 2011, Scott Marlowe wrote:
> 2011/12/7 Raj Mathur (राज माथुर) :
> > QUERY
> > PLAN
> > --
> > ---
> > Limit (cost=46782.15..46782.40 rows=100 width=109)
> > (actual time=4077.866..4078.054 rows=100 loops=1)
> > -> Sort (cost=46782.15..46785.33 rows=1272 width=109) (actual
> > time=4077.863..4077.926 rows=100 loops=1)
> > Sort Key: cdr.calldate, cdr2.calldate, cdr.clid
> > Sort Method: top-N heapsort Memory: 42kB
> > -> Merge Join (cost=2.95..46733.54 rows=1272 width=109)
> > (actual time=0.070..3799.546 rows=168307 loops=1)
>
> Two things to look at here. First is that the estimation of rows
> expected and returned vary by a factor over over 100, which means the
> query planner may be making suboptimal choices in terms of the plan
> it is running. If increasing stats target on the target columns in
> the query helps, then that's worth trying. Raise it and re-analyze
> and see if you get a closer estimate. To test if the merge join is
> the best choice or not, you can use the set enable_xxx for it (in
> this case set enable_mergejoin=off) and then run the query again
> through explain analyze and see if the performance gets any better.
Fixed the first -- all it needed was a vacuum analyse, and the performance
improved by 50%. Enabling/disabling mergejoin doesn't seem to make any
difference to the timing. However, after the vacuum analyse the planner
is now using:
Limit (cost=37499.24..37502.08 rows=1138 width=109) (actual
time=6355.308..6709.661
rows=168307 loops=1)
-> Sort (cost=37499.24..37502.08 rows=1138 width=109) (actual
time=6355.304..6491.595
rows=168307 loops=1)
Sort Key: cdr.calldate, cdr2.calldate, cdr.clid
Sort Method: quicksort Memory: 45211kB
-> Merge Join (cost=34720.94..37441.47 rows=1138 width=109) (actual
time=3438.318..5853.947 rows=168307 loops=1)
Merge Cond: (((cdr.dst)::text = ("substring"((cdr2.dst)::text,
4))) AND
((cdr.clid)::text = (cdr2.clid)::text))
Join Filter: (cdr2.calldate >= cdr.calldate)
-> Sort (cost=26987.11..27509.10 rows=208798 width=43) (actual
time=2631.166..2833.926 rows=208748 loops=1)
Sort Key: cdr.dst, cdr.clid
Sort Method: quicksort Memory: 19696kB
-> Seq Scan on cdr (cost=0.00..8537.98 rows=208798
width=43)
(actual time=0.009..211.330 rows=208798 loops=1)
-> Sort (cost=7684.78..7848.41 rows=65449 width=89) (actual
time=807.031..991.649 rows=240981 loops=1)
Sort Key: ("substring"((cdr2.dst)::text, 4)), cdr2.clid
Sort Method: quicksort Memory: 9889kB
-> Seq Scan on cdr2 (cost=0.00..2449.49 rows=65449
width=89)
(actual time=0.021..125.630 rows=65449 loops=1)
Total runtime: 6823.029 ms
Can you see any place here where adding indexes may help?
Regards,
-- Raj
--
Raj Mathur || r...@kandalaya.org || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves || http://schizoid.in || D17F
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] need help with import
On Thursday 16 Feb 2012, Andreas wrote: > Hi > I get CSV files to import. > Th structure is like this. > main part, sub part > Could be like this > > A, a1 > A, a2 > A, a3 > B, b1 > B, b2 > > The database has a table for main_part and one for sub_part. > The relation needs to be n:m so there is a relation table that holds > ( main_id, sub_id ). > The 2 primary keys main_part.id and sub_part.id are both serials. > > Is there a way to do an import with SQL? > > I can read the CSV into a temporary table > and I can do a > INSERT INTO main_part ( ... ) SELECT DISTINCT main columns FROM > import; as well as a > INSERT INTO sub_part ( ... ) SELECT sub columns FROM import; > > But how would I know what main_id and sub_id to insert into the n:m > relation? Is this what you need? foo=> create table mp(mid serial primary key, m text); CREATE TABLE foo=> create table sp(sid serial primary key, s text); CREATE TABLE foo=> create table ms(mid int references mp, sid int references sp, primary key(mid, sid)); CREATE TABLE foo=> create temporary table t(m text, s text); CREATE TABLE foo=> \copy t from '/tmp/x' csv foo=> select * from t; m | s ---+ A | a1 A | a2 A | a3 B | b1 B | b2 (5 rows) foo=> insert into mp(m) (select distinct m from t); INSERT 0 2 foo=> insert into sp(s) (select distinct s from t); INSERT 0 5 foo=> select * from mp; mid | m -+--- 1 | A 2 | B (2 rows) foo=> select * from sp; sid | s -+ 1 | a1 2 | a2 3 | a3 4 | b1 5 | b2 (5 rows) foo=> insert into ms (select mid, sid from mp, sp where (m,s) in (select m, s from t)); INSERT 0 5 foo=> select * from ms; mid | sid -+- 1 | 1 1 | 2 1 | 3 2 | 4 2 | 5 (5 rows) foo=> Regards, -- Raj -- Raj Mathur || r...@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Simple way to get missing number
On Tuesday 24 Apr 2012, Andreas Kretschmer wrote: > Emi Lu wrote: > > Good morning, > > > > May I know is there a simple sql command which could return missing > > numbers please? > > > > For example, > > > > t1(id integer) > > > > values= 1, 2, 3 500 > > > > select miss_num(id) > > from t1 ; > > something like > > ,[ code ] > > | test=# select * from emi_lu ; > | > | i > | > | --- > | > | 1 > | 2 > | 3 > | 5 > | 6 > | 8 > | 9 > | > | (7 rows) > | > | Time: 0,246 ms > | test=*# select * from generate_Series(1,10) s left join emi_lu on > | (s=emi_lu.i) where i is null; > | > | s | i > | > | +--- > | > | 4 | > | 7 | > | > | 10 | > | > | (3 rows) Nice one, but curious about how would this perform if the numbers in question extended into 7 figures or more? Regards, -- Raj -- Raj Mathur || r...@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] master/detail
On Monday 21 May 2012, Jan Bakuwel wrote: > Hi, > > I'm trying to get my head around the following question. As an > example take a table with products: > > productid (pk) > name > > and productprice > > productpriceid (pk) > productid (fk) > pricedate > price > > There are multiple records in productprice for each product as prices > (often) go up and (less often) go down. > > I'm looking for a query that returns the following: > > productid, name, pricedate, current_price, difference > > current_price is the latest (ie. most recent date) price of the > product and difference is the difference in price between the latest > price and the price before the latest. > > Any suggestions how to do this with SQL only? I can make it work with > a function (probably less efficient) but think this should be > possible with SQL too... Something like this ought to do it (not tested): select latest.price, latest.price - next.price from (select price from productprice where productid = 1 order by pricedate desc limit 1) latest, (select price from productprice where productid = 1 order by pricedate desc limit 2 offset 1) next; Regards, -- Raj -- Raj Mathur || r...@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Flatten table using timestamp and source
On Thursday 24 May 2012, Elrich Marx wrote: > I am quite new to Postgres, so please bear with me. > > I have a table with data in the following format: > > Table name : Time_Source_Table > > Source , Stime > 1, "2012-05-24 13:00:00" > 1, "2012-05-24 13:01:00" > 1, "2012-05-24 13:02:00" > 2, "2012-05-24 13:03:00" > 2, "2012-05-24 13:04:00" > 1, "2012-05-24 13:05:00" > 1, "2012-05-24 13:06:00" > > I’m trying to get to a result that flattens the results based on > source, to look like this: > > Source, Stime, Etime > 1, "2012-05-24 13:00:00","2012-05-24 13:02:00" > 2, "2012-05-24 13:03:00","2012-05-24 13:04:00" > 1, "2012-05-24 13:05:00","2012-05-24 13:06:00" > > Where Etime is the last Stime for the same source. How do you figure out that the Etime for (1, 13:00:00) is (1, 13:02:00) and not (1, 13:01:00)? Regards, -- Raj -- Raj Mathur || r...@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Flatten table using timestamp and source
9 | 1970-01-01 07:15:01 9 | 1970-01-01 07:16:01 9 | 1970-01-01 07:17:01 7 | 1970-01-01 07:18:01 7 | 1970-01-01 07:19:01 7 | 1970-01-01 07:20:01 7 | 1970-01-01 07:21:01 RESULT -- source | start_time | end_time +-+- 1 | 1970-01-01 05:30:01 | 1970-01-01 05:32:01 6 | 1970-01-01 05:33:01 | 1970-01-01 05:37:01 2 | 1970-01-01 05:38:01 | 1970-01-01 05:41:01 6 | 1970-01-01 05:42:01 | 1970-01-01 05:46:01 4 | 1970-01-01 05:47:01 | 1970-01-01 05:51:01 0 | 1970-01-01 05:52:01 | 1970-01-01 05:55:01 7 | 1970-01-01 05:56:01 | 1970-01-01 05:58:01 8 | 1970-01-01 05:59:01 | 1970-01-01 06:03:01 1 | 1970-01-01 06:04:01 | 1970-01-01 06:10:01 8 | 1970-01-01 06:11:01 | 1970-01-01 06:13:01 6 | 1970-01-01 06:14:01 | 1970-01-01 06:16:01 4 | 1970-01-01 06:17:01 | 1970-01-01 06:18:01 9 | 1970-01-01 06:19:01 | 1970-01-01 06:22:01 2 | 1970-01-01 06:23:01 | 1970-01-01 06:25:01 1 | 1970-01-01 06:26:01 | 1970-01-01 06:29:01 4 | 1970-01-01 06:30:01 | 1970-01-01 06:34:01 0 | 1970-01-01 06:35:01 | 1970-01-01 06:37:01 9 | 1970-01-01 06:38:01 | 1970-01-01 06:42:01 1 | 1970-01-01 06:43:01 | 1970-01-01 06:45:01 8 | 1970-01-01 06:46:01 | 1970-01-01 06:50:01 0 | 1970-01-01 06:51:01 | 1970-01-01 06:57:01 2 | 1970-01-01 06:58:01 | 1970-01-01 07:05:01 4 | 1970-01-01 07:06:01 | 1970-01-01 07:07:01 2 | 1970-01-01 07:08:01 | 1970-01-01 07:12:01 7 | 1970-01-01 07:13:01 | 1970-01-01 07:14:01 9 | 1970-01-01 07:15:01 | 1970-01-01 07:17:01 Regards, -- Raj > -Original Message- > From: pgsql-sql-ow...@postgresql.org > [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Raj Mathur (??? > ?) > Sent: 24 May 2012 01:59 PM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] Flatten table using timestamp and source > > On Thursday 24 May 2012, Elrich Marx wrote: > > I am quite new to Postgres, so please bear with me. > > > > I have a table with data in the following format: > > > > Table name : Time_Source_Table > > > > Source , Stime > > 1, "2012-05-24 13:00:00" > > 1, "2012-05-24 13:01:00" > > 1, "2012-05-24 13:02:00" > > 2, "2012-05-24 13:03:00" > > 2, "2012-05-24 13:04:00" > > 1, "2012-05-24 13:05:00" > > 1, "2012-05-24 13:06:00" > > > > I’m trying to get to a result that flattens the results based on > > source, to look like this: > > > > Source, Stime, Etime > > 1, "2012-05-24 13:00:00","2012-05-24 13:02:00" > > 2, "2012-05-24 13:03:00","2012-05-24 13:04:00" > > 1, "2012-05-24 13:05:00","2012-05-24 13:06:00" > > > > Where Etime is the last Stime for the same source. > > How do you figure out that the Etime for (1, 13:00:00) is (1, > 13:02:00) and not (1, 13:01:00)? -- Raj Mathur || r...@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Flatten table using timestamp and source
On Friday 25 May 2012, Raj Mathur (राज माथुर) wrote: > On Thursday 24 May 2012, Elrich Marx wrote: > > If source changes, in this case from 1 to 2, then etime would be > > the last value of stime for source =1; So for source 1 it starts > > at stime 13:00 and continues till 13:02 (etime). > > > > This should result in 3 records, because source is 1, then 2, then > > 1 again. I hope this explains ? > > I think I understand. Here's a partially working example -- it > doesn't compute the last interval. Probably amenable to some severe > optimisation too, but then I don't claim to be an SQL expert :) With the last interval computation: QUERY - with first_last as ( select * from ( select source, time, case when lag(source) over (order by time) != source or lag(source) over (order by time) is null then 1 else 0 end as is_first, case when lead(source) over (order by time) != source or lead(source) over (order by time) is null then 1 else 0 end as is_last from p ) foo where is_first != 0 or is_last != 0 ) select t1.source, start_time, end_time from ( select source, time as start_time from first_last where is_first = 1 ) t1 join ( select source, time as end_time, is_last from first_last where is_last = 1 ) t2 on ( t1.source = t2.source and t2.end_time > t1.start_time and ( ( t2.end_time < ( select time from first_last where source != t2.source and time > t1.start_time order by time limit 1 ) ) or ( t1.start_time = ( select time from first_last where is_first = 1 order by time desc limit 1 ) and t2.end_time = ( select time from first_last where is_last = 1 order by time desc limit 1 ) ) ) ) ; RESULT (with same data set as before) -- source | start_time | end_time +-+- 1 | 1970-01-01 05:30:01 | 1970-01-01 05:32:01 6 | 1970-01-01 05:33:01 | 1970-01-01 05:37:01 2 | 1970-01-01 05:38:01 | 1970-01-01 05:41:01 6 | 1970-01-01 05:42:01 | 1970-01-01 05:46:01 4 | 1970-01-01 05:47:01 | 1970-01-01 05:51:01 0 | 1970-01-01 05:52:01 | 1970-01-01 05:55:01 7 | 1970-01-01 05:56:01 | 1970-01-01 05:58:01 8 | 1970-01-01 05:59:01 | 1970-01-01 06:03:01 1 | 1970-01-01 06:04:01 | 1970-01-01 06:10:01 8 | 1970-01-01 06:11:01 | 1970-01-01 06:13:01 6 | 1970-01-01 06:14:01 | 1970-01-01 06:16:01 4 | 1970-01-01 06:17:01 | 1970-01-01 06:18:01 9 | 1970-01-01 06:19:01 | 1970-01-01 06:22:01 2 | 1970-01-01 06:23:01 | 1970-01-01 06:25:01 1 | 1970-01-01 06:26:01 | 1970-01-01 06:29:01 4 | 1970-01-01 06:30:01 | 1970-01-01 06:34:01 0 | 1970-01-01 06:35:01 | 1970-01-01 06:37:01 9 | 1970-01-01 06:38:01 | 1970-01-01 06:42:01 1 | 1970-01-01 06:43:01 | 1970-01-01 06:45:01 8 | 1970-01-01 06:46:01 | 1970-01-01 06:50:01 0 | 1970-01-01 06:51:01 | 1970-01-01 06:57:01 2 | 1970-01-01 06:58:01 | 1970-01-01 07:05:01 4 | 1970-01-01 07:06:01 | 1970-01-01 07:07:01 2 | 1970-01-01 07:08:01 | 1970-01-01 07:12:01 7 | 1970-01-01 07:13:01 | 1970-01-01 07:14:01 9 | 1970-01-01 07:15:01 | 1970-01-01 07:17:01 7 | 1970-01-01 07:18:01 | 1970-01-01 07:21:01 Would be interested in seeing how to shorten and/or optimise this query. Regards, -- Raj -- Raj Mathur || r...@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Flatten table using timestamp and source
On Saturday 26 May 2012, Raj Mathur (राज माथुर) wrote: > On Friday 25 May 2012, Raj Mathur (राज माथुर) wrote: > > On Thursday 24 May 2012, Elrich Marx wrote: > > > If source changes, in this case from 1 to 2, then etime would be > > > the last value of stime for source =1; So for source 1 it starts > > > at stime 13:00 and continues till 13:02 (etime). > > > > > > This should result in 3 records, because source is 1, then 2, > > > then 1 again. I hope this explains ? > > > > I think I understand. Here's a partially working example -- it > > doesn't compute the last interval. Probably amenable to some > > severe optimisation too, but then I don't claim to be an SQL > > expert :) > > With the last interval computation: Wokeh, much better solution (IMNSHO). Results are the same as earlier, probably still amenable to optimisation and simplification. Incidentally, thanks for handing out the problem! It was a good brain- teaser (and also a good opportunity to figure out window functions, which I hadn't worked with earlier). QUERY - -- -- Compute rows that are the first or the last in an interval. -- with first_last as ( select * from ( select source, time, case when lag(source) over (order by time) != source or lag(source) over (order by time) is null then 1 else 0 end as is_first, case when lead(source) over (order by time) != source or lead(source) over (order by time) is null then 1 else 0 end as is_last from p ) foo where is_first != 0 or is_last != 0 ) -- -- Main query -- select source, start_time, end_time from ( -- Get each row and the time from the next one select source, time as start_time, lead(time) over(order by time) as end_time, is_first from first_last ) bar -- Discard rows generated by the is_last row in the inner query where is_first = 1; ; > RESULT (with same data set as before) > -- > source | start_time | end_time > +-+- > 1 | 1970-01-01 05:30:01 | 1970-01-01 05:32:01 > 6 | 1970-01-01 05:33:01 | 1970-01-01 05:37:01 > 2 | 1970-01-01 05:38:01 | 1970-01-01 05:41:01 > 6 | 1970-01-01 05:42:01 | 1970-01-01 05:46:01 > 4 | 1970-01-01 05:47:01 | 1970-01-01 05:51:01 > 0 | 1970-01-01 05:52:01 | 1970-01-01 05:55:01 > 7 | 1970-01-01 05:56:01 | 1970-01-01 05:58:01 > 8 | 1970-01-01 05:59:01 | 1970-01-01 06:03:01 > 1 | 1970-01-01 06:04:01 | 1970-01-01 06:10:01 > 8 | 1970-01-01 06:11:01 | 1970-01-01 06:13:01 > 6 | 1970-01-01 06:14:01 | 1970-01-01 06:16:01 > 4 | 1970-01-01 06:17:01 | 1970-01-01 06:18:01 > 9 | 1970-01-01 06:19:01 | 1970-01-01 06:22:01 > 2 | 1970-01-01 06:23:01 | 1970-01-01 06:25:01 > 1 | 1970-01-01 06:26:01 | 1970-01-01 06:29:01 > 4 | 1970-01-01 06:30:01 | 1970-01-01 06:34:01 > 0 | 1970-01-01 06:35:01 | 1970-01-01 06:37:01 > 9 | 1970-01-01 06:38:01 | 1970-01-01 06:42:01 > 1 | 1970-01-01 06:43:01 | 1970-01-01 06:45:01 > 8 | 1970-01-01 06:46:01 | 1970-01-01 06:50:01 > 0 | 1970-01-01 06:51:01 | 1970-01-01 06:57:01 > 2 | 1970-01-01 06:58:01 | 1970-01-01 07:05:01 > 4 | 1970-01-01 07:06:01 | 1970-01-01 07:07:01 > 2 | 1970-01-01 07:08:01 | 1970-01-01 07:12:01 > 7 | 1970-01-01 07:13:01 | 1970-01-01 07:14:01 > 9 | 1970-01-01 07:15:01 | 1970-01-01 07:17:01 > 7 | 1970-01-01 07:18:01 | 1970-01-01 07:21:01 Regards, -- Raj -- Raj Mathur || r...@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insane behaviour in 8.3.3
On Friday 15 Jun 2012, Samuel Gendler wrote: > On Fri, Jun 15, 2012 at 1:24 AM, Achilleas Mantzios < > > ach...@matrix.gatewaynet.com> wrote: > > And i forgot to mention, minicom term emulation quality sucks, even > > giving simple > > shell commands is a PITA, upgrading the whole fleet would mean bast > > case scenario > > minimum 21K USD for the whole fleet + suspension of all other > > activities for two months. > > If physical travel was involved, the cost would be increased at > > even higher levels. > > And what is the cost of data corruption on large numbers of systems? > And how much to fix that, especially if multiple systems fail at the > same time? Some things aren't free. $21K in exchange for NOT having > had to keep systems up to date for 4 years seems like a decent > trade. While I agree in principle with what you're saying, this specific comparison would be better stated as "What is the cost of data corruption multiplied by the risk of that corruption occurring?" The cost of upgrading is known and unavoidable. The cost of data corruption, while probably higher (unless Achilles has an effective backup/restore system), needs to be factored by its probability of occurance. Of course, neither you nor I are in Achilles' shoes, so trying to figure out where they pinch is academic at best. Regards, -- Raj -- Raj Mathur || r...@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
