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

Migrate DB from varchar to text

This migration optimizes the database, by changing the data type of some columns from varchar to text

  less than a minute  

Instructions

  1. Open OpenLens
  2. Open a Shell inside the timescale pod

Untitled
Untitled

  1. Execute
psql

Untitled
Untitled

  1. Execute these SQL statements
\c factoryinsight
ALTER TABLE assettable ALTER COLUMN assetid TYPE text;
ALTER TABLE assettable ALTER COLUMN location TYPE text;
ALTER TABLE assettable ALTER COLUMN customer TYPE text;
ALTER TABLE producttable ALTER COLUMN product_name TYPE text;
ALTER TABLE ordertable ALTER COLUMN order_name TYPE text;
ALTER TABLE configurationtable ALTER COLUMN customer TYPE text;
ALTER TABLE componenttable ALTER COLUMN componentname TYPE text;

Untitled
Untitled

  1. Confirm the changes by using
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'assettable';
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'producttable';
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'ordertable';
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'configurationtable';
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'componenttable';

Untitled
Untitled

Last modified May 5, 2023: feat: finishing touches (1147002)