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?

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

--- 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend