less than a minute
Open Lens
Open a Shell inside the timescale pod
pg_dump --host localhost --port 5432 --username postgres --verbose --file '/var/lib/postgresql/ordertable.sql' --table public.ordertable factoryinsight
psql
\c factoryinsight
SELECT order_name, asset_id, count(*) FROM ordertable GROUP BY order_name, asset_id HAVING count(*) > 1;
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 !
SELECT conname FROM pg_constraint WHERE conrelid = 'ordertable'::regclass AND contype = 'u';
ALTER TABLE ordertable DROP CONSTRAINT ordertable_asset_id_order_id_key;
ALTER TABLE ordertable ADD CONSTRAINT ordertable_asset_id_order_name_key UNIQUE (asset_id, order_name);
exit