Skip site navigation (1) Skip section navigation (2)

Excluding null values

From: Sharon Cowling <sharon(dot)cowling(at)sslnz(dot)com>
To: "Pgsql-Novice (E-mail)" <pgsql-novice(at)postgresql(dot)org>
Subject: Excluding null values
Date: 2002-03-13 22:30:00
Message-ID: 200203132223.g2DMNst23888@lambton.sslnz.com (view raw or flat)
Thread:
Lists: 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 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


Responses

pgsql-novice by date

Next:From: thiemoDate: 2002-03-13 23:14:37
Subject: Nested groups
Previous:From: Oliver ElphickDate: 2002-03-13 16:14:31
Subject: Re: Postmaster with -i

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group