How to prevent inserts into the parent table?

Question

There is a table t that has inherited children. I want only children to receive inserts. What is the best way to make the parent table reject inserts?

create table t (c int); create table t1 () inherits (t); 

This should not be possible:

 insert into t (c) values (1); 

EDIT:

I found a model visible solution in addition to what @wildplasser had:

 create table tfk (c integer unique check(false)); create table t (c integer, foreign key (c) references tfk(c)); 

Now insert into t UNLESS is impossible, this value is null, and insert into its children is still possible. This may be a good solution if this column n is already bounded as not null , but otherwise it is not enough. Or does anyone know a trick to do the work above for null values?

News:

I requested a new syntax in the postgresql list, and this was done for 9.2:

Allow CHECK restrictions to be declared NO INHERIT (Nikhil Sontakke, Alex Hunsaker)

This makes them valid only in the parent table and not in the child tables.

Answer 1

You can use a trigger before insertion to raise an error or redirect to the correct table.

Answer 2

 create table t (c int, check (false) no inherit); 

This will prevent insertion into table t . It adds a constraint that will never be true, so data cannot be inserted. no inherit prevent this restriction from affecting child tables.

Answer 3

This is ugly, but it seems to work:

 --SET search_path='tmp'; DROP TABLE dontinsert CASCADE; CREATE TABLE dontinsert ( id INTEGER NOT NULL PRIMARY KEY ); DROP TABLE doinsert CASCADE; CREATE TABLE doinsert () INHERITS (dontinsert) ; CREATE RULE dont_do_it AS ON INSERT TO dontinsert DO INSTEAD NOTHING ; INSERT INTO dontinsert(id) VALUES( 13) ; INSERT INTO doinsert(id) VALUES( 42) ; SELECT id AS id_from_dont FROM dontinsert; SELECT id AS id_from_do FROM doinsert; 

Result:

 SET NOTICE: drop cascades to table doinsert DROP TABLE NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "dontinsert_pkey" for table "dontinsert" CREATE TABLE ERROR: table "doinsert" does not exist CREATE TABLE CREATE RULE INSERT 0 0 INSERT 0 1 id_from_dont -------------- 42 (1 row) id_from_do ------------ 42 (1 row) 

UPDATE: since the OP wants INSERTSs to fail with a lot of noise, I had to add a channel table to it with an impossible limit:

 DROP TABLE alwaysempty CASCADE; CREATE TABLE alwaysempty ( id INTEGER NOT NULL ); ALTER TABLE alwaysempty ADD CONSTRAINT dont_insert_you_sucker CHECK (id > 0 AND id < 0) ; CREATE RULE dont_do_it AS ON INSERT TO dontinsert DO INSTEAD -- NOTHING INSERT INTO alwaysempty (id) VALUES (NEW.id) ; 

New conclusion:

 SET NOTICE: drop cascades to table doinsert DROP TABLE NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "dontinsert_pkey" for table "dontinsert" CREATE TABLE ERROR: table "doinsert" does not exist CREATE TABLE DROP TABLE CREATE TABLE ALTER TABLE CREATE RULE ERROR: new row for relation "alwaysempty" violates check constraint "dont_insert_you_sucker" INSERT 0 1 id_from_dont -------------- 42 (1 row) id_from_do ------------ 42 (1 row) 

Next attempt: move the (ONLY) basetable to an unattractive scheme (since I really hate triggers) ...

 SET search_path='tmp'; DROP SCHEMA hidden CASCADE; CREATE SCHEMA hidden; REVOKE ALL ON SCHEMA hidden FROM PUBLIC; DROP TABLE dontinsert CASCADE; CREATE TABLE dontinsert ( id INTEGER NOT NULL PRIMARY KEY ); DROP TABLE doinsert CASCADE; CREATE TABLE doinsert () INHERITS (dontinsert) ; ALTER TABLE ONLY dontinsert SET SCHEMA hidden; INSERT INTO alwaysempty (id) VALUES (NEW.id) ; INSERT INTO dontinsert(id) VALUES( 13) ; INSERT INTO doinsert(id) VALUES( 42) ; SELECT id AS id_from_dont FROM hidden.dontinsert; SELECT id AS id_from_do FROM doinsert; 

Answer 4

update: revoke update etc. actually bad, as it will cause updates to fail and will not automatically redirect them to partitions :-( ... so the best way is @JoeVanDyk with a simple always-false check condition .

---

as mentioned by @derobert in a comment, it seems good to me, given the overhead, speed and practicality , to do this through rights :

 revoke insert, update, references on my_master_table from my_upd_usr 

( references , because it will not work as intended, if used: inheritance warnings )

(Of course, granting some users the right to paste into the wizard by chance seems possible, for example, by granting some rights to do this for all tables, but I believe that most often it is unlikely that someone will give it “by chance” for DML operations. And even if this person had rights, why should he start pasting into an empty table, seeing that he is not used in this way, and the data is in sections.)