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
– Adrian Klaver, Commented Dec 27, 2020 at 3:03
Thanks Adrian, I figured out what I was missing. Have a Happy New Year! — Joseph Mudloff
– Joseph Mudloff, Commented Dec 27, 2020 at 4:40

Joseph Mudloff · Accepted Answer · 2020-12-27 06:15:49Z

1

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, 2020 at 6:15

answered Dec 27, 2020 at 4:38

Joseph Mudloff

114 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

IMSoP Over a year ago

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.

Joseph Mudloff Over a year ago

@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!

Collectives™ on Stack Overflow

PostgreSQL - CREATE TABLE with int4range constraint

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related