Skip to main content
  • EN

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

0684Q00000ArB5yQAF.jpg

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

0684Q00000ArAuAQAV.jpg

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

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'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

30,108
Posts
114,295
Replies
37,744
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.