CODEWITHSUNDEEP
postgresqlsqldatatypes
tedvangageldonk
tedvangageldonk

Reputation: 35

How to store different types in a column?

I am a novice programmer currently working on how to store PostgreSQL column types in a table column. Specifically, I would like to create a table with a column named 'type' that stores one of the following types: boolean, integer, text and enum. Conceptually, however, I would like a user to store which values (s)he would like to use. An integer, a piece of text, or a list of options should all be possible. See the sample table below.

id    |    type                             | default value
---------------------------------------------------------------
1     |    integer                          |        5
2     |    boolean                          |        
3     |    enum("red", "blue", "yellow")    |       red

The last case confuses me. With my limited knowledge I know that creating a table with a column named type with type enum("boolean", "integer", "text", "enum") is possible, but I don't see how that allows for the last case in the sample table. It seems like this only allows for a type enum, but in that case I cannot specify a default value, such as red, because red is not listed in the options of the enum type.

I am left with two questions:

  1. On creating the table, what type do I give the column named type?
  2. How do I store the third record in the sample table, such that I can also store a default value and such that I can still check whether the default value is actually appropriate for the chosen type?

Any answer or help is greatly appreciated. Thanks!

Upvotes: 0

Views: 1713

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

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.

Upvotes: 1

Related Questions