1

I have a working Excel-based method for creating a truth table from two vectors exported from a PostgreSQL database. The process takes about 4 hours to complete due to a large number of VLOOKUP and COUNTIFS operations, so I am looking for a method of implementing this as a view directly in the database.

The input vectors are produced from two existing views in my database, which have no foreign keys.

To make this question and solution as generic as possible, I've created a parallel problem using two simple tables with sample data to cover all possible cases:

CREATE TABLE group_membership
(
  member character varying(6) NOT NULL,
  group_name character varying(64) NOT NULL
);

INSERT INTO group_membership VALUES ('000001','A');
INSERT INTO group_membership VALUES ('000001','B');
INSERT INTO group_membership VALUES ('000001','B'); -- A value may occur more than once.
INSERT INTO group_membership VALUES ('000001','D'); -- A value may not necessarily have a corresponding row in the group table.
INSERT INTO group_membership VALUES ('000001','D');

INSERT INTO group_membership VALUES ('000002','B');
INSERT INTO group_membership VALUES ('000002','C');
INSERT INTO group_membership VALUES ('000002','E');

INSERT INTO group_membership VALUES ('000003','A');
INSERT INTO group_membership VALUES ('000003','C');

INSERT INTO group_membership VALUES ('000004','D');
INSERT INTO group_membership VALUES ('000004','E');

CREATE TABLE groups
(
  name character varying(64) NOT NULL
);

INSERT INTO groups VALUES ('A');
INSERT INTO groups VALUES ('B');
INSERT INTO groups VALUES ('C');
INSERT INTO groups VALUES ('C'); -- A value may occur more than once.
INSERT INTO groups VALUES ('Z');
-- 'D' and 'E' not present in this table

There are no relations between these two tables.

I am trying to construct a view that will create a binary truth table (matrix) like this:

member A B C Z
000001 t t f f
000002 f t t f
000003 t f t f
000004 f f f f

Where the first column are the distinct members from the group_membership table, and the subsequent columns show the presence or absence of the member in only the groups defined in the group table. The resulting table should be boolean only (TRUE if the member appears in a tuple with the group at least once, FALSE otherwise).

For instance, some specific "cells" in the above table would conform to the following:

SELECT COUNT(*) > 0 AS value FROM group_membership WHERE group_name='A' AND member='000001';
 value
-------
 t
(1 row)

SELECT COUNT(*) > 0 AS value FROM group_membership WHERE group_name='Z' AND member='000001';
 value
-------
 f
(1 row)

And to create the second column (the 'A' column):

SELECT COUNT(*) > 0 AS A FROM group_membership WHERE group_name='A' AND member='000001'
 UNION ALL
SELECT COUNT(*) > 0 AS A FROM group_membership WHERE group_name='A' AND member='000002'
 UNION ALL
SELECT COUNT(*) > 0 AS A FROM group_membership WHERE group_name='A' AND member='000003'
 UNION ALL
SELECT COUNT(*) > 0 AS A FROM group_membership WHERE group_name='A' AND member='000004'
;

Even better would be something like this (1 and 0 instead of TRUE and FALSE):

member A B C Z
000001 1 1 0 0
000002 0 1 1 0
000003 1 0 1 0
000004 0 0 0 0

Where the query for each of the individual "cells" could be of the form:

SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END FROM group_membership WHERE group_name='A' AND member='000001';

My group_membership table has about 50,000 rows, and my group table has about 200 rows.


Note: If you do something like the following to ignore groups that are not in common among the two tables, you will end up eliminating rows like 000004 in the above sample result sets, which is not what I am looking for (member 000004 and group Z should be present in the result set):

SELECT * FROM group_membership WHERE group_name IN (SELECT DISTINCT(name) FROM groups);

As a first stab at solving this problem, I am looking into creating a FUNCTION that relies on a recursive JOIN over the group table to construct the result table.

Update: A FUNCTION requires a RETURNS TABLE definition, which looks like it not be a workable solution, given the variable number of columns in the result set. Some additional ideas I have are to create a function that performs a series of UNIONs over one dimension, and is then wrapped with a view that performs a UNION over a crosstab() of the results over SELECT DISTINCT(name) FROM groups ORDER BY name ASC;

  • This is a dynamic pivot problem, and as such must be broken down into two steps, one to generate the SQL query or table or view and the second to execute it. Does your intended use allow that? – Daniel Vérité Oct 13 '18 at 12:09
  • @DanielVérité Yes, I'm open to the idea of creating an intermediate table or view as part of a two-step approach. – vallismortis Oct 13 '18 at 13:13
3

It seems you basically want this, without writing this:

SELECT member
      ,bool_or(group_name='A')::int as "A"
      ,bool_or(group_name='B')::int as "B"
      ,bool_or(group_name='C')::int as "C"
      ,bool_or(group_name='Z')::int as "Z" 
  FROM group_membership
  GROUP BY member
  ORDER BY member;

Postgres isn't structured to make dynamic pivot tables easy.

 CREATE or replace FUNCTION prepare() returns void language plpgsql as $F$
 BEGIN
     execute (
          WITH g AS ( select name from groups group by name order by name)
          SELECT
          $$
          create or replace function pg_temp.pivot () 
              RETURNS TABLE ( member text,
          $$ || string_agg( quote_ident( name )||' INT',',') || $$ 
          ) LANGUAGE SQL AS $X$
              SELECT member
    $$ || string_agg( ',bool_or(group_name=' || quote_literal( name ) ||
    ')::int AS '|| quote_ident( name ),e'\n') || $$
                 FROM group_membership
                 GROUP BY member
                 ORDER BY member; $X$; $$
    FROM g ) ;
END;
$F$;


select prepare();
select * from pg_temp.pivot();

 member | A | B | C | Z
--------+---+---+---+---
 000001 | 1 | 1 | 0 | 0
 000002 | 0 | 1 | 1 | 0
 000003 | 1 | 0 | 1 | 0
 000004 | 0 | 0 | 0 | 0
(4 rows)

here I use SQL to form the above query as a temporary function and then pull the result fows from that

in the main function I use a subselect which allows me to use a CTE which means I can order the column names.

I could have created a temporary view instead inside the main function but didn't think of that until just now.

I am making the assumption that the values in group_name are no longer than 64 octets, varchar(64) does not enforce this - the type name does, and is probably a better fit for this task.

  • Very clever and this produces the correct results. Nice work! – vallismortis Oct 14 '18 at 15:22
1

Here is a function for generating the value of any cell (I'm using text instead of int to avoid type conflicts when I merge in the headers later):

CREATE OR REPLACE FUNCTION is_group_member(mname text, gname text)
RETURNS text
AS $$
  SELECT CASE WHEN COUNT(*) > 0 THEN '1' ELSE '0' END FROM group_membership WHERE group_name=gname AND member=mname
$$ LANGUAGE SQL STABLE;

SELECT is_group_member('000001','A');
SELECT is_group_member('000001','Z');

With the above function, we can do:

SELECT DISTINCT(name) AS name,is_group_member('000001',name) FROM groups ORDER BY name ASC;
 name | is_group_member
------+-----------------
 A    | 1
 B    | 1
 C    | 0
 Z    | 0
(4 rows)

And we can convert the above query to return an ordered array:

SELECT array_agg(is_member) AS membership FROM (SELECT DISTINCT(name) AS name,is_group_member('000001',name) AS is_member FROM groups ORDER BY name ASC) g;

 membership
------------
 {1,1,0,0}
(1 row)

However, I don't really want the brackets in there, so I'll use string_agg instead:

SELECT string_agg(is_member,',') AS membership FROM (SELECT DISTINCT(name) AS name,is_group_member('000001',name) AS is_member FROM groups ORDER BY name ASC) g;

Converting the above query into a function:

CREATE OR REPLACE FUNCTION group_memberships(mname text)
RETURNS text
AS $$
  SELECT string_agg(is_member,',') AS membership FROM (SELECT DISTINCT(name) AS name,is_group_member(mname,name) AS is_member FROM groups ORDER BY name ASC) g
$$ LANGUAGE SQL STABLE;

SELECT group_memberships('000001');

 group_memberships
-------------------
 1,1,0,0
(1 row)

And then invoking the function in a query:

SELECT DISTINCT(member),group_memberships(member) FROM group_membership ORDER BY member;
 member | group_memberships
--------+-------------------
 000001 | 1,1,0,0
 000002 | 0,1,1,0
 000003 | 1,0,1,0
 000004 | 0,0,0,0
(4 rows)

The above results are precisely what I'm looking for, although I feel that this could be improved upon in the following ways:

  • simplify to use fewer functions, or even collapse into a single query
  • add a first row containing the ordered array of group names
  • expand the results into a table (as in this answer or possibly this answer)

Of course, in retrospect, my group names can't be used as column names because upper/lower case, special characters, white space, etc.

So, staying with the array-based approach, I'll try to get the next best thing.

Getting a row of just the ordered group names:

SELECT '' AS member,string_agg(DISTINCT(name),',' ORDER BY name ASC) AS group_memberships FROM groups;

 member | group_memberships
--------+-------------------
        | A,B,C,Z
(1 row)

Then, I can create a view that unions the result sets to produce the complete matrix:

CREATE VIEW group_memberships AS
  SELECT '' AS member,string_agg(DISTINCT(name),',' ORDER BY name ASC) AS group_memberships FROM groups
   UNION
  SELECT DISTINCT(member),group_memberships(member) FROM group_membership ORDER BY member
;

 member | group_memberships
--------+-------------------
        | A,B,C,Z
 000001 | 1,1,0,0
 000002 | 0,1,1,0
 000003 | 1,0,1,0
 000004 | 0,0,0,0
(5 rows)

With this solution, there are no temporary tables nor materialized views. The view produces results in a form that are easy enough to import into Excel, so this works for my purposes. I would like to solve this problem with fewer functions though (or even no functions).

Exporting on the command line, I can drop the headers from the result set:

C:\temp>C:\Progra~1\PostgreSQL\9.6\bin\psql.exe -U user -d testdb -c "\copy (SELECT * FROM group_memberships) to 'group_membership.csv' WITH (FORMAT CSV, HEADER FALSE);"

Produces the following file:

"","A,B,C,Z"
000001,"1,1,0,0"
000002,"0,1,1,0"
000003,"1,0,1,0"
000004,"0,0,0,0"

This isn't perfect due to the quoting, but it is close enough to import into Excel after some minimal search/replace in a text editor.

To get a little closer to the desired output file:

DROP VIEW group_memberships;
DROP FUNCTION group_memberships(text);
DROP FUNCTION is_group_member(text,text);

CREATE OR REPLACE FUNCTION is_group_member(mname text, gname text)
RETURNS text
AS $$
  SELECT CASE WHEN COUNT(*) > 0 THEN '"1"' ELSE '"0"' END FROM group_membership WHERE group_name=gname AND member=mname
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION group_memberships(mname text)
RETURNS text
AS $$
  SELECT '"' || mname || '"' || ',' || string_agg(is_member,',') AS membership FROM (SELECT DISTINCT(name) AS name,is_group_member(mname,name) AS is_member FROM groups ORDER BY name ASC) g
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE VIEW group_memberships AS
  SELECT '' AS member, '""' || ',"' || string_agg(DISTINCT(name),'","' ORDER BY name ASC) || '"' AS group_memberships FROM groups
   UNION
  SELECT DISTINCT(member),group_memberships(member) FROM group_membership ORDER BY member
;

SELECT group_memberships FROM group_memberships;

    group_memberships
--------------------------
 "","A","B","C","Z"
 "000001","1","1","0","0"
 "000002","0","1","1","0"
 "000003","1","0","1","0"
 "000004","0","0","0","0"
(5 rows)

And running:

C:\temp>C:\Progra~1\PostgreSQL\9.6\bin\psql.exe -U user -d testdb -c "\copy (SELECT group_memberships FROM group_memberships) to 'group_membership.csv' WITH (FORMAT CSV, HEADER FALSE);"

Produces the following file:

""""",""A"",""B"",""C"",""Z"""
"""000001"",""1"",""1"",""0"",""0"""
"""000002"",""0"",""1"",""1"",""0"""
"""000003"",""1"",""0"",""1"",""0"""
"""000004"",""0"",""0"",""0"",""0"""

Incrementally closer, but still not perfect. However, two search/replaces of "" to " yields:

"","A","B","C","Z"
"000001","1","1","0","0"
"000002","0","1","1","0"
"000003","1","0","1","0"
"000004","0","0","0","0"

Which imports directly into Excel. This approach may cause problems if there are any double-quotes in the group or member names, so if anyone has a better workaround for the quoting I would like to hear it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.