How to Check if a User-Defined Type Already Exists in PostgreSQL
- Create a User-Defined Type in PostgreSQL
- Check if a User-Defined Type Already Exists and Drop if It Exists
We can check using the exists query whether the user-defined type is already defined or not. Also, we can do another thing: drop the type if it exists.
Create a User-Defined Type in PostgreSQL
User-defined type is more like the struct from C and C++. We can create a type of data that contain different types of data.
Here’s an example from the official documentation of Postgres:
typedef struct Complex {
double x;
double y;
} Complex;
Let’s say we want to store a complex number with a real and unreal part. These needed to be stored accordingly in x and y.
We can use the data type Complex rather than adding two columns in the table.
Here we have the following table:
create table Users (
id INT,
name VARCHAR(50),
gender VARCHAR(50),
age INT
);
-- Perform Some insert command
insert into Users values (1, 'Tymothy Blezard', 'Polygender', 21);
insert into Users values (2, 'Carlynne Renak', 'Female', 30);
insert into Users values (3, 'Hewitt Mylechreest', 'Male', 26);
insert into Users values (4, 'Griswold Geer', 'Male', 30);
insert into Users values (5, 'Kelley Vines', 'Female', 22);
insert into Users values (6, 'Ted Haizelden', 'Male', 27);
insert into Users values (7, 'Bonita Glewe', 'Female', 27);
insert into Users values (8, 'Edgar Hambers', 'Male', 22);
insert into Users values (9, 'Johnathan Dwelley', 'Male', 22);
insert into Users values (10, 'Fritz Rylance', 'Male', 22);
-- Insertion
SELECT * from Users;
Output:
id | name | gender | age
----+--------------------+------------+-----
1 | Tymothy Blezard | Polygender | 21
2 | Carlynne Renak | Female | 30
3 | Hewitt Mylechreest | Male | 26
4 | Griswold Geer | Male | 30
5 | Kelley Vines | Female | 22
6 | Ted Haizelden | Male | 27
7 | Bonita Glewe | Female | 27
8 | Edgar Hambers | Male | 22
9 | Johnathan Dwelley | Male | 22
10 | Fritz Rylance | Male | 22
(10 rows)
Now we will create a type Person. Let’s say we want to have several values from the users table; we can use the Person as the function’s return type.
Creating the type Person:
CREATE TYPE Person AS (
id INT,
name VARCHAR,
age INT
);
Now, let’s create a function that will take the user id as the input and return the id, name, and age. It will return the Person.
CREATE OR REPLACE FUNCTION get_person (input_id int)
RETURNS Person AS
$$
SELECT
id,
name,
age
FROM
users
WHERE
users.id = input_id ;
$$
LANGUAGE SQL;
id | name | age
----+---------------+-----
10 | Fritz Rylance | 22
(1 row)
Check if a User-Defined Type Already Exists and Drop if It Exists
In Postgres, we have the Person type because above we’ve created. Now, we will drop the type if it exists.
Here’s one method:
DROP TYPE IF EXISTS Person;
Error:
ERROR: cannot drop type person because other objects depend on it
DETAIL: function get_person(integer) depends on type person
HINT: Use DROP ... CASCADE to drop the dependent objects too.
Here it showed an error because another function is using it. So, we need to perform a cascade delete.
So, this indicates that the type is in Postgres, and it’s been used.
Another method of detecting if the type exists or not is to query over the pg_type table.
select exists (select 1 from pg_type where typname = 'person');
This will return true if there’s any type created by the user named Person. Here’s the Output:
exists
--------
t
(1 row)
To know more about the PostgreSQL user-defined types, read this official documentation.
