We are having MS SQL 2008 as database with Hibernate.
Now we are planning to move on to Postgres.
I know that Postgres does not have auto increment feature for primary key column.
I heard about SERIAL data type or Sequences for each table and then using nextval with primary key.
MS SQL Database is large enough containing around 150 tables.
So, I want opinions and want to know that Should We go by create sequences or SERIAL will work.
Which one could be better option ?
In PostgreSQL serial type is automatic, just like auto-increment in MSSQL/MySQL etc. Try for example:
create temporary table test (
id serial primary key,
data text
);
insert into test (data) values ('foo'), ('bar');
select * from test;
id | data ----+------ 1 | foo 2 | bar (2 rows)
It worked in other way. I gave <property name="hbm2ddl.auto">create</property> in hibernate.cfg.xml. SO, tables are created on first run.
Then the requirement changed and I needed to get data for only 12-15 tables. So, I generated a Script for it using MS SQL Server 2008.
Then of course manually altered sysntax for each table's insert.
There are few things which is required.
In MSSQL we had tinyint as datatype for boolean. So, Manually conversion into 'true' and 'false' required for Postgres.
MS SQL exports timstamp in HEXA and gives you CAST ... AS DATETIME during script generation. I changed them to CURRENT_TIMESTAMP in Postgres.
