PG: fix default value for IDENTITY PKs

Fixes #29560 - PostgreSQL identity column not recognized properly
qgis · Feb 7, 2020 · 3f5583c · 3f5583c
1 parent 032ed31
commit 3f5583c
Show file tree

Hide file tree

Showing 2 changed files with 44 additions and 0 deletions.
diff --git a/src/providers/postgres/qgspostgresprovider.cpp b/src/providers/postgres/qgspostgresprovider.cpp
@@ -1099,6 +1099,31 @@ bool QgsPostgresProvider::loadFields()
     }
 
     mAttrPalIndexName.insert( i, fieldName );
+    // If this is an identity field with constraints and there is no default, let's look for a sequence:
+    // we might have a default value created by a sequence named <table>_<field>_seq
+    if ( ! identityMap[tableoid ][ attnum ].isEmpty()
+         && notNullMap[tableoid][ attnum ]
+         && uniqueMap[tableoid][attnum]
+         && defValMap[tableoid][attnum].isEmpty() )
+    {
+      const QString seqName { mTableName + '_' + fieldName + QStringLiteral( "_seq" ) };
+      const QString seqSql { QStringLiteral( "SELECT c.oid "
+                                             "  FROM pg_class c "
+                                             "  LEFT JOIN pg_namespace n "
+                                             "    ON ( n.oid = c.relnamespace ) "
+                                             "  WHERE c.relkind = 'S' "
+                                             "    AND c.relname = %1 "
+                                             "    AND n.nspname = %2" )
+                             .arg( quotedValue( seqName ) )
+                             .arg( quotedValue( mSchemaName ) )
+                           };
+      QgsPostgresResult seqResult( connectionRO()->PQexec( seqSql ) );
+      if ( seqResult.PQntuples() == 1 )
+      {
+        defValMap[tableoid][attnum] = QStringLiteral( "nextval(%1::regclass)" ).arg( quotedIdentifier( seqName ) );
+      }
+    }
+
     mDefaultValues.insert( mAttributeFields.size(), defValMap[tableoid][attnum] );
 
     QgsField newField = QgsField( fieldName, fieldType, fieldTypeName, fieldSize, fieldPrec, fieldComment, fieldSubType );

diff --git a/tests/src/python/test_provider_postgres.py b/tests/src/python/test_provider_postgres.py
@@ -1487,6 +1487,25 @@ def testDefaultValuesAndClauses(self):
         self.assertTrue(feature.attribute(3).startswith(now.strftime('%Y-%m-%d')))
         self.assertEqual(feature.attribute(4), 123)
         self.assertEqual(feature.attribute(5), 'My default')
+
+    def testIdentityPk(self):
+        """Test a table with identity pk, see GH #29560"""
+
+        vl = QgsVectorLayer(self.dbconn + ' sslmode=disable key=\'gid\' srid=4326 type=POLYGON table="qgis_test"."b29560"(geom) sql=', 'testb29560', 'postgres')
+        self.assertTrue(vl.isValid())
+
+        feature = QgsFeature(vl.fields())
+        geom = QgsGeometry.fromWkt('POLYGON EMPTY')
+        feature.setGeometry(geom)
+        self.assertTrue(vl.dataProvider().addFeature(feature))
+
+        del(vl)
+
+        # Verify
+        vl = QgsVectorLayer(self.dbconn + ' sslmode=disable key=\'gid\' srid=4326 type=POLYGON table="qgis_test"."b29560"(geom) sql=', 'testb29560', 'postgres')
+        self.assertTrue(vl.isValid())
+        feature = next(vl.getFeatures())
+        self.assertIsNotNone(feature.id())
 
 
 class TestPyQgsPostgresProviderCompoundKey(unittest.TestCase, ProviderTestCase):