Re: Column default - Mailing list pgsql-novice
From | Keith Worthington |
---|---|
Subject | Re: Column default |
Date | |
Msg-id | 20050214225430.M15170@narrowpathinc.com Whole thread Raw |
In response to | Column default ("Keith Worthington" <keithw@narrowpathinc.com>) |
Responses |
Re: Column default
|
List | pgsql-novice |
> > 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.
pgsql-novice by date: