Re: table partition with inheritance having current_timestamp issue if we miss range table

On Wed, 30 Sep 2020 at 07:18, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
>
> Attached trigger function and trigger with table structure, problem is if the child table does exit it's not omitting the error and data directing to the master table with a futuristic data value of load_dttm.

Using your script, it seems to work ok for me.

postgres=# insert into l_billing_account
values('',1,'2020-09-30','','',now(),'',1,1,now(),'',now(),'','');
INSERT 0 0
postgres=# select tableoid::regclass,load_dttm from l_billing_account;
tableoid | load_dttm
-------------------------------+---------------------
l_billing_account_y2020m09end | 2020-09-30 00:00:00
(1 row)

postgres=# drop table l_billing_account_y2020m09end ;
DROP TABLE
postgres=# insert into l_billing_account
values('',1,'2020-09-30','','',now(),'',1,1,now(),'',now(),'','');
ERROR: relation "l_billing_account_y2020m09end" does not exist
LINE 1: INSERT INTO l_billing_account_y2020m09end VALUES (NEW.*)
^
QUERY: INSERT INTO l_billing_account_y2020m09end VALUES (NEW.*)
CONTEXT: PL/pgSQL function func_l_billing_account_insert_trigger()
line 82 at SQL statement

postgres=# select version();
version
------------------------------------------------------------------------------------------------
PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
9.3.0-10ubuntu2) 9.3.0, 64-bit
(1 row)

Perhaps you have another table by that name some other namespace
that's in search_path.

David