Unanswered

Using Collect to upload multiple rows to PostgresSQL database with an auto-increment Primary Key

(0) Share

Report

Posted on by cd30443

I have a PostgresSQL database table with a Primary Key, set to Not Null, auto-increment, called "pricesid"

I generate a collection in Powerapps which mirrors the above table in terms of column headers, data types etc. The only column that is different is the "pricesid" in my collection.

When I try and use the Collect function, it does not seem to be able to insert, without me defining values for the Primary Key.

If I do define values for the Primary Key, for example, setting them all initially to 0, then again the insert fails as they need to be unique. I don't want to generate a set of values to then insert via Collect as multiple users may be inserting and there could be some overlap.

How do I get the Collect function to work, that seems to need to include the "pricesid" column? It also won't let me set it to anything other than an integer as this is the data type.

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

eka24 20,923 on at

Like (0)

Report

What is the formula for your collection.
Also are you using a Patch to insert the collection? if you can show the formula as well.
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
cd30443 8 on at

Like (0)

Report

My database is called "fcpgf_prices" and my collection is called "newcoll". I am using Collect rather than Patch as I was under tyhe impression this was more efficient, as they are new records to be inserted?

Formula is straightforward but obviously I'm doing something fundamentally wrong!

Collect('[fcpgf].[fcpgf_prices]',newcoll)

Really appreciate the help!

Was this reply helpful? Yes No
eka24 20,923 on at

Like (0)

Report

Try this;
ForAll(newcoll, Patch('[fcpgf].[fcpgf_prices]',
Defaults('[fcpgf].[fcpgf_prices]'),
{Column1: newcollColumn1,
Column2: newcollColumn2,
Column3: newcollColumn3
}))
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
cd30443 8 on at

Like (0)

Report

thanks for the suggestion, I tried this and it still asks for the "pricesid" column to be included. When I include it, I get the same issue as before.

Was this reply helpful? Yes No
Verified answer

timl 36,383 Super User 2025 Season 2 on at

Like (0)

Report

Hi @cd30443

Unfortunately, there is a known bug/incompatibility between PowerApps and PostgresSQL tables with auto-increment primary keys.

There are more details in my post here.

https://powerusers.microsoft.com/t5/Building-Power-Apps/MySQL-Auto-Increment-Primary-Key-Incompatibility/td-p/408532

As far as I know, there are no workarounds, except to remove the auto-increment PK.

Was this reply helpful? Yes No
cd30443 8 on at

Like (0)

Report

Ah, OK. Thanks for the info! Will work on an "auto-increment" in powerapps and remove from PK in the database.

Do you know if it is a bug that is being worked on/fixed?

Thanks

Was this reply helpful? Yes No
timl 36,383 Super User 2025 Season 2 on at

Like (0)

Report

@cd30443 - Unfortunately, I don't know if it's a bug that's being worked on/fixed. I imagine that the product team focus would be on CDS and SQL Server data sources, so I suspect this could be a low priority issue.

Was this reply helpful? Yes No