Обсуждение: CREATE TEMPORARY TABLE LIKE

Hi everyone,

When basing a temporary table of a source table, are triggers included
by default? I have this statement;

CREATE TEMPORARY TABLE dev_main (
LIKE prod_main
INCLUDING ALL
EXCLUDING INDEXES
EXCLUDING CONSTRAINTS
) ON COMMIT DELETE ROWS;

And wondering if there is a trigger (row-based after) on prod_main
it'll fire also on dev_main? I can't find anything in the
documentation that suggests either way nor can I see an explicit
EXCLUDING option to be sure triggers aren't copied.

Does anyone know?

Cheers,

Jim

-- 
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London

I just wrote a little test case... it appears not. Triggers aren't
fired in the temporary table.

Jim

On Thu, 25 May 2023 at 14:06, Jim Vanns <jvanns@ilm.com> wrote:
>
> Hi everyone,
>
> When basing a temporary table of a source table, are triggers included
> by default? I have this statement;
>
> CREATE TEMPORARY TABLE dev_main (
> LIKE prod_main
> INCLUDING ALL
> EXCLUDING INDEXES
> EXCLUDING CONSTRAINTS
> ) ON COMMIT DELETE ROWS;
>
> And wondering if there is a trigger (row-based after) on prod_main
> it'll fire also on dev_main? I can't find anything in the
> documentation that suggests either way nor can I see an explicit
> EXCLUDING option to be sure triggers aren't copied.
>
> Does anyone know?
>
> Cheers,
>
> Jim
>
> --
> Jim Vanns
> Principal Production Engineer
> Industrial Light & Magic, London



-- 
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London

> On 25/05/2023 15:06 CEST Jim Vanns <jvanns@ilm.com> wrote:
>
> When basing a temporary table of a source table, are triggers included
> by default? I have this statement;
>
> CREATE TEMPORARY TABLE dev_main (
> LIKE prod_main
> INCLUDING ALL
> EXCLUDING INDEXES
> EXCLUDING CONSTRAINTS
> ) ON COMMIT DELETE ROWS;
>
> And wondering if there is a trigger (row-based after) on prod_main
> it'll fire also on dev_main? I can't find anything in the
> documentation that suggests either way nor can I see an explicit
> EXCLUDING option to be sure triggers aren't copied.

You can check if triggers exist with psql:

    \d dev_main

or by checking catalog pg_trigger:

    select * from pg_trigger where tgrelid = 'dev_main'::regclass;

But no.  Triggers are not included when creating tables like that.

--
Erik