[Bug] v.db.join fails, existing cat

[ecodiv]

Describe the bug

Running the example from the v.db.join manual page, v.db.join fails with:

GRASS nc_spm_08_grass7/user1:Downloads > v.db.join map=mygeology column=GEO_NAME other_table=nc_geology other_column=geol_id
DBMI-SQLite driver error:
Error in sqlite3_step():
UNIQUE constraint failed: mygeology.cat

DBMI-SQLite driver error:
Error in sqlite3_step():
UNIQUE constraint failed: mygeology.cat

ERROR: Error while executing: 'UPDATE mygeology SET cat = (SELECT cat FROM
       nc_geology WHERE nc_geology.geol_id=mygeology.GEO_NAME)'
ERROR: Error filling columns {'cat': ['INTEGER', '20'], 'geol_id': ['TEXT',
       '1000'], 'longname': ['TEXT', '1000'], 'comment': ['TEXT', '1000']}
GRASS nc_spm_08_grass7/user1:Downloads > 

To reproduce

• db.in.ogr input=nc_geology.csv output=nc_geology
• g.copy vector=geology,mygeology
• v.db.join map=mygeology column=GEO_NAME other_table=nc_geology other_column=geol_id

Expected behavior

Screenshots

System description

• Operating System: Linux, Ubuntu 22.04
• GRASS GIS version: 8.5dev

Additional context

Not sure if this is related to #3765

Related to the example code, why the nc_geology table a cat column has in the first place. Why, when importing a CSV file with db.in.ogr, a new column cat is created?

[ninsbl]

Thanks for catching this. The column column should always be excluded from the join / update operation. This needs to be fixed in v.db.join

[ecodiv]

This is not about the column provided with the column parameter, I think. I think the offending column is the cat column, which is present in both tables. As mentioned, not sure why it was added to the imported nc_geology in the first place.

In any case, it would be good if one could exclude the cat column (and preferably any other column) explicitly, in essence the opposite of the current option to select columns to add using the subset_columns parameter.

[ninsbl]

Right. So it is the Primary Key of the target table that needs to be excluded.

It would be fairly easy to add an exclude_columns option. But I am not sure if it is worth having it in addition to subset_columns ...

[ecodiv]

It would be fairly easy to add an exclude_columns option. But I am not sure if it is worth having it in addition to subset_columns ...

It makes life easier if you have many columns, and you need to exclude a few. You could include all those needed, but, well, is more work and easier to make errors.

[ninsbl]

See also #3249