排除 PG建模器中的约束

Question

我想在pgmodeler中的表中添加排除约束。

示例表：

create table x (
lable varchar(2) not null,
timerange tstzrange not null,
primary key (lable, timerange))
);

我想补充一点：

alter table x add constraint ex_x_overlap
exclude using gist(lable with =, timerange with &&);

我在 pgmodeler 中尝试过什么

• 打开表属性
• 选择的约束
• 添加约束
• 选择一个名称
• 选择类型"排除">
• 添加第一个排除元素，列"标签"，但没有可用的运算符

我的问题是运算符字段是强制性的，但不是自由文本。我需要从其中一个架构中选择一个运算符。但是"="不存在(实际上没有...

我想我误解了什么，感谢任何帮助！

Answer 1

pgmodeler 中的排除约束。

请参考 ： https://m-thirumal.github.io/installation_guide/#/pgmodeler/Exclusion%20Constraints

1. 创建扩展
2. 创建虚拟函数
3. 创建虚拟运算符
4. 向表添加约束

创建虚拟函数：

1. 点击Objects->projects->schema->pg_catalog->function->Create function参数和返回类型

<function name="dummy_func" sql-disabled="true"
window-func="false"
returns-setof="false"
behavior-type="CALLED ON NULL INPUT"
function-type="VOLATILE"
security-type="SECURITY INVOKER"
parallel-type="PARALLEL UNSAFE"
execution-cost="1"
row-amount="0">
<schema name="pg_catalog"/>
<role name="postgres"/>
<language name="sql"/>
<return-type>
<type name="bigint" length="0"/>
</return-type>
<transform-types names="bigint"/>
<parameter name="p1">
<type name="bigint" length="0"/>
</parameter>
<parameter name="p2">
<type name="bigint" length="0"/>
</parameter>
<definition> <![CDATA[null]]> </definition>
</function>

！> 创建函数disable SQL后

创建虚拟运算符：

1. 点击Objects->projects->schema->pg_catalog->function->Create Operator

<operator name="=" sql-disabled="true">
<schema name="pg_catalog"/>
<role name="postgres"/>
<type name="bigint" length="0" ref-type="left-type"/>
<type name="bigint" length="0" ref-type="right-type"/>
<function ref-type="operfunc" signature="pg_catalog.dummy_func(bigint,bigint)"/>
</operator>

！> 创建函数disable SQL后

将其添加到表

Open Table->Constraint->Create new Constraints-> 选择Exclude Constraints-> 选择列

<table name="party_relationship" layers="0" collapse-mode="2" max-obj-count="18" z-value="0">
<schema name="icms"/>
<role name="postgres"/>
<position x="1580" y="2220"/>
<column name="party_relationship_id" not-null="true">
<type name="bigserial" length="0"/>
</column>
<column name="contemporaneous" not-null="true" default-value="''::hstore">
<type name="public.hstore" length="0"/>
<comment> <![CDATA[What is the identification, name, address, email and phone number is used at the time of case. PK of these tables will be stored along with table name]]> </comment>
</column>
<column name="start_time" not-null="true" default-value="current_timestamp">
<type name="timestamptz" length="0"/>
</column>
<column name="end_time" not-null="true" default-value="'infinity'::timestamp">
<type name="timestamptz" length="0"/>
</column>
<column name="description">
<type name="varchar" length="500"/>
<comment> <![CDATA[Any additional Information]]> </comment>
</column>
<column name="row_created_on" not-null="true" default-value="current_timestamp">
<type name="timestamptz" length="0"/>
</column>
<column name="row_updated_on" not-null="true" default-value="current_timestamp">
<type name="timestamptz" length="0"/>
</column>
<column name="row_update_info">
<type name="varchar" length="200"/>
</column>
<constraint name="party_relationship_pk" type="pk-constr" table="icms.party_relationship">
<columns names="party_relationship_id" ref-type="src-columns"/>
</constraint>
<customidxs object-type="column">
<object name="icms_case_id" index="2"/>
<object name="l_party_id" index="3"/>
<object name="parent_party_relationship_id" index="1"/>
<object name="r_party_id" index="4"/>
<object name="relationship_cd" index="5"/>
<object name="row_created_by" index="11"/>
<object name="row_updated_by" index="13"/>
</customidxs>
<customidxs object-type="constraint">
<object name="icms_case_fk" index="4"/>
<object name="l_party_fk" index="3"/>
<object name="party_fk" index="6"/>
<object name="party_fk1" index="7"/>
<object name="party_relationship_fk" index="5"/>
<object name="r_party_fk" index="2"/>
<object name="relationship_cd_fk" index="1"/>
</customidxs></table>

对于差异

在Import & Export->检查Import System Objects和Import extension objects