CODEWITHSUNDEEP
postgresql
SyntaxJunkie
SyntaxJunkie

Reputation: 5

Not sure why I am getting this result in PostgreSQL

I am trying to convert a MySQL database to PostgreSQL and I created this

CREATE TYPE location_m as enum('England','Japan','France','Usa','China','Canada');

CREATE TABLE airport (
  id int NOT NULL,
  owner varchar(40) NOT NULL DEFAULT '',
  location location_m NOT NULL DEFAULT 'England',
  travel_prices varchar(100) NOT NULL DEFAULT '100-100-100-100-100-100',
  profit varchar(100) NOT NULL DEFAULT '0-0-0-0-0-0'
) ;

INSERT INTO airport (id, owner, location, travel_prices, profit) VALUES
(1, 'Mafia', 'Japan', '1000-1000-1000-1000-1000-1000', '0-18000-34000-15500-11000-13000');

What I run the insert it returns with this

psql:main_db.sql:43: ERROR: type "location_m" already exists

I tried looking it up but can't really find anything. I don't understand why it is saying it already exists.

I thought I was doing the enum correctly based on the docs and the other Stack Overflow posts.

Upvotes: 0

Views: 1480

Answers (2)

Belayer
Belayer

Reputation: 14936

A far better idea, use 2 scripts. Put your creates in 1 script, it gets run 1 time. The other script contains only the Insert. If needed delete and re-run as many times as needed. Further IMHO never use 'if exists' on ddl, keep in mind that errors can be your friend. Consider the following scenario.
Your script:

drop type if exists  location_m cascade;
create type location_m as enum('England','Japan','France','Usa','China','Canada');

drop table if exists  airport ;
create table airport (
  id int not null,
  owner varchar(40) not null default '',
  location location_m not null default 'England',
  travel_prices varchar(100) not null default '100-100-100-100-100-100',
  profit varchar(100) not null default '0-0-0-0-0-0'
) ;

insert into airport (id, owner, location, travel_prices, profit) VALUES
(1, 'Mafia', 'Japan', '1000-1000-1000-1000-1000-1000', '0-18000-34000-15500-11000-13000');

The another developer in your organization working on a different project, but using the same naming conventions comes along with their script:

drop type if exists  location_m cascade;
create type location_m as enum('USA', 'Canada', 'Brazil');
 
 create table hotel( id integer generated always as identity
                   , name      text  
                   , location  location_m not null default 'Canada'
                   , corporate_rate money
                   ) ;

Now what does the following give you?

select * 
  from airport
  where location = 'Japan';

See demo here. So what happened? Have fun trying to find this why this happened. Oh well never mind just rerun your script. But soon you are both posting to SO wanting to know what Postgres bug in causing your problem - even though the problem is not on the Postgres side at all.

Upvotes: 0

Bergi
Bergi

Reputation: 664969

That is my entire file so far, except I have DROP TABLE IF EXISTS airport; at the beginning.

If you're doing that, you will also want to place a DROP TYPE IF EXISTS location_m; at the beginning of the script. (Possibly with the CASCADE option if it's already used in a table definition, or make sure to drop all such tables first).

Alternatively, have a look at Check if a user-defined type already exists in PostgreSQL for various other workarounds for CREATE OR REPLACE TYPE …, although if you are working with a migration script that will run only once and expect an empty database, it's probably no harm to just drop and recreate them.

Upvotes: 1

Related Questions