Re: [GENERAL] Re: Partitioning such that key field of inherited tables no longer retains any selectivity
W dniu 11.05.2014 22:01, David G Johnston pisze:
On Sun, May 11, 2014 at 2:08 PM, Tim Kane [via PostgreSQL] <[hidden
email] >wrote:
[--]
This is basically what I intended to describe in "option 2"...without
the benefit of ever having really read the SQL standard.
So the planner would have to know that, for a given table, the
generation expression results in a constant - would likely in fact
have to be a constant expression like, assuming a non-number value,
='column_value', where the "=" sign indicates that this is a
generation expression and not a stored value (like default behaves
currently).
wouldn't it be ways better, if the constraints for partitioning by
inharitance were set at the "master" table, instead of the way it's
currently done at the inharited tables (as exclusive CHECK-s there)?
I mean a constraint like a "function(table columns) reutrning table_name
or tablespace_name of the actual target table"?
create table master (a int, b int, c int);
create table table_a (inharits master);
create table table_b (inharits master);
create function(a,b) returns text as $$ if a > b then return "table_a"
else return "table_b"; end if; end $$
... or:
create function(a,b) returns tablespace as $$ if a > b then return
tablespace("table_a") else return tablespace("table_b"); end if; end $$
alter table master add constraint "partitioning" check/select/route
function(a,b);
-R
Re: [GENERAL] a row not deletes
Thenx for explanations. W dniu 27.04.2014 16:56, David G Johnston pisze: Andres Freund-3 wrote Hi, On 2014-04-27 10:23:18 +0200, Rafał Pietrak wrote: I've just experienced an unexpected (for me) "loss" of DELETE. Is this a feature or a bug (postgres v.s. SQL)? I guess you're using 9.2 or older? You are not allowed to update the deleted row in a BEFORE trigger. The source has this comment about it (in 9.3 onwards): I'm using 9.1 (as of debian wheezy) IOW, it is a bug discovered during the 9.2 release that was deemed improper to back-patch. However, the bug applies to behavior that should only happen by mistake; you should not have a trigger that updates the row you are currently deleting. Hmmm. I was just exersising it, as the most "elegant" resolve to my case: 1. I have a shopping chart with items 2. which (conditionally) turns into an invoice on chart deletion. 3. the assumption is: the chart is not very rigouroiusly checked during its lifetime. 4. but the invoice have to so some "cleanup" is due just before an item is deleted from the chart. But, I understand that as of now, I cannot do that by "trigger avalanche" :( -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] a row not deletes
Hi the list, I've just experienced an unexpected (for me) "loss" of DELETE. Is this a feature or a bug (postgres v.s. SQL)? test case - test=# CREATE TABLE test (a int, b text); test=# INSERT INTO test (a,b) values (1,'asd'); test=# INSERT INTO test (a,b) values (2,'dfg'); test=# INSERT INTO test (a,b) values (3,'ghj'); test=# CREATE or replace FUNCTION test_del () returns trigger language plpgsql as $$ begin update test t set b = 'will delete this' where t.a=old.a; return old; end; $$; test=# CREATE TRIGGER test_trig BEFORE DELETE ON test for each row execute procedure test_del(); test=# DELETE FROM test where a=2; DELETE 0 test=# SELECT * from test; a | b +- 1 | asd 3 | ghj 2 | will delete this (3 rows) e.g.: an indicated row is not deleted, despite the fact, that the selector wasn't changed by the intermediate UPDATE. I understand, that the bucket was changed by the update, but should that matter? -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is there a way to deliver an array over column from a query window?
W dniu 04/26/2013 09:54 PM, Misa Simic pisze: SELECT DISTINCT a, b, c, array_agg(d) OVER (PARTITION BY c ) FROM ( SELECT a, b, c, d FROM testy where e <> 'email' and c='1035049' ORDER BY a, b, c, e ) t Doesnt give u desired result? Hmm... actualy, it looks like it does. I wouldn't thought, that the sort order is maintaned from subquery, but if it does, this is just it. It looks like I've just overdone the solution. -R On Friday, April 26, 2013, Rafał Pietrak wrote: W dniu 04/26/2013 05:25 PM, Tom Lane pisze: =?ISO-8859-2?Q?Rafa=B3_Pietrak?= writes: array_agg(distinct v order by v) -- works in postgres, but actually I need: array_agg(distinct v order by v,x) -- which doesn't. (ERROR: expressions must appear in argument list), Why do you think you need that? AFAICS, the extra order-by column could not in any way affect the result of the operation. In my particular case (e.g. not in general, since I assume, we all agree, that people do sort things comming out of the query for one purpose or another), is that: 1. the information i retrieve (the V), is a telephone number. 2. my database does keep numerous contact information (e.g. telephone numbers, email, etc) for "entities" registered here - e.g people/companies leave contact information of various relevance: my-private, my-office, my-lawyer, etc. 3. when I need to get in touch with somebody, I need to choose the number that is "most relevant" - one person leaves "my-private" phone, and "my-lawyer" phone; the other leaves "my-office", and "my-lawyer". 4. in the above example I'd like to peek: "my-private" for the first person, and "my-office" for the other. I wouldn't like to relay on randomness provided by the database query plan. 5. so I have "the other" column (the X, e.g "my-something"), that I'd like to sort the array elements by. And peek just the first element of the array. BTW: I've just rid off the array, and cooked a plain table join with "distinct on ()", which gives just what I needed. My initial plan of using array was to reduce the intermediate row-sets as much as possible as early as possible. Yet, in this case, plain old RDB joins proved to be better (may be not faster - a big multitable join is formed along the query, but conceptually cleaner, which works for me, the database isn't terribly big). So I have my problem solved, although I haven't figured out a way to have controll over the sort order of array_agg() result - which might be otherwise usefull. thnx, -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is there a way to deliver an array over column from a query window?
W dniu 04/26/2013 05:25 PM, Tom Lane pisze: =?ISO-8859-2?Q?Rafa=B3_Pietrak?= writes: array_agg(distinct v order by v) -- works in postgres, but actually I need: array_agg(distinct v order by v,x) -- which doesn't. (ERROR: expressions must appear in argument list), Why do you think you need that? AFAICS, the extra order-by column could not in any way affect the result of the operation. In my particular case (e.g. not in general, since I assume, we all agree, that people do sort things comming out of the query for one purpose or another), is that: 1. the information i retrieve (the V), is a telephone number. 2. my database does keep numerous contact information (e.g. telephone numbers, email, etc) for "entities" registered here - e.g people/companies leave contact information of various relevance: my-private, my-office, my-lawyer, etc. 3. when I need to get in touch with somebody, I need to choose the number that is "most relevant" - one person leaves "my-private" phone, and "my-lawyer" phone; the other leaves "my-office", and "my-lawyer". 4. in the above example I'd like to peek: "my-private" for the first person, and "my-office" for the other. I wouldn't like to relay on randomness provided by the database query plan. 5. so I have "the other" column (the X, e.g "my-something"), that I'd like to sort the array elements by. And peek just the first element of the array. BTW: I've just rid off the array, and cooked a plain table join with "distinct on ()", which gives just what I needed. My initial plan of using array was to reduce the intermediate row-sets as much as possible as early as possible. Yet, in this case, plain old RDB joins proved to be better (may be not faster - a big multitable join is formed along the query, but conceptually cleaner, which works for me, the database isn't terribly big). So I have my problem solved, although I haven't figured out a way to have controll over the sort order of array_agg() result - which might be otherwise usefull. thnx, -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is there a way to deliver an array over column from a query window?
W dniu 04/26/2013 12:25 AM, Merlin Moncure pisze: [--] select array_agg(v order by v desc) from generate_series(1,3) v; also, 'distinct' select array_agg(distinct v order by v desc) from (select generate_series(1,3) v union all select generate_series(1,3)) q; [] This feature was added w/9.0. This means you are on 8.4. Time to upgrade... I tried it, and it looks, like I can have just one column "processed" by the contruct "distinct ... order..." written under array_agg(). So it apears, that its "sole purpose" is to yield an array with distinct values, should there be duplicates in the source (table or query). Having distinct values is fine, but I actually need to have the resulting array, contain values in sort-order, which is defined by values in another column. like: array_agg(distinct v order by v) -- works in postgres, but actually I need: array_agg(distinct v order by v,x) -- which doesn't. (ERROR: expressions must appear in argument list), but that suggestion is not possible here, e.g.: array_agg(distinct v,x order by v,x) -- is actually a two argument array_agg() function call. Elaborating on the second example above: what I need is: "feed the array_agg() only with distinct (by sorting for uniqness) v-values, but feed them sorted according to x-values". This is what I need. I'd apreciate other ideas (my yesterday plan to avoid array untill late in the query failed). Any sugestions welcome. (Or may be I'm just plain wrong about the above "distinct" construct - then will apreciate a pointer to some documentation). thnx, -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is there a way to deliver an array over column from a query window?
W dniu 04/26/2013 12:25 AM, Merlin Moncure pisze: On Thu, Apr 25, 2013 at 1:30 PM, Rafał Pietrak wrote: [--] No, I don't (manual: http://www.postgresql.org/docs/9.1/static/tutorial-window.html, have just one word "distinct" on that page, and it's not in the above context). And I cannot duplicate the above: # select array_agg(distinct v order by v desc) from (select generate_series(1,3) v union all select generate_series(1,3)) q; ERROR: syntax error at or near "order" LINE 1: select array_agg(distinct v order by v desc) from (select ge... Did I miss something?? This feature was added w/9.0. This means you are on 8.4. Time to upgrade... Yes, true. I kind of postpone that until debian-wheeze gets released as stable. But what about the postgres documentation. I haven't found that feature in postgres-v9.1 docs (link above). Where can I read about it? -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is there a way to deliver an array over column from a query window?
W dniu 04/25/2013 03:44 PM, Merlin Moncure pisze:
On Wed, Apr 24, 2013 at 2:44 AM, Rafał Pietrak wrote:
W dniu 03/24/2013 12:11 PM, Rafał Pietrak pisze:
W dniu 03/24/2013 12:06 PM, Misa Simic pisze:
maybe,
SELECT DISTINCT issuer,amount, array_agg(REFERENCE) over (partition by
invoice_nr) from invoices;
RIGHT. Thenx. (and the first thing I did, I've read the doc on
array_agg() what stress makes from people :(
Actually, I have a problem with that (which I haven't noticed earlier
because the data I'm having, don't have to many "duplicates" that cause it).
The problem is, that:
--
SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ) FROM testy
k where k.e <> 'email' and k.c='1035049' ;
a |b |c| array_agg
--+--+-+---
1035 | 10410053 | 1035049 | {9902031328529,5951948640868}
---
is _almost_ fine. But I actually need to have control over the order in
which the array gathered its values. So I try:
SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ORDER BY k.e)
FROM testy k where k.e <> 'email' and k.c='1035049' ;
you are aware of in-aggregate ordering (not completely sure if it
meets your use case?
select array_agg(v order by v desc) from generate_series(1,3) v;
also, 'distinct'
select array_agg(distinct v order by v desc) from (select
generate_series(1,3) v union all select generate_series(1,3)) q;
No, I don't (manual:
http://www.postgresql.org/docs/9.1/static/tutorial-window.html, have
just one word "distinct" on that page, and it's not in the above
context). And I cannot duplicate the above:
# select array_agg(distinct v order by v desc) from (select
generate_series(1,3) v union all select generate_series(1,3)) q;
ERROR: syntax error at or near "order"
LINE 1: select array_agg(distinct v order by v desc) from (select ge...
Did I miss something??
In the mean time, I was working towards:
# with ktkt(b,l,s,t) as (SELECT a, b, c, array_agg(k.d)OVER (PARTITION
BY k.c ORDER BY k.e) FROM testy k where k.e <> 'email') select
distinct on (b,l,s) b,l,s,t from ktkt k where k.s='1035049' order by
b,l,s,array_length(t,1) desc;
b |l |s| t
--+--+-+---
1035 | 10410053 | 1035049 | {9902031328529,5951948640868}
(1 row)
---
Which gives the "expected" result, not exactly, because:
my final goal is to select one contact information for an "entity",
which is, say: telephone, and which is, say: mobile. Taking into
account, that the main contact information table is roughly: CREATE
TABLE testy (id_a, id_b, id_c, conact_value, contact_kind, primary key
(id_a, id_b,id_c)). ... I cannot collapse the multiple identification
columns - they collectively form a unique ID, of an entity. That main
contact information table has associated tables like to "fixed/mobile"
("testy" has additional FK columns for that).
And the above "partial result" isn't working towards my final goal.
But, while writing this response, It occured to me, that, may be I
shouldn't build the array so early in the query, but start with a wider
join (only reduced by the desired contact attributes) . OK. I'll do
some testing with that.
Still, I'll be greatfull for some explanations why the "distinct"
disdn't work for me. May be that would be a tool for this case.
thnx,
-R
Re: [GENERAL] is there a way to deliver an array over column from a query window?
W dniu 03/24/2013 12:11 PM, Rafał Pietrak pisze:
W dniu 03/24/2013 12:06 PM, Misa Simic pisze:
maybe,
SELECT DISTINCT issuer,amount, array_agg(REFERENCE) over (partition
by invoice_nr) from invoices;
RIGHT. Thenx. (and the first thing I did, I've read the doc on
array_agg() what stress makes from people :(
Actually, I have a problem with that (which I haven't noticed earlier
because the data I'm having, don't have to many "duplicates" that cause
it). The problem is, that:
--
SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ) FROM
testy k where k.e <> 'email' and k.c='1035049' ;
a |b |c| array_agg
--+--+-+---
1035 | 10410053 | 1035049 | {9902031328529,5951948640868}
---
is _almost_ fine. But I actually need to have control over the order in
which the array gathered its values. So I try:
SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ORDER BY
k.e) FROM testy k where k.e <> 'email' and k.c='1035049' ;
a |b |c| array_agg
--+--+-+---
1035 | 10410053 | 1035049 | {5951948640868}
1035 | 10410053 | 1035049 | {5951948640868,9902031328529}
(2 rows)
--
And this is not at all what I've expected - the aggerate function
returned different values over the selected partition.
I understand, that this behavior (of changing the aggregate function
return values) is there for the purpose of having sum() - and the like -
aggregate functions return accumulating/averaged/etc values as of the
example in postgres documentation (
http://www.postgresql.org/docs/9.1/static/tutorial-window.html)
But the array_agg() is significantly different from other aggregate
functions - it maintains all the trasspassed values within; under such
circumstances: is it reasonable to copy that functionality (of PARTITION
OVER ... ORDER BY...) in it?
A particular value relevant to a particular row (when SELECT withiout
DISTINCT) can be retrieved by RANK() function used as an index into the
resulting array.
But, if (unfortunately) this functionality have to stay: Can somebody
pls help me cooking an SQL that returns the same value of array_agg()
over the entire partition, while letting me control the order of
aggregated values, based on the order of column "E"?
My table for the showcase was:
---
SELECT * FROM testy;
a |b |c| d | e
--+--+-+--+---
1035 | 10410053 | 1035049 | 9902031328529| tel
1035 | 10410053 | 1035049 | 5291286...@gmail.com | email
1035 | 10410053 | 1035049 | 5951948640868| tel2
(3 rows)
--
thx
-R
Re: [GENERAL] using text search
Ha! Got it!
for enybody whois interested:
with tst(regexp) as (SELECT '(' || array_to_string(array_agg(phrase),
'|') || ')' from KEYWORDS) select o.* from ORDERS o, tst t where o.info
~ t.regexp;
execution time: 6400ms. (keywords=4, orders=1mln)
BTW: does anybody know if there is an index, that could improve the
performence of the above regexp?
-R
W dniu 04/23/2013 02:30 PM, Alfonso Afonso pisze:
Hi Rafal
This function returns the position where the substring is found, so you could
do a query with clause position(table1.field in table2.field)
The 0 result is not found and maybe, in your case, is faster the use of
internal text functions instead of like comparison... hope helps.
Bye
El 23/04/2013, a las 11:24, Rafał Pietrak escribió:
W dniu 04/22/2013 08:43 PM, Alfonso Afonso pisze:
I forgot to say that the function is "position ( txtseach in txtcomplete)" :)
Bye
Alfonso, thenx
But if I may: How can I use that function? In a context of my problem?
then again. At the edge of desperation, I'm thinking of writing a function,
that will fetch all the KEYWORDS in one query, then cook explicit WHERE clause
by string operations, and then EXECUTE it. With (currently) four keywords, I'd
expect such function to return results within 5 seconds at most.
but I'd expect that there should be a way to "tell this" to postgresql SQL
directly. Isn't it?
-R
Alfonso Afonso
(personal)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] using text search
W dniu 04/22/2013 08:43 PM, Alfonso Afonso pisze: I forgot to say that the function is "position ( txtseach in txtcomplete)" :) Bye Alfonso, thenx But if I may: How can I use that function? In a context of my problem? then again. At the edge of desperation, I'm thinking of writing a function, that will fetch all the KEYWORDS in one query, then cook explicit WHERE clause by string operations, and then EXECUTE it. With (currently) four keywords, I'd expect such function to return results within 5 seconds at most. but I'd expect that there should be a way to "tell this" to postgresql SQL directly. Isn't it? -R
Re: [GENERAL] using text search
Hi,
W dniu 04/22/2013 05:57 PM, Alfonso Afonso pisze:
Hi Rafal
Maybe you should think or consider to have normalized database to do this kind
of select.
I mean that if the keyword is a key you could add a column to table orders and
have it ordered and indexed without having to reevaluate the query every time.
I wouldn't like to do that for the following reasons:
1. postgres is actually a "slave" database, where data is copied for www
publication from "root" database. the copying is achieved by means of
files produced by an export program, over which I don't have any
control; not to mention, that I don't have any control over the
layour/design/access to the "root" database. Under such circumstances
I'd rather keep postgres schema as close to the file format, not to the
"things" I do with the data afterwords - just in case "they" choose to
change the format.
2. The whole lot is reinitialized by night, so normalization would have
to be rerun as frequently.
3. The actual text-in-text search is not going to be very frequent -
like once a week, or after every import; I can materialize a VIEW for
that purpose, but it would save me an ocasional second query-run, when I
spot errors in results of the first. Not much of a gain, but I keep that
in mind for the future.
About your question, if you are searching a text inside another text you could
obtain bizarre results and consume a lot of resources, but my approximation to
your problem
Yes. Now I can see the "lot of resources": the query (like you suggest
below) currently runs for 5 minutes and haven't ended, yet. And this is
really bad, since my "volumes" are the following:
1. currently KEYWORDS table contains just 4 (four) rows.
2. currently ORDERS contains c.a. 1mln records.
3. currently SELECT * from ORDERS where info ~~ ('%' ||
'some-test-pattern' || '%'); complets in 1sec.
4. let's (for the purpose of this emial clearity) assing XX*PAT*XX to: "
info ~~ ('%' || 'some-test-pattern' || '%')", for different PATTERNS
5. currently SELECT * from ORDERS where (XX*PAT1*XX) or (XX*PAT2*XX);
executes in 1.7sec.
So putting 'some-test-pattern' into a table makes things "nonelinearly"
worse.
I can provide EXPLAIN ANALYSE of both cases If that would help, but that
will need some time, since the "two table" variant haven't finished in
5min, and I killed it before knowing how long it takes to complete.
-R
would be doing direct select instead of join select (in fact it is almost the
same, but more readable on this cases), like:
select o.* from orders as o, keywords as k
where o.info like ('%' || k.phrase || '%')
Hope help you.
Bye for now
El 22/04/2013, a las 14:15, Rafał Pietrak escribió:
... or not (I'm not quite sure)
Hello,
I have the following tables:
CREATE TABLE orders (info text, );
CREATE TABLE keywords (phrase text, .);
And I need to find all the ORDERS rows, which conain a PHRASE present in the
info column ... like so:
SELECT o.* from orders o join keywords k on (o.info ~~ '%' || k.phrase || '%');
... only this does not work, since:
ERROR: argument of JOIN/ON must be type boolean, not type text
is this possible in SQL? Or may be this a job for "ts_something()" (havent'
learned to use them, yet)???
-R
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Alfonso Afonso
(personal)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] using text search
... or not (I'm not quite sure) Hello, I have the following tables: CREATE TABLE orders (info text, ); CREATE TABLE keywords (phrase text, .); And I need to find all the ORDERS rows, which conain a PHRASE present in the info column ... like so: SELECT o.* from orders o join keywords k on (o.info ~~ '%' || k.phrase || '%'); ... only this does not work, since: ERROR: argument of JOIN/ON must be type boolean, not type text is this possible in SQL? Or may be this a job for "ts_something()" (havent' learned to use them, yet)??? -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is there a way to deliver an array over column from a query window?
W dniu 03/24/2013 12:06 PM, Misa Simic pisze: maybe, SELECT DISTINCT issuer,amount, array_agg(REFERENCE) over (partition by invoice_nr) from invoices; RIGHT. Thenx. (and the first thing I did, I've read the doc on array_agg() what stress makes from people :( thenx again, -R 2013/3/24 Rafał Pietrak <mailto:ra...@zorro.isa-geek.com>> Hi, I really don't know how to ask for what I'm looking for; but I think, may be, calling it an ARRAY yielding aggregate function for use within a query WINDOW would do? I'm looking for something like: SELECT DISTINCT issuer,amount, array(REFERENCE) over (partition by invoice_nr) from invoices; where the invoices table contain the usual invoice data, like: - amount - time of issue - the issuer ID - and the above REFERENCE, is the reference to a bank transfer record in another table. The thing is, that I have multiple funds transfer records referring to a single invoice; and (I currently think, that :) I need to fetch them all in a single column - so preferrably within an ARRAY of references (or just transfer IDs) to table containing funds transfer records. In other words, I wouldn't like to collapse the query at the application level ... but "if everything else fails", that is the last resort I keep in mind. The reason I'm not taking the last resort now, is that I also have multiple invoices to a single funds transfer record. And ultimately I'm hoping to cook a query, that would window-and-balance the two tables (invoices, and funds transfer) - so that most of the job is done by SQL/funciton/view, not by the external application. I've looked up postgres documentation, looking for a sort of "aggregate" function, that would not compute anything, but just build an ARRAY from all its input, but couldn't locate any. Is there a way to achieve this in postgresql? -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] is there a way to deliver an array over column from a query window?
Hi, I really don't know how to ask for what I'm looking for; but I think, may be, calling it an ARRAY yielding aggregate function for use within a query WINDOW would do? I'm looking for something like: SELECT DISTINCT issuer,amount, array(REFERENCE) over (partition by invoice_nr) from invoices; where the invoices table contain the usual invoice data, like: - amount - time of issue - the issuer ID - and the above REFERENCE, is the reference to a bank transfer record in another table. The thing is, that I have multiple funds transfer records referring to a single invoice; and (I currently think, that :) I need to fetch them all in a single column - so preferrably within an ARRAY of references (or just transfer IDs) to table containing funds transfer records. In other words, I wouldn't like to collapse the query at the application level ... but "if everything else fails", that is the last resort I keep in mind. The reason I'm not taking the last resort now, is that I also have multiple invoices to a single funds transfer record. And ultimately I'm hoping to cook a query, that would window-and-balance the two tables (invoices, and funds transfer) - so that most of the job is done by SQL/funciton/view, not by the external application. I've looked up postgres documentation, looking for a sort of "aggregate" function, that would not compute anything, but just build an ARRAY from all its input, but couldn't locate any. Is there a way to achieve this in postgresql? -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] self join for history analyzis
W dniu 01/26/2013 02:49 PM, Alban Hertroys pisze: On Jan 26, 2013, at 13:32, Rafał Pietrak wrote: I have a usage recording table: CREATE TABLE readings(tm timestamp, bytesin int, bytesout int); The readouts are made "occasionally" - the timespan between the readouts are not very precise, but there is a lot of those readouts. when presenting data, for every readout I need to compute the difference between it and the one immediately preceding it, and divide that by respective measurement interval. You don't need a self-join, you need a window function. See: http://www.postgresql.org/docs/9.1/static/tutorial-window.html For example: SELECT timestamp, lag(timestamp) OVER (ORDER BY timestamp) FROM readings; Yes. That's what I needed (another example, of how fragmented knowledge of a subject makes one (myself) use quite inapropriate keywords when searching). Thenx, -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] self join for history analyzis
Hello the list, For some time now, I'm struggling with a problem of self join of a table: I have a usage recording table: CREATE TABLE readings(tm timestamp, bytesin int, bytesout int); The readouts are made "occasionally" - the timespan between the readouts are not very precise, but there is a lot of those readouts. I need to make a self join of that table to analyze the bandwidth usage e.g.: when presenting data, for every readout I need to compute the difference between it and the one immediately preceding it, and divide that by respective measurement interval. Initially I've put an additional column with a serial into the readouts table, and did a join on (p.serial = n.serial+1); but that had an occasional glitch, when serial actually skipped a value. So I'm trying to work out a more resiliant/general solution. So far to no avail. Is there an "sql-idiom" (receipt?) to do such join? the better if without the spurious seiral column. Thenx -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OLD pseudo relation for INSERT in rules and triggers
W dniu 01/01/2013 07:24 PM, Adrian Klaver pisze: On 01/01/2013 10:17 AM, Rafał Pietrak wrote: [---] If not, I think having OLD.* in INSERT rules/triggers is worth pondering. The thing is, that it would be a valuable tool to mimic table-propper functionality by a view. The OLD.* preudorelation on INSERT could provide column defaults from the underlaying table definition. [] http://www.postgresql.org/docs/9.2/interactive/sql-alterview.html "SET/DROP DEFAULT These forms set or remove the default value for a column. A default value associated with a view column is inserted into INSERT statements on the view before the view's ON INSERT rule is applied, if the INSERT does not specify a value for the column." Aha. I felt, I was missing something. It is not quite the same, but close. It's not the same, as one has to keep track of the default value for one thing in two places; but it's surely better then nothing (as I thought, was the case). thenx again, -R -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] OLD pseudo relation for INSERT in rules and triggers
Hello the list,
As far as I can tell from a quick search through postgresql
documentation, the OLD.* pseudorelation is not available for INSERT
triggers and rules. And a little googleing I did, haven't pointed me to
anything relevant to the following. My apology if I miss in my search
such discussion happening earlier (I apreciate a pointer, where I can
see pros and cons that've been raisen back then);
If not, I think having OLD.* in INSERT rules/triggers is worth pondering.
The thing is, that it would be a valuable tool to mimic table-propper
functionality by a view. The OLD.* preudorelation on INSERT could
provide column defaults from the underlaying table definition.
like:
CREATE TABLE test (tm timestamp default now(), info text);
CREATE TABLE test_view AS SELECT * FROM test;
CREATE RULE with_defaults AS ON INSERT to test_view DO INSTEAD INSERT
INTO test (tm,info) VALUES ( //COALESCE(NEW.tm, OLD.tm), NEW.text);
so:
INSERT INTO test_view (info) VALUES ('hello');
and:
INSERT INTO test_view (tm, info) VALUES (null, 'hello');
both work just as if test_view was a TABLE with a default tm value defined.
-R
