testbaking=> testbaking=> drop table if exists tmp ; NOTICE: table "tmp" does not exist, skipping DROP TABLE testbaking=> drop table if exists results ; NOTICE: table "results" does not exist, skipping DROP TABLE testbaking=> drop table if exists bakers; NOTICE: table "bakers" does not exist, skipping DROP TABLE testbaking=> drop table if exists episodes; NOTICE: table "episodes" does not exist, skipping DROP TABLE testbaking=> testbaking=> create table bakers( testbaking(> baker varchar(10) primary key testbaking(> , fullname varchar(100) testbaking(> , age int not null testbaking(> , occupation varchar(100) testbaking(> , hometown varchar(100) testbaking(> ); CREATE TABLE testbaking=> testbaking=> create table episodes ( testbaking(> id int primary key testbaking(> , title varchar(100) testbaking(> , firstaired date testbaking(> , viewers7day float check(viewers7day > 0) testbaking(> , signature varchar(100) testbaking(> , technical varchar(100) testbaking(> , showstopper varchar(100) testbaking(> ) ; CREATE TABLE testbaking=> testbaking=> testbaking=> create table results ( testbaking(> episodeid int testbaking(> , baker varchar(100) testbaking(> , result varchar(20) testbaking(> , primary key (episodeid, baker) testbaking(> , foreign key (episodeid) references episodes(id) testbaking(> on delete cascade testbaking(> on update cascade testbaking(> , foreign key (baker) references bakers(baker) testbaking(> on delete cascade testbaking(> on update cascade testbaking(> ); CREATE TABLE testbaking=> testbaking=> create table tmp ( testbaking(> id int primary key testbaking(> , episodeid int testbaking(> , baker varchar(100) testbaking(> , foreign key (episodeid, baker) references results(episodeid, baker) testbaking(> on delete cascade testbaking(> on update set null testbaking(> ) ; CREATE TABLE testbaking=> testbaking=> testbaking=> insert into bakers values('Antony','Antony Amourdoux',30,'Banker','London'); INSERT 0 1 testbaking=> insert into bakers values('Briony','Briony Williams',33 ,'Full-time parent','Bristol'); INSERT 0 1 testbaking=> insert into bakers values('Dan','Dan Beasley-Harling',36 ,'Full-time parent','London'); INSERT 0 1 testbaking=> insert into bakers values('Imelda','Imelda McCarron',33 ,'Countryside recreation officer','County Tyrone'); INSERT 0 1 testbaking=> insert into bakers values('Jon','Jon Jenkins',47 ,'Blood courier','Newport'); INSERT 0 1 testbaking=> insert into bakers values('Karen','Karen Wright',60 ,'In-store sampling assistant','Wakefield'); INSERT 0 1 testbaking=> insert into bakers values('Kim-Joy','Kim-Joy Hewlett',27 ,'Mental health specialist','Leeds'); INSERT 0 1 testbaking=> insert into bakers values('Luke','Luke Thompson',30 ,'Civil servant/house and techno DJ','Sheffield'); INSERT 0 1 testbaking=> insert into bakers values('Manon','Manon Lagrève',26 ,'Software project manager','London'); INSERT 0 1 testbaking=> insert into bakers values('Rahul','Rahul Mandal',30 ,'Research scientist','Rotherham'); INSERT 0 1 testbaking=> insert into bakers values('Ruby','Ruby Bhogal',29 ,'Project manager','London'); INSERT 0 1 testbaking=> insert into bakers values('Terry','Terry Hartill',56 ,'Retired air steward','West Midlands'); INSERT 0 1 testbaking=> testbaking=> testbaking=> insert into episodes values(1,'Biscuits', date '2018-8-28', 9.55,'24 Regional Biscuits','8 Wagon Wheels','3D Biscuit Self-Portrait'); INSERT 0 1 testbaking=> insert into episodes values(2,'Cakes', date '2018-9-4', 9.31,'16 Traybakes','Le Gâteau Vert','Chocolate Collar Cake'); INSERT 0 1 testbaking=> insert into episodes values(3,'Bread', date '2018-9-11', 8.91,'12 Chelsea Buns','8 Non-Yeasted Garlic Naan Breads','Korovai'); INSERT 0 1 testbaking=> insert into episodes values(4,'Desserts', date '2018-9-18', 8.88,'Meringue Roulade','Raspberry Blancmange with 12 Langues du Chat biscuits','Melting Chocolate Ball Dessert'); INSERT 0 1 testbaking=> insert into episodes values(5,'Spice', date '2018-9-25', 8.67,'Ginger Cake','12 Ma''amoul','Spiced Biscuit Chandelier'); INSERT 0 1 testbaking=> insert into episodes values(6,'Pastry', date '2018-10-2', 9.30,'12 Samosas','6 Puits D''amour','Shaped Banquet Pie'); INSERT 0 1 testbaking=> insert into episodes values(7,'Vegan', date '2018-10-9', 9.54,'8 Savoury Vegan Tartlets','Vegan Tropical Fruit Pavlova','Vegan Celebratory Cake'); INSERT 0 1 testbaking=> insert into episodes values(8,'Danish', date '2018-10-16', 9.69,'2 Smørrebrød','14 Æbleskiver','Kagemand/Kagekone'); INSERT 0 1 testbaking=> insert into episodes values(9,'Pâtisserie (Semi-final)', date '2018-10-23', 9.50,'24 Madeleines','Torta Setteveli','Parisian Pâtisserie Window'); INSERT 0 1 testbaking=> insert into episodes values(10,'Final', date '2018-10-30', 10.34,'12 Iced Doughnuts','6 Campfire Pita Breads','Landscape Dessert'); INSERT 0 1 testbaking=> testbaking=> insert into results values(1,'Manon','star baker') ; INSERT 0 1 testbaking=> insert into results values(1,'Imelda','eliminated') ; INSERT 0 1 testbaking=> insert into results values(2,'Rahul','star baker') ; INSERT 0 1 testbaking=> insert into results values(2,'Luke','eliminated') ; INSERT 0 1 testbaking=> insert into results values(3,'Rahul','star baker') ; INSERT 0 1 testbaking=> insert into results values(3,'Antony','eliminated') ; INSERT 0 1 testbaking=> insert into results values(4,'Dan','star baker') ; INSERT 0 1 testbaking=> insert into results values(5,'Terry','eliminated') ; INSERT 0 1 testbaking=> insert into results values(5,'Kim-Joy','star baker') ; INSERT 0 1 testbaking=> insert into results values(5,'Karen','eliminated') ; INSERT 0 1 testbaking=> insert into results values(6,'Briony','star baker') ; INSERT 0 1 testbaking=> insert into results values(6,'Dan','eliminated') ; INSERT 0 1 testbaking=> insert into results values(7,'Kim-Joy','star baker') ; INSERT 0 1 testbaking=> insert into results values(7,'Jon','eliminated') ; INSERT 0 1 testbaking=> insert into results values(8,'Ruby','star baker') ; INSERT 0 1 testbaking=> insert into results values(8,'Manon','eliminated') ; INSERT 0 1 testbaking=> insert into results values(9,'Ruby','star baker') ; INSERT 0 1 testbaking=> insert into results values(9,'Briony','eliminated') ; INSERT 0 1 testbaking=> testbaking=> insert into tmp values(1, 5, 'Kim-Joy'); INSERT 0 1 testbaking=> insert into tmp values(2, 5, 'Kim-Joy'); INSERT 0 1 testbaking=> insert into tmp values(3, 8, 'Manon'); INSERT 0 1 testbaking=> testbaking=> \d List of relations Schema | Name | Type | Owner --------+----------+-------+--------- public | bakers | table | kkuzmin public | episodes | table | kkuzmin public | results | table | kkuzmin public | tmp | table | kkuzmin (4 rows) testbaking=> select * from bakers; baker | fullname | age | occupation | hometown ---------+---------------------+-----+-----------------------------------+--------------- Antony | Antony Amourdoux | 30 | Banker | London Briony | Briony Williams | 33 | Full-time parent | Bristol Dan | Dan Beasley-Harling | 36 | Full-time parent | London Imelda | Imelda McCarron | 33 | Countryside recreation officer | County Tyrone Jon | Jon Jenkins | 47 | Blood courier | Newport Karen | Karen Wright | 60 | In-store sampling assistant | Wakefield Kim-Joy | Kim-Joy Hewlett | 27 | Mental health specialist | Leeds Luke | Luke Thompson | 30 | Civil servant/house and techno DJ | Sheffield Manon | Manon Lagrève | 26 | Software project manager | London Rahul | Rahul Mandal | 30 | Research scientist | Rotherham Ruby | Ruby Bhogal | 29 | Project manager | London Terry | Terry Hartill | 56 | Retired air steward | West Midlands (12 rows) testbaking=> select * from episodes; id | title | firstaired | viewers7day | signature | technical | showstopper ----+-------------------------+------------+-------------+--------------------------+-------------------------------------------------------+-------------------------------- 1 | Biscuits | 2018-08-28 | 9.55 | 24 Regional Biscuits | 8 Wagon Wheels | 3D Biscuit Self-Portrait 2 | Cakes | 2018-09-04 | 9.31 | 16 Traybakes | Le Gâteau Vert | Chocolate Collar Cake 3 | Bread | 2018-09-11 | 8.91 | 12 Chelsea Buns | 8 Non-Yeasted Garlic Naan Breads | Korovai 4 | Desserts | 2018-09-18 | 8.88 | Meringue Roulade | Raspberry Blancmange with 12 Langues du Chat biscuits | Melting Chocolate Ball Dessert 5 | Spice | 2018-09-25 | 8.67 | Ginger Cake | 12 Ma'amoul | Spiced Biscuit Chandelier 6 | Pastry | 2018-10-02 | 9.3 | 12 Samosas | 6 Puits D'amour | Shaped Banquet Pie 7 | Vegan | 2018-10-09 | 9.54 | 8 Savoury Vegan Tartlets | Vegan Tropical Fruit Pavlova | Vegan Celebratory Cake 8 | Danish | 2018-10-16 | 9.69 | 2 Smørrebrød | 14 Æbleskiver | Kagemand/Kagekone 9 | Pâtisserie (Semi-final) | 2018-10-23 | 9.5 | 24 Madeleines | Torta Setteveli | Parisian Pâtisserie Window 10 | Final | 2018-10-30 | 10.34 | 12 Iced Doughnuts | 6 Campfire Pita Breads | Landscape Dessert (10 rows) testbaking=> select * from results; episodeid | baker | result -----------+---------+------------ 1 | Manon | star baker 1 | Imelda | eliminated 2 | Rahul | star baker 2 | Luke | eliminated 3 | Rahul | star baker 3 | Antony | eliminated 4 | Dan | star baker 5 | Terry | eliminated 5 | Kim-Joy | star baker 5 | Karen | eliminated 6 | Briony | star baker 6 | Dan | eliminated 7 | Kim-Joy | star baker 7 | Jon | eliminated 8 | Ruby | star baker 8 | Manon | eliminated 9 | Ruby | star baker 9 | Briony | eliminated (18 rows) testbaking=> select * from tmp; id | episodeid | baker ----+-----------+--------- 1 | 5 | Kim-Joy 2 | 5 | Kim-Joy 3 | 8 | Manon (3 rows) testbaking=> testbaking=> DELETE FROM bakers WHERE baker='Jon'; DELETE 1 testbaking=> SELECT * FROM bakers; baker | fullname | age | occupation | hometown ---------+---------------------+-----+-----------------------------------+--------------- Antony | Antony Amourdoux | 30 | Banker | London Briony | Briony Williams | 33 | Full-time parent | Bristol Dan | Dan Beasley-Harling | 36 | Full-time parent | London Imelda | Imelda McCarron | 33 | Countryside recreation officer | County Tyrone Karen | Karen Wright | 60 | In-store sampling assistant | Wakefield Kim-Joy | Kim-Joy Hewlett | 27 | Mental health specialist | Leeds Luke | Luke Thompson | 30 | Civil servant/house and techno DJ | Sheffield Manon | Manon Lagrève | 26 | Software project manager | London Rahul | Rahul Mandal | 30 | Research scientist | Rotherham Ruby | Ruby Bhogal | 29 | Project manager | London Terry | Terry Hartill | 56 | Retired air steward | West Midlands (11 rows) testbaking=> SELECT * FROM results; episodeid | baker | result -----------+---------+------------ 1 | Manon | star baker 1 | Imelda | eliminated 2 | Rahul | star baker 2 | Luke | eliminated 3 | Rahul | star baker 3 | Antony | eliminated 4 | Dan | star baker 5 | Terry | eliminated 5 | Kim-Joy | star baker 5 | Karen | eliminated 6 | Briony | star baker 6 | Dan | eliminated 7 | Kim-Joy | star baker 8 | Ruby | star baker 8 | Manon | eliminated 9 | Ruby | star baker 9 | Briony | eliminated (17 rows) testbaking=> SELECT * FROM tmp; id | episodeid | baker ----+-----------+--------- 1 | 5 | Kim-Joy 2 | 5 | Kim-Joy 3 | 8 | Manon (3 rows) testbaking=> DELETE FROM bakers WHERE baker='Manon; testbaking'> '; DELETE 0 testbaking=> DELETE FROM bakers WHERE baker='Manon'; DELETE 1 testbaking=> SELECT * FROM bakers; baker | fullname | age | occupation | hometown ---------+---------------------+-----+-----------------------------------+--------------- Antony | Antony Amourdoux | 30 | Banker | London Briony | Briony Williams | 33 | Full-time parent | Bristol Dan | Dan Beasley-Harling | 36 | Full-time parent | London Imelda | Imelda McCarron | 33 | Countryside recreation officer | County Tyrone Karen | Karen Wright | 60 | In-store sampling assistant | Wakefield Kim-Joy | Kim-Joy Hewlett | 27 | Mental health specialist | Leeds Luke | Luke Thompson | 30 | Civil servant/house and techno DJ | Sheffield Rahul | Rahul Mandal | 30 | Research scientist | Rotherham Ruby | Ruby Bhogal | 29 | Project manager | London Terry | Terry Hartill | 56 | Retired air steward | West Midlands (10 rows) testbaking=> SELECT * FROM results; episodeid | baker | result -----------+---------+------------ 1 | Imelda | eliminated 2 | Rahul | star baker 2 | Luke | eliminated 3 | Rahul | star baker 3 | Antony | eliminated 4 | Dan | star baker 5 | Terry | eliminated 5 | Kim-Joy | star baker 5 | Karen | eliminated 6 | Briony | star baker 6 | Dan | eliminated 7 | Kim-Joy | star baker 8 | Ruby | star baker 9 | Ruby | star baker 9 | Briony | eliminated (15 rows) testbaking=> SELECT * FROM tmp; id | episodeid | baker ----+-----------+--------- 1 | 5 | Kim-Joy 2 | 5 | Kim-Joy (2 rows) testbaking=> UPDATE tmp SET baker = 'Kim Joy' WHERE baker = 'Kim-Joy'; ERROR: insert or update on table "tmp" violates foreign key constraint "tmp_episodeid_baker_fkey" DETAIL: Key (episodeid, baker)=(5, Kim Joy) is not present in table "results". testbaking=> UPDATE bakers SET baker = 'Kim Joy' ; ERROR: duplicate key value violates unique constraint "bakers_pkey" DETAIL: Key (baker)=(Kim Joy) already exists. testbaking=> SELECT * FROM bakers; baker | fullname | age | occupation | hometown ---------+---------------------+-----+-----------------------------------+--------------- Antony | Antony Amourdoux | 30 | Banker | London Briony | Briony Williams | 33 | Full-time parent | Bristol Dan | Dan Beasley-Harling | 36 | Full-time parent | London Imelda | Imelda McCarron | 33 | Countryside recreation officer | County Tyrone Karen | Karen Wright | 60 | In-store sampling assistant | Wakefield Kim-Joy | Kim-Joy Hewlett | 27 | Mental health specialist | Leeds Luke | Luke Thompson | 30 | Civil servant/house and techno DJ | Sheffield Rahul | Rahul Mandal | 30 | Research scientist | Rotherham Ruby | Ruby Bhogal | 29 | Project manager | London Terry | Terry Hartill | 56 | Retired air steward | West Midlands (10 rows) testbaking=> UPDATE bakers SET baker = 'Kim Joy' WHERE baker = 'Kim-Joy'; UPDATE 1 testbaking=> SELECT * FROM bakers; baker | fullname | age | occupation | hometown ---------+---------------------+-----+-----------------------------------+--------------- Antony | Antony Amourdoux | 30 | Banker | London Briony | Briony Williams | 33 | Full-time parent | Bristol Dan | Dan Beasley-Harling | 36 | Full-time parent | London Imelda | Imelda McCarron | 33 | Countryside recreation officer | County Tyrone Karen | Karen Wright | 60 | In-store sampling assistant | Wakefield Luke | Luke Thompson | 30 | Civil servant/house and techno DJ | Sheffield Rahul | Rahul Mandal | 30 | Research scientist | Rotherham Ruby | Ruby Bhogal | 29 | Project manager | London Terry | Terry Hartill | 56 | Retired air steward | West Midlands Kim Joy | Kim-Joy Hewlett | 27 | Mental health specialist | Leeds (10 rows) testbaking=> SELECT * FROM results; episodeid | baker | result -----------+---------+------------ 1 | Imelda | eliminated 2 | Rahul | star baker 2 | Luke | eliminated 3 | Rahul | star baker 3 | Antony | eliminated 4 | Dan | star baker 5 | Terry | eliminated 5 | Karen | eliminated 6 | Briony | star baker 6 | Dan | eliminated 8 | Ruby | star baker 9 | Ruby | star baker 9 | Briony | eliminated 5 | Kim Joy | star baker 7 | Kim Joy | star baker (15 rows) testbaking=> SELECT * FROM tmp; id | episodeid | baker ----+-----------+------- 1 | | 2 | | (2 rows) testbaking=>