Thread: Table with default value

Table with default value

From
Sharon Cowling
Date:
Hi,

In my java code I test if a licence number already exists, if it does, error, if not carry on and insert into database.
I am not using the unique constraint as this does not work through the front-end for some reason (It would make life
easierif it did!).  I get an error in my code when I test for the value and get a null back (null values are perfectly
acceptable). So I thought I would re create my table with a default value of 'No' for drivers licence.  For some reason
thisdoes not work?!  It should do, heres the info, any ideas?! 

CREATE TABLE person4(
person_id INT NOT NULL,
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
dob date NOT NULL,
street VARCHAR(50) NOT NULL,
suburb VARCHAR(50),
city VARCHAR(50) NOT NULL,
homephone VARCHAR(15),
workphone VARCHAR(15),
mobile VARCHAR(15),
type VARCHAR(30) NOT NULL,
date_approved DATE NOT NULL,
approved_by VARCHAR(50) NOT NULL,
vehicle_type VARCHAR(50),
vehicle_rego VARCHAR(6),
drivers_licence VARCHAR(20) DEFAULT 'No',
firearms_licence VARCHAR(20) DEFAULT 'No',
notes VARCHAR(80),
PRIMARY KEY (person_id));

user=> \d
                     Table "person4"
    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(20) | default 'No'
 firearms_licence | character varying(20) | default 'No'
 notes            | character varying(80) |
Index: person4_pkey


user=> select * from person4;
 person_id | firstname | lastname |    dob     | street | suburb | city | homephone | workphone | mobile |      type
 | date_approved | approved_by | vehicle_type | vehicle_rego | drivers_licence | firearms_licence | notes 

-----------+-----------+----------+------------+--------+--------+------+-----------+-----------+--------+----------------+---------------+-------------+--------------+--------------+-----------------+------------------+-------
       778 | sdf       | sdf      | 11/11/1977 | sf     |        | dsf  |           |           |        |
Owner/Relative| 11/01/2002    | test        |              |              |                 |                  | 
(1 row)

As you can see there is no default value of 'No' for drivers licence.

The below error is from the front end when trying to insert a value for drivers_licence - A query runs to select
drivers_licencefrom person4 where user input value = drivers_licence to see if it exists, should be 'No' and carry on
withouterror: 

java.lang.NullPointerException
    at org.postgresql.jdbc2.ResultSet.getString(ResultSet.java:171)
    at org.postgresql.jdbc2.ResultSet.getString(ResultSet.java:611)

Regards,

Sharon Cowling


Re: Table with default value

From
Oliver Elphick
Date:
On Fri, 2002-01-11 at 03:14, Sharon Cowling wrote:
>
> user=> \d
>                      Table "person4"
>     Attribute     |         Type          |   Modifier
> ------------------+-----------------------+--------------
...
>  drivers_licence  | character varying(20) | default 'No'
...
>
> user=> select * from person4;
>  person_id | firstname | lastname |    dob     | street | suburb | city | homephone | workphone | mobile |      type
   | date_approved | approved_by | vehicle_type | vehicle_rego | drivers_licence | firearms_licence | notes 
>
-----------+-----------+----------+------------+--------+--------+------+-----------+-----------+--------+----------------+---------------+-------------+--------------+--------------+-----------------+------------------+-------
>        778 | sdf       | sdf      | 11/11/1977 | sf     |        | dsf  |           |           |        |
Owner/Relative| 11/01/2002    | test        |              |              |                 |                  | 
> (1 row)
>
> As you can see there is no default value of 'No' for drivers licence.

The default only operates if you do an insert without mentioning the
column.  It will not override an explicitly inserted null or empty
string.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "Enter into his gates with thanksgiving, and into his
      courts with praise. Be thankful unto him, and bless
      his name."           Psalms 100:4

Attachment