created type not found in create table
| От | Open _ |
|---|---|
| Тема | created type not found in create table |
| Дата | |
| Msg-id | 1957561924.2643508.1608830429291@mail.yahoo.com обсуждение исходный текст |
| Ответы |
Re: created type not found in create table
|
| Список | pgsql-general |
I'm using
I have psql (PostgreSQL) 13.1 on centOS 7.1
I do everything through layers of bash scripts and bash script functions.
So posting all the code would be huge.
The scripts run as postgres
Each script step tests if that object already exists before creating
Each statement is a separate call to psql
So each statement is a separate session
The steps are:
create the database users
created a database "StaffDB" (Yes with capitial letters because I want it that way)
created a schema "staffadmin" (ok I gave in and used lowercase for all except DB names)
set the search_path
create 3 types
create a table using those 3 types
and it says type not found.
Here is the output:
2020-12-24 16:18:54: - bootstrap_StaffDB is running as postgres
2020-12-24 16:18:54: Step 0 - Create database users
2020-12-24 16:18:54: Creating StaffDB users
2020-12-24 16:18:55: OptimusPrime already exists
2020-12-24 16:18:55: Minerva already exists
2020-12-24 16:18:55: BKuserUP already exists
2020-12-24 16:18:55: StaffDB users complete
2020-12-24 16:18:55: StaffDB Step 1 Create StaffDB database
2020-12-24 16:18:55: Start of create_database function for database: StaffDB
2020-12-24 16:18:55: Creating the StaffDB Database
\set ON_ERROR_STOP on
CREATE DATABASE "StaffDB"
with owner "XXXXXX" ;
CREATE DATABASE
2020-12-24 16:18:55: StaffDB database created
2020-12-24 16:18:55: StaffDB Step 2 - grant database level perms
\set ON_ERROR_STOP on
grant all privileges on database "StaffDB" to "XXXXXX";
GRANT
grant connect, temp on database "StaffDB" to "YYYYYY";
GRANT
grant connect, temp on database "StaffDB" to "ZZZZZZ";
GRANT
2020-12-24 16:18:55: StaffDB Step 3 - create StaffAdmin schema and objects
2020-12-24 16:18:55: Running /home/solid/DB/schema/postgres/StaffDB/staffadmin/boot_schema_StaffAdmin.inc
2020-12-24 16:18:55: Starting to boot the StaffDB.staffadmin schema
2020-12-24 16:18:55: StaffDB:staffadmin - Step 1 - create schema
2020-12-24 16:18:54: Step 0 - Create database users
2020-12-24 16:18:54: Creating StaffDB users
2020-12-24 16:18:55: OptimusPrime already exists
2020-12-24 16:18:55: Minerva already exists
2020-12-24 16:18:55: BKuserUP already exists
2020-12-24 16:18:55: StaffDB users complete
2020-12-24 16:18:55: StaffDB Step 1 Create StaffDB database
2020-12-24 16:18:55: Start of create_database function for database: StaffDB
2020-12-24 16:18:55: Creating the StaffDB Database
\set ON_ERROR_STOP on
CREATE DATABASE "StaffDB"
with owner "XXXXXX" ;
CREATE DATABASE
2020-12-24 16:18:55: StaffDB database created
2020-12-24 16:18:55: StaffDB Step 2 - grant database level perms
\set ON_ERROR_STOP on
grant all privileges on database "StaffDB" to "XXXXXX";
GRANT
grant connect, temp on database "StaffDB" to "YYYYYY";
GRANT
grant connect, temp on database "StaffDB" to "ZZZZZZ";
GRANT
2020-12-24 16:18:55: StaffDB Step 3 - create StaffAdmin schema and objects
2020-12-24 16:18:55: Running /home/solid/DB/schema/postgres/StaffDB/staffadmin/boot_schema_StaffAdmin.inc
2020-12-24 16:18:55: Starting to boot the StaffDB.staffadmin schema
2020-12-24 16:18:55: StaffDB:staffadmin - Step 1 - create schema
2020-12-24 16:18:55: Start of create_schema function for database: StaffDB
2020-12-24 16:18:55: Creating the StaffDB.staffadmin schema
\set ON_ERROR_STOP on
CREATE SCHEMA if not exists staffadmin
authorization "XXXXXX";
CREATE SCHEMA
2020-12-24 16:18:55: StaffDB.staffadmin schema created
\set ON_ERROR_STOP on
show search_path ;
search_path
-----------------
"$user", public
(1 row)
\set ON_ERROR_STOP on
alter role postgres in database "StaffDB" set search_path = 'staffadmin';
ALTER ROLE
\set ON_ERROR_STOP on
show search_path ;
search_path
-------------
staffadmin
(1 row)
2020-12-24 16:18:55: Creating the StaffDB.staffadmin schema
\set ON_ERROR_STOP on
CREATE SCHEMA if not exists staffadmin
authorization "XXXXXX";
CREATE SCHEMA
2020-12-24 16:18:55: StaffDB.staffadmin schema created
\set ON_ERROR_STOP on
show search_path ;
search_path
-----------------
"$user", public
(1 row)
\set ON_ERROR_STOP on
alter role postgres in database "StaffDB" set search_path = 'staffadmin';
ALTER ROLE
\set ON_ERROR_STOP on
show search_path ;
search_path
-------------
staffadmin
(1 row)
2020-12-24 16:18:55: StaffDB:staffadmin - Step 2 - grant schema privileges
2020-12-24 16:18:55: StaffDB:staffadmin - Step 3 - Create StaffAdmin Types
2020-12-24 16:18:55: Start of create_type function for StaffDB nully
2020-12-24 16:18:55: Creating the StaffDB nully type
\set ON_ERROR_STOP on
CREATE TYPE staffadmin.nully
as ENUM ('','Y') ;
CREATE TYPE
2020-12-24 16:18:55: StaffDB nully type created
2020-12-24 16:18:55: Start of create_type function for StaffDB staff_roll
2020-12-24 16:18:55: Creating the StaffDB staff_roll type
\set ON_ERROR_STOP on
CREATE TYPE staffadmin.staff_roll
as ENUM ('Not yet defined', 'Everything', 'Sys Admin', 'Developer', 'DBA', 'Security', 'Art & Apperance', 'Support', 'Manager') ;
CREATE TYPE
2020-12-24 16:18:55: StaffDB staff_roll type created
2020-12-24 16:18:55: Start of create_type function for StaffDB staff_status
2020-12-24 16:18:55: Creating the StaffDB staff_status type
\set ON_ERROR_STOP on
CREATE TYPE staffadmin.staff_status
as ENUM ('New since','Active since', 'Off-line until', 'Exited on' ) ;
CREATE TYPE
2020-12-24 16:18:55: StaffDB:staffadmin - Step 3 - Create StaffAdmin Types
2020-12-24 16:18:55: Start of create_type function for StaffDB nully
2020-12-24 16:18:55: Creating the StaffDB nully type
\set ON_ERROR_STOP on
CREATE TYPE staffadmin.nully
as ENUM ('','Y') ;
CREATE TYPE
2020-12-24 16:18:55: StaffDB nully type created
2020-12-24 16:18:55: Start of create_type function for StaffDB staff_roll
2020-12-24 16:18:55: Creating the StaffDB staff_roll type
\set ON_ERROR_STOP on
CREATE TYPE staffadmin.staff_roll
as ENUM ('Not yet defined', 'Everything', 'Sys Admin', 'Developer', 'DBA', 'Security', 'Art & Apperance', 'Support', 'Manager') ;
CREATE TYPE
2020-12-24 16:18:55: StaffDB staff_roll type created
2020-12-24 16:18:55: Start of create_type function for StaffDB staff_status
2020-12-24 16:18:55: Creating the StaffDB staff_status type
\set ON_ERROR_STOP on
CREATE TYPE staffadmin.staff_status
as ENUM ('New since','Active since', 'Off-line until', 'Exited on' ) ;
CREATE TYPE
2020-12-24 16:18:55: StaffDB staff_status type created
2020-12-24 16:18:55: StaffDB:staffadmin - Step 4 - Create Staff Table
2020-12-24 16:18:55: Starting create_table_staff.inc
2020-12-24 16:18:55: Creating the staffdb.staffadmin.staff table
\set ON_ERROR_STOP on
CREATE TABLE IF NOT EXISTS staffadmin.staff (
staff_id serial NOT NULL ,
shortname varCHAR(12) NOT null ,
fullname varCHAR(48) NOT null ,
created timeSTAMP NOT null ,
role staffadmin.staff_role NOT null ,
status staffadmin.staff_status NOT null ,
status_date DATE NOT null ,
email varCHAR(60) NOT null ,
email_verified staffadmin.nully null ,
login_cnt integer NOT null DEFAULT '0',
last_login timeSTAMP DEFAULT null );
psql:/tmp/psql_tmp.5133.sql:16: ERROR: type "staffadmin.staff_role" does not exist
LINE 9: role staffadmin.staff_role NOT null ,
^
2020-12-24 16:18:56: __ ERROR __ Statement Failure While creating StaffDB.staffadmin.staff table
2020-12-24 16:18:56: __ ERROR __ - Thu - Script bootstrap_StaffDB FAILED EXIT_CD=10
2020-12-24 16:18:55: StaffDB:staffadmin - Step 4 - Create Staff Table
2020-12-24 16:18:55: Starting create_table_staff.inc
2020-12-24 16:18:55: Creating the staffdb.staffadmin.staff table
\set ON_ERROR_STOP on
CREATE TABLE IF NOT EXISTS staffadmin.staff (
staff_id serial NOT NULL ,
shortname varCHAR(12) NOT null ,
fullname varCHAR(48) NOT null ,
created timeSTAMP NOT null ,
role staffadmin.staff_role NOT null ,
status staffadmin.staff_status NOT null ,
status_date DATE NOT null ,
email varCHAR(60) NOT null ,
email_verified staffadmin.nully null ,
login_cnt integer NOT null DEFAULT '0',
last_login timeSTAMP DEFAULT null );
psql:/tmp/psql_tmp.5133.sql:16: ERROR: type "staffadmin.staff_role" does not exist
LINE 9: role staffadmin.staff_role NOT null ,
^
2020-12-24 16:18:56: __ ERROR __ Statement Failure While creating StaffDB.staffadmin.staff table
2020-12-24 16:18:56: __ ERROR __ - Thu - Script bootstrap_StaffDB FAILED EXIT_CD=10
I get the same error if I remove the staffadmin schema specifier from the column lines in the create table statement.
role staff_role NOT null ,
status staff_status NOT null ,
status_date DATE NOT null ,
email varCHAR(60) NOT null ,
email_verified nully null ,
login_cnt integer NOT null DEFAULT '0',
last_login timeSTAMP DEFAULT null );
psql:/tmp/psql_tmp.10244.sql:16: ERROR: type "staff_role" does not exist
LINE 9: role staff_role NOT null ,
^
status staff_status NOT null ,
status_date DATE NOT null ,
email varCHAR(60) NOT null ,
email_verified nully null ,
login_cnt integer NOT null DEFAULT '0',
last_login timeSTAMP DEFAULT null );
psql:/tmp/psql_tmp.10244.sql:16: ERROR: type "staff_role" does not exist
LINE 9: role staff_role NOT null ,
^
В списке pgsql-general по дате отправления: