Excluding null values

Поиск
Список
Период
Сортировка
От Sharon Cowling
Тема Excluding null values
Дата
Msg-id 200203132223.g2DMNst23888@lambton.sslnz.com
обсуждение исходный текст
Ответы Re: Excluding null values  ("Josh Berkus" <josh@agliodbs.com>)
Список pgsql-novice
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
applicationcode) but the rest of them can be null, only when I bring up the list of possible locations for a person I
onlywant 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


В списке pgsql-novice по дате отправления:

Предыдущее
От: Nicolas Boretos
Дата:
Сообщение: Sparc/Solaris2.6 Postgresql 7.1.3 Binary?
Следующее
От: "Josh Berkus"
Дата:
Сообщение: Re: Excluding null values