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

Started by aalmost 8 years ago6 messagesgeneral

372660931@qq.com

almost 8 years ago

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

adrian.klaver@aklaver.com

almost 8 years ago

In reply to: a (#1)

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

372660931@qq.com

almost 8 years ago

In reply to: Adrian Klaver (#2)

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

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

372660931@qq.com

almost 8 years ago

In reply to: Adrian Klaver (#2)

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

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

adrian.klaver@aklaver.com

almost 8 years ago

In reply to: a (#1)

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

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

adrian.klaver@aklaver.com

almost 8 years ago

In reply to: a (#4)

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

On 06/13/2018 10:34 PM, a wrote:

Sorry I was in the wrong db last time

TESTDB=# \dn
锟�拷2 锟�拷2 锟杰癸拷模式锟叫憋拷
锟�拷2 锟斤拷锟狡�拷2 锟�拷2|锟�拷2 拥锟斤拷锟斤拷
---------+----------
锟�拷2MjorTbl | postgres
锟�拷2Rate锟�拷2 锟�拷2 | postgres
锟�拷2public锟�拷2 | postgres

In addition try:

\dT "MjorTbl".mort

--
Adrian Klaver
adrian.klaver@aklaver.com