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