Is it possible to define a constraint based on the values in other rows in the current table?

On 2017-07-19 13:37, Glen Huang wrote:

Hi,

I'd like to enforce that in a transaction, after a couple inserts &
updates, a particular column has continuous values like 1, 2, 3, and
never any gaps. Is it possible to do?

I gave a concrete example here:
https://stackoverflow.com/questions/45187113 didn't get any answers
yet.

Am I looking at the wrong direction? Should such feature be
implemented with constraints?

Thanks.

If the value is only used for sorting then the exact value doesn't
matter,
only that there are nu duplicates. 4,5,6 orders the exact same way as
1,2,3 or 500,540,615

You are guaranteed to get gaps anyway when you remove a record.

Personally I'd sooner create a trigger that generates a new value on
INSERT, and that
(if you *really* feel a need to) can fix gaps on DELETE and UPDATE

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

On 20 Jul 2017, at 5:26 AM, Hannes Erven <hannes@erven.at> wrote:

Hi Glen,

I'd like to enforce that in a transaction, after a couple inserts & > updates, a particular column has continuous values like 1, 2, 3, and
never any gaps. Is it possible to do?> > I gave a concrete example here: > https://stackoverflow.com/questions/45187113 didn't get any answers yet.

I've posted a comment to SO; basically what I'm saying is yes, use a transaction constraint trigger to enforce your requirement.

This previous question has an example of such a trigger:

https://stackoverflow.com/a/37277421/1980653

HTH & best regards

-hannes

Using a trigger sounds like the right approach. I’ll try it out. Thanks guys.

Hannes, thanks for posting an answer to SO, I’ve accepted it. :)

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