2010-05-19

Exclusion Constraints

In future Postgresql versions they are talking about exclusion constraints. Hitting just the thing I have been writing about in several posts about not overlapping and how to dirty hack implement it somehow with Oracle. Nice video presentation about the issue from San Francisco PostgreSQL User Group. And some pdf about the same issue.

2010-05-18

Types of columns

Just participated C.J.Date two day seminar in 10 hours today. Thanks to the ash from Iceland. Monday was delayed.

Just a small thing picked up from the massive amount of information. To avoid type conversions behind the scenas while doing natural joins avoid using different types for columns named similarily. Just checking that



select * from (
select count(distinct data_type
||'|'||cast(data_length as varchar2(30))
||'|'||cast(data_precision as varchar2(30))
||'|'||cast(data_scale as varchar2(30))
) over (partition by column_name) as dis
, table_name
, column_name
, data_type
, data_length
, data_precision
, data_scale
from user_tab_columns
)
where dis > 1
;



Well on the other hand do not use select *. As an analogy I would suggess not to use natural joins. Mr Date suggested to use views on to of base tables. That makes sense. And a good thing here user_tab_columns has also the columns in views included.

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.