PostgreSQL - CREATE TABLE with int4range constraint

Question

I want to use a range constraint in creating a table, as it seems more elegant than what I'm currently doing:

DROP TABLE IF EXISTS "coa_sandbox"."account_list";
CREATE TABLE "coa_sandbox"."account_list" (
id serial unique not null,
account_number int unique not null CONSTRAINT within_range CHECK (account_number >= 10000 AND account_number <= 99999),
account_name text unique not null
) WITH (oids = false);

Maybe it's silly, but I think using int4range seems to be a more professional approach.

I've been using Erwin Brandstetter's solution to this problem [https://stackoverflow.com/a/35028185] as inspiration, without success. I re-created my table above, without the constraint, then tried altering the table. I started with:

ALTER TABLE "coa_sandbox"."account_list" ADD CONSTRAINT within_range
  CHECK ("account_number" = ANY ('{9999,99999}'::int4range[]));

... with this result:

Error in query: ERROR: malformed range literal: "9999"
DETAIL: Missing left parenthesis or bracket.

Note that I'm using Adminer 4.7.8 to perform these queries.

I've continued trying with different combinations of brackets, curly braces & parenthesis, using the error details and my intermediate level of knowledge regarding these items in Perl as influence.

I got nuthin'. I've been searching the PostgreSQL docs for details on syntax, and if the solution is evident then I'm just not seeing it.

Will someone please set me straight on this? If my initial working solution is the best approach, please tell me. If I'm heading in the right direction but missing the correct details, please tell me what I'm missing.

Thank you.

You are mixing a range type with and array. `'{9999,99999}' ` is a an array construct and `[]` marks something as a an array of the given type. `int4range` is a range type and cannot be made an array. — Adrian Klaver, Dec 27 '20 at 03:03
Thanks Adrian, I figured out what I was missing. Have a Happy New Year! — Joseph Mudloff, Dec 27 '20 at 04:40

Joseph Mudloff · Accepted Answer · 2020-12-27T06:15:49.117

0

Finally figured it out. The CONTAINED IN (<@) operator is what I was looking for.

The statement I've wanted is:

DROP TABLE IF EXISTS "coa_sandbox"."account_list";
CREATE TABLE "coa_sandbox"."account_list" (
id serial unique not null,
account_number int unique not null CONSTRAINT within_range
  CHECK ("account_number" <@ int4range(10000,100000)),
account_name text unique not null
) WITH (oids = false);

edited Dec 27 '20 at 06:15

answered Dec 27 '20 at 04:38

Joseph Mudloff

1
3

Note that the part of this code that gets rid of the error shown is that you're constructing the range differently. In your original code you were trying to parse the literal as `int4range[]` which means "an array of ranges of integers"; in this code, you are correctly constructing a single `int4range`. – IMSoP Dec 27 '20 at 11:17
@IMSoP Thank you for chiming in on this. I learned that the hard way... Adrian's comment made me take a step back and look at it from other angles, finally finding the relevant operator info in the docs. Hopefully your comment will help clarify this for anyone who comes across this as a search result. Cheers! – Joseph Mudloff Dec 28 '20 at 14:56

PostgreSQL - CREATE TABLE with int4range constraint

1 Answers1