Postgres 10

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 TABLE route
( 
  route_id    INTEGER NOT NULL,
  origin      VARCHAR(2) NOT NULL,
  destination VARCHAR(2) NOT NULL, 
  -- CHECK (destination != origin) - can do it this way (remove comma)
  CONSTRAINT routes_orig_dest_distinct_ck CHECK (destination != origin)
  -- Better as it gives a meaningful name to the CONSTRAINT
  -- You can check this by swapping the CONSTRAINTs
);
 INSERT INTO route
   ( route_id, origin, destination)
 VALUES 
   ( 1, 'A', 'B' ),
   ( 2, 'C', 'D' ),
   ( 3, 'B', 'A' ),
   ( 4, 'C', 'A' ),
   ( 5, 'D', 'C' );
5 rows affected

INSERT INTO route VALUES    ( 6, 'Z', 'Z' ); 
-- will fail because of CONSTRAINT,
-- notice informative name!
ERROR:  new row for relation "route" violates check constraint "routes_orig_dest_distinct_ck"
DETAIL:  Failing row contains (6, Z, Z).


SELECT * FROM route;


route_id
origin
destination


1
A
B

2
C
D

3
B
A

4
C
A

5
D
C


SELECT point_1, point_2, COUNT(*)
FROM
(
  SELECT 
    CASE 
      WHEN origin < destination THEN origin ELSE destination
    END AS point_1,
    CASE
      WHEN destination > origin THEN destination ELSE origin
    END as point_2
  FROM
    route
) tab
GROUP BY point_1, point_2
ORDER BY point_1, point_2;


point_1
point_2
count


A
B
2

A
C
1

C
D
2


WITH the_route AS
(
  SELECT 
    CASE 
      WHEN origin < destination THEN origin ELSE destination
    END AS point_1,
    CASE
      WHEN destination > origin THEN destination ELSE origin
    END as point_2
  FROM
    route
)
SELECT point_1, point_2, COUNT(*) 
FROM 
  the_route
GROUP BY point_1, point_2
ORDER BY point_1, point_2;


point_1
point_2
count


A
B
2

A
C
1

C
D
2