serial type produce only even or odd numbers in postgresql

Question

I want set some constraint to the serial type,it only produce even or odd numbers.

Answer 1

SERIAL is a syntax sugar around creating and using sequences.

So you could do it all manually and create a special type of sequence that suits your needs:

CREATE SEQUENCE tablename_colname_seq INCREMENT BY 2 START WITH 2;

CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq');

ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Or if you already have a table and a SERIAL column you could change the underlying sequence:

ALTER SEQUENCE tablename_colname_seq INCREMENT BY 2;

The name of the underlying sequence could be retrieved by "describing" the table using psql:

\d tablename

Answer 2

Simply, set your serial to increment by 2, and to start on either 1 or 2 for producing either odd or even number:

Odd

CREATE SEQUENCE odd_seq INCREMENT BY 2 START WITH 1;

Even

CREATE SEQUENCE even_seq INCREMENT BY 2 START WITH 2;