1

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.

5
  • Instead of using DBManager to query your PostGIS database, try using a client like DBeaver... Sometimes DBManager uses the sqlite syntax for sql and rename columns in the database - DBeaver is the best tool for interacting with your postgresql/postgis database. Aug 7 '20 at 18:05
  • Thanks for the direction @DPSSpatial – does DBeaver let me easily load into QGIS for visualizing?
    – Unrelated
    Aug 7 '20 at 18:10
  • 2
    Always make sure your queries have unique aliases across the return cursor. Above, that would be SELECT a.pid pid_20, a.owner owner_20, b.pid pid_19, b.owner owner_19
    – Vince
    Aug 7 '20 at 18:10
  • @Vince Ahh thank you! a much cheaper option and now I know better practice!
    – Unrelated
    Aug 7 '20 at 18:13
  • @Unrelated you can use the spatial viewer in DBeaver to see the result, and if you need to style it further, you can create a table in the database to view in QGIS. Aug 7 '20 at 18:16
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.

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.