public inbox for [email protected]
help / color / mirror / Atom feedFrom: Phani Prathyush Somayajula <[email protected]>
To: Pgsql-admin <[email protected]>
To: [email protected] <[email protected]>
To: pgAdmin Support <[email protected]>
Subject: Assign User Defined DataType To Columns
Date: Wed, 2 Aug 2023 13:51:17 +0000
Message-ID: <VI1PR10MB7671CCB681768701B55BB8298D0BA@VI1PR10MB7671.EURPRD10.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <CAGA3vBvOOmrhpOJc3gjMcQS_TDfGR7OH_vaodLUQoJxYO8wZSQ@mail.gmail.com>
References: <CAGA3vBt726ha1P91DrSOF8oc57X27tLO8o9Wi9oDN5L3qqRi7Q@mail.gmail.com>
<[email protected]>
<CAGA3vBspwtKvaFBXT4c78cdW+mgfXJ68MSDL3TiVdMxpZPp_VA@mail.gmail.com>
<[email protected]>
<CAGA3vBvOOmrhpOJc3gjMcQS_TDfGR7OH_vaodLUQoJxYO8wZSQ@mail.gmail.com>
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 = ',');
[cid:[email protected]]
I’ve a table :
[cid:[email protected]]
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
Attachments:
[image/png] image001.png (26.6K, 3-image001.png)
download | view image
[image/png] image002.png (40.5K, 4-image002.png)
download | view image
view thread (15+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Assign User Defined DataType To Columns
In-Reply-To: <VI1PR10MB7671CCB681768701B55BB8298D0BA@VI1PR10MB7671.EURPRD10.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox