On Tue, Mar 11, 2025 at 3:06 AM Achilleas Mantzios asked:
> is it still harder than the trigger ?
>
I think the trigger wins: no extension needed, arguably better error
output, easier to understand at a glance, and can quickly change the
business logic by adjusting the function. Pretty short too.
On 3/11/25 00:28, Rhys A.D. Stewart wrote:
Greetings,
I think I got it :
ALTER TABLE shelves ADD CONSTRAINT shelves_excl EXCLUDE USING gist(
array_remove((ARRAY[l_mug_id,c_mug_id,r_mug_id]::int[]),NULL) WITH && );
but the caveat is I had to cast to 32-bit int array. Haven't found any opcla
Greetings,
> I think I got it :
>
> ALTER TABLE shelves ADD CONSTRAINT shelves_excl EXCLUDE USING gist(
> array_remove((ARRAY[l_mug_id,c_mug_id,r_mug_id]::int[]),NULL) WITH && );
>
> but the caveat is I had to cast to 32-bit int array. Haven't found any
> opclass for int8 (bigint) in intarray
On 3/10/25 10:43, Achilleas Mantzios - cloud wrote:
On 3/8/25 21:01, Rhys A.D. Stewart wrote:
Greetings All,
I have the following table:
CREATE TABLE shelves(
shelf_id bigint PRIMARY KEY,
l_mug_id bigint UNIQUE,
c_mug_id bigint UNIQUE,
r_mug_id bigint UNIQUE,
CHECK
Hey Rhys,
I think you might be better served doing something like
CREATE TABLE shelve_items(
id bigint PRIMARY KEY,
shelf_id bigint,
column_name VARCHAR,
mug_id bigint UNIQUE
)
and then putting a unique index on (shelf_id, column_name):
CREATE UNIQUE INDEX shelve
On 3/8/25 21:01, Rhys A.D. Stewart wrote:
Greetings All,
I have the following table:
CREATE TABLE shelves(
shelf_id bigint PRIMARY KEY,
l_mug_id bigint UNIQUE,
c_mug_id bigint UNIQUE,
r_mug_id bigint UNIQUE,
CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id AND c_mu
Greetings All,
I have the following table:
CREATE TABLE shelves(
shelf_id bigint PRIMARY KEY,
l_mug_id bigint UNIQUE,
c_mug_id bigint UNIQUE,
r_mug_id bigint UNIQUE,
CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id AND c_mug_id
<> r_mug_id),
EXCLUDE USING gist (l_mug_i
On Sat, 2025-03-08 at 14:01 -0500, Rhys A.D. Stewart wrote:
> I have the following table:
>
> CREATE TABLE shelves(
> shelf_id bigint PRIMARY KEY,
> l_mug_id bigint UNIQUE,
> c_mug_id bigint UNIQUE,
> r_mug_id bigint UNIQUE,
> CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_i
On Sat, Mar 8, 2025 at 12:01 PM Rhys A.D. Stewart
wrote:
>
> CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id AND c_mug_id
> <> r_mug_id),
> EXCLUDE USING gist (l_mug_id WITH <>, c_mug_id WITH <>, r_mug_id
> WITH <>) -- Not working as expected (or my expectations are wrong).
> );
>
>
