Add auto increment column in PostgreSQL

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

1CREATE SEQUENCE <Sequence name>

==>

1CREATE 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 

 

1	ALTER TABLE <Table name>

2	ALTER COLUMN <Column name>

3

SET DEFAULT NEXTVAL(<Created sequence name>);

==>

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

NVL() function in Postgre SQL

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.

1

Ex:

2	SELECT coalesce(<column name 1>, <New value>), <Column name 2>

3	FROM <Table name>

4	WHERE <Some Condition>

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

Oracle/PLSQL: NVL Function

In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is encountered.

Syntax

The syntax for the NVL function is:

NVL( string1, replace_with )

string1 is the string to test for a null value.
replace_with is the value returned if string1 is null.

Applies To

• Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example #1

select NVL(supplier_city, 'n/a')
from suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value.

Example #2

select supplier_id,
NVL(supplier_desc, supplier_name)
from suppliers;

This SQL statement would return the supplier_name field if the supplier_desc contained a null value. Otherwise, it would return the supplier_desc.

Example #3

select NVL(commission, 0)
from sales;

This SQL statement would return 0 if the commission field contained a null value. Otherwise, it would return the commission field.

Frequently Asked Questions

---

Question: I tried to use the NVL function through VB to access Oracle DB.
To be precise,

select NVL(Distinct (emp_name),'AAA'),................ from.................

I got an oracle error when I use distinct clause with NVL, but when I remove distinct it works fine.
Answer: It is possible to the use the DISTINCT clause with the NVL function. However, the DISTINCT must come before the use of the NVL function. For example:

select distinct NVL(emp_name, 'AAA')
from employees;

Hope this helps!

---

Question: Is it possible to use the NVL function with more than one column with the same function call? To be clear, if i need to apply this NVL function to more than one column like this:

NVL(column1;column2 ...... , here is the default value for all )

Answer: You will need to make separate NVL function calls for each column. For example:

select NVL(table_name, 'not found'), NVL(owner, 'not found')
from all_tables;