Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
I would do a CHECK (trim(a) <> '') TRIM() would add some processing time, so I'd include it only if there was a chance of spaces getting added. From a puritanical point of view, it is definitely a good idea. To the original poster, this syntax should work in MySQL as well: create table mytable (mycol text not null check (mycol <> '')); Problem is, if you created your table before MySQL 5, and now simply want to ALTER your table (which is what I gather you wish to do, as you already have the table) then adding the CHECK condition may not work. I cannot help in this case, and from the turn this thread has taken, not many others I suppose. Why not try a MySQL experts list instead of PostgreSQL, but be prepared to have to recreate the table in MySQL 5 with the CHECK constraint, and then importing your data in to it. Good luck! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
On 22/02/07, Shashank Tripathi <[EMAIL PROTECTED]> wrote: > I would do a CHECK (trim(a) <> '') TRIM() would add some processing time, so I'd include it only if there was a chance of spaces getting added. From a puritanical point of view, it is definitely a good idea. To the original poster, this syntax should work in MySQL as well: create table mytable (mycol text not null check (mycol <> '')); Problem is, if you created your table before MySQL 5, and now simply want to ALTER your table (which is what I gather you wish to do, as you already have the table) then adding the CHECK condition may not work. I cannot help in this case, and from the turn this thread has taken, not many others I suppose. Why not try a MySQL experts list instead of PostgreSQL, but be prepared to have to recreate the table in MySQL 5 with the CHECK constraint, and then importing your data in to it. Good luck! Sorry, I spoke too soon. MySQL does not do the constraints jig yet. "The CHECK clause is parsed but ignored by all storage engines." - From http://dev.mysql.com/doc/refman/5.0/en/create-table.html So you may want to adopt some kludges, such as updatable views: http://arjen-lentz.livejournal.com/49881.html If I were you, I'd just stick to error-checking in the application layer for now, or consider slowly switching to PostgreSQL. (No plug intended) Shanx ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
Zoltan Boszormenyi wrote:
I would do a CHECK (trim(a) <> '')
If you were ok with a string consisting soley of whitespace.
I meant NOT NULL CHECK(trim(a) <> ''), keeping the context of the
above example.
Right. I plead that it was late when i replied. I honestly don't know
what i was thinking.
trim() trims whitespace only from the beginning and the end of the
strings but not from the middle:
# select trim(' a b c ');
I realise that. I was thinking of this case:
select trim('');
Though, i'm not sure what my point was. Again, it was late. Carry on!
brian
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
On Thu, Feb 22, 2007 at 11:27:18AM +0100, Zoltan Boszormenyi wrote: > >>I would do a CHECK (trim(a) <> '') > > > >Whitespaces are values too, you know. > > Yes, I know. But e.g. for a real people name, would you store > accidentally entered spaces before or after the actual name, too? > Which would also ruin sorting by name. But of course, it doesn't > make sense in every case. Yeah but if you're going down that path then you either trim on the insert or use a trigger (rule?) to automatically trim your data for you. Doing it in a check wont do much of anything for you in the case you describe. -- "To the extent that we overreact, we proffer the terrorists the greatest tribute." - High Court Judge Michael Kirby ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
CaT írta: On Thu, Feb 22, 2007 at 09:13:13AM +0100, Zoltan Boszormenyi wrote: Chris ?rta: CaT wrote: On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote: create table a(a text not null check (char_length(a) > 0)); What's wrrong with using a <> '' Nothing, I just thought of the other way first :) I would do a CHECK (trim(a) <> '') Whitespaces are values too, you know. Yes, I know. But e.g. for a real people name, would you store accidentally entered spaces before or after the actual name, too? Which would also ruin sorting by name. But of course, it doesn't make sense in every case. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
On Thu, Feb 22, 2007 at 09:13:13AM +0100, Zoltan Boszormenyi wrote: > Chris ?rta: > >CaT wrote: > >>On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote: > >>>create table a(a text not null check (char_length(a) > 0)); > >> > >>What's wrrong with using > >> > >>a <> '' > > > >Nothing, I just thought of the other way first :) > > I would do a CHECK (trim(a) <> '') Whitespaces are values too, you know. -- "To the extent that we overreact, we proffer the terrorists the greatest tribute." - High Court Judge Michael Kirby ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Checking for string data that makes sense Re: [GENERAL] postgresql vs mysql
Zoltan Boszormenyi wrote: Chris írta: CaT wrote: On Thu, Feb 22, 2007 at 01:08:04PM +1100, Chris wrote: In postgres, to stop an empty blank string: create table a(a text not null check (char_length(a) > 0)); What's wrrong with using a <> '' sd the check? Or is this just a flavour thing? Nothing, I just thought of the other way first :) Probably better doing it as a <> '' otherwise postgres might have to run the char_length function every time you do an insert (ie might be a very slight performance issue). I would do a CHECK (trim(a) <> '') If you were ok with a string consisting soley of whitespace. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
