Views don't have primary keys, so comparing them doesn't work

14 views
Skip to first unread message

mai...@umich.edu

unread,
Jul 10, 2019, 7:54:06 PM7/10/19
to DB Solo
I want to compare views across two PostGreSQL databases. The views, of course, don't have primary keys—you can't assign a primary key to a view in PostGreSQL, for good reason. Since there are some rows which are duplicates, I can't just select all the columns as the primary keys—I get an error saying that there are duplicate keys.

The order of rows in the views is determined by the queries and the tables they pull from. So why can't the "Compare Table Data" just diff the two row-by-row without needing a primary key?

This is the reason I downloaded DB Solo, but it looks like it won't work for my purposes. 

Thanks,

Matt

DB Solo Admin

unread,
Jul 10, 2019, 8:00:09 PM7/10/19
to DB Solo
Try creating a view that has the rownumber in the result set, then use that column as the primary key. That would work as long as the 'order' of the rows is the same on both sides and there is an equal number of rows on both sides.

marko
Reply all
Reply to author
Forward
0 new messages