PostgreSQL Creating an Insert Trigger which Remaps Columns

Question

I'm wondering if I can use a trigger on a table to "ignore" columns that are in a COPY statement from STDIN but which are not in the target table. Sorry if the wording/syntax of the question is off, but here is and explanation of what I'm trying to say. I'm new to triggers so any advice is helpful.

I'm using the PostGIS Shapefile importer to copy shapefiles to the spatial tables in my PostgreSQL database.

This creates a COPY statement which contains all the fields in the shapefile something like:

COPY "public"."stations" ("column1","column2","column3","column4", geom) FROM stdin;

column1 and column2 are in the file but not in the target table, so the COPY fails.

Is there a way to create a trigger to create something that would have the same result as:

COPY "public"."stations" ("column3","column4", geom) FROM stdin;

Erwin Brandstetter · Accepted Answer · 2012-06-14 14:26:10Z

2

No, you cannot skip columns that are present in the input file. This will error out, before triggers are even invoked. And you cannot use rules either. I quote the manual:

COPY FROM will invoke any triggers and check constraints on the destination table. However, it will not invoke rules.

You can either edit the file or use a temporary staging table:

COPY to a temporary table with matching columns.
Use INSERT to write the desired columns to the final target table(s) - or the whole range of SQL DDL commands for more sophisticated matters.

edited Jun 14, 2012 at 14:26

answered Jun 14, 2012 at 2:36

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

avianattackarmada Over a year ago

The step I ended up was creating a view with the uploaded columns and running the inserst to the view (there is an option to run the upload as a INSERT ON instead of a COPY, which I had missed). And setting an INSTEAD OF trigger on the view to insert into the target table with the desired columns. - If that wording makes any sense

Erwin Brandstetter Over a year ago

@avianattackarmada: It does. And it is probably the fastest implementation of the above route.

Collectives™ on Stack Overflow

PostgreSQL Creating an Insert Trigger which Remaps Columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related