PostgreSQL table query migration

#1

Hi,
I want migrate 2 DB. The db name are the same in both and table name are different (although with the same internal structure).
Example: db1.tableX and db2.table_X

I have developed many queries with Metabase and it is not feasible to manually change all table names for each query.

What I thought was to update Metabase PostgreSQL database. It works for query_type=native but I’m lost how to do it for query_type=query.

What I do to migrate tables:

Case when query_type=native.

#1 Update database
UPDATE report_card SET database_id = 2 WHERE query_type = 'native' AND database_id=1;

#2 Replace database name
UPDATE report_card SET dataset_query = REPLACE(dataset_query, '"database":1', '"database":2') WHERE query_type = 'native' AND database_id = 2

#3 Replace table name
UPDATE report_card SET dataset_query = REPLACE(dataset_query, '`tableX`','`table_X`') WHERE query_type = 'native' AND dataset_query LIKE '%`subscription`%' AND database_id = 2;

:warning: Case when query_type=query.

This is the data that I get from report_card.dataset_query.
{“query”:{“source-table”:73,“breakout”:[[“fk->”,[“field-id”,563],[“field-id”,3045]],[“fk->”,[“field-id”,550],[“field-id”,585]],[“field-id”,553]],“aggregation”:[[“count”],[“sum”,[“field-id”,557]]],“filter”:[“and”,[“not-null”,[“fk->”,[“field-id”,563],[“field-id”,3045]]],[“between”,[“field-id”,548],“2019-05-13”,“2019-05-19”],["=",[“fk->”,[“field-id”,559],[“field-id”,858]],“CANC”]]},“type”:“query”,“database”:1}

However, I’m not sure which column of the Metabase database should update to change the index. And I do not know what report_card.dataset_query.index refers to that table.

Someone can help me? Someone know other method?
Best regards,
Joaquin

#2

Can you create views on the new database to match the table names on the old? Much better than messing with the Metabase database in an unsupported way.