How do I create an custom range type in PostgreSQL for example from 1 to 100?

Question

I have a column that I want to accept values from 1 to 100, is it possible ? I know this is accomplished with functions, but I'm looking for a simple solution as mentioned above.

Answer 1

You can use a check constraint:

create table foo
( 
   some_value int not null check (some_value between 1 and 100)
);

Alternatively if you need that in multiple tables, a domain might be useful

create domain one_to_hundred
  as integer not null
  check (value between 1 and 100);

create table foo
( 
   some_value one_to_hundred
);

Answer 2

Use a check constraint on your range:

CREATE TABLE foo (
  bar int4range NOT NULL,
  CONSTRAINT foo_bar_check CHECK (bar <@ '[0,100]'::int4range)
)
;

INSERT INTO foo(bar) VALUES('[20,30]');
INSERT INTO foo(bar) VALUES('(30,200]'); -- fail