created type not found in create table

[Open _ <opensheart@xxxxxxxxx>]

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: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: 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 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

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 ,
                            ^