Saturday, April 30. 2011Using Domains to Enforce Business RulesPrinter FriendlyRecommended Books: PostgreSQL 9.0 Manual - Volume 1A: SQL Reference
PostgreSQL 9.0 Reference - Volume 1B: SQL Command Reference
PostgreSQL 9 High Performance / Admin Cookbook combo PostGIS in Action Trackbacks
Trackback specific URI for this entry
No Trackbacks
Comments
Display comments as
(Linear | Threaded)
Regarding the casting: In DB2 you can define domains such that it isn't allowed to assign values to a column that has a different "data type" (even if the constraints were OK). Imagine two columns "distance" and "duration in seconds" both implemented as e.g. integers. Nothing keeps you from updating the "distance" column with values from the "duration" column. In DB2 you can base each column on a different domain and an update will throw an error. Very nice feature and probably something that would nice to have in PostgreSQL as well.
#1
on
2011-05-01 04:19
Always good to learn a new feature. Never knew you could do things like this in SQL.
Please put the direct FREE download links to the ebooks on your site. It is difficult to find the ebooks on piratebay.
#3
on
2011-05-02 04:01
Sunil,
Which ebooks are you referring to?
#3.1
on
2011-05-02 07:54
It's a pitty that this couldn't be done in 1 ALTER TABLE statement. But on the otherhand, that's why PostgreSQL has transactional DDL!
How so? - you can use ALTER TABLE to convert a data type that was not a domain to one that is.
So for my payroll example - lets say it looked like this: CREATE TABLE payroll(pid serial primary key, employee_id varchar(20), payamt numeric(10,2), dtpaythru date); and later I wanted to change dtpaythru to a dom_payday. Just like any other type, I can do this: ALTER TABLE payroll ALTER COLUMN dtpaythru TYPE dom_payday; It will validate to ensure your existing data conforms to the rules of the domain though and will fail if it doesn't.
#4.1
on
2011-05-03 07:50
You mean
ALTER DOMAIN ALTER CONSTRAINT as opposed to having to (which exists?) ALTER DOMAIN DROP CONSTRAINT
#4.1.1.1
on
2011-05-03 08:18
But it's not possible with 1 ALTER DOMAIN statement to ADD and DROP a number of constraint's.
But it most cases ALTER DOMAIN ALTER CONSTRAINT will work just fine. The author does not allow comments to this entry
|
Entry's LinksQuicksearchCalendar
Categories
Blog Administration |