Hello,
I have a table which needs to be written to PostGIS. One of my dataset columns contains a list of buffer distances for example 300, 200, 100m. It would be ideal to write this as an array to PostGIS for example {300, 200, 100}.
If the table already exists with an array column in PostGIS, FME will update the array correctly. If the table doesn't exist I can't see how to set the column type as an array in FME. Is it possible?
Thanks :)
Best answer by chrisatsafe
Hi @jw4554,
You'll likely need to do this in an SQLExecutor as the writer attribute types do not appear to support ARRAY. By using the SQLExecutor, you can set an SQL Statement like
FME_SQL_DELIMITER ;
CREATE TABLE "public"."arraysql" (
id serial PRIMARY KEY,
name VARCHAR (100),
phones TEXT []
);
INSERT INTO "public"."arraysql" (name, phones)
VALUES('John Smith',ARRAY [ '(410)-589-5846','(410)-589-5555' ]);and it will insert the attributes as an array in the db column.
+2Hi @jw4554,
You'll likely need to do this in an SQLExecutor as the writer attribute types do not appear to support ARRAY. By using the SQLExecutor, you can set an SQL Statement like
FME_SQL_DELIMITER ;
CREATE TABLE "public"."arraysql" (
id serial PRIMARY KEY,
name VARCHAR (100),
phones TEXT []
);
INSERT INTO "public"."arraysql" (name, phones)
VALUES('John Smith',ARRAY [ '(410)-589-5846','(410)-589-5555' ]);and it will insert the attributes as an array in the db column.
+2Hi @jw4554,
You'll likely need to do this in an SQLExecutor as the writer attribute types do not appear to support ARRAY. By using the SQLExecutor, you can set an SQL Statement like
FME_SQL_DELIMITER ;
CREATE TABLE "public"."arraysql" (
id serial PRIMARY KEY,
name VARCHAR (100),
phones TEXT []
);
INSERT INTO "public"."arraysql" (name, phones)
VALUES('John Smith',ARRAY [ '(410)-589-5846','(410)-589-5555' ]);and it will insert the attributes as an array in the db column.
In your workspace, you can use 2 SQLExecutors (1 to create the table and 1 for the inserts). Using this example if you concatenate the phone numbers into a single attribute value (wrapped in single quotes and comma-separated), your SQL will look something like this.
INSERT INTO "public"."arraysql" (name, phones)
VALUES('@Value(name)',ARRAY [ @Value(phones) ]);Otherwise, you can build the array in the SQL statement if you are working with list features.
INSERT INTO "public"."arraysql" (name, phones)
VALUES('@Value(name)',ARRAY [ '@Value(_list{0}.phones)','@Value(_list{1}.phones)' ]);
This is perfect thank you. In the end I got the best performance, and clearest workspace, by using a feature writer to write all the columns apart from the array, then I used a SQLExecutor to update the table like you suggested in your last snippet.
+2This is perfect thank you. In the end I got the best performance, and clearest workspace, by using a feature writer to write all the columns apart from the array, then I used a SQLExecutor to update the table like you suggested in your last snippet.
@jw4554 great - thanks for following up to let me know you got it working!
+4Hi @jw4554 ,
You said you were able to write the array if the table already existed in the PostGIS database. Could you show a snippet of the workspace where you do that?
I am trying to write an existing list to an existing table and always end up with empty array field. I did many attempts with different attribute names and data types, the problem is, I think, that FME does not offer any array data type in the PostGIS writer.
Thanks!
I added an idea for that, feel free to upvote:
https://community.safe.com/s/bridea/a0rDm000000CbxRIAS/add-array-feature-type-to-postgresql-writer
Hi @jw4554 ,
You said you were able to write the array if the table already existed in the PostGIS database. Could you show a snippet of the workspace where you do that?
I am trying to write an existing list to an existing table and always end up with empty array field. I did many attempts with different attribute names and data types, the problem is, I think, that FME does not offer any array data type in the PostGIS writer.
Thanks!
Hi! I've managed to do that by setting the datatype in the FME writer to varchar of sufficient length, and then inserting data like {1,13,2} in an _int4 column. Using FME 2018.1
@pmalvenius If you create the table first using the postgres ARRAY type, you can use the FME Postgres writer and all you have to do is wrap your list of 1,2,3 in brackets. See the end of the discussion here
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
