Postgresql – Filtering data that could have more than one category

postgresql

I have a table like the following:

Annotation (
    document,
    term,
    category
)

Where document and term are some ID, while category is an integer.

The couple document – term is not unique, i.e. I could have the same couple with a different category.

document_id_1, term_id_1, category_1
document_id_1, term_id_1, category_2
document_id_1, term_id_1, category_3

I would like to design a query such that it return only the couple document–term for whom there exists a only a row with category = 1.

E.g. in the previous example the couple document_id_1 – term_id_1 is not returned becouse there exist also other two rows, with different values of category.

Can you give me some hints on how to do that?

Best Answer

If I understood you correctly, one way to achieve this is getting those rows where category = 1, then check for the non-existence of non-category1 rows:

SELECT document, term
FROM Annotation ann
WHERE 
    category = 1
    AND NOT EXISTS (
        SELECT 1
        FROM Annotation a
        WHERE 
            a.document = ann.document
            AND a.term = ann.term
            AND category <> 1
    )
;

(Thanks to DavideChicco.it for showing that this was overly complicated).

Related Solutions

PostgreSQL Query – Differentiate Between No Child Rows and No Parent Row in One SELECT

I think I managed to get a satisfactory solution using the following two features:

Subselect bound to a column, which allows me to check if a row exists and (importantly) get a NULL value otherwise (e.g. SELECT (SELECT id FROM a WHERE id = 1) as a_id))
Common Table Expressions

Initial data:

CREATE TABLE people 
(
  id integer not null primary key, 
  name text not null
);

CREATE TABLE thing_types 
(
  id integer not null primary key, 
  name text not null
);

CREATE TABLE things
(
  id integer not null primary key, 
  person_id integer not null references people(id), 
  thing_type_id integer not null references thing_types(id), 
  name text not null
);

INSERT INTO people VALUES (1, 'Bill');
INSERT INTO thing_types VALUES (1, 'game');

INSERT INTO things VALUES (1, 1, 1, 'Duke Nukem');
INSERT INTO things VALUES (2, 1, 1, 'Warcraft 2');

And the query:

WITH v AS (
  SELECT (SELECT id FROM people WHERE id=<person_id_param>) AS person_id, 
         (SELECT id FROM thing_types WHERE id=<thing_type_param>) AS thing_type_id
)
SELECT v.person_id, v.thing_type_id, things.name 
FROM 
  v LEFT JOIN things 
    ON v.person_id = things.person_id AND v.thing_type_id = things.thing_type_id

This query will always return at least one row. In case if both parent table ids are valid and there are some records, I get

person_id  thing_type_id  name
-------------------------------------
        1              1   Duke Nukem
        1              1   Warcraft 2

If either person_id or thing_type_id are invalid, I get one row where name is NULL and either person_id or thing_type_id is NULL:

person_id  thing_type_id  name
-------------------------------------
     NULL              1         NULL

If both person_id and thing_type_id are valid but there are no records in things, I get one row where both person_id and thing_type_id are not NULL, but the name is NULL:

person_id  thing_type_id  name
-------------------------------------
        1              1         NULL

Since I have a NOT NULL constraint on things.name, I know that this case can only mean that there are no matching records in things.

PostgreSQL – How to Compare xmin and txid_current() After Transaction ID Wraparound

You can strip the added epoch to match the value in xmin, i.e. extract the 4-byte integer from the bigint. Since xmin is type xid and not (signed!) integer, we compare the text representation instead:

SELECT * FROM test
WHERE  xmin::text = (txid_current() % (2^32)::bigint)::text;

Detailed explanation:

How to view tuples changed in a PostgreSQL transaction?

Best Answer

Related Solutions

PostgreSQL Query – Differentiate Between No Child Rows and No Parent Row in One SELECT

PostgreSQL – How to Compare xmin and txid_current() After Transaction ID Wraparound

Related Question