Excluding null values

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

Excluding null values

От:
Sharon Cowling <sharon.cowling@sslnz.com>
Дата:
I have a table which stores a list of locations that a person can have.  In my application I need to get this list of locations, but I don't want the list to contain any null values, a  person must have at least 1 location (done in application code) but the rest of them can be null, only when I bring up the list of possible locations for a person I only want the not null ones, is there any way of achieving this in sql, or will I need to do this at the application level?  Normally I would just say where column is not null, but there are 11 columns in this table that can be not null.

This is the table structure:

shaz=> \d forest_person
            Table "forest_person"
 Attribute  |         Type          | Modifier
------------+-----------------------+----------
 person_id  | integer               | not null
 location1  | character varying(30) |
 location2  | character varying(30) |
 location3  | character varying(30) |
 location4  | character varying(30) |
 location5  | character varying(30) |
 location6  | character varying(30) |
 location7  | character varying(30) |
 location8  | character varying(30) |
 location9  | character varying(30) |
 location10 | character varying(30) |
 location11 | character varying(30) |
 location12 | character varying(30) |
Index: forest_person_pkey


Here is the person table:

shaz=> \d person
                   Table "person"
    Attribute     |         Type          | Modifier
------------------+-----------------------+----------
 person_id        | integer               | not null
 firstname        | character varying(25) | not null
 lastname         | character varying(25) | not null
 dob              | date                  | not null
 street           | character varying(50) | not null
 suburb           | character varying(50) |
 city             | character varying(50) | not null
 homephone        | character varying(15) |
 workphone        | character varying(15) |
 mobile           | character varying(15) |
 type             | character varying(30) | not null
 date_approved    | date                  | not null
 approved_by      | character varying(50) | not null
 vehicle_type     | character varying(50) |
 vehicle_rego     | character varying(6)  |
 drivers_licence  | character varying(10) |
 firearms_licence | character varying(20) |
 notes            | character varying(80) |
 status           | character varying(10) |
Indices: firstname_idx,
         fullname_idx,
         lastname_idx,
         person_drivers_licence_key,
         person_firearms_licence_key,
         person_pkey

Regards,

Sharon Cowling

Re: Excluding null values

От:
"Josh Berkus" <josh@agliodbs.com>
Дата:
Ms. Cowling,

> I have a table which stores a list of locations that a person can
>  have.  In my application I need to get this list of locations, but I
>  don't want the list to contain any null values, a  person must have
>  at least 1 location (done in application code) but the rest of them
>  can be null, only when I bring up the list of possible locations for
>  a person I only want the not null ones, is there any way of
>  achieving this in sql, or will I need to do this at the application
>  level?  Normally I would just say where column is not null, but
>  there are 11 columns in this table that can be not null.

Hmmm ... I'm not sure where to start.  Your database design is *not*
 normalized.  Is this a legacy structure you're saddled with, or is it
 possible to re-design it?

-Josh

Re: Excluding null values

От:
"Josh Berkus" <josh@agliodbs.com>
Дата:
Sharon,

> I think the best idea for this scenario is a trigger, I had a go at
>  writing one for this but since a trigger runs for each row my
>  function code did not work.

OK.  When I get back to my office, I'll send some sample code; I have a
 trigger I use all the time for this purpose.

-Josh
FAQ