Postgres 11

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?.

select version();


version


PostgreSQL 11.3 (Debian 11.3-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit


create table eavtable (
id integer PRIMARY KEY,
user_id integer,
attribute_key varchar(99),
attribute_value varchar(99)
);

insert into eavtable values( 1  , 1 , 'first_name' , 'Jesus');
insert into eavtable values( 2  , 1 , 'last_name'  , 'Christ');
insert into eavtable values( 3  , 1 , 'Email'      , 'oldEmail1@test.com');
insert into eavtable values( 4  , 1 , 'newEmail'   , 'newEmail1@test.com');
insert into eavtable values( 5  , 2 , 'first_name' , 'Ram');
insert into eavtable values( 6  , 2 , 'last_name'  , 'Krish');
insert into eavtable values( 7  , 2 , 'Email'      , 'oldEmail2@test.com');
insert into eavtable values( 8  , 2 , 'newEmail'   , 'newEmail2@test.com');
insert into eavtable values( 9  , 3 , 'first_name' , 'Muhammad');
insert into eavtable values( 10 , 3 , 'last_name'  , 'Nab');
insert into eavtable values( 11 , 3 , 'Email'      , 'oldEmail3@test.com');
insert into eavtable values( 12 , 3 , 'newEmail'   , null);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select * 
from eavtable;


id
user_id
attribute_key
attribute_value


1
1
first_name
Jesus

2
1
last_name
Christ

3
1
Email
oldEmail1@test.com

4
1
newEmail
newEmail1@test.com

5
2
first_name
Ram

6
2
last_name
Krish

7
2
Email
oldEmail2@test.com

8
2
newEmail
newEmail2@test.com

9
3
first_name
Muhammad

10
3
last_name
Nab

11
3
Email
oldEmail3@test.com

12
3
newEmail
null


with updata as (
select user_id,attribute_value 
from eavtable
where attribute_key='newEmail'
)
UPDATE eavtable
SET attribute_value = updata.attribute_value
FROM updata 
WHERE eavtable.user_id = updata.user_id 
and eavtable.attribute_key = 'Email';
3 rows affected

select * 
from eavtable;


id
user_id
attribute_key
attribute_value


1
1
first_name
Jesus

2
1
last_name
Christ

4
1
newEmail
newEmail1@test.com

5
2
first_name
Ram

6
2
last_name
Krish

8
2
newEmail
newEmail2@test.com

9
3
first_name
Muhammad

10
3
last_name
Nab

12
3
newEmail
null

3
1
Email
newEmail1@test.com

7
2
Email
newEmail2@test.com

11
3
Email
null


delete from eavtable where attribute_key = 'newEmail';
3 rows affected

select * 
from eavtable;


id
user_id
attribute_key
attribute_value


1
1
first_name
Jesus

2
1
last_name
Christ

5
2
first_name
Ram

6
2
last_name
Krish

9
3
first_name
Muhammad

10
3
last_name
Nab

3
1
Email
newEmail1@test.com

7
2
Email
newEmail2@test.com

11
3
Email
null

id	user_id	attribute_key	attribute_value
1	1	first_name	Jesus
2	1	last_name	Christ
3	1	Email	oldEmail1@test.com
4	1	newEmail	newEmail1@test.com
5	2	first_name	Ram
6	2	last_name	Krish
7	2	Email	oldEmail2@test.com
8	2	newEmail	newEmail2@test.com
9	3	first_name	Muhammad
10	3	last_name	Nab
11	3	Email	oldEmail3@test.com
12	3	newEmail	null