Azure Postgresql how to create a CAST

Laurens van der Stoep (NL) 35

Hello,

I am currently in the process of configuring a database within Azure Database for PostgreSQL flexible server. However when i try to create two CAST objects i get the following error: ERROR: must be owner of type integer or type text.

Queries:

CREATE CAST (integer AS text) 
WITH FUNCTION text(integer) 
AS IMPLICIT;

CREATE CAST (bytea as text)
WITH FUNCTION bytea2text(bytea)
AS implicit;

When i run the query below i see that the owner of type text is azuresu.

SELECT typowner::regrole 
  FROM pg_type t 
 	WHERE typname = 'text';

How can i resolve this issue and why am i not owner if the types in my own database?

ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee

2023-09-21T09:57:01.72+00:00

Hi Laurens van der Stoep (NL) •,

Welcome to Microsoft Q&A forum.

As I understand, you are getting error: "must be owner of type integer or type text" when using CAST in Azure Database for PostgreSQL Flexible Server.

I am working on this and will update you. Thanks
ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee

2023-09-25T04:17:36.72+00:00

Hi Laurens van der Stoep (NL) •,

Thanks for your patience.

I am awaiting reply from the internal team, thanks.

2 answers

ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee

2023-09-26T08:10:20.3033333+00:00

Hi Laurens van der Stoep (NL) •,

Thanks for your patience.

I have got the below reply from the internal team:

Currently, the super user is the owner of the system defined datatypes in Flexible Servers. We are evaluating the options if we can provide the functionality to alter the ownership of the data types to azure_pg_admin or not.
We will be providing an update once our analysis is complete.

We appreciate your patience until team shares its analysis.

Thank you.
Please sign in to rate this answer.
Dmitriy Chernichenko 5 Reputation points

2023-11-30T14:50:45.6333333+00:00

Hi, ShaktiSingh-MSFT

Is there any progress with this functionality?

ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee

2023-12-01T04:01:06.17+00:00

Hi Laurens van der Stoep (NL) •,

I am checking on this and will get back to you.

Thanks

nishino-ti 36 Reputation points

2024-02-28T08:34:51.7066667+00:00

Hi ShaktiSingh-MSFT

Has there been any progress since then?

I'm also having trouble with the same problem.

ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee

2024-02-28T14:20:15.62+00:00

Hi
Dmitriy Chernichenko •, nishino-ti •,

Could you please share the CAST that you are trying to use.

Thanks

nishino-ti 36 Reputation points

2024-02-29T01:36:04.4966667+00:00

Hi, ShaktiSingh-MSFT I would like to use these 6 types. CAST (bigint AS text), CAST (smallint AS text), CAST (integer AS text), CAST (real AS text), CAST (double precision AS text), CAST (numeric AS text) Thanks

nishino-ti 36 Reputation points

2024-03-01T01:21:27.2033333+00:00

If possible, could Microsoft add CAST?

Thanks

ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee

2024-03-01T04:17:12.6466667+00:00

I am checking with the internal team. Thanks for your patience.

ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee

2024-03-04T05:30:01.49+00:00

FYI, I am still awaiting reply from the internal team.

Thanks

ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee

2024-03-06T09:25:53+00:00

Hi nishino-ti,

Can you confirm if the list (6 types. CAST (bigint AS text), CAST (smallint AS text), CAST (integer AS text), CAST (real AS text), CAST (double precision AS text), CAST (numeric AS text) is the complete create cast statement that you are looking for, without any additional options like, with/without functions, implicit/explicit casts.

Thanks

ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee

2024-03-08T04:43:43.5366667+00:00

Hi nishino-ti,

Just checking if you got a chance to reply to the previous comment?

Thanks

nishino-ti 36 Reputation points

2024-03-08T13:10:32.7666667+00:00

Hi ShaktiSingh-MSFT,

I'm sorry that I was late. Options are required.The desired statement is as follows.

CREATE CAST (int2 AS text) WITH INOUT AS IMPLICIT;

CREATE CAST (int4 AS text) WITH INOUT AS IMPLICIT;

CREATE CAST (int8 AS text) WITH INOUT AS IMPLICIT;

CREATE CAST (numeric AS text) WITH INOUT AS IMPLICIT;

CREATE CAST (real AS text) WITH INOUT AS IMPLICIT;

CREATE CAST (double precision AS text) WITH INOUT AS IMPLICIT;

Thanks

ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee

2024-03-11T05:57:48.0033333+00:00

Hi nishino-ti,

Thanks for sharing.

I will check and get back.

Thank you.

ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee

2024-03-14T08:45:53.97+00:00

Hi nishino-ti,

This is to inform you that reply is awaited from the internal team.

Thanks

ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee

2024-03-18T08:14:35.09+00:00

Hi nishino-ti,

Could you please raise support case and mention server details over there so that team can apply these.

Let me know if you know any help in getting the support case raised.

Thanks

nishino-ti 36 Reputation points

2024-03-19T07:23:30.17+00:00

Hi ShaktiSingh-MSFT,

I Create an Azure support request.

I will post here when I get information from support.

Thanks

Ladislav Gallay 146 Reputation points

2024-04-12T06:42:56.7833333+00:00

We are also in need of running these two commands on Postgres Flexible Server 16.

Error:

PG::InsufficientPrivilege: ERROR: must be owner of type character varying or type uuid

Is there a way how to run it or can you run it for us if we give you the name of the database instance and subscription?

Thank you!

CREATE CAST (character varying AS uuid) WITH INOUT AS IMPLICIT; CREATE CAST (character varying AS bigint) WITH INOUT AS IMPLICIT;

ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee

2024-04-12T09:19:15.09+00:00

Hi Ladislav Gallay •,

Could you please raise support case and mention server details over there so that team can apply these.

Let me know if you know any help in getting the support case raised.

Thanks

Lazar, Alexandru 0 Reputation points

2024-06-05T10:56:24.22+00:00

Hi, are there any news regarding the alteration of the ownership of the data types to azure_pg_admin? Tickets seem like a slow and inconvenient process.
Sign in to comment
Javier Andres Veliz Miranda 0 Reputation points

2024-04-19T16:19:59.39+00:00

Hola, tengo el mismo error, Se encontro alguna solucion para esto ?
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

Azure Postgresql how to create a CAST

2 answers