Triggers when importing data

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Triggers when importing data

Sathish Kumar
Hi, 
I am trying to export and import sql file of a database. I would like to know whether it will execute all the triggers when importing the sql dump which is for Insert or Update or Delete.
Export:pg_dump -h test -U db_admin --format=plain --no-owner --no-acl production     | sed -E 's/(DROP|CREATE|COMMENT ON) EXTENSION/-- \1 EXTENSION/g' > test.sql
Import: psql -h test -U db_admin -d production -W < test.sql
Reply | Threaded
Open this post in threaded view
|

Re: Triggers when importing data

Laurenz Albe
Sathish Kumar wrote:
> I am trying to export and import sql file of a database. I would like to know
> whether it will execute all the triggers when importing the sql dump which
> is for Insert or Update or Delete.
>
> Export:pg_dump -h test -U db_admin --format=plain --no-owner --no-acl production     | sed -E 's/(DROP|CREATE|COMMENT ON) EXTENSION/-- \1 EXTENSION/g' > test.sql
>
> Import: psql -h test -U db_admin -d production -W < test.sql

That will call all triggers on tables that have no primary key
or unique constraints.

On all other tables, that will give you a constraint violation because
the same rows are already in the table...

What are you trying to achieve?

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com


Reply | Threaded
Open this post in threaded view
|

Re: Triggers when importing data

Adrian Klaver-4
In reply to this post by Sathish Kumar
On 11/28/18 9:43 PM, Sathish Kumar wrote:
> Hi,
>
> I am trying to export and import sql file of a database. I would like to know whether it will execute all the triggers when importing the sql dump which is for Insert or Update or Delete.

Well you command as constructed below will have no INSERTs(or UPDATES OR
DELETES). The data will be moved into the tables using COPY. Even if you
use --inserts(https://www.postgresql.org/docs/10/app-pgdump.html) that
is all you will get. Can you explain more what it is you are trying to
achieve or expect?

>
> Export:pg_dump -h test -U db_admin --format=plain --no-owner --no-acl production     | sed -E 's/(DROP|CREATE|COMMENT ON) EXTENSION/-- \1 EXTENSION/g' > test.sql
>
> Import: psql -h test -U db_admin -d production -W < test.sql
>


--
Adrian Klaver
[hidden email]