Assign User Defined DataType To Columns - Mailing list pgsql-admin
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 :
CREATE TABLE uibackend.auditlog (
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
Attachment
pgsql-admin by date: