Postgres 16

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

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create database kongdb;

CREATE DATABASE


DROP USER IF EXISTS konguser;
CREATE USER konguser WITH ENCRYPTED PASSWORD 'kongpassword';

DROP ROLE



CREATE ROLE


SELECT * FROM pg_user WHERE  usename = 'konguser';
DROP USER IF EXISTS konguser;


usename
usesysid
usecreatedb
usesuper
userepl
usebypassrls
passwd
valuntil
useconfig


konguser
16385
f
f
f
f
********
null
null



SELECT 1



DROP ROLE


DO $f$ BEGIN
EXECUTE (SELECT 'CREATE USER konguser WITH ENCRYPTED PASSWORD ''kongpassword''; ' 
         WHERE NOT EXISTS (SELECT FROM pg_user WHERE usename = 'konguser'));
END $f$;

DO


SELECT * FROM pg_user WHERE  usename = 'konguser';
DROP USER IF EXISTS konguser;


usename
usesysid
usecreatedb
usesuper
userepl
usebypassrls
passwd
valuntil
useconfig


konguser
16386
f
f
f
f
********
null
null



SELECT 1



DROP ROLE


DO $f$ BEGIN
EXECUTE (SELECT $q$CREATE USER konguser WITH ENCRYPTED PASSWORD 'kongpassword'; $q$ 
         WHERE NOT EXISTS (SELECT FROM pg_user WHERE usename = 'konguser'));
END $f$;

DO


SELECT * FROM pg_user WHERE  usename = 'konguser';
DROP USER IF EXISTS konguser;


usename
usesysid
usecreatedb
usesuper
userepl
usebypassrls
passwd
valuntil
useconfig


konguser
16387
f
f
f
f
********
null
null



SELECT 1



DROP ROLE


DO
$$
BEGIN
   IF NOT EXISTS ( SELECT FROM pg_user
                   WHERE  usename = 'konguser') THEN

      CREATE USER konguser WITH ENCRYPTED PASSWORD 'kongpassword';
      GRANT ALL PRIVILEGES ON DATABASE kongdb TO konguser;
      --echo "test database & user successfully created"
      RAISE NOTICE 'test user successfully created';
   ELSE
      RAISE NOTICE 'test user already exists';
   END IF;
END
$$;

DO


SELECT * FROM pg_user WHERE  usename = 'konguser';
REVOKE ALL PRIVILEGES ON DATABASE kongdb from konguser;
drop user konguser;


usename
usesysid
usecreatedb
usesuper
userepl
usebypassrls
passwd
valuntil
useconfig


konguser
16388
f
f
f
f
********
null
null



SELECT 1



REVOKE



DROP ROLE