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 1gyeas-0003eo-1d for pgsql-admin@arkaria.postgresql.org; Tue, 26 Feb 2019 15:24:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gyeaq-0002o7-O8 for pgsql-admin@arkaria.postgresql.org; Tue, 26 Feb 2019 15:24:28 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gyeaq-0002o0-An for pgsql-admin@lists.postgresql.org; Tue, 26 Feb 2019 15:24:28 +0000 Received: from mail-ot1-x329.google.com ([2607:f8b0:4864:20::329]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gyean-0007LR-IO for pgsql-admin@lists.postgresql.org; Tue, 26 Feb 2019 15:24:27 +0000 Received: by mail-ot1-x329.google.com with SMTP id z19so11419490otm.2 for ; Tue, 26 Feb 2019 07:24:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=2/IbVXxfhk8GCv6trRF7RoHVo7dyHYJ9CdSk7JLlgs0=; b=kQfa6tXdXOGrJT70N1KM9w29YVrLMxmvRyCBrirCtMW4P4Z+bwQ5jHNQlttR4DKidM DzZjEfmZR5/bH2NTZgUc2Ajm2zI+vPOw1Pg5o1c1XCJBU6WpwpgAks9iZuNGovT/KovT X9iclHK4HnMkBn5W0C1SWaGiBG3XcVuH7ccDZucf7uUwZ4WRK9ui9clOcrOVG7yrux4n l/hBiIPZ7D2fNSf2eAIoGnirv+Jvu6h79xZzjeU3neAQk2tPuyZnf06Wh8S977SIPP6e PqIVP3UFjZUOq86JyYSWqjIFlst6B6Jrkw5fsSPpXz8adBWUnDfbHeb1DHCqSk384NLP hSOg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=2/IbVXxfhk8GCv6trRF7RoHVo7dyHYJ9CdSk7JLlgs0=; b=g66QMDQbLY1v3GOzCX+u3iywMM248xZ8Mo8ir/P/vJxJN845Pcu4pn8ocgDcppzMW5 rd6SiKbXS4EjDUkZK3WZt59vUifPIc4UuyDbqLo0Wq5bAyufu811RXhfM5dDN+CbE2xn o2Ter/4IBtLaCkDHh4KPQrxOy3SLr0XMs7+XGjgAQZUYiLiWo+kfcy+3OECp0i5eFfrY UE20TTD95zPRqmsKbfErpJIOQk8j0U8olXFPuzFfYmBcnp8ubsq72Q5api0Rr2Xf76H5 ATm71iUYusTNa6zx62xQxB5Lttma+d10ZkpcY2yBrs0MIi3vphDUEql139C/lK3DDpu+ lcww== X-Gm-Message-State: AHQUAua19ISih4l5ScNXfciC5hpQbNRZmgoxsGKQ2adCZy3WnelDiYr/ lmMu8JKCidIlVafcV7+n1dNEdRkJ X-Google-Smtp-Source: AHgI3IatpaZlN+NhKHDQlKI8GqBnTXcG0UeyzgjxH3puMxS5Z9Acp1LzeqliNXcYSP0BESXb37bKVg== X-Received: by 2002:a05:6830:1197:: with SMTP id u23mr14986204otq.254.1551194664177; Tue, 26 Feb 2019 07:24:24 -0800 (PST) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id 97sm5952322otc.25.2019.02.26.07.24.23 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 26 Feb 2019 07:24:23 -0800 (PST) Subject: Re: How to restrict To: pgsql-admin@lists.postgresql.org References: <6C004315-7728-4909-BCCD-2DE8F5DA3920@illinois.edu> From: Ron Message-ID: <27032520-e411-6506-a0ca-eaf258524ffe@gmail.com> Date: Tue, 26 Feb 2019 09:24:22 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.4.0 MIME-Version: 1.0 In-Reply-To: <6C004315-7728-4909-BCCD-2DE8F5DA3920@illinois.edu> Content-Type: multipart/alternative; boundary="------------5B4F5AA29C4C8F12375195C6" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------5B4F5AA29C4C8F12375195C6 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 2/26/19 9:11 AM, 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? > Use either a CHECK constraint or FOREIGN KEY. For just a few items*, *understanding that you must drop and recreate the constraint to modify it. http://www.postgresqltutorial.com/postgresql-check-constraint/ CREATE TABLE foo (     bar VARCHAR(20) CHECK (bar IN ('X', 'Y', 'Z')) ); For more items, and simpler additions: http://www.postgresqltutorial.com/postgresql-foreign-key/ -- Angular momentum makes the world go 'round. --------------5B4F5AA29C4C8F12375195C6 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 2/26/19 9:11 AM, 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? 


Use either a CHECK constraint or FOREIGN KEY.

For just a few items, understanding that you must drop and recreate the constraint to modify it. http://www.postgresqltutorial.com/postgresql-check-constraint/
CREATE TABLE foo
(
    bar VARCHAR(20) CHECK (bar IN ('X', 'Y', 'Z'))
);

For more items, and simpler additions: http://www.postgresqltutorial.com/postgresql-foreign-key/

--
Angular momentum makes the world go 'round.
--------------5B4F5AA29C4C8F12375195C6--