Обсуждение: workarounds for ci_text
Hi, 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) Will this work? Is this the right syntax? Thanks H.F.
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