Re: Column default

--- Keith Worthington <keithw@narrowpathinc.com>
wrote:

> Hi All,
>
> I am having difficulty understanding the use of
> defaults.
>
> I have a table with defaults defined in three
> columns.  When I run a function
> that inserts/or updates records the default value is
> not written into the
> record.  I thought this was a result of updates
> occurring and not inserts.
> So, I changed the source table to have the same
> defaults.  However when I use
> the COPY command to place data in the source table
> the defaults do not appear
> to be applied there either.
>
> When I run a query on the table specifying WHERE
> ptos_uom_factor ISNULL I get
> lots of records back.  I though that using a default
> would prevent this outcome.
>
> When is the default value applied?  On INSERT?
> UPDATE? COPY? None of the above?

A default will be applied on INSERT, if you do not
supply a value for that column.  My understanding (I
haven't tested this lately) is that COPY will cause
the default to be applied, if you supply a column list
to the COPY command, not including the column with the
default value.

The most powerful and flexible way to limit or test
the values that are entered into a column is with a
trigger.

>
> TIA
>
>         Column        |          Type          |
>        Modifiers
>
>
----------------------+------------------------+--------------------------------
> -
>  id                   | character varying(20)  | not
> null
>  description          | character varying(30)  | not
> null
>  item_class           | smallint               | not
> null
>  inactive             | boolean                | not
> null
>  sales_description    | character varying(160) |
>  purchase_description | character varying(160) |
>  last_unit_cost       | real                   | not
> null
>  costing_method       | smallint               | not
> null
>  sales_gl_account     | character varying(15)  |
>  inventory_gl_account | character varying(15)  |
>  cogs_gl_account      | character varying(15)  |
>  item_type            | character varying(8)   |
>  unit_of_measure      | character varying(6)   |
> default 'ea'::character varying
>  weight               | real                   |
>  reorder_point        | real                   |
>  reorder_quantity     | real                   |
>  purchase_uom         | character varying(6)   |
> default 'ea'::character varying
>  ptos_uom_factor      | real                   |
> default 1
>
>
> Kind Regards,
> Keith
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
>       subscribe-nomail command to
> majordomo@postgresql.org so that your
>       message can get through to the mailing list
> cleanly
>




__________________________________
Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search.
http://info.mail.yahoo.com/mail_250