Archive

Posts Tagged ‘PostgreSQL’

NVL() function in Postgre SQL

August 25, 2009 8 comments

Do Postgre have any function that equals to NVL() of Oracle? of course, it do have the same function but with different name. Below is the systax:

coalesce(expr1, expr2, expr3,....)

this function’ll returns the first non-null expression that is passed to it.

Ex:  
SELECT coalesce(<column name 1>, <New value>), <Column name 2> 
FROM <Table name> 
WHERE <Some Condition>

If <Column name 1> is NULL, it’ll be replaced by <New value>

Categories: PostgreSQL Tags:

Add auto increment column in PostgreSQL

August 10, 2009 13 comments

In PostgreSQL, we cannot just add an column and mark it as auto increment like in MySQL or SQL Server. Instead, we have to create an sequence and link it to the specified column.

1. Assume that we have a table called [testtbl] with an unique column called [id]

2. Generate sequence

CREATE SEQUENCE <Sequence name>

=>

CREATE SEQUENCE <testtbl_id_seq>

※After the sequence’s already created, we can call NEXTVAL(‘<Sequence name>’) to generate a new value automatically.


3. Link the sequence to the unique column

ALTER TABLE <Table name>
ALTER COLUMN <Column name>
SET DEFAULT NEXTVAL(<Created sequence name>);

=>

ALTER TABLE testtbl
ALTER COLUMN id
SET DEFAULT NEXTVAL('testtbl_id_seq');

That’s all what we have to do.

Categories: PostgreSQL Tags: