Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to reference a composite type in schemas not "public"?

How to reference a composite type in schemas not "public"?

From
"a"
Date:
Hi I have created some composite type:

create type "MjorTbl".mort as(
adjfac    float8,
tablename text,
subtype   text,
improv   float8,
selfac    slfc
);

The schema is different from public, while I would like to create table using the composite type, it reports a error:

create type "MjorTbl".decrmt as(
nodecrmt  int4,
mortality "MjorTbl"."mort"
);

ERROR:  type "MjorTbl.mort" does not exist
SQL state: 42704

create type "MjorTbl".decrmt as(
nodecrmt  int4,
mortality mort
);

ERROR:  type "mort" does not exist
SQL state: 42704

How can I reference the created composite type correctly??

Thanks 

Shore


Re: How to reference a composite type in schemas not "public"?

From
Adrian Klaver
Date:
On 06/13/2018 08:34 PM, a wrote:
> Hi I have created some composite type:
> 
> create type "MjorTbl".mort as(
> adjfac    float8,
> tablename text,
> subtype   text,
> improv  float8,
> selfac    slfc
> );
> 
> The schema is different from public, while I would like to create table 
> using the composite type, it reports a error:
> 
> create type "MjorTbl".decrmt as(
> nodecrmt  int4,
> mortality "MjorTbl"."mort"
> );
> 
> ERROR:  type "MjorTbl.mort" does not exist
> SQL state: 42704

In psql what does \dn show?

> 
> create type "MjorTbl".decrmt as(
> nodecrmt  int4,
> mortality mort
> );
> 
> ERROR:  type "mort" does not exist
> SQL state: 42704
> 
> How can I reference the created composite type correctly??
> 
> Thanks
> 
> Shore
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: How to reference a composite type in schemas not "public"?

From
"a"
Date:
Hey thank you~


postgres=# \dn
    架构模式列表
  名称   |  拥有者
---------+----------
 pgagent | postgres
 public  | postgres
(2 行记录)


------------------ Original ------------------
From: "Adrian Klaver";
Date: Thursday, Jun 14, 2018 12:06 PM
To: "a"<372660931@qq.com>; "pgsql-general";
Subject: Re: How to reference a composite type in schemas not "public"?

On 06/13/2018 08:34 PM, a wrote:
> Hi I have created some composite type:
>
> create type "MjorTbl".mort as(
> adjfac    float8,
> tablename text,
> subtype   text,
> improv  float8,
> selfac    slfc
> );
>
> The schema is different from public, while I would like to create table
> using the composite type, it reports a error:
>
> create type "MjorTbl".decrmt as(
> nodecrmt  int4,
> mortality "MjorTbl"."mort"
> );
>
> ERROR:  type "MjorTbl.mort" does not exist
> SQL state: 42704

In psql what does \dn show?

>
> create type "MjorTbl".decrmt as(
> nodecrmt  int4,
> mortality mort
> );
>
> ERROR:  type "mort" does not exist
> SQL state: 42704
>
> How can I reference the created composite type correctly??
>
> Thanks
>
> Shore
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: How to reference a composite type in schemas not "public"?

From
"a"
Date:
Sorry I was in the wrong db last time

TESTDB=# \dn
    架构模式列表
  名称   |  拥有者
---------+----------
 MjorTbl | postgres
 Rate    | postgres
 public  | postgres


------------------ Original ------------------
From:  "Adrian Klaver";<adrian.klaver@aklaver.com>;
Date:  Jun 14, 2018
To:  "a"<372660931@qq.com>; "pgsql-general"<pgsql-general@postgresql.org>;
Subject:  Re: How to reference a composite type in schemas not "public"?

On 06/13/2018 08:34 PM, a wrote:
> Hi I have created some composite type:
>
> create type "MjorTbl".mort as(
> adjfac    float8,
> tablename text,
> subtype   text,
> improv  float8,
> selfac    slfc
> );
>
> The schema is different from public, while I would like to create table
> using the composite type, it reports a error:
>
> create type "MjorTbl".decrmt as(
> nodecrmt  int4,
> mortality "MjorTbl"."mort"
> );
>
> ERROR:  type "MjorTbl.mort" does not exist
> SQL state: 42704

In psql what does \dn show?

>
> create type "MjorTbl".decrmt as(
> nodecrmt  int4,
> mortality mort
> );
>
> ERROR:  type "mort" does not exist
> SQL state: 42704
>
> How can I reference the created composite type correctly??
>
> Thanks
>
> Shore
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: How to reference a composite type in schemas not "public"?

From
Adrian Klaver
Date:
On 06/13/2018 08:34 PM, a wrote:
> Hi I have created some composite type:
> 
> create type "MjorTbl".mort as(
> adjfac    float8,
> tablename text,
> subtype   text,
> improv  float8,
> selfac    slfc
> );
> 
> The schema is different from public, while I would like to create table 
> using the composite type, it reports a error:
> 
> create type "MjorTbl".decrmt as(
> nodecrmt  int4,
> mortality "MjorTbl"."mort"
> );
> 
> ERROR:  type "MjorTbl.mort" does not exist
> SQL state: 42704
> 
> create type "MjorTbl".decrmt as(
> nodecrmt  int4,
> mortality mort
> );
> 
> ERROR:  type "mort" does not exist
> SQL state: 42704
> 
> How can I reference the created composite type correctly??

Well it works here:
select version();
                                       version 

------------------------------------------------------------------------------------
  PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 
4.8.5, 64-bit

create schema "MjorTbl";
CREATE SCHEMA

create type "MjorTbl".mort as(
adjfac    float8,
tablename text,
subtype   text,
improv  float8
);
CREATE TYPE

create type "MjorTbl".decrmt as(
nodecrmt  int4,
mortality "MjorTbl"."mort"
);
CREATE TYPE

You might have a permissions issue. In your original post where all the 
commands run as the same user and from the same schema?

> 
> Thanks
> 
> Shore
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: How to reference a composite type in schemas not "public"?

From
Adrian Klaver
Date:
On 06/13/2018 10:34 PM, a wrote:
> Sorry I was in the wrong db last time
> 
> TESTDB=# \dn
>      架构模式列表
>    名称   |  拥有者
> ---------+----------
>   MjorTbl | postgres
>   Rate    | postgres
>   public  | postgres
> 
> 

In addition try:

\dT "MjorTbl".mort


-- 
Adrian Klaver
adrian.klaver@aklaver.com