I have table called "strukturvieniba" which has fields "id_strukturvieniba" which is int, primary key and field "nosaukums" which is text. I'd like to create a constraint or check (i'm not sure which one is it) so I won't be able to instert anything but letters in "nosaukums" field. How can I do that?
Add a comment
|
2 Answers
You could add check (nosaukums ~ '^[:alpha:]*$') to your CREATE TABLE statement (or maybe create an explicit constraint). This will check for letters.
Could look like
ALTER TABLE strukturvieniba
ADD CONSTRAINT nosaukums_check CHECK (nosaukums ~ '^[:alpha:]*$'::text);
5 Comments
MarisP
What would be the full expresion? I'm messing up.
MarisP
yeah, thanks, it sort of helps with my problem... The thing is that i'm using UTF-8, so whenever I enter letter like "ā" or "ž" it thinks it is not text. Is there a way to fix that?
frlan
Puh. Not sure for UTF characters, whether there is some good regex for. Looking a little around I found kind of inverted approach: stackoverflow.com/questions/3820034/… -- so don't look for matching characters but not matching ones.
MarisP
Yeah, I guess this would work. But the thing is that there's just so much of those symbols, that it will take forever for me to type them. I guses I'll just have live without the UTF-8's.
frlan
Maybe you don't need to filter too much and a [[:alpha:]ž] etc would do a better. Just play a little around with regex I suggest
Try Like This
Add Check constraint
CHECK(nosaukums ~* '^[a-zA-Z]')
