Обсуждение: if !NULL ?

Поиск
Список
Период
Сортировка

if !NULL ?

От
Lev Lvovsky
Дата:
ok, so more odd-ball questions

I have two columns in a table:

bool_bill | billing_id

one is a boolean that determines if we bill a customer, and the other one
is NULL if we don't, and has a billing_id of that customer if we do.  the
billing_id uses a foreign key on the billing table to check that the
billing_id exists.

what I'd like to do is if bool_bill = y, then there has to be something in
billing_id (automatically checked with the foreign key).

again, do I need a function for this?

thanks!!!

-lev

Re: if !NULL ?

От
Stephan Szabo
Дата:
On Thu, 16 May 2002, Lev Lvovsky wrote:

>
> ok, so more odd-ball questions
>
> I have two columns in a table:
>
> bool_bill | billing_id
>
> one is a boolean that determines if we bill a customer, and the other one
> is NULL if we don't, and has a billing_id of that customer if we do.  the
> billing_id uses a foreign key on the billing table to check that the
> billing_id exists.
>
> what I'd like to do is if bool_bill = y, then there has to be something in
> billing_id (automatically checked with the foreign key).
>
> again, do I need a function for this?

I think a table check constraint like:
(bool_bill=false or billing_id is not null)
will make billing_id not accept nulls when bool_bill is true.

Re: if !NULL ?

От
"McCaffity, Ray (Contractor)"
Дата:
Is there a way to do to define a default value when doing this?

something like...

select distinct
        uid ....stuff...

      e.thingy  AS  Ifnull(ulk,description,'UNKNOWN'),

        ... more stuff ...

As I want to do is have a default value of "UNKNOWN" instead of null.



-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Thursday, May 16, 2002 6:17 PM
To: Lev Lvovsky
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] if !NULL ?


On Thu, 16 May 2002, Lev Lvovsky wrote:

>
> ok, so more odd-ball questions
>
> I have two columns in a table:
>
> bool_bill | billing_id
>
> one is a boolean that determines if we bill a customer, and the other one
> is NULL if we don't, and has a billing_id of that customer if we do.  the
> billing_id uses a foreign key on the billing table to check that the
> billing_id exists.
>
> what I'd like to do is if bool_bill = y, then there has to be something in
> billing_id (automatically checked with the foreign key).
>
> again, do I need a function for this?

I think a table check constraint like:
(bool_bill=false or billing_id is not null)
will make billing_id not accept nulls when bool_bill is true.



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: if !NULL ?

От
Stephan Szabo
Дата:
On Fri, 17 May 2002, McCaffity, Ray (Contractor) wrote:

> Is there a way to do to define a default value when doing this?
>
> something like...
>
> select distinct
>         uid ....stuff...
>
>       e.thingy  AS  Ifnull(ulk,description,'UNKNOWN'),
>
>         ... more stuff ...
>
> As I want to do is have a default value of "UNKNOWN" instead of null.

Well, you can just set a default on the column, although in the
case of foreign keys the default isn't necessarily automatically
allowed unless it's NULL.

Re: if !NULL ?

От
Darren Ferguson
Дата:
Try the COALESCE function

dev=> select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

dev=> SELECT COALESCE(NULL,'UNKNOWN');
  case
---------
 UNKNOWN
(1 row)


dev=> SELECT COALESCE('Temp','UNKNOWN');
 case
------
 Temp
(1 row)


Darren Ferguson

On Fri, 17 May 2002, McCaffity, Ray (Contractor) wrote:

> Is there a way to do to define a default value when doing this?
>
> something like...
>
> select distinct
>         uid ....stuff...
>
>       e.thingy  AS  Ifnull(ulk,description,'UNKNOWN'),
>
>         ... more stuff ...
>
> As I want to do is have a default value of "UNKNOWN" instead of null.
>
>
>
> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> Sent: Thursday, May 16, 2002 6:17 PM
> To: Lev Lvovsky
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] if !NULL ?
>
>
> On Thu, 16 May 2002, Lev Lvovsky wrote:
>
> >
> > ok, so more odd-ball questions
> >
> > I have two columns in a table:
> >
> > bool_bill | billing_id
> >
> > one is a boolean that determines if we bill a customer, and the other one
> > is NULL if we don't, and has a billing_id of that customer if we do.  the
> > billing_id uses a foreign key on the billing table to check that the
> > billing_id exists.
> >
> > what I'd like to do is if bool_bill = y, then there has to be something in
> > billing_id (automatically checked with the foreign key).
> >
> > again, do I need a function for this?
>
> I think a table check constraint like:
> (bool_bill=false or billing_id is not null)
> will make billing_id not accept nulls when bool_bill is true.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>