- PostgreSQL Server Programming(Second Edition)
- Usama Dar Hannu Krosing Jim Mlodgenski Kirk Roybal
- 283字
- 2021-07-23 20:36:46
Data cleaning
In the preceding code, we notice that employee names don't have consistent cases. It will be easy to enforce consistency by adding a constraint, as shown here:
CHECK (emp_name = upper(emp_name))
However, it is even better to just make sure that the name is stored as uppercase, and the simplest way to do this is by using trigger
:
CREATE OR REPLACE FUNCTION uppercase_name () RETURNS trigger AS $$ BEGIN NEW.emp_name = upper(NEW.emp_name); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER uppercase_emp_name BEFORE INSERT OR UPDATE OR DELETE ON salaries FOR EACH ROW EXECUTE PROCEDURE uppercase_name ();
The next set_salary()
call for a new employee will now insert emp_name
in uppercase:
postgres=# SELECT set_salary('arnold',80); -[ RECORD 1 ]------------------- set_salary | INSERTED USER arnold
As the uppercasing happens inside a trigger, the function's response still shows a lowercase name, but in the database, it is uppercased:
postgres=# SELECT * FROM salaries; -[ RECORD 1 ]--- emp_name | Bob salary | 1300 -[ RECORD 2 ]--- emp_name | Fred salary | 750 -[ RECORD 3 ]--- emp_name | Frank salary | 100 -[ RECORD 4 ]--- emp_name | ARNOLD salary | 80
After fixing the existing mixed-case employee names, we can make sure that all employee names will be uppercased in the future by adding a constraint:
postgres=# update salaries set emp_name = upper(emp_name) where not emp_name = upper(emp_name); UPDATE 3 postgres=# alter table salaries add constraint emp_name_must_be_uppercasepostgres CHECK (emp_name = upper(emp_name)); ALTER TABLE
If this behavior is needed in more places, it will make sense to define a new type – say u_text
, which is always stored as uppercase. You will learn more about this approach in Chapter 14, PostgreSQL as Extensible RDBMS.
- 少年輕松趣編程:用Scratch創(chuàng)作自己的小游戲
- Hadoop+Spark大數(shù)據(jù)分析實戰(zhàn)
- 零基礎(chǔ)學(xué)MQL:基于EA的自動化交易編程
- Mastering Predictive Analytics with Python
- 深入淺出RxJS
- Python Essentials
- ArcGIS for Desktop Cookbook
- SciPy Recipes
- Instant Zurb Foundation 4
- 會當(dāng)凌絕頂:Java開發(fā)修行實錄
- 數(shù)據(jù)結(jié)構(gòu):Python語言描述
- 原型設(shè)計:打造成功產(chǎn)品的實用方法及實踐
- Backbone.js Patterns and Best Practices
- JavaScript程序設(shè)計實例教程(第2版)
- 精通Rust(第2版)