will the edited form in odk central get updated in pg

[stephexcel2004]

Would like to know whether the script also check the latest edited submission and update the old submission in pg

[mathieubossaert]

No as we do not edit submission into central, we do not manage it.
But I think it could be quite easy adapting the "ON CONFLICT" clause here :

central2pg/insert_into_from_refcursor.sql

Line 63 in a39e7d4

VALUES ' || _sql_val ||' ON CONFLICT (data_id) DO NOTHING;';

Will take a look at it. If you try something let me know if it works as expected.

[mathieubossaert]

To complete my first response :
we use PostgreSQL's UPSERT (with unique indexes xx__id attributes) to catch and manage conflicts on inserts when data already exits in the database. And for the moment we manage such cases with "DO NOTHING" action.

[PhilKnight]

I would also really like to have this feature. I took a look at it, it is more complex than my first attempt which did not work:
VALUES ' || _sql_val ||' ON CONFLICT (data_id) DO UPDATE SET' || _sql_col || '= EXCLUDED.' || _sql_col || ';
This is because _sql_val and _sql_col are comma separated lists not single values.
I also wondered whether it was necessary to update here also:

central2pg/get_submission_from_central.sql

Line 63 in a39e7d4

EXECUTE format('INSERT into '||destination_schema_name||'.'||destination_table_name||'(form_data) SELECT json_array_elements(form_data -> ''value'') AS form_data FROM central_json_from_central ON CONFLICT ((form_data ->> ''__id''::text)) DO NOTHING;');

[mathieubossaert]

Glad to see users of my work from all around the world:-) I you

@PhilKnight you are right, we'll have to build the update statement :
SET column_1 = excluded.column_1, column_2 = excluded.column_2

The second update you mentioned is also needed but easy to build in a static string (SET form_data = excluded.form_data)

One big issue to investigate is how to only update submissions that have been truly edited.
Each time we invoke central2pg we get all submissions from central (I hope to be able to filter it one day : getodk/central-backend#391 ) and we don't want to update all the data, only the edited one.

I want to make this possible in order to avoid consuming resources for data that I already have for months in my database.

I wish I had some time this summer to explore it but I couldn't.
I started working on API calls using the expand=* parameter and submissionDate filtering but didn't get very far.

[mathieubossaert]

I will have another strategy (not trying to filter subtables nor explore expanded json) that consists in :

• filtering submission table to get relevant submission_id
• asking each subtables for listed submission_id.
  I read too quickly @matthew-white's response here : https://forum.getodk.org/t/using-filter-with-repeat-tables-to-download-odata/36245/2
  He mentionned how to get subtable data for a given submission_id and this could really fit our needs

It is currently possible to fetch a subtable for a single Submission:/
v1/projects/{{projectId}}/forms/{{xmlFormId}}.svc/Submissions('{{instanceId}}')/{{repeatName}}

Will try to do it before the end of the year to be ready for next field season.