PostgreSQL – How to Select Specific Items from Table A Appearing with All Items of a Column in Table B

postgresqlqueryrelational-divisionsubquery

I have 2 tables with the following schemas:

t1(id, type) where id and type are the primary key

t2(type, rate) where type is the primary key

I want to find all id's which appear with all types from t2.

For example given the tables:

t1:

 id | type
 ---------
  1 | a 
  1 | b
  1 | c
  2 | a
  2 | b

and t2:

 type | rate
 -----------
    a | 100
    b | 100
    c | 200

We can see that there're 3 unique types in t2 and only id 2 from t1 appears with all of them.

I figured out a way to get the id's by counting the number of unique types and then grouping t1 by id's and selecting only those whose count equals of the total number of types from t2.

But I was wondering if there're other logical constructs like in/not in, exists or Cartesian product which can also be used here with more elegance?

Best Answer

I don't know postgresql but shouldn't the following work?

select id
from t1
join t2 on t1.type = t2.type
group by id
having count(1) = (select count(1) from t2)

You are selecting the ID values.

The join means the type value on t1 must exist in t2. (I note you did not explicitly state there is a foreign key relationship there - hence this join, to avoid the case where, say, you have three distinct type values in t1 for a given ID value, but they are not the same three distinct type values in t2).

We use group by for two reasons: first, it means each ID will receive only 1 row in the output, but more importantly, this allows us to use the aggregate count function.

The count function essentially counts the number of rows for the given ID value (given the group by clause) and we are saying that number of rows must match the number of rows in t2. We can leave it as simply as that because type - the column we are interested in - in t2, is a key column and so will be unique across each row.

Wondering if I've missed something, in my postgresql ignorance?

Edit: Seems to work fine to me. I've used the setup SQL from another answer here to create a SQL Fiddle showing my solution against your question data.

View the SQL Fiddle

Direct reference of sequence and using concat

One possible solution is to reference the seqence in insert statement directly and prepend your node-id. A similar question including answer you can find here: https://stackoverflow.com/a/17925601/4206293

Using a UUID

Another possible solution is, if you don't need you node-id in the primary-key field, you can use the uuid-ossp extension which provides the type uuid and the functions to generate uuids: http://www.postgresql.org/docs/9.4/static/uuid-ossp.html

Use a trigger for these solutions

For both solutions: you can use a trigger to set the primary key.

Example:

-- table
CREATE TABLE test(
   id character varying(10) NOT NULL,
  "name" character varying,
   CONSTRAINT idx_pk PRIMARY KEY (id)
);

-- seqence
CREATE SEQUENCE test_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- Create Function
CREATE OR REPLACE FUNCTION insert_trigger() 
RETURNS TRIGGER    
AS $$
BEGIN
   NEW.id := '100' || nextval('test_seq')::TEXT;
   RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

-- add Trigger
CREATE TRIGGER insert_table_trigger
BEFORE INSERT ON pkTable
FOR EACH ROW EXECUTE PROCEDURE insert_trigger();

If you now insert a value in the table

INSERT INTO test (name) VALUES ('test text');

you get your primary key '1001'

Using MINVALUE and MAXVALUE

Another way is you use the MINVALUE and MAXVALUE of SEQUENCE to define an numeric space:

CREATE SEQUENCE node100_seq
  INCREMENT 1
  MINVALUE 100000000000
  MAXVALUE 100999999999
  START 100000000000
  CACHE 1;

CREATE SEQUENCE node200_seq
  INCREMENT 1
  MINVALUE 200000000000
  MAXVALUE 200999999999
  START 200000000000
  CACHE 1;

Best Answer

Related Solutions

Mysql – Query from database

Sql-server – Non-sequential Primary Key performance in PostgreSQL 9.3

Direct reference of sequence and using concat

Using a UUID

Use a trigger for these solutions

Using MINVALUE and MAXVALUE

Related Question