A simple restriction of the domain of the SQL data type by a constraint is the most natural way. You have two options
- adding a constraint to the column of your table(s) in question
- creating a domain for each data type you want to be tailored to fit your given limitations of the programming language
An example of the first options looks like
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK (column_name > -128 AND column_name < 127);
(change the table, constraint and column name as needed, and adjust the range of the checked bounds)
The second option is a two step process. First add a new domain to your DB (in this example the domain type is named c_byte8) with the command
CREATE DOMAIN c_byte8 NUMERIC CONSTRAINT c_byte8_constraint
CHECK (VALUE > -128 AND VALUE < 127);
Second create your table with the appropriate types, i.g. c_byte8 instead of NUMERIC as the attribute's type. E.g.
CREATE TABLE test_table_1 (id NUMERIC, byte_value c_byte8);
Please keep in mind that the first solution will require you to add constraints to each attribute you have defined, or you will define in the future. It makes your DB-create script less readable, because of the repetition of the same constraint condition every time you use the restricted domain. A second downside of this approach is, that you can easily miss some columns of your schema when you add those constraints to an existing schema.
An upside of the first solution is that you can extend an existing schema without much hassle.
As for the second approach, the plus is a more readable DB creation script, as is the same with the table definitions of a running production DB, but changing an existing production DB is more complicated.
The documentation states the size of datatypes here.
0 (the number) is stored as 1 byte. Other single digit numbers will be stored in 2 bytes (one for the exponent, one for the mantissa). You can test this yourself by creating a test table and using the VSIZE()
function on test data (doc link).
A CHAR(n)
will be stored in n
bytes.
I'd always store numbers in a NUMBER
. Unless you're dealing with trillions of rows, the space saving will not be significant enough.
Best Answer
The default default value for any new table column is the default value of the data type.
And the default default value for data types is
NULL
- which is the case for all basic data types in Postgres. But any valid value is allowed for custom types or domains.The manual on
CREATE TYPE
:The manual on
CREATE DOMAIN
:You only use the
DEFAULT
clause for columns inCREATE TABLE
to specify a different default. Else, you do nothing.like you display in the question is not valid in PostgresWITH DEFAULT
To reset a column default to the default default of the data type (typically
NULL
), drop the specified default value of the column.The manual on
ALTER TABLE
:NOT NULL
constraints are related but completely independent. A column definedNOT NULL
and with no customDEFAULT
(and no not-null default for the type) requires you to provide a not-null value for everyINSERT
.Aside: you probably do not want to use the data type
char(12)
in Postgres.