Postgres 12

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 12.0 (Debian 12.0-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 word
(
  word_id SERIAL,
  the_word   VARCHAR (50) NOT NULL, -- I don't like having tables and columns with the same name
  pronunciation VARCHAR (50), -- allow NULLs for now - you can change it later
  CONSTRAINT word_pk PRIMARY KEY (word_id),
  CONSTRAINT word_the_word_uq UNIQUE (the_word)  
    -- no duplicated words!
    -- although this is interesting, what about, say, bear, bear and bear, one is
    -- a large mammal, one is to carry and yet another is to tolerate something.
    -- I leave this up to you! Maybe another field for meaning?
);

INSERT INTO word (the_word, pronunciation)
VALUES
('angry', 'ˈaNGgrē'), ('enraged', 'enˈrājd'), ('furious', NULL), ('irated', NULL), 
('love', NULL), ('affection', NULL), ('passion', NULL), ('devotion', NULL);

-- is 'irated' a word? Irked perhaps?
8 rows affected

SELECT * FROM word;


word_id
the_word
pronunciation


1
angry
ˈaNGgrē

2
enraged
enˈrājd

3
furious
null

4
irated
null

5
love
null

6
affection
null

7
passion
null

8
devotion
null


CREATE TABLE synonym
(
  word_1 INTEGER NOT NULL,
  word_2 INTEGER NOT NULL,
  CONSTRAINT synonyms_not_match_ck CHECK (word_1 != word_2),
  CONSTRAINT synonyn_pk PRIMARY KEY (word_1, word_2),
  CONSTRAINT synonym_w1_fk FOREIGN KEY (word_1) REFERENCES word (word_id),
  CONSTRAINT synonym_w2_fk FOREIGN KEY (word_2) REFERENCES word (word_id)
);

INSERT INTO synonym VALUES 
(1, 2), (1, 3), (1, 4), 
(2, 1), (2, 3), (2, 4),
(3, 1), (3, 2), (3, 4),
(4, 1), (4, 2), (4, 3);

12 rows affected

-- SELECT * FROM synonym;
CREATE TABLE antonym
(
  word_1 INTEGER NOT NULL,
  word_2 INTEGER NOT NULL,
  CONSTRAINT antonyms_not_match_ck CHECK (word_1 != word_2),
  CONSTRAINT antonyn_pk PRIMARY KEY (word_1, word_2),
  CONSTRAINT antonym_w1_fk FOREIGN KEY (word_1) REFERENCES word (word_id),
  CONSTRAINT antonym_w2_fk FOREIGN KEY (word_2) REFERENCES word (word_id)
);
INSERT INTO antonym VALUES 
(5, 6), (5, 7), (5, 8), 
(6, 5), (6, 7), (6, 8),
(7, 5), (7, 6), (7, 8),
(8, 5), (8, 6), (8, 7);
12 rows affected

SELECT * FROM antonym
ORDER BY word_1;


word_1
word_2


5
6

5
7

5
8

6
5

6
7

6
8

7
5

7
6

7
8

8
5

8
6

8
7


-- SELECT all the synonyms of 'enraged'
SELECT w.word_id, w.the_word, w.pronunciation
FROM word w 
WHERE w.word_id IN   
(
  SELECT word_2 FROM synonym 
  WHERE word_1 =
  (
    SELECT word_id FROM word WHERE the_word = 'enraged'
  )
);


word_id
the_word
pronunciation


1
angry
ˈaNGgrē

3
furious
null

4
irated
null


-- Works as a single statement
-- FROM here: https://stackoverflow.com/questions/50604838/deleting-with-self-referential-foreign-key-in-postgres 
WITH cte1 AS
(
  DELETE FROM synonym 
  WHERE word_1 = (SELECT word_id FROM word WHERE the_word = 'enraged')
  OR    word_2 = (SELECT word_id FROM word WHERE the_word = 'enraged')
),
cte2 AS
(
  DELETE FROM word WHERE the_word = 'enraged'
)
SELECT COALESCE(NULL, 'CTE succeeded') AS end_of_query;


end_of_query


CTE succeeded


-- Empty set. PostgreSQL really is the dog's!
-- 0 is impossible for word_1
SELECT word_1 AS x, CAST(word_2 AS TEXT) AS y FROM synonym WHERE word_1 = 2 OR word_2 = 2
UNION
SELECT word_id, the_word FROM word WHERE the_word = 'enraged';
word_id	the_word	pronunciation
1	angry	ˈaNGgrē
2	enraged	enˈrājd
3	furious	null
4	irated	null
5	love	null
6	affection	null
7	passion	null
8	devotion	null