Thread: Auomatic numbering, replacing nulls and IF

I'm a child of Oracle and MS-SQL (forgive me) and have only been playing
with Postgres for a few days. I have 3 questions that I couldn't find
answers for in the online docs:

1) Is it possible to create an INT PRIMARY KEY column that automagically
numbers itself? MS-SQL does this using the IDENTITY keyword.

2) The ISNULL function in both Oracle and MS-SQL takes 2 numbers and returns
the second if the first is null, otherwise it returns the first. ISNULL in
Postgres seems to be something quite different. Before I go writing my own
function to do it, is there one already?

3) How do I do 'IF this_sql THEN that_sql'? I keep getting:
parse error at or near "IF"
--

================================
Luke Pascoe
Senior Developer
IT Support & Development Limited
http://wwwitsd.co.nz
Mobile: (021) 303019
Email: luke@itsd.co.nz
================================

...
> 2) The ISNULL function in both Oracle and MS-SQL takes 2 numbers and returns
> the second if the first is null, otherwise it returns the first. ISNULL in
> Postgres seems to be something quite different. Before I go writing my own
> function to do it, is there one already?

Look at COALESCE(). Does ISNULL() actually do something different in
Oracle and M$SQL? I'm curious because our definition conforms to the
SQL9x standard, and those databases claim to generally conform also.

                           - Thomas

> 1) Is it possible to create an INT PRIMARY KEY column that automagically
> numbers itself? MS-SQL does this using the IDENTITY keyword.

As you already mentioned, SERIAL/SEQUENCEs.

> 2) The ISNULL function in both Oracle and MS-SQL takes 2 numbers and
returns
> the second if the first is null, otherwise it returns the first. ISNULL in
> Postgres seems to be something quite different. Before I go writing my own
> function to do it, is there one already?

I think you're looking for coalesce():

"COALESCE(value[, ...])
The COALESCE function returns the first of its arguments that is not NULL.
This is often useful to substitute a default value for NULL values when data
is retrieved for display, for example:

SELECT COALESCE(description, short_description, '(none)') ...'

> 3) How do I do 'IF this_sql THEN that_sql'? I keep getting:
> parse error at or near "IF"

I'm not sure exactly what you need... but it sounds a lot like CASE:

http://postgresql.overtone.org/users-lounge/docs/7.2/postgres/functions-cond
itional.html

Greg

Le Jeudi 14 Mars 2002 21:52, Pestilence a écrit :
> 1) Is it possible to create an INT PRIMARY KEY column that automagically
> numbers itself? MS-SQL does this using the IDENTITY keyword.
Use serial type.

> 2) The ISNULL function in both Oracle and MS-SQL takes 2 numbers and
> returns the second if the first is null, otherwise it returns the first.
> ISNULL in Postgres seems to be something quite different. Before I go
> writing my own function to do it, is there one already?

See COALESCE and NULLIF at
http://www.postgresql.com/idocs/index.php?functions-conditional.html

> 3) How do I do 'IF this_sql THEN that_sql'? I keep getting:
> parse error at or near "IF"

CASE WHEN condition THEN result
         [WHEN ....]
          ELSE

Cheers,
Jean-Michel POURE

ISNULL (T-SQL)
Replaces NULL with the specified replacement value.

Syntax
ISNULL(check_expression, replacement_value)

Arguments
check_expression
Is the expression to be checked for NULL. check_expression can be of any type.
replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as
check_expresssion. 
Return Types
Returns the same type as check_expression.

Remarks
The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned.


Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: iharding@tpchd.org

>>> Thomas Lockhart <thomas@fourpalms.org> 03/20/02 07:14AM >>>
...
> 2) The ISNULL function in both Oracle and MS-SQL takes 2 numbers and returns
> the second if the first is null, otherwise it returns the first. ISNULL in
> Postgres seems to be something quite different. Before I go writing my own
> function to do it, is there one already?

Look at COALESCE(). Does ISNULL() actually do something different in
Oracle and M$SQL? I'm curious because our definition conforms to the
SQL9x standard, and those databases claim to generally conform also.

                           - Thomas

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Fri, 15 Mar 2002, Pestilence wrote:

> 2) The ISNULL function in both Oracle and MS-SQL takes 2 numbers and returns
> the second if the first is null, otherwise it returns the first. ISNULL in
> Postgres seems to be something quite different. Before I go writing my own
> function to do it, is there one already?
>

Coalesce

> 3) How do I do 'IF this_sql THEN that_sql'? I keep getting:
> parse error at or near "IF"

In what context?
If you mean like
IF (select * from foo) then delete from foo
kind of context, that kind of thing is availble
via pl functions but not AFAIK via direct sql.

If you mean in a select list:
select (if a is 2 then 3 else 1)
you can use CASE.