I have a question: we have a table that has (among other stuff) a couple of IP/port pairs that I would like to be globally unique to avoid crosstalk in streaming binaries that use this configuration.

Assume a table as follows

Create table config 
(
    Id serial NOT NULL,
    One_IP text,
    One_Port integer,
    Two_IP text,
    Two_Port integer
);

Then I could add constraints

   UNIQUE(One_IP, One_Port)

and

   UNIQUE(Two_IP, Two_Port)

This gets me much of the way there. The will be no crosstalk among different rows an each set.

But these are independent of each other – no "One" IP/port pair will repeat and no "Two" IP/Port pair will repeat but I could still have a One IP/Port the same as a two IP/Port (on same or different rows) Is there a way to create a constraint that combines them? (not a simple 4 column unique)

If I have these rows:

Then I shouldn't be able to insert any of the following rows ...

CodePudding user response：

Any variation on "column1 and column2 and ..." indicates a schema design problem. Lists in SQL are represented by join tables.

Also note that Postgres has network address types.

create table devices (
    id serial primary key
);

create table device_addresses (
    device_id int not null references devices(id),
    ip inet not null,
    port integer not null check(port > 0),

    unique(ip, port)
);

With this design, a device can have as many addresses as it wants, and you can avoid duplicate addresses.

You can pull add the addresses for a single device together by grouping by the device id and using string_agg to aggregate all the addresses.

select
  name,
  string_agg(ip || ':' || port, ', ') as addresses
from devices d
left join device_addresses da on d.id = da.device_id
group by d.id

Demonstration

CodePudding user response：

Maybe you need to restructure your table

Create table config (
                Id serial NOT NULL,
                IP text,
                Port integer,
                OtherConfigID integer NULL)

And that way you can put a unique constraint on IP & Port.

OtherPortID has to be NULL so you can insert the first port details when you don't have the ID of the second port config, but practically you will always populate it.