Update 4.3.5 created new table names in postgres database

Setup, Configuration and Use Migration
1

Hi experts,
I did the 4.3.5 update some days ago and noticed that my grafana view is not working any more, because it doesn’t get any new data from openhab. I’ve found out that openhab created all my data tables with a case sensitive name. That’s why I get no more new data in my dashboards, because they get stored in another table.

image
image420×184 17.4 KB

I could copy my old data to the new tables, but I don’t even now if I could find an automated solution to match all 300 table names with their previous version…
And further I would have to update all my grafana dashboards to the new names…

image
image277×375 28.8 KB

Any ideas to solve this mess?

Thank you

2

First step is done, got a listing of the changed table names…

SELECT 
    t1.table_name AS originalName,
    t2.table_name AS newName
FROM 
    information_schema.tables t1
JOIN 
    information_schema.tables t2
ON 
    lower(t1.table_name) = lower(t2.table_name)
AND 
    t1.table_name <> t2.table_name
AND
    t1.table_name = lower(t1.table_name)
ORDER BY t1.table_name;
3

Here is what I’ve used to merge my tables. I did a semi automatic style SQL command generation and executed them manually, starting by one and then in an increasing amount after I got confident…

If you try to recreate what I did please notice that the queries I wrote here don’t copy or delete anything. They just create the commands that are needed.

// List all tables with same name but different
WITH TablePairs AS (
    SELECT 
        t1.table_name AS originalName,
        t2.table_name AS newName
    FROM 
        information_schema.tables t1
    JOIN 
        information_schema.tables t2
    ON 
        lower(t1.table_name) = lower(t2.table_name)
    AND 
        t1.table_name <> t2.table_name
    AND
        t1.table_name = lower(t1.table_name)
    ORDER BY t1.table_name
    -- LIMIT 1
)
SELECT 
    *
FROM TablePairs



//Create SQL command to count all tables entries to compare later before dropping old tables
//Last UNION has to be removed manually

WITH TablePairs AS (
    SELECT 
        t1.table_name AS originalName,
        t2.table_name AS newName
    FROM 
        information_schema.tables t1
    JOIN 
        information_schema.tables t2
    ON 
        lower(t1.table_name) = lower(t2.table_name)
    AND 
        t1.table_name <> t2.table_name
    AND
        t1.table_name = lower(t1.table_name)
    ORDER BY t1.table_name
    -- LIMIT 1
)
SELECT 
    'SELECT '''|| newName || ''' as newTableName, (SELECT COUNT(*) AS newTableCount FROM "' || newName || '"), ''' || originalName || ''' as originalTableName, (SELECT COUNT(*) FROM "' || originalName || '" as originalTableCount) UNION'
FROM TablePairs



//Create SQL commands to copy data
WITH TablePairs AS (
    SELECT 
        t1.table_name AS originalName,
        t2.table_name AS newName
    FROM 
        information_schema.tables t1
    JOIN 
        information_schema.tables t2
    ON 
        lower(t1.table_name) = lower(t2.table_name)
    AND 
        t1.table_name <> t2.table_name
    AND
        t1.table_name = lower(t1.table_name)
    ORDER BY t1.table_name
)
SELECT 
    'INSERT INTO "' || newName || '" SELECT * FROM "' || originalName || '";'
FROM TablePairs



//Create SQL commands to drop old tables
WITH TablePairs AS (
    SELECT 
        t1.table_name AS originalName,
        t2.table_name AS newName
    FROM 
        information_schema.tables t1
    JOIN 
        information_schema.tables t2
    ON 
        lower(t1.table_name) = lower(t2.table_name)
    AND 
        t1.table_name <> t2.table_name
    AND
        t1.table_name = lower(t1.table_name)
    ORDER BY t1.table_name
)
SELECT 
    'Drop table "' || originalName || '";'
FROM TablePairs