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

  • There is no such thing as "the first 10 rows". Rows in a relational database have no sort order. You can only talk about "the first rows" if you can specify an ORDER BY clause that defines the sort order during retrieval. – a_horse_with_no_name Nov 23 '18 at 16:37
  • hi a_horse_with_no_name - Ok, fair enough. How about limit 10 per table instead. – Phil_in_Tx Nov 23 '18 at 17:20

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)

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.