Re: Column default

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