<div xmlns="http://www.w3.org/1999/xhtml">Dear Gunce,</div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml">It is expected behavior since PostgreSQL 9.5. (It is not a bug, it is feature :)) CHECK constraints created locally on foreign tables are not actually enforced. These constraints are used by local planner if <span>constraint exclusion</span> is enabled. So, you can insert any row to foreign table which does not violate check constraints on the remote side. If you enable constraint exclusion then select the students_fdw foreign table for id > 10, no result returns to you. Please have a look to following example.</div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml">postgres=# \d+ students_fdw;<br /> Foreign table "public.students_fdw"<br /> Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description<br />--------+-------------------+-----------+----------+---------+-------------+----------+--------------+-------------<br /> id | integer | | | | | plain | |<br /> name | character varying | | | | | extended | |<br />Partition of: students_local FOR VALUES FROM (1) TO (10)<br />Partition constraint: ((id IS NOT NULL) AND (id >= 1) AND (id < 10))<br />Server: remote_pg10<br /><br />postgres=# select * from students_fdw;<br /> id | name <br />-----+--------<br /> 5 | Samed<br /> 100 | Deneme<br /> 100 | Deneme<br /> 150 | Deneme<br /> 150 | Deneme<br />(5 rows)<br /><br />postgres=# select * from students_fdw where id > 10;<br /> id | name<br />----+------<br />(0 rows)<br /> </div><div xmlns="http://www.w3.org/1999/xhtml">postgres=# <strong>set constraint_exclusion = off;</strong><br />SET<br />postgres=# select * from students_fdw where id > 10;<br /> id | name <br />-----+--------<br /> 100 | Deneme<br /> 100 | Deneme<br /> 150 | Deneme<br /> 150 | Deneme<br />(4 rows)</div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml">Best regards.</div><div xmlns="http://www.w3.org/1999/xhtml">İyi çalışmalar.</div><div xmlns="http://www.w3.org/1999/xhtml">Samed YILDIRIM</div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml"> </div><div xmlns="http://www.w3.org/1999/xhtml">03.10.2018, 11:34, "Günce Kaya" <guncekaya14(at)gmail(dot)com>:</div><blockquote xmlns="http://www.w3.org/1999/xhtml" type="cite"><div> <div><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;"><div> <p style="line-height:1.38;margin-top:0pt;margin-bottom:0pt;"><span style="background-color:transparent;color:#000000;font-family:courier new;font-size:11pt;font-style:normal;font-variant:normal;font-weight:400;text-decoration:none;vertical-align:baseline;white-space:pre-wrap;">If I insert a row to partitioned table which is in local, a partitioned table’s constraints should work. If I add a constraint on a partitioned table, I should be make sure I can not insert a row which is out of partitioned table’s constraints. But I could. I think, partitioned table’s constraints should work with regard to environment. If I work on local, constraints work for local tables and should not consider other environment’s constraints. </span></p></div></blockquote><div> </div><div><span style="font-family:monospace,monospace;">To be more clear, I would like to add EXPLAIN ANALYZE script.</span></div><div> </div><div><span style="font-family:monospace,monospace;">artemis=# explain (analyze,verbose) insert into students values(2,'another name');<br /> QUERY PLAN <br /> <br />--------------------------------------------------------------------------------------<br />------------------<br /> Insert on public.students (cost=0.00..0.01 rows=1 width=36) (actual time=1.536..1.53<br />9 rows=0 loops=1)<br /> <strong><span style="color:#ff0000;"> Remote SQL: INSERT INTO public.students(id, name) VALUES ($1, $2)</span></strong><br /> -> Result (cost=0.00..0.01 rows=1 width=36) (actual time=0.005..0.013 rows=1 loop<br />s=1)<br /> Output: 2, 'another name'::character varying<br /> Planning time: 0.063 ms<br /> Execution time: 2.877 ms<br />(6 rows)</span></div><div> </div><div><span style="font-family:monospace,monospace;">We use push-down for INSERT statement but I think constraints should be checked before sending SQL statements then INSERT statements should be perform. </span></div><div> </div><div><span style="font-family:monospace,monospace;">If it's nature, why do we require to add unused constraint for FDW side only?</span></div><div> </div></div><span style="font-family:monospace,monospace;">-- </span><div><span style="font-family:monospace,monospace;">Gunce Kaya</span></div><div> </div><div><a target="_blank" style="color:rgb(17,85,204);" href="https://tr.linkedin.com/in/guncekaya">Linkedin</a><span style="font-size:small;"> - </span><a target="_blank" style="color:rgb(17,85,204);" href="https://twitter.com/gguncesi">Twitter</a><span style="font-size:small;"> - </span><a target="_blank" style="color:rgb(17,85,204);" href="http://www.guncekaya.blogspot.com.tr/">Blog</a></div></div></blockquote>