1

I need to find a way to stack up numeric id fields from two tables without duplications. To put it simply here is what I want to do schematically :

Table 1

Id
1
2

Table 2

Id
1
2
3

Expected result (Addition and reset of table 2 Id)

Id
1
2
3
4
5

I'm currently copy/pasting and editing rows manually but it's completely suboptimal and time consuming. Any ideas, plugins, functions...?

2

Using the DB manager SQL window on some mock-up layers:

Table 1:

enter image description here

And table 2:

enter image description here

I can write a SQL to union them together with all records together but a useless ID column that contains duplicates, but both tables:

enter image description here

But by using a sub-query and the row_number function, I can create a new unique ID column simply numbering the records:

enter image description here

From there you can load the result into the QGIS window and export it to a new file.

3
  • I tested that function on pgAdmin and it worked very well, thank you !
    – Caligraphy
    Feb 3 at 22:47
  • @Caligraphy oh you have postgis already?!? You should have said so... !! Try DBeaver if you can - much easier than pgadmin (which is for DB admins), and more functional than DB manager in qgis... Feb 4 at 0:20
  • I will definitively give it a try !
    – Caligraphy
    Feb 8 at 10:26
0

Using DPSSpatial SQL function I tried to subquery two tables which where NOT ascending ordered with DB Manager virtual layers as following :

WITH query_name1 AS (
     SELECT * FROM layer1 ORDER BY field ASC
     )
   , query_name2 AS (
     SELECT * FROM layer2 ORDER BY field ASC
     )
SELECT
row_number() over () AS newunique_id
, data.* from

(
SELECT * FROM query_name1
union
SELECT * FROM query_name2
)
AS data

However It did'nt worked as expected as layers 1 and 2 did'nt came up ordered by ascending. Any tips to make it work ?

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.