Create a Table of Two Types in Postgresql

Create a table of two types in PostgreSQL

You cannot make prod_id the primary key of table1 because the only columns are the two composite types info and movie. You cannot access the base types of these composite types in a PRIMARY KEY clause.

What you were trying to do works with a pk constraint on info or movie.

Except, it's probably not what you were looking for, which is not possible this way.

You could implement something like this with ...

Inheritance

Here you can inherit from multiple parent tables (substitute for your types). Example:

CREATE TABLE info (
  prod_id integer
 ,category integer
);

CREATE TABLE movie (
   title text
  ,actor text
  ,price float
);

CREATE  TABLE movie_info (
   PRIMARY KEY(prod_id)             -- now we can use the base column!
)
INHERITS (info, movie);

INSERT INTO movie_info (prod_id, category, title, actor, price)
VALUES (1, 2, 'who donnit?', 'James Dean', '15.90');

SELECT * FROM movie_info;

-> SQLfiddle demonstrating both.

Be sure to read about limitations of inheritance in the manual.

Sane way to store different data types within same column in postgres?

You've basically got two choices:

Option 1: A sparse table

Have one column for each data type, but only use the column that matches that data type you want to store. Of course this leads to most columns being null - a waste of space, but the purists like it because of the strong typing. It's a bit clunky having to check each column for null to figure out which datatype applies. Also, too bad if you actually want to store a null - then you must chose a specific value that "means null" - more clunkiness.

Option 2: Two columns - one for content, one for type

Everything can be expressed as text, so have a text column for the value, and another column (int or text) for the type, so your app code can restore the correct value in the correct type object. Good things are you don't have lots of nulls, but importantly you can easily extend the types to something beyond SQL data types to application classes by storing their value as json and their type as the class name.

I have used option 2 several times in my career and it was always very successful.

PostgreSQL (Create Table)

yes ,numeric data type allows negative values .

Postgresql: how to create types in postgresql

The correct way to do this, is to use a lookup table and a foreign key:

create table status
(
   id   integer primary key,
   name text not null 
);

insert into status (id, name) 
values
  (1, 'Completed'),
  (2, 'Pending'),
  (3, 'Failed'),
  (4, 'Created');

create table some_table
(
   id integer primary key, 
   status_id integer not null references status
);

That is the most flexible way to handle this in a relational database.

If you know that you will hardly ever change those values, you can use a check constraint:

create table some_table
(
   id integer primary key, 
   status text not null, 
   constraint check_status 
       status in ('Completed', 'Pending', 'Failed', 'Created')
);

This has the disadvantage that you are storing the same values over and over again, so the size of the table will be bigger compared to the foreign key solution.

The third option is to use an enum type

create type status_type AS ENUM (''Completed', 'Pending', 'Failed', 'Created');

Then use that type in the table:

create table some_table
(
   id integer primary key, 
   status status_type not null
);

This has a similar storage requirement as the foreign key solution but displays the status as "clear text".

Implementing UML classes in postgresql: create type vs create table

The model

This diagram is incorrect. It says that a Team is composed of Equipments that will be deleted if the Team gets deleted. Remove the black diamond and it'll be fine.

By the way, the arrow is not wrong, but it is not necessary if your UML class diagram is meant for a relational model: the implementation of an association with an RDBMS will always make it bidirectional.

The database

A user defined data type is an SQL features that allows to create a composite type made of several elements. The goal is that you can use the newly created types like the build-in types.

If you create two user defined types in postgresql for your model, you will therefore have anyway to create two tables each with a column of the given type (more explanations here). So there is no decisive advantage to opt for SQL types for implementing UML classes.

The creation of types is mostly advisable in SQL if you are working with value objects. Value objects have no identity and are solely defined by the values they carry. They are generally as type of several attributes in the same or in different classes/tables.

By the way, in an UML model, you should in principle represent value types with «datatype» classifiers. But some people model such value types with normal classes and it's a topic of discussion.

A typical example of value object in a business application is a CurrencyAmount that would be composed of a monetary value and a currency code.

Creating a 2-way relationship in PostgreSQL table

Let's say your query to get a one-way relationship looks like this:

SELECT room_uuid AS left_uuid, house_the_room_is_in_uuid AS right_uuid
FROM rooms
WHERE house_the_room_is_in_uuid IS NOT NULL
AND is_active

All you need to get the reverse relationship is to put the list in the other order; the rest of the query doesn't need to change, however complex it is:

SELECT house_the_room_is_in_uuid AS left_uuid, room_uuid AS right_uuid
FROM rooms
WHERE house_the_room_is_in_uuid IS NOT NULL
AND is_active

Both of those will be valid as queries to insert into a table with two UUID columns:

CREATE TABLE my_lookup_table (left_uuid UUID, right_uuid UUID);

INSERT INTO my_lookup_table (left_uuid, right_uuid)
SELECT ... -- either of the above

To combine them, either insert each into the same table in turn, or use a UNION to create one result set with both sets of rows:

SELECT room_uuid AS left_uuid, house_the_room_is_in_uuid AS right_uuid
FROM rooms
WHERE is_in_house_uuid IS NOT NULL
AND is_active

UNION

SELECT house_the_room_is_in_uuid AS left_uuid, room_uuid AS right_uuid
FROM rooms
WHERE is_in_house_uuid IS NOT NULL
AND is_active

All that's required for a union is that the queries have the same number and type of columns. The names (if relevant at all) come from the first query, but I find it more readable if you include the aliases on both.

Since the result of that UNION is itself just a two-column result set, it can be used with the same INSERT statement as before. That would allow you to insert into the table even if it had a self-referencing foreign key constraint as discussed here:

ALTER TABLE my_lookup_table ADD CONSTRAINT 
    my_lookup_table_combinations_must_be_unique
    UNIQUE (left_uuid, right_uuid);

ALTER TABLE my_lookup_table ADD CONSTRAINT 
    my_lookup_table_must_have_rows_both_ways_around
    FOREIGN KEY (right_uuid, left_uuid)
    REFERENCES my_lookup_table (left_uuid, right_uuid);

If you tried to insert just one set of rows, this would fail, but with the UNION, by the end of the statement/transaction, each row is in the table both ways around, so the constraint is met.

storing varying data types in a (postgresql) database

My preferred way is single table with both key and value being varchar and/or text.

The only big disadvantage of table-per-type in that comment I can think of is that it's hard to constraint the uniqueness of the key in pair with the entity this key-value entry belongs to.

Table inheritance (but still with table-per-type) would be another way to go and IMHO it's brutally type-safe and compliant with postgres and ORM concepts:

CREATE TABLE key_values(user_id integer references users(id), key varchar(255), type varchar(255), CONSTRAINT UNIQUE(user_id, key));
CREATE TABLE key_values_int(value integer) INHERITS (key_values);
CREATE TABLE key_values_string(value varchar(255)) INHERITS (key_values);
and so on...

This perfectly maps to JPA.

How to store different types in a column?

I think you are confusing/mixing a lot of different things.

Regarding enum type

You can declare a column of a table to be of enumerated type. For this you need to define the type:

CREATE TYPE my_color_enum AS ENUM ( 'red', 'blue', 'yellow' );

Then use it in table just like any other type:

CREATE TABLE test ( column1 my_color_enum );

If you need to assign a default value to column1 it only lets you define a default value that exists in your ENUM type, so for example while this is valid:

CREATE TABLE test ( column1 my_color_enum default 'red');

This is not and will yield an error:

CREATE TABLE test ( column1 my_color_enum default 'green' );

Because "green" value is not present in your type. The error message will look something like this (this is a loose translation, not exact error message):

Invalid input value for enum my_color_enum: "green"

Storing different datatypes in one column

This seems like a bad design, but if you really need it, and would rather avoid text datatype and then casting back and forth, you could use json, jsonb and so forth...

It will come to bite you if you decide to have a lookup table which stores data types.

---

Related Topics