Postgres get the different objects from different group in the same model

Question

I currently have trouble figuring out how to get the different objects that exist in this group by a field to another group by the same field but different value. I'm using sqlalchemy to connect my flask to postgresql I have the ReferenceCity model like this:

class ReferenceCity(Base):
    __tablename__ = 'reference_cities'
    id = Column(Integer, primary_key=True)
    site_id = Column(Integer, ForeignKey('sites.id'), nullable=False)
    name = Column(String)

basically ReferenceCity will have 2 group by site_id .Example site_id =1 and site_id = 2

the name field can be repeat , example: can have 2 ReferenceCity objects with the name field both are Newyork but each has different site_id.

SELECT count(*)
    FROM public.reference_cities
    WHERE site_id=1;
//got 711

SELECT count(*)
    FROM public.reference_cities
    WHERE site_id=2;
//got 709

I executed the count(*) command on pgadmin to check the number of objects from each group of site_id and i got 711 object from site_id = 1 and 709 from site_id = 2 .

So there must be 2 cities that the other group don't have, and i'm trying to find a SELECT query to find those missing 2 cities and i can't find any yet.

Example data:

ReferenceCity table
id name        site_id
1  newyork     1
2  california  1
3  texas       1    
4  newyork     2
5  california  2
6  texas       2
7  detroit     2
8  chicago     2

How do i select get detroit and chicago from site_id=2 Glad if someone can help :)

richyen · Accepted Answer · 2019-11-08T22:55:23.290

You can use an EXCEPT clause to isolate the extra rows:

SELECT name
FROM reference_cities
WHERE site_id = 2

EXCEPT

SELECT name
FROM reference_cities
WHERE site_id = 1;

Output:

postgres=# create table reference_cities (id int, name text, site_id int);
CREATE TABLE
postgres=# insert into reference_cities values (1,'newyork',1),(2,'california',1),(3,'texas',1),(4,'newyork',2),(5,'california',2),(6,'texas',2),(7,'detroit',2),(8,'chicago',2);
INSERT 0 8
postgres=# SELECT name
FROM reference_cities
WHERE site_id = 2
EXCEPT
SELECT name
FROM reference_cities
WHERE site_id = 1;
  name   
---------
 chicago
 detroit
(2 rows)

Disclosure: I work for EnterpriseDB (EDB)

Postgres get the different objects from different group in the same model

1 Answers1