DataTypes in PostgreSQL

It's quit a different world of PostgreSQL from MySQL. Before I began to use PostgreSQL, I thought all the relational DB are the same, just be familiar with ANSI SQL, and everything will be done. PostgreSQL proved me that it's a ludicrous thought, as an advance object-relational DB, PostgreSQL has guide me a new idea of DB world. It's not just a dull warehouse,where I put and get, but an amazing technology that with so many smart and fashion thoughts.

I learned two new tips of PostgreSQL's datatype today:

First is a notational convenience for unique identifier columns. In other relational DB, I am so familiar with the command

CREATE TABLE sometable (some_id INT(8) AUTO_INCREMENT, PRIMARY KEY(some_id));

but it was replaced by a notational type -- SERIAL or BIGSERIAL, just like the serialization in Java :)

CREATE TABLE sometable (some_id SERIAL, PRIMARY KEY(some_id));

then it will equal to:

CREATE TABLE sometable (some_id integer NOT NULL DEFAULT nextval('sometable_some_id::regclass')
, PRIMARY KEY(some_id));

Click here to see more.

Another tip is more useful, it's about the enum type in PostgreSQL, (MySQL support Enum, as I know, but it was still convenient to simulate enum type in PostgreSQL):
Here is a good article about it.

Three methods available:
Both 1st and 2nd methods use check constraint to the field, while 3rd use foreign key
Both 2nd and 3rd methods define a new DataType.
As to me, I prefered 2nd.

1st:

CREATE TABLE average_temperature
(
 year       INTEGER,
 temp       REAL,
 season     TEXT (CHECK season IN ('spring', 'summer', 'autumn', 'winter'))
);

2nd:

CREATE DOMAIN season_type
AS TEXT
CHECK (VALUE IN ('spring', 'summer', 'autumn', 'winter'));

CREATE TABLE average_temperature
(
 year    INTEGER,
 temp    REAL,
 season  season_type
);

3rd: (since every value based on an integer key, it will be able to sort and compare values)

CREATE TABLE season_lookup
(
 code  INT PRIMARY KEY,
 value TEXT NOT NULL
);
INSERT INTO season_lookup VALUES(1,'spring');
INSERT INTO season_lookup VALUES(2,'summer');
INSERT INTO season_lookup VALUES(3,'autumn');
INSERT INTO season_lookup VALUES(4,'winter');
CREATE TABLE average_temperature
(
 year   INTEGER,
 temp   REAL,
 season INT REFERENCES season_lookup(code)
);