On Thu, Apr 23, 2009 at 11:33:34AM +0200, Ivan Sergio Borgonovo wrote: > Karsten Hilbert <Karsten.Hilbert@xxxxxxx> wrote: > > > On Thu, Apr 23, 2009 at 12:02:13AM +0100, Seref Arikan wrote: > > > > > I have a set of dynamically composed objects represented in > > > Java, with string values for various attributes, which have > > > variable length. In case you have suggestions for a better type > > > for this case, it would be my pleasure to hear about them. > > > > Seref, he's suggesting you use TEXT instead of > > VARCHAR(something). In PG it's actually usually *less* > > overhead to use the unbounded text datatype (no length check > > required). > > > > Length checks mandated by business logic can be added by > > more dynamic means -- check constraints, triggers, etc which > > allow for less invasive change if needed. > > Could you point us to some example of a constraint/trigger (etc...) > that is going to provide the same checking of varchar and explain > (if the code/example... doesn't make it clear) why it should be > faster or less invasive? check constraint based: create table foo ( txt text check (char_length(txt) < 1001) ); trigger based: create function trf_check_length_1000() ... returns trigger ... $$...$$; create table foo ( txt text ); create trigger check_txt_length before INSERT or UPDATE ... execute trf_check_length_1000(); faster: - TEXT is (judging by previous comments on this list) marginally faster than VARCHAR(1000) because a) it runs the same code but b) doesn't have to check for the 1000 length - other options (VARCHAR, constraint, trigger) incur additional overhead and are thus slower less invasive: Well, poor wording on my part, perhaps. What I meant is that changing a check constraint or trigger appears to be a less costly operation on a table than changing the datatype of a column (although I seem to remember there being some optimizations in place for the case of changing the *length* of a varchar). I may be wrong in the above and if so it better be brought to our collective attention for the benefit of readers. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
