Missing / misnamed fields in PostgreSQL table join in QGIS

Question

First, I'm brand new to PostgreSQL and PostGIS, so my question is probably asked wrong, and it may be a duplicate – but my search attempts didn't find anything. Also, I don't expect this is an issue with the data_ ̄_(ツ)_/ ̄ _but, for reproduction reasons, the tables are the 2020 and 2019 CSVs from Boston's property assessment.

Problem: When I join two tables with matching field names in the QGIS DB Manager, only one set of fields appear; when I rename the fields, the wrong fields are renamed. When I run the same commands in a pgAdmin window, these problems don't happen.

What I did & what happened

I tried joining two tables in the QGIS DB Manager

SELECT
 a.pid, a.owner, b.pid, b.owner
FROM 
 properties_20 AS a 
 INNER JOIN properties_19 AS b ON a.pid = b.pid
LIMIT 3;

Resulting view:

pid owner
0502309029 LADAKH REALTY LLC MASS LLC
0502309031 ROGERS ALEX A
0502309032 CLAIRE A WALTON 2012

The resulting view only had two fields: pid and owner. I expected four: a pid for each table and an owner for each table. As I'm new to all of this, though, I thought it might be a matter of matching field names, so I renamed the owner fields with AS.

SELECT
 a.pid, a.owner AS owner_20, b.pid, b.owner AS owner_19
FROM 
 properties_20 AS a 
 INNER JOIN properties_19 AS b ON a.pid = b.pid
LIMIT 3;

The resulting view added a third column named owner_19, but the column's contents were the table's pid values:

pid owner_20 owner_19
0502309029 LADAKH REALTY LLC MASS LLC 0502309029
0502309031 ROGERS ALEX A 0502309031
0502309032 CLAIRE A WALTON 2012 0502309032

I then tested the same commands outside of QGIS, using pgAdmin 4:

First query (no renaming)

pid owner pid owner
"0502309029" "LADAKH REALTY LLC MASS LLC" "0502309029" "LADAKH REALTY LLC"
"0502309031" "ROGERS ALEX A" "0502309031" "ROGERS ALEX A"
"0502309032" "CLAIRE A WALTON 2012" "0502309032" "CLAIRE A WALTON 2012"

Second query (renaming)

pid owner_20 pid owner_19
"0502309029" "LADAKH REALTY LLC MASS LLC" "0502309029" "LADAKH REALTY LLC"
"0502309031" "ROGERS ALEX A" "0502309031" "ROGERS ALEX A"
"0502309032" "CLAIRE A WALTON 2012" "0502309032" "CLAIRE A WALTON 2012"

Primary question: Why is this happening? Is there a way I can fix this so it works in QGIS? Is my syntax somehow lazy?

Secondary question: If I should just be working in the pgAdmin window, how do load the results as a new layer in QGIS?

I considered posting this on the StackOverflow site, but because the problem is specific to QGIS I asked here.

Answer 1

If you treat each cursor SQL statement as if it were the query after the AS in a CREATE VIEW directive, then you won't give the cursor an excuse for mangling the column identities.

In fact, you can use CREATE VIEW to debug the problem:

CREATE VIEW xx_cursor_check AS
SELECT
 a.pid, a.owner AS owner_20, b.pid, b.owner AS owner_19
FROM 
 properties_20 AS a 
INNER JOIN properties_19 AS b ON a.pid = b.pid
LIMIT 3;

ERROR: column "pid" specified more than once
SQL state: 42701

Then, if you alias all column names to be unique:

CREATE VIEW xx_cursor_check AS
SELECT
 a.pid pid_20, a.owner owner_20, b.pid pid_19, b.owner owner_19
FROM 
 properties_20 a 
INNER JOIN 
 properties_19 b ON a.pid = b.pid
LIMIT 3;
DROP VIEW IF EXISTS xx_cursor_check;

DROP VIEW
Query returned successfully in 76 msec.

The key here is to always make sure your queries have unique aliases across the return cursor.

Notes:

1. Since this isn't an OUTER JOIN, you really don't need to list pid twice
2. If you try the incompletely aliased query from an ArcMap 10.7.1 Make Query Table, it will eventually fail with the same "42701" error.