|
Hi All, I’ve a user defined data type as : CREATE TYPE uibackend."_operation" ( INPUT = array_in, OUTPUT = array_out, RECEIVE = array_recv, SEND = array_send, ANALYZE = array_typanalyze, ALIGNMENT = 4, STORAGE = any, CATEGORY = A, ELEMENT = uibackend.operation, DELIMITER = ',');
I’ve a table :
And its DDL is :
id bigserial NOT NULL, "module" varchar(100) NULL, submodule varchar(100) NULL, operation varchar(100) NULL, value jsonb NULL, modifiedby varchar(100) NULL, modifiedat timestamp NULL, status uibackend.auditlogstatus NULL, CONSTRAINT auditlog_pkey PRIMARY KEY (id) ); Now I want to change the data type of the column operation to operation data type(which is user defined) as ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE operation USING operation::operation; But I’ve been facing issues like this :
uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE operation using (operation)::operation; ERROR: cannot cast type real to operation LINE 1: ... COLUMN operation TYPE operation using (operation)::operatio... ^ uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE operation using (_operation)::operation; ERROR: column "_operation" does not exist LINE 1: ...tlog ALTER COLUMN operation TYPE operation using (_operation... ^ HINT: Perhaps you meant to reference the column "auditlog.operation". uibackend=> uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE uibackend.operation; ERROR: column "operation" cannot be cast automatically to type operation HINT: You might need to specify "USING operation::operation". uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE uibackend.operation USING operation::operation; ERROR: cannot cast type real to operation LINE 1: ...operation TYPE uibackend.operation USING operation::operatio... ^ uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE uibackend.operation USING uibackend.operation::operation; ERROR: missing FROM-clause entry for table "uibackend" LINE 1: ...R COLUMN operation TYPE uibackend.operation USING uibackend.... ^ uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE uibackend.operation USING uibackend.operation::uibackend.operation; ERROR: missing FROM-clause entry for table "uibackend" LINE 1: ...R COLUMN operation TYPE uibackend.operation USING uibackend.... ^ uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE uibackend.operation USING operation::uibackend.operation; ERROR: cannot cast type real to operation LINE 1: ...operation TYPE uibackend.operation USING operation::uibacken... ^ uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE uibackend.operation USING operation::uibackend.operation; ERROR: cannot cast type real to operation LINE 1: ...operation TYPE uibackend.operation USING operation::uibacken... ^ uibackend=> uibackend=> uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation TYPE uibackend.operation USING operation::uibackend.operation;^C uibackend=> uibackend=> ALTER TABLE table_name auditlog ALTER COLUMN operation set data type uibackend.operation us uibackend=> ALTER TABLE table_name auditlog ALTER COLUMN operation set data type uibackend.operation using operation::operation; ERROR: syntax error at or near "auditlog" LINE 1: ALTER TABLE table_name auditlog ALTER COLUMN operation set d... ^ uibackend=> ALTER TABLE table_name uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation; ERROR: syntax error at or near "uibackend" LINE 1: ALTER TABLE table_name uibackend.auditlog ALTER COLUMN opera... ^ uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation; ERROR: column "operation" cannot be cast automatically to type operation HINT: You might need to specify "USING operation::operation". uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation using operation::operation; ERROR: cannot cast type real to operation LINE 1: ... set data type uibackend.operation using operation::operatio... ^ uibackend=> uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation using (operation)::operation; ERROR: cannot cast type real to operation LINE 1: ...et data type uibackend.operation using (operation)::operatio... ^ uibackend=> uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation using (operation)::text; ERROR: result of USING clause for column "operation" cannot be cast automatically to type operation HINT: You might need to add an explicit cast. uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation using operation::text; ERROR: result of USING clause for column "operation" cannot be cast automatically to type operation HINT: You might need to add an explicit cast. uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation using auditlog.operation::text; ERROR: result of USING clause for column "operation" cannot be cast automatically to type operation HINT: You might need to add an explicit cast. uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation using (operation()); ERROR: function operation() does not exist LINE 1: ...peration set data type uibackend.operation using (operation(... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. uibackend=> ALTER TABLE uibackend.auditlog ALTER COLUMN operation set data type uibackend.operation using (operation); ERROR: result of USING clause for column "operation" cannot be cast automatically to type operation HINT: You might need to add an explicit cast. uibackend=> set search_path to uibackend; SET uibackend=> alter table auditlog alter COLUMN operation type operation using operation::operation; ERROR: cannot cast type real to operation LINE 1: ...er COLUMN operation type operation using operation::operatio... ^ uibackend=> alter table auditlog alter COLUMN operation type operation using operation::uibackend._operation; ERROR: cannot cast type real to operation[] LINE 1: ...er COLUMN operation type operation using operation::uibacken... ^ uibackend=> alter table auditlog alter COLUMN operation type operation using (operation)::uibackend._operation; ERROR: cannot cast type real to operation[] LINE 1: ... COLUMN operation type operation using (operation)::uibacken... ^ uibackend=> alter table auditlog alter COLUMN operation type operation using CA uibackend=> alter table auditlog alter COLUMN operation type operation using CAST(operation as operation); ERROR: cannot cast type real to operation LINE 1: ...itlog alter COLUMN operation type operation using CAST(opera... ^ uibackend=> alter table auditlog alter COLUMN operation type operation using CAST(operation as uibackend.operation); ERROR: cannot cast type real to operation LINE 1: ...itlog alter COLUMN operation type operation using CAST(opera... ^ uibackend=> alter table auditlog alter COLUMN operation type operation using operation::text; ERROR: result of USING clause for column "operation" cannot be cast automatically to type operation HINT: You might need to add an explicit cast. uibackend=> alter table auditlog alter COLUMN operation set data type operation using operation::text; ERROR: result of USING clause for column "operation" cannot be cast automatically to type operation HINT: You might need to add an explicit cast. uibackend=> alter table auditlog alter COLUMN operation set data type operation using (operation)::text; ERROR: result of USING clause for column "operation" cannot be cast automatically to type operation HINT: You might need to add an explicit cast. uibackend=> alter table auditlog alter COLUMN operation type operation using CAST('operation' as uibackend.operation); ERROR: invalid input value for enum operation: "operation" LINE 1: ... alter COLUMN operation type operation using CAST('operation... ^ uibackend=> alter table auditlog alter COLUMN operation type operation using CAST('operation' as uibackend._operation); ERROR: malformed array literal: "operation" LINE 1: ... alter COLUMN operation type operation using CAST('operation... ^ DETAIL: Array value must start with "{" or dimension information. uibackend=> alter table auditlog alter COLUMN operation type operation using CAST{'operation' as uibackend._operation}; ERROR: syntax error at or near "{" LINE 1: ...g alter COLUMN operation type operation using CAST{'operatio... ^ uibackend=> alter table auditlog alter COLUMN operation type operation using CAST({'operation'} as uibackend._operation); ERROR: syntax error at or near "{" LINE 1: ... alter COLUMN operation type operation using CAST({'operatio... ^ uibackend=> alter table auditlog alter COLUMN operation type operation using CAST({'operation'} as operation); ERROR: syntax error at or near "{" LINE 1: ... alter COLUMN operation type operation using CAST({'operatio... ^ uibackend=> alter table auditlog alter COLUMN operation type operation using CAST('operation' as uibackend._operation); ERROR: malformed array literal: "operation" LINE 1: ... alter COLUMN operation type operation using CAST('operation... ^ DETAIL: Array value must start with "{" or dimension information. uibackend=> alter table auditlog alter COLUMN operation type operation using CAST({operation} as uibackend._operation); ERROR: syntax error at or near "{" LINE 1: ... alter COLUMN operation type operation using CAST({operation... ^ uibackend=> uibackend=> alter table auditlog alter COLUMN operation type operation using CAST('operation{}' as uibackend._operation); ERROR: malformed array literal: "operation{}" LINE 1: ... alter COLUMN operation type operation using CAST('operation... ^ DETAIL: Array value must start with "{" or dimension information. uibackend=> Any suggestions how to modify the column ? PS: I had to paste this lengthy log because I wanted you all know that I’ve tried these many ways to change the data type in vain.
Regards, Pratz |
