CODEWITHSUNDEEP
postgresqltypescreate-table
Sebastian Widz
Sebastian Widz

Reputation: 2072

How to create regtype column in postgreSQL that includes length and precision parameter

I would like to store some meta information in postgreSQL database. This requires to store the column type information. I am aware of regtype type but it does not store information about length or precision.

How can I achieve this. I could use a TEXT column instead but then I would need to take care of all the validations and referential integrity. Is there more convenient way o achieve this?

Below I present example code.

CREATE TABLE foo
(name TEXT,
sql_type regtype);

INSERT INTO foo
VALUES('my_field_1', 'character varying'::regtype);

INSERT INTO foo
VALUES('my_field_2', 'VARCHAR(50)'::regtype);

INSERT INTO foo
VALUES('my_field_3', 'NUMERIC(32,16)'::regtype);

SELECT * from foo;

The result is as follows:

name             sql_type
text             regtype
-------------------------------------
my_field_1      character varying
my_field_2      character varying
my_field_3      numeric

Expected result:

name             sql_type
text             regtype
-------------------------------------
my_field_1      character varying  <-- I won't need such cases
my_field_2      character varying(50)
my_field_3      numeric(32,16)

I am currently using PostgreSQL 9.6

Upvotes: 5

Views: 2044

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246308

The type regclass is a convenience type that internally is just the type's numeric object identifier, so it does not contain information about scale, precision, length and other type modifiers.

I would store the type together with its modifiers as text.

But if you want, you can also do it like this:

CREATE TABLE coldef (
   column_name              name     NOT NULL,
   data_type                regtype  NOT NULL,
   numeric_precision        smallint
      CHECK (numeric_precision IS NULL
             OR numeric_precision BETWEEN 1 AND 1000),
   numeric_scale            smallint
      CHECK (numeric_scale IS NULL
             OR numeric_scale BETWEEN 0 AND numeric_precision),
   character_maximum_length integer
      CHECK (character_maximum_length IS NULL
             OR character_maximum_length BETWEEN 1 AND 10485760),
   datetime_precision       smallint
      CHECK (datetime_precision IS NULL
             OR datetime_precision BETWEEN 0 AND 6),
   interval_precision       smallint
      CHECK (interval_precision IS NULL
             OR interval_precision BETWEEN 0 AND 6)
);

You can add more check constraints to make sure that there are no forbidden combinations, like a character varying with a numeric precision, or that numeric_precision must be NOT NULL when numeric_scale is.

Get inspired by the catalog table information_schema.columns that contains the column metadata.

Upvotes: 4

Related Questions