Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gyeVE-0003Nh-4b for pgsql-admin@arkaria.postgresql.org; Tue, 26 Feb 2019 15:18:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gyeVC-00062Q-A2 for pgsql-admin@arkaria.postgresql.org; Tue, 26 Feb 2019 15:18:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gyeVB-000616-Vj for pgsql-admin@lists.postgresql.org; Tue, 26 Feb 2019 15:18:38 +0000 Received: from hermes.gatewaynet.com ([193.92.121.98]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gyeV3-0003ts-Ko for pgsql-admin@lists.postgresql.org; Tue, 26 Feb 2019 15:18:37 +0000 Received: from smadev.internal.net (pc131.internal.net [10.9.200.131]) by hermes.gatewaynet.com (8.15.2/8.15.2/Debian-8) with ESMTPS id x1QFIL77025496 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT) for ; Tue, 26 Feb 2019 17:18:26 +0200 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id x1QFILrD014013 for ; Tue, 26 Feb 2019 17:18:21 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: How to restrict To: pgsql-admin@lists.postgresql.org References: <6C004315-7728-4909-BCCD-2DE8F5DA3920@illinois.edu> From: Achilleas Mantzios Message-ID: <11341ee7-23e6-adfb-b14f-218eeffa8102@matrix.gatewaynet.com> Date: Tue, 26 Feb 2019 17:18:21 +0200 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:60.0) Gecko/20100101 Thunderbird/60.3.2 MIME-Version: 1.0 In-Reply-To: <6C004315-7728-4909-BCCD-2DE8F5DA3920@illinois.edu> Content-Type: multipart/alternative; boundary="------------3680213CDC97B5F499948481" Content-Language: en-US X-Virus-Scanned: clamav-milter 0.100.2 at hermes X-Virus-Status: Clean X-Spam-Status: No, score=-27.0 required=5.0 tests=ALL_TRUSTED,DYN_HAM_COORD, DYN_HAM_IT,DYN_HAM_NAUTICAL,DYN_SPAM_MED6,HTML_MESSAGE autolearn=unavailable autolearn_force=no version=3.4.2 X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on hermes.gatewaynet.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------3680213CDC97B5F499948481 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 26/2/19 5:11 μ.μ., Campbell, Lance wrote: > > PostgreSQL 10.x > > What is the best way to restrict the values on a text field to make sure they only contain particular values:  Example:  The field “type” can only contain the values of “X”, “Y” and “Z”. Would a > trigger be the best strategy?  Or is there a special SQL type I should use in this particular case? > Easy with a CHECK CONSTRAINT, like : create table baba (type varchar(10) CHECK (type in ('X','Y','Z'))); > > Thanks, > > *LANCE CAMPBELL * > > /Software Architect/ > > Web Services > > Public Affairs > > Contact the Webtools Team > > 217.333.0382 > > lance@illinois.edu > > /var/folders/wp/1f6l7hw95y718z976kgnl5f9kr5rtc/T/com.microsoft.Outlook/WebArchiveCopyPasteTempFiles/signature_logo.png > > /Under the Illinois Freedom of Information Act any written communication to or from university employees regarding university business is a public record and may be subject to public disclosure./ > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt --------------3680213CDC97B5F499948481 Content-Type: multipart/related; boundary="------------120D58F88D06816001217EE3" --------------120D58F88D06816001217EE3 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 26/2/19 5:11 μ.μ., Campbell, Lance wrote:

PostgreSQL 10.x

 

What is the best way to restrict the values on a text field to make sure they only contain particular values:  Example:  The field “type” can only contain the values of “X”, “Y” and “Z”.  Would a trigger be the best strategy?  Or is there a special SQL type I should use in this particular case? 

Easy with a CHECK CONSTRAINT, like :
create table baba (type varchar(10) CHECK (type in ('X','Y','Z')));

 

Thanks,

 

LANCE CAMPBELL

Software Architect

 

Web Services

Public Affairs

Contact the Webtools Team

217.333.0382

lance@illinois.edu

 

 

/var/folders/wp/1f6l7hw95y718z976kgnl5f9kr5rtc/T/com.microsoft.Outlook/WebArchiveCopyPasteTempFiles/signature_logo.png

 

Under the Illinois Freedom of Information Act any written communication to or from university employees regarding university business is a public record and may be subject to public disclosure.

 



-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--------------120D58F88D06816001217EE3 Content-Type: image/png; name="image001.png" Content-Transfer-Encoding: base64 Content-ID: Content-Disposition: inline; filename="image001.png" iVBORw0KGgoAAAANSUhEUgAAAHcAAAAVCAYAAAELAkFNAAAAAXNSR0IArs4c6QAAAARnQU1B AACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAm/SURBVFhH7Vl9iB1XFX8bo8XmY/Pe zNt0ZWPK5u2+mXnZDUlqIpJ2bdmP9+bOvE2abpMSQQVNK1j9S0SkhCIqftAiBautoAUxUhSk UCEQCVSrbdraNlC01dRu0tjW3STbbTTU1Yy/35l7J/Pem32J+WhSzQ8uc++5n+ece885906u y60921cZezF3rnjDL0eSWb/zvQXH38us5aiHhQYUXPVHnc0VV2+JbDeoFZzwrtzawdFTrzd2 3mdX6pHlhdEy118pdA0bNPm6yrdXb45yXHJ/pXpuyz6kKvGsgOX5t+uswK6MR5ar3uQszJPW tTpcblcwKzHl90cH1YAUrP7qZ7hk5vllp4KjXpNBytUdtleP2BksvVIc2BoJzxyAfJtBBOCf g1EGmiKwyurnOpsTvtPfgS3yRZ+bKR8jI1kt5eSonWxbHLg5nodsJ9oy4MQQgdGcQVqDRgyg Hba9YNZy/VMxPeacoGbjiYMay8uXjyyy3CBK9td5KexcYCZlyjsjqzX54uPZ6hrR76s1J8pd u32jJkNvsRgTfQB2Kg/xfoVf6rXoDS1mvuD6R/hlH9lUXniIZSvZdOPQO85MuV5mOceJ6+s+ Oi0FDaMn6kgIgJ3KY7vfzS83Cw+f0Bx/Eukx5gnocka+5iDi63kT72NecL4TS9kLIpnYVY+z TEAqR+WrJyZsT92PTbjnjKLmgFwExDhLUcdnFGJMiZrfoheWWkVdP8hyg6iRutcHV1+6zfXh wZGZY6o/mm5Kc/VSlFux/TrdLI0O4a5UXcqCtaL+AZalpgmk8wzrYoKl3mgh3Yd52xnr1kUB ac1Gmyh6waaCpyZoDzQpxsTEe9hHpHkaHVYlCLGGQV0+jUkYL6qaBmxs7U1TmtwKGDSq2dh1 Grb0/kuDezFt6Ay4L6lqXYwPhquwddL7GDTsVeYp3PgkhtHi/sAmDQfmh+yD+fezrN2AuD+W 2R+ucZb5a0rVIvyLbLcEtNaG4eaD1YCLxbCjDuXd2j3CRLm2Nc0w6may5gDTD5hxmhkueqMl zi+uiuewUhcvkuD8NLw56lwzvqxzQOUlrRxfxjpOCIf8i4SOlMvtWjAPw4d1UYwAGTcM8wiI hjHvosEtXaTFVkg0/GuWmxlG3fO2V7uLeUI0TpN6Qc5wpboqnTrLY9eyju0a6zavyg0NLcw6 w83nlWNknWHb9W8olIOtPT23vF+TYmSfYTit+jACkOt1ka64mljLdOr21DsXD7xTeDPoa9Es 06c23JB5JinxZs10ayOSBrdXul0apBcGqj1xfmJxczur/JElmtYRU2LwOGC7boPvS3ynQVef 6m0ep1hRaxhalkrVq4TwQm0wernmyrll+hvS76uD0Y6Nw5nMwmtPpmMeOmCrHNyniwkKZX+3 cc7N4NlE/TPM513/Vjr5ghe8JJUA5viYBACp8ATn8nhseCSGkpi42F/dpKt5STnJ86mLmEMb KtDEWHmhWOqcOzC6n9oks7MBY+lvLJGKDGAhL7e6DPUdXUwAo/Dj9ORp0MqiXlwI/agYJSSE 1XeQZnlqBxdomAUjR0Qg5eDLLNMGCBOyjl0LpI2j3jLGDxeMemy0VB/LxXLwhWQ3rBscfbqB 2UV3iOXLwsVhFvGg6/+Di5ULRROzHAftn2PegEcnjiPVD1hOM2s7wZAW4CQCjQ+RluBSM8sw Xujcmqxz1bBhdomz2eJ2LHj+nWyThrg4Vx1gPs0sYbvhk6ynQLil4c6+KhWXA7Pd3cHVsj3B GDUtAjCaxW0bc7zAvEG+d6JThOD632e5mVmDWMvB28la3IGx8zqzuEh/zwQP9KMgdxhmG4IO JPbJYtb4Tox/H7dgmlmUp2Sre/GtJO8FHyRjeh1isdPM0t9iZ/zbxODo/6DUXQhrLFaPFg+J URWDDlpjc5UyiWX2abbGXEg6UIBlnpXFaWYJXK1OiNEhk/hKtNVfTc5j2hpTgKynwEhjPzB8 4IL42YZISep2LRA/m6YjdQ2oXtMnw882+NQsGo1S0atPtBgdIMvP0hpDOeMm1Pz/iqCuGxw5 +VflRod9L5o8izQTOtEnN94Y5XpubZHuFVxm4OXn7zi6s/q4vqZtVTqRNoU0g/oIl6KdG66f 72LUCj5oOv5LsBdzdmXLHG5gn9Y1Aqvs74F3mSuiDkfsXk1uC7R7SMZCP+R3a3Jb2J6/i3PY 3jj7/E6TBbBpt8RjBXNWpX4UHmudrmoAbONtbEdewNPBtA1No1iqrrIq4QMY7w2xz3RaTLCZ oB2y3fq3F/WOZHp4ubU66rjlhrJWmKKqrkoAB7gdNvv5xGYjSYzlhgexxm/BR4hJzBUd/5c9 Ff9XeVzJR9fedBzKTJ67TToB5d++YdPJpeXgsRUVf2+XF+yzymPx8+WZQOXSQwuTjGsaH+/h dfeScXrgrBAlC7Enh+Ojc8l4G8kCHNDdnIOCMJ7eQDw+x9KReuwgaz/R1QkkmKWS6Ez5zNCk 3IJTG0mcLcMjGdOfxhp/Awe53zhJziHhkhu+lXdvrOjuAiqXkUOiOP00QdAnxVEFnTmcquMf Q2j1CMbbgzRNmcjaXfVivne4U3eLwfiRD1Q8wUaxfLdheCWPVdfcWdRNzx7vOuUyZGOC8Lzw n7ZbXaubtVVuoaw+LxEPFYc2EPrndFULMMePzHz85t1aosB2ygX948I31+YGM2yrqxK0PMka nFG5bS4L8+JdpNzYbKoDCGs/wXYUvpwEzMd2tuPfJu2alEuBos3rEtKSj3LwNdLbAe0fjceS eX6ryW2VmytVr+L6kk0koTFlKsr+M0Lu79rO6HrduhFXlEul8aV3aCHDfZi3p2QOCs9RR3GH uRf5U9nKVYlyYZ6/KQO3Ac1pSrnJP622yj2NBYwT0GY3lHo4ka22OvydjA0W6rYxLrlZdtU9 mtwWjcr1f6rJbfHfKDd9AbXd2jaZh4ITsx2fmGazzJevtFlG/Rd1VQugvPtjZRgTroZ11RmV 24n7XvLC1oQCgkaRC/qCxz9dNgEVGWXAgbZTyE+nk+0F02j3B16IdR9RLvtAiG9n9YF/nMZ4 R8xlGrRzUq5gaGghxnrarDNLuQRPiyiE9SagQtCDPo9j3CegtBPJGPINjptnUYM2yu0ATw93 rZmQ06n5n4Gi9+H7M/D0XOyL6Uq2gkf12cvmKgTm5002rgUY41X+QZA++iqU1TZJuNZQSPnK +Ar2OZurEK4o7PuXFuVqIPDZZlXGT5IXrGfeq1Chz3ehnIewhqOiIG4cJgrfC18puOHX9Rte C6hcrGHeq1DnyqFlWO+XoOADUOS/ZHxE3uIq3PAYxn/Q/P698ojxP4tc7j8gMqgPv+E4KQAA AABJRU5ErkJgggAAAAAA --------------120D58F88D06816001217EE3-- --------------3680213CDC97B5F499948481--