Thread: composite type and domain
Why is it not possible to create domain on composite type ? Consider the example, I got (a bytea, b timestamp, c timestamp). Where b < c always, and both b and c have some default value, a can stay null. Now, I don't want to go berserk, and create aditional table for that, because type is shared between two tables. But it would be nice, to be able to create domain based on that type. Any reasons I can't , or is it just something I do wrong? That's on 8.3/8.4 -- GJ
2009/5/25 Grzegorz Jaśkiewicz <gryzman@gmail.com>: > Why is it not possible to create domain on composite type ? > > Consider the example, I got (a bytea, b timestamp, c timestamp). Where > b < c always, and both b and c have some default value, a can stay > null. > Now, I don't want to go berserk, and create aditional table for that, > because type is shared between two tables. But it would be nice, to be > able to create domain based on that type. > Any reasons I can't , or is it just something I do wrong? Another potential way of getting there is to define a table with a table constraint that gives you what you want...and use the table for your composite (I usually do this anyways). This doesn't work either, because table constraints aren't enforced during casts....maybe they should be? merlin
When I start to complain about domains and types in postgresql, people often ask me - so what's exactly wrong with it - well, here you go. I am trying to provide some feedback ;)
Grzegorz Jaśkiewicz wrote: > Why is it not possible to create domain on composite type ? > > Consider the example, I got (a bytea, b timestamp, c timestamp). Where > b < c always, and both b and c have some default value, a can stay > null. > Now, I don't want to go berserk, and create aditional table for that, > because type is shared between two tables. But it would be nice, to be > able to create domain based on that type. > Any reasons I can't , or is it just something I do wrong? > > That's on 8.3/8.4 > I think Postgres' type system is as good any and better than most... even if it doesn't do what you are asking. They ALL have their problems. What I do is handle it in the constructors. So if someone passes b > c it swaps them. It doesn't help for casts, so I'm careful to always use the constructors. I wrote a couple articles about doing this in Postgres and Oracle on my blog. http://scottrbailey.wordpress.com/2009/05/19/timespan-postgresql/ Scott
Scott Bailey wrote: > Grzegorz Jaśkiewicz wrote: >> Why is it not possible to create domain on composite type ? >> >> Consider the example, I got (a bytea, b timestamp, c timestamp). Where >> b < c always, and both b and c have some default value, a can stay >> null. >> Now, I don't want to go berserk, and create aditional table for that, >> because type is shared between two tables. But it would be nice, to be >> able to create domain based on that type. >> Any reasons I can't , or is it just something I do wrong? >> >> That's on 8.3/8.4 >> > > I think Postgres' type system is as good any and better than most... > even if it doesn't do what you are asking. They ALL have their problems. > > What I do is handle it in the constructors. So if someone passes b > c > it swaps them. It doesn't help for casts, so I'm careful to always use > the constructors. > > I wrote a couple articles about doing this in Postgres and Oracle on my > blog. > > http://scottrbailey.wordpress.com/2009/05/19/timespan-postgresql/ > > Scott I was also thinking that if you wanted more of the domain behavior; your constructors could throw an exception rather than silently swapping b and c, as I chose to do. Scott
well, I need database to guard data, not application. Application can check things too, but database's job is to make sure data is integral.
Grzegorz Jaśkiewicz wrote: > well, I need database to guard data, not application. > Application can check things too, but database's job is to make sure > data is integral. Who said anything about the application level?
2009/5/27 Scott Bailey <artacus@comcast.net>: > Who said anything about the application level? can you give an example please ? -- GJ
Grzegorz Jaśkiewicz wrote: > 2009/5/27 Scott Bailey <artacus@comcast.net>: > >> Who said anything about the application level? > > > can you give an example please ? > Did you read the article I sent you earlier? I'm doing almost the exact same thing you are doing save the bytea field. I create a timespan composite type that has a start and end times. But instead of doing casts and using a domain catch any errors like you are trying to do, I'm using constructors to return a timespan. The signature for the constructors look like so: timespan(timestamp, timestamp) timespan(timestamp, numeric) timespan(timestamp, interval) timespan(varchar, varchar) And the constructors do the work of making sure that the start time always comes before the end time. Anyhow read the article, it has all the examples you need. http://scottrbailey.wordpress.com/2009/05/19/timespan-postgresql/
On Fri, May 29, 2009 at 3:37 AM, Scott Bailey <artacus@comcast.net> wrote: > Did you read the article I sent you earlier? Well, the difference here is that this way db doesn't really check anything :) you just choose path of execution, that you created prior. That's cheating :p So yes, I read that article, but that's not the right solution, it gives you an illusion, only if you use it the way it was meant to be (tm). -- GJ
Grzegorz Jaśkiewicz wrote: > On Fri, May 29, 2009 at 3:37 AM, Scott Bailey <artacus@comcast.net> wrote: >> Did you read the article I sent you earlier? > > Well, the difference here is that this way db doesn't really check > anything :) you just choose path of execution, that you created prior. > That's cheating :p > > So yes, I read that article, but that's not the right solution, it > gives you an illusion, only if you use it the way it was meant to be > (tm). Fair enough, but: 1) There's nothing wrong with cheating 2) By raising an exception in the constructors when b > c, you'll get almost the same behavior as having a domain. 3) You can't do what you want in Postgres so your only other options are to hack the code for domains so they work with complex types or build your type in C and create your own I/O functions... both of those are a whole lot more work.