PostgreSQL – Comparing Column Values Without Common Table Elements

postgresql

I have a need to display the value stored in columns of separate tables where the tables and columns have nothing in common. I just want to compare the content of the rows of each column side-by-side. And just the 1st 10 rows would be sufficient for my purposes. There are no primary keys or common link between the tables.

I have many tables with hundreds of columns to compare. Is there a simple way to do this?

i.e.
Table1, column3 | Table2, column6 | Table3, column1

Resulting in something like the following

tbl1col3 | tbl2col6 | tbl3col1
123.9    | abc      | x12
234.0    | bcd      | o23

Using postgresql 9.6, pgadmin4 v3.5

Best Answer

like this perhaps.

WITH t1 AS ( SELECT col3 AS tbl1col3,row_number()OVER() FROM tbl1 limit 10)
    ,t2 as ( select col6 AS tbl2col6,row_number()OVER() FROM tbl2 limit 10)
    ,t3 as ( select col1 AS tbl3col1,row_number()OVER() FROM tbl3 limit 10)
SELECT tbl1col3, tbl2col6, tbl3col1 
FROM t1 
    FULL OUTER JOIN t2 USING (row_number)
    FULL OUTER JOIN t3 USING (row_number)
;

Related Solutions

Postgresql – Update column by comparing date and string values together

If you want to ignore the time part and match where the days are the same then:

UPDATE dim_sesid ds
       SET role =
             (SELECT min (er.role) 
                FROM EMPLOYEEROLE er
               WHERE substr(DS.NAME, 1, 8) || 'xxxxxx'
                  || substr(DS.NAME, 15, length(USERNAME))
               like to_char(thedate::timestamp, 'yyyymmdd') || 'xxxxxx' || USERNAME);

At the moment, for the first row, the to_char(thedate::timestamp, 'yyyymmdd') || er.username would return 20111012aaa which doesn't exist. By replacing the time portion with the same set of characters (xxxxxx) then we can ignore that and it becomes:

           WHERE '20111012' || 'xxxxxx'
              || 'aaa'
           like '20111012' || 'xxxxxx' || 'aaa'

The problem is though that you have two roles for 13th January 2012. The min will always return the day guard leaving the night guard superfluous.

Cross-Product Between Table Column and Input Values in PostgreSQL

In other RDBMS (like SQL Server before 2008 - as per Paul's comment) one might cross join to a subquery with UNION ALL SELECT, but there are more convenient and efficient options in Postgres.

And you don't need a CTE for this. You can use it, but it has no performance benefit.

Provide a set with VALUES:

VALUES computes a row value or set of row values specified by value expressions. It is most commonly used to generate a "constant table" within a larger command, but it can be used on its own.
```
SELECT t.i, m.col1
FROM   mTable m
CROSS  JOIN (VALUES (1), (2)) t(i);
```

Provide an array and unnest()

2a. with an array constructor:

SELECT i, m.col1
FROM   mTable m
CROSS  JOIN unnest (ARRAY[1,2]) i;

2b. With an array literal:

SELECT i, m.col1
FROM   mTable m
CROSS  JOIN unnest ('{1,2}'::int[]) i;

Add ORDER BY i, m.col1 if you need the sort order in your result.

About row and array syntax:

Array of strings when updating a field

Best Answer

Related Solutions

Postgresql – Update column by comparing date and string values together

Cross-Product Between Table Column and Input Values in PostgreSQL

Related Question