agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedIssues with lag command
5+ messages / 5 participants
[nested] [flat]
* Issues with lag command
@ 2017-07-28 17:48 Mohamed DIA <macdia2002@gmail.com>
2017-07-28 19:21 ` Re: Issues with lag command Steve Midgley <science@misuse.org>
2017-07-28 19:53 ` Re: Issues with lag command Igor Neyman <ineyman@perceptron.com>
0 siblings, 2 replies; 5+ messages in thread
From: Mohamed DIA @ 2017-07-28 17:48 UTC (permalink / raw)
To: pgsql-sql
Hello
I have a test table with the following structure (2 columns: ID and time_id
)and data
ID, time_id
1;"2015-01-01"
2;""
3;""
4;"2015-01-02"
5;""
6;""
7;""
8;"2015-01-03"
9;""
10;""
11;""
12;""
13;"2015-01-05"
14;""
15;""
16;""
I'd like to update line 2 and 3 with the date in record 1 (2015-01-01)
Update line 5,6 and 7 with the date in record 4 (2015-01-02) and so on
The general logic is that anytime we find a record with a time_id null, we
would like to update it with the previous time_id that is not null.
I use the LAG function and the below code
CREATE OR REPLACE FUNCTION public.update_test_dates()
RETURNS SETOF test AS
$BODY$
DECLARE
r test%rowtype;
BEGIN
FOR r IN SELECT * FROM test order by id
LOOP
-- can do some processing here
if r.time_id is null
then
update test set time_id= (select lag(time_id) OVER (ORDER BY id)
from test where id=r.id) where id=r.id;
end if;
RETURN NEXT r; -- return current row of SELECT
END LOOP;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
select * from update_test_dates();
However, it does not work. Postgres update all rows with a NULL value
Any one can tell me what needs to be changed in my procedure in order to
fix the issue?
Regards
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Issues with lag command
2017-07-28 17:48 Issues with lag command Mohamed DIA <macdia2002@gmail.com>
@ 2017-07-28 19:21 ` Steve Midgley <science@misuse.org>
2017-07-28 19:29 ` Re: Issues with lag command David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Steve Midgley @ 2017-07-28 19:21 UTC (permalink / raw)
To: Mohamed DIA <macdia2002@gmail.com>; +Cc: pgsql-sql
On Fri, Jul 28, 2017 at 10:48 AM, Mohamed DIA <macdia2002@gmail.com> wrote:
> Hello
> I have a test table with the following structure (2 columns: ID and
> time_id )and data
>
> ID, time_id
>
> 1;"2015-01-01"
> 2;""
> 3;""
> 4;"2015-01-02"
> 5;""
> 6;""
> 7;""
> 8;"2015-01-03"
> 9;""
> 10;""
> 11;""
> 12;""
> 13;"2015-01-05"
> 14;""
> 15;""
> 16;""
>
> I'd like to update line 2 and 3 with the date in record 1 (2015-01-01)
> Update line 5,6 and 7 with the date in record 4 (2015-01-02) and so on
>
> The general logic is that anytime we find a record with a time_id null, we
> would like to update it with the previous time_id that is not null.
> I use the LAG function and the below code
>
>
>
> CREATE OR REPLACE FUNCTION public.update_test_dates()
> RETURNS SETOF test AS
> $BODY$
> DECLARE
>
> r test%rowtype;
> BEGIN
> FOR r IN SELECT * FROM test order by id
>
>
> LOOP
> -- can do some processing here
> if r.time_id is null
> then
> update test set time_id= (select lag(time_id) OVER (ORDER BY id)
> from test where id=r.id) where id=r.id;
> end if;
>
> RETURN NEXT r; -- return current row of SELECT
> END LOOP;
> RETURN;
> END
> $BODY$
> LANGUAGE plpgsql;
>
> select * from update_test_dates();
>
>
>
> However, it does not work. Postgres update all rows with a NULL value
> Any one can tell me what needs to be changed in my procedure in order to
> fix the issue?
>
> Regards
>
I may be wrong about this, but doesn't lag need an offset value? So your
statement should be `lag(time_id, 1)`?
Maybe lag defaults to offset 1 but if not, then it would seem your current
statement is grabbing its own row's time_id, and you want to pull the
time_id from the row offset by 1?
Steve
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Issues with lag command
2017-07-28 17:48 Issues with lag command Mohamed DIA <macdia2002@gmail.com>
2017-07-28 19:21 ` Re: Issues with lag command Steve Midgley <science@misuse.org>
@ 2017-07-28 19:29 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2017-07-28 19:29 UTC (permalink / raw)
To: Steve Midgley <science@misuse.org>; +Cc: Mohamed DIA <macdia2002@gmail.com>; pgsql-sql
On Fri, Jul 28, 2017 at 12:21 PM, Steve Midgley <science@misuse.org> wrote:
>
>> The general logic is that anytime we find a record with a time_id null,
>> we would like to update it with the previous time_id that is not null.
>> I use the LAG function and the below code
>>
>>
Lag can be made to work but only if you know that maximum lag that
guarantees a non-null value is present. If you cannot pick a reasonable
number then you should write a custom aggregate function. I believe I've
seen posts (probably to -general) detailing this and you might find it in
blog posts or the like as well. I'm unable to provide a working example
right now.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Issues with lag command
2017-07-28 17:48 Issues with lag command Mohamed DIA <macdia2002@gmail.com>
@ 2017-07-28 19:53 ` Igor Neyman <ineyman@perceptron.com>
2017-07-29 08:05 ` Re: Issues with lag command Harald Fuchs <hari.fuchs@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Igor Neyman @ 2017-07-28 19:53 UTC (permalink / raw)
To: Mohamed DIA <macdia2002@gmail.com>; pgsql-sql
Hello
I have a test table with the following structure (2 columns: ID and time_id )and data
ID, time_id
1;"2015-01-01"
2;""
3;""
4;"2015-01-02"
5;""
6;""
7;""
8;"2015-01-03"
9;""
10;""
11;""
12;""
13;"2015-01-05"
14;""
15;""
16;""
I'd like to update line 2 and 3 with the date in record 1 (2015-01-01)
Update line 5,6 and 7 with the date in record 4 (2015-01-02) and so on
How about simple SQL instead of PlSql:
UPDATE test T1 SET time_id = (SELECT T2.time_id FROM test T2 WHERE T2.id =
(SELECT max(T3.id) FROM test T3 WHERE T3.id < T1.id AND T3.time_id IS NOT NULL)
)
WHERE T1.time_id IS NULL;
Regards,
Igor Neyman
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Issues with lag command
2017-07-28 17:48 Issues with lag command Mohamed DIA <macdia2002@gmail.com>
2017-07-28 19:53 ` Re: Issues with lag command Igor Neyman <ineyman@perceptron.com>
@ 2017-07-29 08:05 ` Harald Fuchs <hari.fuchs@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Harald Fuchs @ 2017-07-29 08:05 UTC (permalink / raw)
To: pgsql-sql
Igor Neyman <ineyman@perceptron.com> writes:
> Hello
> I have a test table with the following structure (2 columns: ID and time_id )and data
>
> ID, time_id
> 1;"2015-01-01"
> 2;""
> 3;""
> 4;"2015-01-02"
> 5;""
> 6;""
> 7;""
> 8;"2015-01-03"
> 9;""
> 10;""
> 11;""
> 12;""
> 13;"2015-01-05"
> 14;""
> 15;""
> 16;""
> I'd like to update line 2 and 3 with the date in record 1 (2015-01-01)
> Update line 5,6 and 7 with the date in record 4 (2015-01-02) and so on
> How about simple SQL instead of PlSql:
>
> UPDATE test T1 SET time_id = (SELECT T2.time_id FROM test T2 WHERE T2.id =
> (SELECT max(T3.id) FROM test T3 WHERE T3.id < T1.id AND T3.time_id IS NOT NULL)
> )
> WHERE T1.time_id IS NULL;
You don't need that many table aliases:
UPDATE test
SET time_id =
( SELECT T1.time_id
FROM test T1
WHERE T1.id < test.id
AND T1.time_id IS NOT NULL
ORDER BY T1.id DESC
LIMIT 1
)
WHERE time_id IS NULL;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2017-07-29 08:05 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-07-28 17:48 Issues with lag command Mohamed DIA <macdia2002@gmail.com>
2017-07-28 19:21 ` Steve Midgley <science@misuse.org>
2017-07-28 19:29 ` David G. Johnston <david.g.johnston@gmail.com>
2017-07-28 19:53 ` Igor Neyman <ineyman@perceptron.com>
2017-07-29 08:05 ` Harald Fuchs <hari.fuchs@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox