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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

  From Date Subject
Next Message thiemo 2002-03-13 23:14:37 Nested groups
Previous Message Oliver Elphick 2002-03-13 16:14:31 Re: Postmaster with -i