Thread: Column default

Column default

From
"Keith Worthington"
Date:
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

Re: Column default

From
Jeff Eckermann
Date:
--- 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

Re: Column default

From
"Keith Worthington"
Date:
> > 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.
[snip]

Jeff,

Thanks for the post.

I do not believe that I can follow your advice as I do not know which records
will have values for the columns containing the default and I process
everything at once.

At the risk of being extra wordy I will go through the whole process that I
currently follow.

1) A bash script runs the following command
   psql --echo-all \
        --dbname IPADB \
        --username loader \
        --command \
          "COPY data_transfer.tbl_peachtree_item
           FROM stdin
           WITH DELIMITER AS ','
                NULL AS '';"

2) This places the data in the following table.
IPADB=# \d data_transfer.tbl_peachtree_item
                    Table "data_transfer.tbl_peachtree_item"
        Column        |          Type          |            Modifiers
----------------------+------------------------+---------------------------------
 item_id              | character varying(20)  | not null
 description          | character varying(30)  |
 item_class           | smallint               |
 inactive             | boolean                |
 sales_description    | character varying(160) |
 purchase_description | character varying(160) |
 last_unit_cost       | real                   |
 costing_method       | smallint               |
 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
Indexes:
    "tbl_peachtree_item_pkey" PRIMARY KEY, btree (item_id)
Triggers:
    tgr_xfr_item_data AFTER INSERT ON data_transfer.tbl_peachtree_item FOR
EACH STATEMENT EXECUTE PROCEDURE data_transfer.tf_xfr_item_data()

3) The trigger fires after the insert and runs the following procedure.
-- Function: data_transfer.tf_xfr_item_data()
-- DROP FUNCTION data_transfer.tf_xfr_item_data();
CREATE OR REPLACE FUNCTION data_transfer.tf_xfr_item_data()
  RETURNS "trigger" AS
$BODY$
   DECLARE
      rcrd_item RECORD;
   BEGIN
      FOR rcrd_item IN SELECT data_transfer.tbl_peachtree_item.item_id,
                              data_transfer.tbl_peachtree_item.description,
                              data_transfer.tbl_peachtree_item.item_class,
                              data_transfer.tbl_peachtree_item.inactive,
                              data_transfer.tbl_peachtree_item.sales_description,

data_transfer.tbl_peachtree_item.purchase_description,
                              data_transfer.tbl_peachtree_item.last_unit_cost,
                              data_transfer.tbl_peachtree_item.costing_method,
                              data_transfer.tbl_peachtree_item.sales_gl_account,

data_transfer.tbl_peachtree_item.inventory_gl_account,
                              data_transfer.tbl_peachtree_item.cogs_gl_account,
                              data_transfer.tbl_peachtree_item.item_type,
                              data_transfer.tbl_peachtree_item.unit_of_measure,
                              data_transfer.tbl_peachtree_item.weight,
                              data_transfer.tbl_peachtree_item.reorder_point,
                              data_transfer.tbl_peachtree_item.reorder_quantity,
                              data_transfer.tbl_peachtree_item.purchase_uom,
                              data_transfer.tbl_peachtree_item.ptos_uom_factor
                         FROM data_transfer.tbl_peachtree_item
                        ORDER BY data_transfer.tbl_peachtree_item.item_id
      LOOP
--       Attempt to retrieve a matching record from the
--       target table.
         PERFORM peachtree.tbl_item.id
            FROM peachtree.tbl_item
           WHERE peachtree.tbl_item.id = rcrd_item.item_id;
         IF NOT FOUND THEN
--          A matching record was not found.  Insert the record.
            INSERT INTO peachtree.tbl_item
                        ( id,
                          description,
                          item_class,
                          inactive,
                          sales_description,
                          purchase_description,
                          last_unit_cost,
                          costing_method,
                          sales_gl_account,
                          inventory_gl_account,
                          cogs_gl_account,
                          item_type,
                          unit_of_measure,
                          weight,
                          reorder_point,
                          reorder_quantity,
                          purchase_uom,
                          ptos_uom_factor )
                 VALUES ( rcrd_item.item_id,
                          rcrd_item.description,
                          rcrd_item.item_class,
                          rcrd_item.inactive,
                          rcrd_item.sales_description,
                          rcrd_item.purchase_description,
                          rcrd_item.last_unit_cost,
                          rcrd_item.costing_method,
                          rcrd_item.sales_gl_account,
                          rcrd_item.inventory_gl_account,
                          rcrd_item.cogs_gl_account,
                          rcrd_item.item_type,
                          rcrd_item.unit_of_measure,
                          rcrd_item.weight,
                          rcrd_item.reorder_point,
                          rcrd_item.reorder_quantity,
                          rcrd_item.purchase_uom,
                          rcrd_item.ptos_uom_factor );
         ELSE
--          A matching record was found.  Update the record.
            UPDATE peachtree.tbl_item
               SET description = rcrd_item.description,
                   item_class = rcrd_item.item_class,
                   inactive = rcrd_item.inactive,
                   sales_description = rcrd_item.sales_description,
                   purchase_description = rcrd_item.purchase_description,
                   last_unit_cost = rcrd_item.last_unit_cost,
                   costing_method = rcrd_item.costing_method,
                   sales_gl_account = rcrd_item.sales_gl_account,
                   inventory_gl_account = rcrd_item.inventory_gl_account,
                   cogs_gl_account = rcrd_item.cogs_gl_account,
                   item_type = rcrd_item.item_type,
                   unit_of_measure = rcrd_item.unit_of_measure,
                   weight = rcrd_item.weight,
                   reorder_point = rcrd_item.reorder_point,
                   reorder_quantity = rcrd_item.reorder_quantity,
                   purchase_uom = rcrd_item.purchase_uom,
                   ptos_uom_factor = rcrd_item.ptos_uom_factor
             WHERE peachtree.tbl_item.id = rcrd_item.item_id;
         END IF;
         IF NOT FOUND THEN
--          The record was not inserted nor updated properly.
--          Write it to the load_error table.
            INSERT INTO load_error.tbl_peachtree_item
                        ( item_id,
                          description,
                          item_class,
                          inactive,
                          sales_description,
                          purchase_description,
                          last_unit_cost,
                          costing_method,
                          sales_gl_account,
                          inventory_gl_account,
                          cogs_gl_account,
                          item_type,
                          unit_of_measure,
                          weight,
                          reorder_point,
                          reorder_quantity,
                          purchase_uom,
                          ptos_uom_factor )
                 VALUES ( rcrd_item.item_id,
                          rcrd_item.description,
                          rcrd_item.item_class,
                          rcrd_item.inactive,
                          rcrd_item.sales_description,
                          rcrd_item.purchase_description,
                          rcrd_item.last_unit_cost,
                          rcrd_item.costing_method,
                          rcrd_item.sales_gl_account,
                          rcrd_item.inventory_gl_account,
                          rcrd_item.cogs_gl_account,
                          rcrd_item.item_type,
                          rcrd_item.unit_of_measure,
                          rcrd_item.weight,
                          rcrd_item.reorder_point,
                          rcrd_item.reorder_quantity,
                          rcrd_item.purchase_uom,
                          rcrd_item.ptos_uom_factor );
         END IF;
--       The record has been processed.  Remove it from the
--       transfer table.
         DELETE
           FROM data_transfer.tbl_peachtree_item
          WHERE data_transfer.tbl_peachtree_item.item_id = rcrd_item.item_id;
      END LOOP;
--    Update inventory.tbl_item with the new items.
--    Delete all inactive item ids from the inventory.tbl_item table.
--    It is easier to clear the table and reload it then figure out
--    what has changed to inactive.
      DELETE
        FROM inventory.tbl_item;
--    Insert all active item ids of type 'DIR' or 'NET' in the
--    inventory.tbl_item table.
--    Retrieve all of the applicable item ids.
      FOR rcrd_item IN SELECT peachtree.tbl_item.id AS item_id
                         FROM peachtree.tbl_item
                        WHERE ( NOT peachtree.tbl_item.inactive )
                          AND ( peachtree.tbl_item.item_type = 'DIR' OR
                                peachtree.tbl_item.item_type = 'NET' )
                        ORDER BY item_id
      LOOP
         INSERT INTO inventory.tbl_item
                     ( inventory_type,
                       item_id )
              VALUES ( 'physical',
                       rcrd_item.item_id );
      END LOOP;
      RETURN NULL;
   END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION data_transfer.tf_xfr_item_data() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION data_transfer.tf_xfr_item_data() TO postgres;
GRANT EXECUTE ON FUNCTION data_transfer.tf_xfr_item_data() TO GROUP "Loaders";

4) Which writes the data into the following two tables.
IPADB=# \d peachtree.tbl_item
                           Table "peachtree.tbl_item"
        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
Indexes:
    "tbl_part_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "tbl_item_fkey1" FOREIGN KEY (sales_gl_account) REFERENCES peachtree.tbl_gl_
account(account_id) ON UPDATE CASCADE ON DELETE RESTRICT
    "tbl_item_fkey2" FOREIGN KEY (inventory_gl_account) REFERENCES peachtree.tbl
_gl_account(account_id) ON UPDATE CASCADE ON DELETE RESTRICT
    "tbl_item_fkey3" FOREIGN KEY (cogs_gl_account) REFERENCES peachtree.tbl_gl_a
ccount(account_id) ON UPDATE CASCADE ON DELETE RESTRICT

IPADB=# \d inventory.tbl_item
             Table "inventory.tbl_item"
     Column     |         Type          | Modifiers
----------------+-----------------------+-----------
 inventory_type | character varying(20) | not null
 item_id        | character varying(20) | not null
Indexes:
    "tbl_item_pkey" PRIMARY KEY, btree (inventory_type, item_id)
Foreign-key constraints:
    "tbl_item_fkey1" FOREIGN KEY (inventory_type) REFERENCES
inventory.tbl_type(inventory_type) ON UPDATE CASCADE ON DELETE RESTRICT
    "tbl_item_fkey2" FOREIGN KEY (item_id) REFERENCES peachtree.tbl_item(id)
ON UPDATE CASCADE ON DELETE RESTRICT

Now the questions.

Why aren't the defaults applied when data arrives that is an update?  What is
the elegant way to ensure that if records are updated with missing information
the defaults are applied?

Why aren't the defaults applied when data arrives that is an insert?  What is
the elegant way to ensure that if records are inserted with missing
information the defaults are applied?

Kind Regards,
Keith

PS I truly apologize for the length.

Re: Column default

From
Jeff Eckermann
Date:
--- Keith Worthington <keithw@narrowpathinc.com>
wrote:

>Why aren't the defaults applied when data arrives
>that is an update?

The updated row will consist of all of the previous
column values, except for those which your update
statement has explicitly changed.

>What is
>the elegant way to ensure that if records are
>updated with missing
>information
>the defaults are applied?

With a trigger.  Since you are doing your processing
via a trigger anyway, you could just put your data
validation logic right there in the function.

>
>Why aren't the defaults applied when data arrives
>that is an insert?

They are.  Your last message did not show any evidence
that that was not happening.

>What is
>the elegant way to ensure that if records are
>inserted with missing
>information the defaults are applied?

As above.





__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail