psql client for PostgreSQL

psql is a command line client for the PostgreSQL Database Engine. If you install postgreSQL on Debian psql is installed by default. After you switched to the postgteSQL user:

su postgres

you can access the client with this simpel command:

psql

and you can exit with this command:

\q

You can set a password with this command inside of the psql client:

ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'postgres';

you can call the help with this command:

\?

It’s a mess! I think more “NOT SEXY” is not possible! MySQL is maybe not as good as PostgreSQL, but from the usability it is 10 times better. In MySQL the command for exit is “exit” not “\q” and for showing the databases the command is “show databases” and for showing tables “show tables”. Much more intuitive than PostgreSQL!

Installing PostgreSQL 8.3 on Debian Linux (leny)

Installing PostgreSQL 8.3 on Debian Linux is pretty easy. With this command it is done.

apt-get install postgresql-8.3

Now the DB system is installed under:

/etc/postgresql/8.3/

You can see the status of the RDBMS with this command:

/etc/init.d/postgresql-8.3 status

You can start it

/etc/init.d/postgresql-8.3 start

and you can stop it

/etc/init.d/postgresql-8.3 stop

After the successful installation there is also a “postgres” user on your system. You can set the password of this user with this command:

passwd postgres

Now you can login as postgres user with:

su postgres

And with this command you can create a database:

createdb testdatabase

Your DB ist up and running.

MySQL to PostgresSQL migration

Today I migrated a 10 MB database from MySQL to PostgresSQL. I used the “ESF Database Migration Toolkit – Standard 6.5.06”. It works pretty good for me.

I access the database with a spring/hibernate based java application. After some litle changes on the applicationContext.xml I could successfully start my app and access the postgresSQL database.

When I tried to enter new data through the java application I got some errors:

Caused by: java.sql.BatchUpdateException: Batch-Eintrag 0 insert into

The reason for this error is that postgresSQL uses sequences to generate IDs. The migration toolkit from ESF created for every table a separate sequence. By default hibernate creates one sequence for all tables.

After I customized the hibernate mappings to use the sequences created by the migration toolkit from ESF, it works fine for me. Here is the right mapping for a custom sequence.

<id name="id" type="long" column="`ID`">
 <generator >
 <param name="sequence">work_unit_id_seq</param>
 </generator>
 </id>

It is absolute important that the sequence name is spelled in small letters, in the postgresSQL database.