Postgres 15

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

select now(),version(),setseed(.42);

create schema if not exists rowan;
create table if not exists rowan.employees (
  emp_id integer generated by default as identity primary key, 
  name text, 
  salary numeric);
insert into rowan.employees(name,salary) values 
  ('John', 5), ('Bob', 7), ('Dan', 3)
  returning *;

now
version
setseed

2024-04-30 16:35:42.702637+01
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 SCHEMA

CREATE TABLE

emp_id
name
salary

1
John
5

2
Bob
7

3
Dan
3

INSERT 0 3

CREATE FUNCTION cap_salary() RETURNS trigger AS $f$ 
BEGIN
    IF NEW.salary > 70000 THEN
       NEW.salary = 70000;
    END IF;
    RETURN NEW;
END $f$ LANGUAGE plpgsql;

CREATE TRIGGER cap_salary_on_update
    BEFORE UPDATE ON rowan.employees
    FOR EACH ROW EXECUTE FUNCTION cap_salary();

update rowan.employees set salary = 60000 
  where emp_id = 3 returning *;
update rowan.employees set salary = null 
  where emp_id = 3 returning *;
update rowan.employees set salary = 80000 
  where emp_id = 3 returning *;

CREATE FUNCTION

CREATE TRIGGER

emp_id
name
salary

3
Dan
60000

UPDATE 1

emp_id
name
salary

3
Dan
null

UPDATE 1

emp_id
name
salary

3
Dan
70000

UPDATE 1

insert into rowan.employees (name,salary)
select md5(random()::text), (random()*3e5)::numeric(8,2) from generate_series(1,2e5);

INSERT 0 200000

vacuum analyze rowan.employees;

VACUUM

select setseed(.42);
explain analyze verbose 
  update rowan.employees set salary = (random()*1e5)::numeric(8,2)
  returning *;

select * from rowan.employees limit 25;

setseed

SELECT 1

QUERY PLAN

Update on rowan.employees  (cost=0.00..5870.06 rows=200003 width=38) (actual time=0.101..5979.843 rows=200003 loops=1)

  Output: emp_id, name, salary

  ->  Seq Scan on rowan.employees  (cost=0.00..5870.06 rows=200003 width=38) (actual time=0.023..1132.668 rows=200003 loops=1)

        Output: ((random() * '100000'::double precision))::numeric(8,2), ctid

Planning Time: 0.249 ms

Trigger cap_salary_on_update: time=1114.413 calls=200003

Execution Time: 6024.654 ms

EXPLAIN

emp_id
name
salary

1
John
70000

2
Bob
36247.07

3
Dan
70000

4
3d575d5d7264d91f9cf1a6083b64d06f
6432.00

5
febd3f9481884c9fb53f32867d9468d6
9425.70

6
5934085e0ee2a640555c650c07d6ca7e
21386.44

7
70a08605c0842045792b9c1aadd35b0f
49087.56

8
4231a53380066b7bdd5dc97785e67ada
38924.70

9
b5d7db62684ec03324b428795ee2e03b
57549.96

10
052aff3d6737b5cb34c7aa55666caaf0
70000

11
ca369b17d09b1cc371282ac3b3380cbc
56952.42

12
78412cd710fbba67de12c9cb6a21fb20
70000

13
d230e4a099922caa73dc3fa1bf786c58
70000

14
c844055155af2598df4d54f071a3d3e5
70000

15
b5c590282aed12c2da3ac7a78d9df5cb
13082.32

16
8f54f1637f4896ec9830a83d782a528f
19408.57

17
fa651a6d422c3515802e61d67a00f58e
31188.74

18
78302e0c024b50103ac4814f573cf1d8
70000

19
ba1a70411ba22cddb64ed951c23b4297
8205.32

20
bc6ed85d92dbc21dfe0a331f976b047f
70000

21
3bee67bff05b17b5adf3e5a550a6f88a
40966.58

22
4fc87d72b124bf146879031f088062d2
31712.34

23
017e62f5a25a84822973a632d8e9ee05
42310.64

24
2bd3b5355d15cbd2b80dcc44dc6a7b49
46534.59

25
665976c2a0325d242c90c25bc586c7ec
70000

SELECT 25

emp_id	name	salary
1	John	70000
2	Bob	36247.07
3	Dan	70000
4	3d575d5d7264d91f9cf1a6083b64d06f	6432.00
5	febd3f9481884c9fb53f32867d9468d6	9425.70
6	5934085e0ee2a640555c650c07d6ca7e	21386.44
7	70a08605c0842045792b9c1aadd35b0f	49087.56
8	4231a53380066b7bdd5dc97785e67ada	38924.70
9	b5d7db62684ec03324b428795ee2e03b	57549.96
10	052aff3d6737b5cb34c7aa55666caaf0	70000
11	ca369b17d09b1cc371282ac3b3380cbc	56952.42
12	78412cd710fbba67de12c9cb6a21fb20	70000
13	d230e4a099922caa73dc3fa1bf786c58	70000
14	c844055155af2598df4d54f071a3d3e5	70000
15	b5c590282aed12c2da3ac7a78d9df5cb	13082.32
16	8f54f1637f4896ec9830a83d782a528f	19408.57
17	fa651a6d422c3515802e61d67a00f58e	31188.74
18	78302e0c024b50103ac4814f573cf1d8	70000
19	ba1a70411ba22cddb64ed951c23b4297	8205.32
20	bc6ed85d92dbc21dfe0a331f976b047f	70000
21	3bee67bff05b17b5adf3e5a550a6f88a	40966.58
22	4fc87d72b124bf146879031f088062d2	31712.34
23	017e62f5a25a84822973a632d8e9ee05	42310.64
24	2bd3b5355d15cbd2b80dcc44dc6a7b49	46534.59
25	665976c2a0325d242c90c25bc586c7ec	70000