[postgres] SQL-query postcode check

[Anoniem: 25455]

Ben nog een beetje bezig met de integriteit van mijn database
en hierbij maak ik gebruik van CHECK.

Bijv. bij de email_response
email_response char(1) DEFAULT ‘N’ CHECK (email_response IN(‘Y’, ‘N’)),


Check-ups van bijv. ingevoerde letters lukt nog wel, maar hoe doe je dat nu bij het invoeren van een postcode, waar je moet controleren dat de eerste 4 cijfers zijn en daarna 2 hoofdletters ???

postcode varchar(6) not null CHECK (postcode IN(......)), zou dat ongeveer hetzelfde kunnen als in PHP-check of mag je geen ranges opgeven in PSQL?


P.S. voor gebruik in PHP weet ik hem wel, maar ik wil hem specifiek gewoon ook voor mijn database. (voor eventueel voorzetje:)

code:

/[0-9]{4}(\s?)[a-zA-Z]{2}/

tnx in advance,

[jochemd]

Anoniem: 25455 schreef op 31 March 2003 @ 13:39:
Ben nog een beetje bezig met de integriteit van mijn database
en hierbij maak ik gebruik van CHECK.

Bijv. bij de email_response
email_response char(1) DEFAULT ‘N’ CHECK (email_response IN(‘Y’, ‘N’)),

Auw!!!
Gebruik toch gewoon een BOOLEAN veld.

Check-ups van bijv. ingevoerde letters lukt nog wel, maar hoe doe je dat nu bij het invoeren van een postcode, waar je moet controleren dat de eerste 4 cijfers zijn en daarna 2 hoofdletters ???

postcode varchar(6) not null CHECK (postcode IN(......)), zou dat ongeveer hetzelfde kunnen als in PHP-check of mag je geen ranges opgeven in PSQL?

P.S. voor gebruik in PHP weet ik hem wel, maar ik wil hem specifiek gewoon ook voor mijn database. (voor eventueel voorzetje:)

code:

/[0-9]{4}(\s?)[a-zA-Z]{2}/

Dan heb je hem toch al bijna?

[ Voor 3% gewijzigd door jochemd op 31-03-2003 14:01 ]

[Freee!!]

Ik zou je aanraden om die postcode check alleen uit te voeren als er voor het land (een code voor) Nederland is ingegeven. Voor andere landen gelden andere regels

[Anoniem: 25455]

1. email_response
Heb je gelijk in en ga ik ook gelijk even veranderen.

2. postcode-check
Er staat wel in hoe LIKE en SIMILAR TO gebruikt, maar niet hoe je kan definieren dat hij kijkt voor [a-zA-Z] voor de eerste vier & [0-9] voor de laatste twee. Hij laat alleen zien met bijv % wat ervoor mag komen te staan of erna.

Kan je daar nog even mee helpen dan?

[ACM]

Bekijk dit es?

[jochemd]

Anoniem: 25455 schreef op 31 maart 2003 @ 14:20:
2. postcode-check
Er staat wel in hoe LIKE en SIMILAR TO gebruikt, maar niet hoe je kan definieren dat hij kijkt voor [a-zA-Z] voor de eerste vier & [0-9] voor de laatste twee.

Jawel hoor, gewoon even doorscrollen naar het stukje over POSIX regular expressions. Het volgende werkt voor mij en moet je in een CHECK kunnen zetten

code:

test=> select '1a34AB' ~ '^[[:digit:]]{4}[[:upper:]]{2}$';
 ?column?
----------
 f
(1 row)


test=> select '1234AB' ~ '^[[:digit:]]{4}[[:upper:]]{2}$';
 ?column?
----------
 t
(1 row)


test=> select '1234aB' ~ '^[[:digit:]]{4}[[:upper:]]{2}$';
 ?column?
----------
 f
(1 row)


test=>

[Anoniem: 25455]

snap het nog niet helemaal --> moet nou toch goed zijn ??

code:

postcode varchar(6) not null CHECK (postcode IN( '^[[:digit:]]{4}[[:upper:]]{2}$'))

_{~= heb ik weggelaten omdat dat gewoon de versnelde versie van LIKE is en er
dan een bolean waarde uitkomt.
^= voor het begin van de string
$= voor het einde van de string}


Als ik nu een insert maak krijg ik:
ERROR: ExecAppend: rejected due to CHECK constraint gebruiker_postcode
terwijl postcode bestaat uit 4 cijfers en 2 hoofdletters (1234AB)

[ACM]

Euh ja...
IN is een heel andere operator dan LIKE, SIMILAR TO of ~=

en volgens mij is ~= GEEN synoniem voor LIKE...
Owja, uit 'iets LIKE ietsanders' komt ook een boolean

[ Voor 24% gewijzigd door ACM op 31-03-2003 17:17 ]

[jochemd]

code:

postcode CHAR(6) NOT NULL CHECK (postcode ~ '^[[:digit:]]{4}[[:upper:]]{2}$')

[Anoniem: 25455]

ACM schreef:
en volgens mij is ~= GEEN synoniem voor LIKE...

staat op de pagina die jij me toewees:
The operator ~~ is equivalent to LIKE, and ~~* corresponds to ILIKE
las dus verkeerd, moest een dubbele zijn, ben beetje brak vandaag

offtopic:
wel lachen, want vlak ervoor wees jochemd me ook al naar dezelfde site

Sorry, maar dat is het enige wat ik tot nu toe had geleerd over CHECK
Hoe moet je het dan coderen dat hij het alleen vergelijkt zoals mijn voorbeeld van email-response ?

[jochemd]

Trouwens, als je echt leuk wil doen, Nederlandse postcodes hebben geen F, I, O, Q, U en Y. Daarnaast komen de combinaties SA en SS niet voor en beginnen postcodes niet met een 0

[B-Man]

^[1-9]{1}[0-9]{3}[A-Z^FIOQU]{2}$

Nu nog de combinaties SA en SS...

[ACM]

Anoniem: 25455 schreef op 31 March 2003 @ 17:56:
Sorry, maar dat is het enige wat ik tot nu toe had geleerd over CHECK
Hoe moet je het dan coderen dat hij het alleen vergelijkt zoals mijn voorbeeld van email-response ?

Een check-query moet true zijn en dan accepteert de DB het veld.

Dus als er
veldnaam char(100) check (true)

staat wordt dat altijd geaccepteerd
Met:
veldnaam char(100) check (CHAR_LENGTH(veldnaam) > 10)

wordt er gekeken of ie langer dan 10 chars is (kweet niet zeker of die char_length wel bestaat)
En met iets als:
veldnaam char(100) CHECK (veldnaam LIKE '%a%')
check je of ie een a bevat

Elke vergelijking levert in principe true of false op, in een DB.
Dus ook veldnaam IN ('Y', 'N', 'U') of veldnaam BETWEEN 1 AND 100 etc

Je kan al dat soort vergelijkingen dus prima in een check-constraint kwijt, je bent niet beperkt tot het gebruik van de IN vergelijker (die checked of de waarde in een bepaalde set voorkomt)

[ACM]

B-Man schreef op 31 maart 2003 @ 18:32:
^[1-9]{1}[0-9]{3}[A-Z^FIOQU]{2}$

Nu nog de combinaties SA en SS...

Zoiets?

^[1-9]{1}[0-9]{3}([A-Z^FIOQUS][A-Z^FIOQU]|S[A-Z^FIOQUAS])$

(werkt dat A-^letters wel?)

[ Voor 11% gewijzigd door ACM op 31-03-2003 18:37 ]

[Anoniem: 25455]

jochemd schreef op 31 March 2003 @ 17:54:

code:

postcode CHAR(6) NOT NULL CHECK (postcode ~ '^[[:digit:]]{4}[[:upper:]]{2}$')

tnx, ik had

code:

postcode char(6) not null CHECK (postcode LIKE( '^[[:digit:]]{4}[[:upper:]]{2}$'))

maar die werkte niet.

Dankzij alle inzet is het nu wel gelukt en heb ik er nou wel aardig wat inzicht over de CHECK-functie gekregen.

[ACM]

Btw, een andere, mooiere (?) oplossing is het zelf een type definieren hiervoor:
http://developer.postgresql.org/docs/postgres/xtypes.html

Echter is het niet gemakkelijk om te doen en kan je als beginner er denk ik beter nog maar niet aan beginnen
Het kan wel aardig wat opslag schelen, in principe is vier bytes voldoende voor de opslag en kan je er in zo'n geval hele nuttige eigen functies en operators bij bouwen, bijvoorbeeld controleren of een bepaalde postcode in een bepaald postcode gebied ligt met een simpele operator en daarmee een hoop werk besparen als je het vaak gebruikt
Ook kan het dan in de performance aardig schelen omdat je de functies evt al in C kan bouwen, ipv in SQL.

En de check met LIKE werkt natuurlijk niet omdat PostgreSQL dan geen regexps interpreteerd.

[ Voor 7% gewijzigd door ACM op 31-03-2003 19:52 ]

[Anoniem: 25455]

ACM schreef op 31 March 2003 @ 19:51:
En de check met LIKE werkt natuurlijk niet omdat PostgreSQL dan geen regexps interpreteerd.

natuurlijk ;-)

Laat het alleen nog wel even zoals het nu is, ben er pas 8 weken mee bezig.
Voor de toekomst is het wel een optie.

[jochemd]

ACM schreef op 31 maart 2003 @ 19:51:
Btw, een andere, mooiere (?) oplossing is het zelf een type definieren hiervoor:
http://developer.postgresql.org/docs/postgres/xtypes.html

Als we toch gaan refereren aan de volgende versie van PostgreSQL (IIRC is er recent een snapshot uitgekomen die stabiel is) dan is het misschien handiger om een DOMAIN te definieren dan een geheel nieuw datatype (de huidige implementatie ondersteunt geen CHECK constraint). Heeft de voordelen van een speciaal datatype, maar is iets makkelijker dan in C zelf een datatype schrijven.

* jochemd kan niet wachten op de volgende release

[ACM]

jochemd schreef op 31 March 2003 @ 21:06:
Als we toch gaan refereren aan de volgende versie van PostgreSQL

Dat was meer omdat dat lekker vlot te laden html-files zijn ipv slome php's die alles opvissen