Multiple column key constraint is wrong interpreted by Postgres-reader

I'm trying to write postgres data from to a new schema. The table I'm reading has a unique constraint on two columns like this:

CREATE TABLE public.real_right
(
    share_numerator smallint,
    share_denominator smallint,
    upi character varying(50) COLLATE pg_catalog."default" NOT NULL,
    party_id character varying(100) COLLATE pg_catalog."default" NOT NULL,
    is_deleted boolean,
    CONSTRAINT "PK_REAL_RIGHT" PRIMARY KEY (upi, party_id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

If I take a look at the reader it shows me only a primary key at upi

When I use Table definition: Copy from Reader ...

Then the bulk data load fails because of the primary key violation.

So the question is: Does the postgres reader supports multiple key constraints?

And is there a workaround?

thanks in advance

Page 1 / 1

I just tested with FME 2018 and the PostGIS writer, it seems indeed that FME doesn't manage multiple field primary keys for table creation:

Writer setup:

Resulting table created by FME 2018, we see that only "col1" has been set as the primary key:

My suggestion is that you manually create (or fix) the target table and not let FME create/recreate it for you.

I just tested with FME 2018 and the PostGIS writer, it seems indeed that FME doesn't manage multiple field primary keys for table creation:

Writer setup:

Resulting table created by FME 2018, we see that only "col1" has been set as the primary key:

My suggestion is that you manually create (or fix) the target table and not let FME create/recreate it for you.

I'm not happy with the answer but thanks for your input. I will post an Idea on the forum.

I agree it's not great. If you post the link here I'll give the idea a vote for sure.

I've made an idea on the forum: https://knowledge.safe.com/idea/78863/support-multiple-column-field-constraints-for-post.html?

Let the voting begin.

I just tested with FME 2018 and the PostGIS writer, it seems indeed that FME doesn't manage multiple field primary keys for table creation:

Writer setup:

Resulting table created by FME 2018, we see that only "col1" has been set as the primary key:

My suggestion is that you manually create (or fix) the target table and not let FME create/recreate it for you.

I experienced a similar problem with MS SQL Server. FME does not either handle Multiple Primary Keys when writing to MS SQL Server. Is this a general FME limitation?

Hi @stalknecht, @david_r, and @tomchr.

Thanks for sharing your experience with trying to use composite keys in FME.

FME does support writing to existing tables that have multiple keys already defined or creating a table in SQL (i.e. in a statement before translation), which is what @david_r suggested.

As you've discovered, FME doesn't yet support reading such tables and only assigns one primary key to the attributes that are read in.

@stalknecht, thanks for posting your idea for our team to consider. It will help us prioritize any future improvements.

Reply

Community Stats

Reply

Community Stats

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded