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 cities (
city_id integer GENERATED BY DEFAULT AS IDENTITY,
language_code character varying(50) NOT NULL,
city character varying(50) NOT NULL
);
INSERT INTO "public"."cities"("city_id","language_code","city")
VALUES
(1,E'en',E'London'),
(1,E'es',E'Londres'),
(1,E'pt',E'Londres'),
(2,E'de',E'Koln'),
(2,E'en',E'Cologne'),
(3,E'it',E'Cologne');
6 rows affected
SELECT city, COUNT(city_id) AS total
FROM cities
GROUP BY city
HAVING COUNT( DISTINCT city_id) > 1;
| city | total |
|---|---|
| Cologne | 2 |
SELECT city_id, language_code, city
FROM cities;
| city_id | language_code | city |
|---|---|---|
| 1 | en | London |
| 1 | es | Londres |
| 1 | pt | Londres |
| 2 | de | Koln |
| 2 | en | Cologne |
| 3 | it | Cologne |
SELECT DISTINCT city_id, city
FROM cities;
| city_id | city |
|---|---|
| 1 | London |
| 1 | Londres |
| 3 | Cologne |
| 2 | Cologne |
| 2 | Koln |
WITH deduped_cities AS (
SELECT DISTINCT city, city_id
FROM cities
ORDER BY city
)
SELECT city, COUNT(city_id) AS total
FROM deduped_cities
GROUP BY city
HAVING COUNT(city_id) > 1;
| city | total |
|---|---|
| Cologne | 2 |