CODEWITHSUNDEEP
sqldatabasepostgresql
user6902061
user6902061

Reputation:

Different data types in column

I have a table that stores some properties of objects. Each property has it's own row in the table, and it references the object's id from another table. Properties are currently of type boolean(0), numeric(1) or free text(2) and they store a single value.

The question is, how do I determine which row is of which type? Should the property table have a column called type and then store the value in a separate TEXT column or possibly store the value as a JSON object? Is there a single good solution to this? I believe my worry here is about preserving the data type, which could be difficult to handle if the data is stored in a TEXT column.

My gut tells me that storing the type and value in an JSON object would be the way to go here.

Object table:

  id | name
-----+-----
 123 | abc

Idea for property table:

 id | object_id | type | data
----+-----------+------+-------
  1 | 123       | 1    | 123.12
  2 | 123       | 0    | f

Other idea for property table:

 id | object_id | type | data
----+-----------+-----------------------
  1 | 112       | 1    | {value:123.12}
  2 | 112       | 0    | {value:false}

Upvotes: 0

Views: 1319

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246318

Every column must have a determined type in PostgreSQL, so you cannot have a field that can hold different types.

Using JSON won't help much if you need the attributes within the database, because you can only get text from JSON values.

I would use three tables for that, one for each type:

CREATE TABLE bool_property (
   id integer PRIMARY KEY,
   object_id integer NOT NULL REFERENCES object(id),
   data boolean NOT NULL
);

CREATE TABLE num_property (
   id integer PRIMARY KEY,
   object_id integer NOT NULL REFERENCES object(id),
   data numeric NOT NULL
);

CREATE TABLE text_property (
   id integer PRIMARY KEY,
   object_id integer NOT NULL REFERENCES object(id),
   data text NOT NULL
);

Alternatively, if you want a single table, use

CREATE TABLE property (
   id integer PRIMARY KEY,
   object_id integer NOT NULL REFERENCES object(id),
   datatype regtype NOT NULL,
   bool_data boolean,
   num_data numeric,
   text_data text,
   CHECK (CASE datatype
             WHEN 'boolean'::regtype
             THEN bool_data IS NOT NULL
                AND num_data IS NULL
                AND text_data IS NULL
             WHEN 'numeric'::regtype
             THEN bool_data IS NULL
                AND num_data IS NOT NULL
                AND text_data IS NULL
             WHEN 'text'::regtype
             THEN bool_data IS NULL
                AND num_data IS NULL
                AND text_data IS NOT NULL
             ELSE FALSE
          END
         )
);

But that might be more difficult in joins.

Upvotes: 1

Related Questions