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)
);

Install Jetspeed with PostgreSQL

Since my graduation design request PostgreSQL, I have to give up MySql. PostgreSQL is quit different from MySql, as most extension commands of MySql, like SHOW TABLES , does not work in Postgre, and the structure of Postgre is entirely unfamiliar for me.

Although the installation of Jetspeed's DB is so simple that just three steps:
1 create a database
2 grant the privilege
3 download the JDBC
which still took me almost two hours to accomplish it, for the account and network configuration is some bit complex.

First of all, write a test java file, which is of great help, the following code (replace 'testdb') should be included in the file:

Class.forName("org.postgresql.Driver");
Connection db = DriverManager.getConnection("jdbc:postgresql://localhost:5432/jetspeed", "jetspeed", "jetspeed");

Compile it, JDBC file was not necessary during compilation phase, but it should be appended to the classpath before executing the test program.

Second, install PostgreSQL if it was not installed before. Source installation would be a better choice, and here is the link of PostgreSQL official site. Install via apt would be the most convenient way, I like apt and ubuntu :) .

Third, initialize a DB cluster(apt installation has already initialize one), like the following command:

$ initdb -D /usr/local/pgsql/data

Find pg_hba.conf and postgresql.conf in the directory of DB cluster. Login the cli environment of PostgreSQL (notice: there are two databases already exist, template0 and template1):

$ postmaster -S -D /usr/local/pgsql/data  #start the db server background
$ psql template0

Fourth, assure that the TCP/IP connection was available. Modify postgresql.conf as below:

listen_addresses='*'
port=5432
tcpip_socket=true

Fifth, modify pg_hba.conf (refer to this):

local all    password
host all all 127.0.0.1/32 password

Caution: Start a new line at the end of file. And restart the server after the configuration completed.

Sixth, create a user and create the database, then grant the control privilege to the user. There're several ways to do it, just like this one:

$ createuser -d -P  # create a user with the privilege of database create and a prompt for the password of the new userd
... # input the user info,like username is 'jetspeed' and password is 'jetspeed'
$ createdb jetspeed -U jetspeed -W  # create a new db and the creator is 'jetspeed'
# if error reported, maybe pg_hba.conf was incorrected configured.

Seventh, run the JDBC test. If the test succeed, there would be no problem of Jetspeed installation.