This article is only relevant for v0.9.5 and earlier.

Migrating DB ordertable

Ordertable has a unique constraint on asset_id and order_id. This migration changes the constraint to asset_id and order_name.

less than a minute

Instructions

Open OpenLens
Open a Shell inside the timescale pod
Create a backup of the order table !
```
pg_dump --host localhost --port 5432 --username postgres --verbose --file "/var/lib/postgresql/ordertable.sql" --table public.ordertable factoryinsight
```
Execute
```
psql
```
Select the factoryinsight db
```
\c factoryinsight
```
Check if any old orders would fail under the new constraints
```
SELECT order_name, asset_id, count(*) FROM ordertable GROUP BY order_name, asset_id HAVING count(*) > 1;
```
If there are any conflicts, remove the conflicting data
```
DELETE FROM ordertable ox USING (
     SELECT MIN(CTID) as ctid, order_name, asset_id
     FROM ordertable
     GROUP BY order_name, asset_id HAVING count(*) > 1
     ) b
WHERE ox.order_name = b.order_name AND ox.asset_id = b.asset_id
AND ox.CTID <> b.ctid;
```
If the data can not be removed (e.g. is still required), please make sure to rename the order_names to prevent duplicates !
Retrieve the name of the old unique constraint
```
SELECT conname FROM pg_constraint WHERE conrelid = 'ordertable'::regclass AND contype = 'u';
```
Delete the old constraint
```
ALTER TABLE ordertable DROP CONSTRAINT ordertable_asset_id_order_id_key;
```
Create the new constraint
```
ALTER TABLE ordertable ADD CONSTRAINT ordertable_asset_id_order_name_key UNIQUE (asset_id, order_name);
```
Exit
```
exit
```
Close the pod shell. The effect is immediate, you don’t need to restart the container