[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: created type not found in create table
From: Open _ <opensheart () yahoo ! com>
Date: 2020-12-24 17:20:29
Message-ID: 1957561924.2643508.1608830429291 () mail ! yahoo ! com
[Download RAW message or body]
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 "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 ,
^
[Attachment #3 (text/html)]
<html><head></head><body><div class="ydp61e7b8e0yahoo-style-wrap" \
style="font-family:bookman old style, new york, times, serif;font-size:16px;"><div \
dir="ltr" data-setdir="false">I'm using <div><div dir="ltr" data-setdir="false">I \
have <span>psql (PostgreSQL) 13.1 on centOS 7.1</span></div><div dir="ltr" \
data-setdir="false"><span>I do everything through layers of bash scripts and bash \
script functions.<br></span><div><span>So posting all the code would be \
huge.</span></div><div><br><span></span></div><div dir="ltr" data-setdir="false">The \
scripts run as postgres<br></div><div><br></div></div><div dir="ltr" \
data-setdir="false"><span>Each script step tests if that object already exists before \
creating<br></span></div><div dir="ltr" data-setdir="false"><span>Each statement is a \
separate call to psql</span></div><div dir="ltr" data-setdir="false"><span>So each \
statement is a separate session</span></div><div dir="ltr" \
data-setdir="false"><span><br></span></div><div dir="ltr" \
data-setdir="false"><span><br></span></div><div dir="ltr" \
data-setdir="false"><span>The steps are:</span></div><div dir="ltr" \
data-setdir="false"><span> create the database \
users<br></span></div></div></div><div dir="ltr" data-setdir="false"> created a \
database "StaffDB" (Yes with capitial letters because I want it that \
way)</div><div dir="ltr" data-setdir="false"> created a schema \
"staffadmin" (ok I gave in and used lowercase for all except DB \
names)</div><div dir="ltr" data-setdir="false"> set the search_path</div><div \
dir="ltr" data-setdir="false"> create 3 types</div><div dir="ltr" \
data-setdir="false"> create a table using those 3 types</div><div dir="ltr" \
data-setdir="false"> and it says type not found.</div><div \
dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">Here is \
the output:<br><div dir="ltr" data-setdir="false"><div dir="ltr" \
data-setdir="false">2020-12-24 16:18:54: - bootstrap_StaffDB is running as \
postgres<br>2020-12-24 16:18:54: Step 0 - Create database users<br>2020-12-24 \
16:18:54: Creating StaffDB users<br>2020-12-24 16:18:55: OptimusPrime already \
exists<br>2020-12-24 16:18:55: Minerva already exists<br>2020-12-24 \
16:18:55: BKuserUP already exists<br>2020-12-24 16:18:55: StaffDB users \
complete<br>2020-12-24 16:18:55: StaffDB Step 1 Create StaffDB database<br>2020-12-24 \
16:18:55: Start of create_database function for database: \
StaffDB<br>2020-12-24 16:18:55: Creating the StaffDB \
Database<br><br> \set ON_ERROR_STOP on<br> CREATE DATABASE \
"StaffDB"<br> with owner "XXXXXX" ;<br> CREATE \
DATABASE<br><br>2020-12-24 16:18:55: StaffDB database \
created<br>2020-12-24 16:18:55: StaffDB Step 2 - grant database level \
perms<br><br> \set ON_ERROR_STOP on<br> grant all privileges \
on database "StaffDB" to "XXXXXX";<br> GRANT<br> grant \
connect, temp on database "StaffDB" to "YYYYYY";<br> \
GRANT<br> grant connect, temp on database "StaffDB" to \
"ZZZZZZ";<br> GRANT<br><br>2020-12-24 16:18:55: StaffDB Step 3 - create \
StaffAdmin schema and objects<br>2020-12-24 16:18:55: Running \
/home/solid/DB/schema/postgres/StaffDB/staffadmin/boot_schema_StaffAdmin.inc<br>2020-12-24 \
16:18:55: Starting to boot the StaffDB.staffadmin schema<br>2020-12-24 \
16:18:55: StaffDB:staffadmin - Step 1 - create schema<br><div>2020-12-24 \
16:18:55: Start of create_schema function for database: \
StaffDB<br>2020-12-24 16:18:55: Creating the StaffDB.staffadmin \
schema<br><br> \set ON_ERROR_STOP on<br> CREATE SCHEMA if not \
exists staffadmin<br> \
authorization "XXXXXX";<br> CREATE SCHEMA<br><br>2020-12-24 \
16:18:55: StaffDB.staffadmin schema created<br><br> \
\set ON_ERROR_STOP on<br> show search_path \
;<br> search_path<br> \
-----------------<br> "$user", public<br> (1 \
row)<br><br><br> \set ON_ERROR_STOP on<br> alter \
role postgres in database "StaffDB" set search_path = 'staffadmin';<br> \
ALTER ROLE<br><br><br> \set ON_ERROR_STOP on<br> \
show search_path ;<br> search_path<br> \
-------------<br> staffadmin<br> (1 row)<br><br></div> \
<div dir="ltr" data-setdir="false">2020-12-24 16:18:55: StaffDB:staffadmin - Step 2 - \
grant schema privileges<br>2020-12-24 16:18:55: StaffDB:staffadmin - Step 3 - Create \
StaffAdmin Types<br>2020-12-24 16:18:55: Start of create_type function \
for StaffDB nully<br>2020-12-24 16:18:55: Creating the StaffDB \
nully type<br><br> \set ON_ERROR_STOP on<br> CREATE TYPE \
staffadmin.nully<br> as ENUM ('','Y') \
;<br> CREATE TYPE<br><br>2020-12-24 16:18:55: StaffDB \
nully type created<br>2020-12-24 16:18:55: Start of create_type function \
for StaffDB staff_roll<br>2020-12-24 16:18:55: Creating the StaffDB \
staff_roll type<br><br> \set ON_ERROR_STOP on<br> CREATE TYPE \
staffadmin.staff_roll<br> as ENUM ('Not yet \
defined', 'Everything', 'Sys Admin', 'Developer', 'DBA', 'Security', 'Art & \
Apperance', 'Support', 'Manager') ;<br> CREATE TYPE<br><br>2020-12-24 \
16:18:55: StaffDB staff_roll type created<br>2020-12-24 \
16:18:55: Start of create_type function for StaffDB \
staff_status<br>2020-12-24 16:18:55: Creating the StaffDB \
staff_status type<br><br> \set ON_ERROR_STOP on<br> CREATE \
TYPE staffadmin.staff_status<br> as ENUM ('New \
since','Active since', 'Off-line until', 'Exited on' ) ;<br> CREATE \
TYPE<br><br><div>2020-12-24 16:18:55: StaffDB staff_status type \
created<br>2020-12-24 16:18:55: StaffDB:staffadmin - Step 4 - Create Staff \
Table<br>2020-12-24 16:18:55: Starting create_table_staff.inc<br>2020-12-24 \
16:18:55: Creating the staffdb.staffadmin.staff table<br><br> \
\set ON_ERROR_STOP on<br><font face="courier new, courier, monaco, monospace, \
sans-serif"> CREATE TABLE IF NOT EXISTS staffadmin.staff \
(<br> \
staff_id \
serial NOT NULL \
,<br> \
shortname \
varCHAR(12) NOT null \
,<br> \
fullname \
varCHAR(48) NOT null \
,<br> created \
timeSTAMP \
NOT null ,<br> \
role \
staffadmin.staff_role NOT null ,<br> \
status staffadmin.staff_status NOT \
null ,<br> \
status_date \
DATE \
NOT null ,<br> \
email \
varCHAR(60) NOT null \
,<br> email_verified \
staffadmin.nully \
null ,<br> \
login_cnt \
integer \
NOT null DEFAULT '0',<br> \
last_login \
timeSTAMP DEFAULT null \
);</font><br><b> psql:/tmp/psql_tmp.5133.sql:16: ERROR: type \
"staffadmin.staff_role" does not exist<br></b><font face="courier new, courier, \
monaco, monospace, sans-serif"> LINE 9: \
role \
staffadmin.staff_role NOT null \
,<br> &nb \
sp; \
^</font><br><br>2020-12-24 16:18:56: __ ERROR __ Statement Failure While creating \
StaffDB.staffadmin.staff table<br>2020-12-24 16:18:56: __ ERROR __ - Thu - \
Script bootstrap_StaffDB FAILED \
EXIT_CD=10<br><br></div><div><br></div><div dir="ltr" data-setdir="false">I get the \
same error if I remove the staffadmin schema specifier from the column lines in the \
create table statement.</div><div dir="ltr" data-setdir="false"><br></div><div \
dir="ltr" data-setdir="false"><span></span><div><font face="courier new, courier, \
monaco, monospace, sans-serif"> \
role staff_role \
NOT null ,<br> \
status \
staff_status NOT \
null ,<br> \
status_date \
DATE \
NOT null ,<br> \
email \
varCHAR(60) NOT null \
,<br> email_verified \
nully \
null ,<br> \
login_cnt \
integer \
NOT null DEFAULT '0',<br> \
last_login \
timeSTAMP DEFAULT null \
);<br> psql:/tmp/psql_tmp.10244.sql:16: ERROR: type "staff_role" \
does not exist<br> LINE 9: \
role \
staff_role NOT null \
,<br> &nb \
sp; \
^</font><br><br></div><div><br></div></div></div><div><br></div></div></div></div></div></body></html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic