Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


create table my_table(id         integer  generated always as identity
                                          primary key
                     , name      text
                     , is_readed boolean
                     ); 

insert into my_table( name, is_readed)
     select 'entry-' || to_char(x,'fm999')
          , random() < .4
       from generate_series(1,20) gs(x);

select * 
  from my_table;

CREATE TABLE



INSERT 0 20




id
name
is_readed


1
entry-1
f

2
entry-2
t

3
entry-3
t

4
entry-4
t

5
entry-5
t

6
entry-6
f

7
entry-7
t

8
entry-8
t

9
entry-9
f

10
entry-10
t

11
entry-11
t

12
entry-12
t

13
entry-13
t

14
entry-14
f

15
entry-15
f

16
entry-16
t

17
entry-17
t

18
entry-18
f

19
entry-19
f

20
entry-20
f



SELECT 20


with unwatched_rows as (
        select id 
          from my_table 
         where is_readed = false for update
     )
   , upt as (
       update my_table 
          set is_readed = true 
        where id in (select id from unwatched_rows)
     ) 
select * 
  from unwatched_rows;


id


1

6

9

14

15

18

19

20



SELECT 8


select * from my_table;


id
name
is_readed


2
entry-2
t

3
entry-3
t

4
entry-4
t

5
entry-5
t

7
entry-7
t

8
entry-8
t

10
entry-10
t

11
entry-11
t

12
entry-12
t

13
entry-13
t

16
entry-16
t

17
entry-17
t

1
entry-1
t

6
entry-6
t

9
entry-9
t

14
entry-14
t

15
entry-15
t

18
entry-18
t

19
entry-19
t

20
entry-20
t



SELECT 20


create or replace function unwatched_rows()
  returns setof my_table.id%type 
 language sql 
as $$ 
    update my_table 
       set is_readed = true
     where not is_readed    -- same as: is_readed = false
    returning id;
$$;

CREATE FUNCTION


insert into my_table( name, is_readed)
     select 'entry-' || to_char(x,'fm999')
          , random() < .4
       from generate_series(21,40) gs(x);

select * 
  from my_table
 where id > 20;

INSERT 0 20




id
name
is_readed


21
entry-21
t

22
entry-22
f

23
entry-23
t

24
entry-24
f

25
entry-25
f

26
entry-26
f

27
entry-27
t

28
entry-28
f

29
entry-29
f

30
entry-30
f

31
entry-31
f

32
entry-32
f

33
entry-33
t

34
entry-34
f

35
entry-35
f

36
entry-36
f

37
entry-37
t

38
entry-38
f

39
entry-39
f

40
entry-40
f



SELECT 20


 select * 
   from unwatched_rows();


unwatched_rows


22

24

25

26

28

29

30

31

32

34

35

36

38

39

40



SELECT 15


   select * 
  from my_table
 where id > 20;


id
name
is_readed


21
entry-21
t

22
entry-22
t

23
entry-23
t

24
entry-24
t

25
entry-25
t

26
entry-26
t

27
entry-27
t

28
entry-28
t

29
entry-29
t

30
entry-30
t

31
entry-31
t

32
entry-32
t

33
entry-33
t

34
entry-34
t

35
entry-35
t

36
entry-36
t

37
entry-37
t

38
entry-38
t

39
entry-39
t

40
entry-40
t



SELECT 20

id	name	is_readed
1	entry-1	f
2	entry-2	t
3	entry-3	t
4	entry-4	t
5	entry-5	t
6	entry-6	f
7	entry-7	t
8	entry-8	t
9	entry-9	f
10	entry-10	t
11	entry-11	t
12	entry-12	t
13	entry-13	t
14	entry-14	f
15	entry-15	f
16	entry-16	t
17	entry-17	t
18	entry-18	f
19	entry-19	f
20	entry-20	f

id	name	is_readed
21	entry-21	t
22	entry-22	f
23	entry-23	t
24	entry-24	f
25	entry-25	f
26	entry-26	f
27	entry-27	t
28	entry-28	f
29	entry-29	f
30	entry-30	f
31	entry-31	f
32	entry-32	f
33	entry-33	t
34	entry-34	f
35	entry-35	f
36	entry-36	f
37	entry-37	t
38	entry-38	f
39	entry-39	f
40	entry-40	f