--
Sent via pgsql-admin mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Please keep the list posted!
Nguyen Hoai Nam wrote:
> Step1: I would to create "network" table with three columns including id, network_id, subnet. It's
> like below:
>
> +--------------------+---------------+
> | id |network_id | subnet |
> +------------------------------------+
> | 1 | aa |192.168.1.0/24 |
> | | | |
> | | | |
> +--------+-----------+---------------+
>
> This table have condition: If a new record with overlap subnet and same value's network_id then DB
> will not allow inster to DB
>
> For example:
>
> Request1: test=> INSERT INTO network VALUES (2, aa,'192.168.1.0/24');
> The result is that DB doesn't allow to insert to DB. Becase it violate overlap CIDR and same value's
> network_id
>
> Request2: test=> INSERT INTO network VALUES (3, bb,'192.168.1.0/24');
> The result is that DB ALLOW to insert to DB. Because this reqest has network_id = bb, this value is
> different with existing value (aa)
This is getting more difficult, but you can *still* do it with an exclusion
constraint in PostgreSQL. You need to install an extension with a
GiST operator class for varchar:
test=# CREATE EXTENSION btree_gist;
Then you can do the following:
CREATE TABLE network (
id integer PRIMARY KEY,
network_id varchar(20) NOT NULL,
subnet cidr NOT NULL
);
ALTER TABLE network
ADD CONSTRAINT network_subnet_excl
EXCLUDE USING gist (
network_id gist_text_ops WITH =,
subnet inet_ops WITH &&
);
Then you get:
test=> INSERT INTO network VALUES (1, 'aa','192.168.1.0/24');
INSERT 0 1
test=> INSERT INTO network VALUES (2, 'aa', '192.168.1.0/24');
ERROR: conflicting key value violates exclusion constraint "network_subnet_excl"
DETAIL: Key (network_id, subnet)=(aa, 192.168.1.0/24) conflicts with existing key (network_id, subnet)=(aa, 192.168.1.0/24).
test=> INSERT INTO network VALUES (3, 'bb', '192.168.1.0/24');
INSERT 0 1
As Kevin said, using SERIALIZABLE transactions is an alternative, but a constraint
is probably better.
Yours,
Laurenz Albe
Nguyen Hoai Nam wrote:
> 2016-05-24 17:31 GMT+07:00 Albe Laurenz <lauren...@wien.gv.at>:
>> This is getting more difficult, but you can *still* do it with an exclusion
>> constraint in PostgreSQL. You need to install an extension with a
>> GiST operator class for varchar:
>>
>> test=# CREATE EXTENSION btree_gist;
>
> At this point, the system require superuser permission. But in my case, an user doesn't have superuser
> permission so they can't create a btree_gist extersion. Do you any idea to solve this? Currently, I am
> trying to use "GIN" but it's not still success. How about "GIN"?
Yes, you need a superuser to install the extension for you.
This has to be done only once, and from then on everybody can
use the feature.
I'm sure there is a superuser who can do that for you, right?
Yours,
Laurenz Albe