workarounds for ci_text

On Thu, 4 Feb 2016 13:48:37 +0200
Heine Ferreira <heine.ferreira@gmail.com> wrote:

As far as I can tell, you can't restrict the length of a ci_text field like
char(5) or varchar(5)?
Let's say you got a stock table and you want an alphanumeric stock
code that is the primary key but it must be case insensitive can I do
something like this:

create table stock(stock code varchar(5), stock_desc varchar(50))
primary key pk_stock_code ilike(stock_code)

You may be better served by using a check constraint to enforce
the length limit, or even creating a domain:
http://www.postgresql.org/docs/9.5/static/ddl-constraints.html
http://www.postgresql.org/docs/9.5/static/sql-createdomain.html

But that depends on whether you're only trying to enforce the
uniqueness or if you want things such as case insensative matching
of the key. Your approach will only give you the former, whereas
CITEXT will give you both.

I don't think your syntax will work, though. I'm guessing that
PRIMARY KEY pk_stock_code lower(stock_code) will, though.

--
Bill Moran

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general