pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to restrict
4+ messages / 4 participants
[nested] [flat]
* How to restrict
@ 2019-02-26 15:11 Campbell, Lance <lance@illinois.edu>
0 siblings, 2 replies; 4+ messages in thread
From: Campbell, Lance @ 2019-02-26 15:11 UTC (permalink / raw)
To: pgsql-admin
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?
Thanks,
LANCE CAMPBELL<https://directory.illinois.edu/person/lance;
Software Architect
Web Services<https://webtools.illinois.edu/;
Public Affairs<https://publicaffairs.illinois.edu/;
Contact the Webtools Team<https://go.illinois.edu/contactUs;
217.333.0382
lance@illinois.edu<mailto:lance@illinois.edu>
[/var/folders/wp/1f6l7hw95y718z976kgnl5f9kr5rtc/T/com.microsoft.Outlook/WebArchiveCopyPasteTempFiles/signature_logo.png]<http://illinois.edu/;
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.
Attachments:
image001.png (image/png, 2.5K) download
view image
* Re: How to restrict
@ 2019-02-26 15:18 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Campbell, Lance <lance@illinois.edu>
1 sibling, 0 replies; 4+ messages in thread
From: Achilleas Mantzios @ 2019-02-26 15:18 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 <https://directory.illinois.edu/person/lance>*
>
> /Software Architect/
>
> Web Services <https://webtools.illinois.edu/;
>
> Public Affairs <https://publicaffairs.illinois.edu/;
>
> Contact the Webtools Team <https://go.illinois.edu/contactUs;
>
> 217.333.0382
>
> lance@illinois.edu <mailto:lance@illinois.edu>
>
> /var/folders/wp/1f6l7hw95y718z976kgnl5f9kr5rtc/T/com.microsoft.Outlook/WebArchiveCopyPasteTempFiles/signature_logo.png <http://illinois.edu/;
>
> /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
Attachments:
image001.png (image/png, 2.5K) download
view image
* Re: How to restrict
@ 2019-02-26 15:24 Ron <ronljohnsonjr@gmail.com>
parent: Campbell, Lance <lance@illinois.edu>
1 sibling, 1 reply; 4+ messages in thread
From: Ron @ 2019-02-26 15:24 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to restrict
@ 2019-02-26 15:39 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Shreeyansh Dba @ 2019-02-26 15:39 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Yes, @Ron reply makes sense you can use a CHECK constraint or FOREIGN KEY.
Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com
On Tue, Feb 26, 2019 at 8:54 PM Ron <ronljohnsonjr@gmail.com> wrote:
> 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.
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-02-26 15:39 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-02-26 15:11 How to restrict Campbell, Lance <lance@illinois.edu>
2019-02-26 15:18 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2019-02-26 15:24 ` Ron <ronljohnsonjr@gmail.com>
2019-02-26 15:39 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox