created type not found in create table

[Open _]

I'm using I have psql (PostgreSQL) 13.1 on centOS 7.1I 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 psqlSo 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 "XX" ;
   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 "XX";
   GRANT
   grant connect, temp  on database "StaffDB" to "YY";
   GRANT
   grant connect, temp  on database "StaffDB" to "ZZ";
   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 "XX";
   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 
St

Re: created type not found in create table

[Open _]

 oh, duh..  works now...  Thanks for the second pair of eyes.
I thought I had ruled that out by doing a search on the type name and making 
sure it was the same all places... (which is a long ingrained habit). Must have 
searched on one of the other type names.
This is my first use of such a type in a postgres table,  thought it was 
something I didn't understand yet.



On Thursday, December 24, 2020, 12:25:29 PM EST, Tom Lane 
 wrote:  
 
 Open _  writes:
>    CREATE TYPE staffadmin.staff_roll
>    as ENUM ('Not yet defined', 'Everything', 'Sys Admin', 'Developer', 
> 'DBA', 'Security', 'Art & Apperance', 'Support', 'Manager') ;
>    CREATE TYPE

> ...

>    psql:/tmp/psql_tmp.5133.sql:16: ERROR:  type "staffadmin.staff_role" does 
> not exist
>    LINE 9:   role   staffadmin.staff_role   NOT null ,
>     ^


Uh, you didn't spell it that way before.

            regards, tom lane  

ltree and PHP

[Open _]

I've created the ltree extensioncreated a table with an ltree column called 
ltree_path
In php I can access the table, insert, update, delete.but ltree operators don't 
work.
select count() from schema.table 
where 'A.B.C' @> ltree_path;
gives me a "could not determine polymorphic type anyrange because input has 
type unknown'
The same statement works just fine in psql, but not in PHP
If I change the statement to:

select count() from schema.table 
where cast('A.B.C' as schema.ltree) @> ltree_path;
I get a different error: "operator does not exist:"
pointing to the @>
Is there someplace to 'create' or include ltree in PHP ?

Understaning postgresql instance vs database

[Open _]

In Informix, the instance is everything.   Buffer pools, transaction logging, 
backup and restores and all monitoring is done at the instance level
databases are just logical collections of tables in an instance.
In DB2 there is some instance configuration, but most everything is at the 
database level.   Buffer pools, logging, backups and restores are done at the 
database level.   Process load monitoring might be done at the instance level 
with db2top.
It seems like Postgres is like DB2, there are some things at the instance level 
and some at the database level.
But I'm having a hard time sorting out what is where.
 Like what in PostgreSQL's statistics collector is an instance statistic verses 
a database statistic. 

I found a pgtop utility but it seems to require a database parameter
So are all stats database specific?or is the top info instance stats and the 
list portion database specific?