8

I partitionned a table (over 13 millions geographic point data) in my PostgreSQL database with this statement:

CREATE TABLE data.data_fusion (
 id                BIGINT NOT NULL,
 source            TEXT,
 id_source         VARCHAR,
 id_vn             INTEGER,
 taxref_id         INTEGER,
 ...
 geom              GEOMETRY(Point, 2154),
 equipe            VARCHAR,
 exp_excl          BOOLEAN
 )
PARTITION BY LIST (equipe);

And for each child table I did create primary key and indexes like in this example.

CREATE TABLE data.data_fusion_lpo07 PARTITION OF data.data_fusion FOR VALUES IN ('lpo07');
ALTER TABLE data.data_fusion_lpo07 ADD PRIMARY KEY (id);
CREATE INDEX ON data.data_fusion_lpo07 (source);
CREATE INDEX ON data.data_fusion_lpo07 (id_vn);
CREATE INDEX ON data.data_fusion_lpo07 (taxref_id);
CREATE INDEX ON data.data_fusion_lpo07 (nom_vern);
CREATE INDEX ON data.data_fusion_lpo07 (nom_sci);
CREATE INDEX ON data.data_fusion_lpo07 USING GIST (geom);
CREATE INDEX ON data.data_fusion_lpo07 (equipe);
CREATE INDEX ON data.data_fusion_lpo07 (code_nidif_oiseau);

Actually, there is no problem to load child tables in my QGIS project, but I'm unable to load parent table 'data.data_fusion'. I've got the following error messages:

  • Using browser panel:

    Layer is not valid: The layer dbname='dbwork' host=bdd.faura.org port=5432 sslmode=require authcfg=lpoaura srid=2154 type=Point table="data"."data_fusion" (geom) sql= is not a valid layer and can not be added to the map
    
  • Using add layer menu

    dbname='dbwork' host=bdd.faura.org port=5432 sslmode=require authcfg=lpoaura key='""' estimatedmetadata=true srid=2154 type=Point table="data"."data_fusion" (geom) sql= is an invalid layer - not loaded
    

PostgreSQL console indicates an pk issue (no pk on parent table):

  Unexpected relation type 'p'.
  PostgreSQL layer has no primary key.

Is there a way to load my parent partitionned table into QGIS (v 2.18.14) ?

6

It looks like native PostgreSQL partitioning isn't supported in QGIS 2.x. It should work as expected in QGIS 3. See related ticket and commit.

As a workaround, you could create a view that's just a SELECT * from your master table and bring that into QGIS.

  • Thanks! that worked for me. Even whith row level security enabled on table! Actually, i can't migrate to newer version of qgis, there is a problem connection to postgresql with secure ssl protocol, like mentionned in this issue: issues.qgis.org/issues/17672 – lpofredc Mar 20 '18 at 19:56

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.