Inserting Records - Primary Key set to database sequence?

(1) Share

Report

Posted on by mjsonline

Hi there,

I'm creating an application to insert records into a Postgres database, but having problems with the Primary Key.

Powerapps is insisting that I try to set a value, despite the database being set to default new records to the next value in the sequence.

Is there anyway I can force PowerApps to rely on the database sequence?

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (0)

Sort by

Tom_Slosse 1,430 on at

Like (0)

Report
Copy link

Link copied!

Hey @mjsonline,

Is your primary key in your Postgre Database an incremental ID?

So that means that if a db has an incremental ID, PowerApps will not ask you to give a value for that in for example a Patch function. If it doenst have an incremental ID it will ask to give a primary key for each record you wanna save.

Kind regards

Tom

Was this reply helpful? Yes No
Ramazan_BIYIK 49 on at

Like (0)

Report
Copy link

Link copied!

Hi Tom,
I have the same issue,

Hi for all. I created an mssql database with primary key and auto increment 'id' column. Sql connector(insert row action) didn't ask me 'id' column on connector as supposed because database is automatically insert 'id' column.

However with postgres insert row action, flow asks me 'id' column whatever i do. Beside it didn't work stable.With 'stable' , i mean that: Sometimes(very rare) even if insert row action failed, it insert the datas with correct id. But i can't see the logs.

I though that my database table settings is wrong but i can insert data manually without insert 'id' column cause it is auto incremented.

I tried that:
BIGINT instead INT ---> Didn't work
My_Table_id_seq && primary key && unique && not null ---> Didn't work

Please help is this because of flow or because of me?

By the way i started my postgres database inside docker container.
Docker env: Azure VM ubuntu 16.04
Flow env: Windows 10.
Used on-premises data gateway

Was this reply helpful? Yes No
Tom_Slosse 1,430 on at

Like (0)

Report
Copy link

Link copied!

Hey @Ramazan_BIYIK

I don't have much experience with Postgre database and how to set them up.

I encountered this same issue with a SQL database and I changed my ID column to a Auto Increment ID, and also made it the primary ID of the table, and that worked fine for me.

So my ID is a INT, NOT NULL and the primary key.

So I assume it must be the same for all databases.

And are you building a PowerApp or a Flow?

Kind regards

Tom

Was this reply helpful? Yes No
Ramazan_BIYIK 49 on at

Like (0)

Report
Copy link

Link copied!
Wow! That was quick.

In sql you can easily make identity column with right click(with mssql program). In postgres u have to type to default section '
nextval('issue_infos_id_seq'::regclass)
'
It suppose to be worked the same way. But i could't figure out why

Was this reply helpful? Yes No
Tom_Slosse 1,430 on at

Like (0)

Report
Copy link

Link copied!
Hey @Ramazan_BIYIK

And what if you lose the ::regclass does that solve anything?

And how did you configure your table? with the GUI or via a query?

I suppose your query could look something like this:

CREATE SEQUENCE id_seq; CREATE TABLE user ( user_id int NOT NULL DEFAULT nextval('id_seq') );

but don't quote me on that because I'm not a Postgre specialist 🙂

Kind regards

Tom

Was this reply helpful? Yes No
Ramazan_BIYIK 49 on at

Like (0)

Report
Copy link

Link copied!

Hi Tom
I tried to delete 'REGCLASS' but didn't work. It(postgres) has auto complete. Even if i delete REGCLASS, it is coming back after few second. My mind is stuck on that: I can achieve my goal with sql but can't achieve with postgres even though every column properties are the same with sql database.

Was this reply helpful? Yes No
Tom_Slosse 1,430 on at

Like (0)

Report
Copy link

Link copied!

Hey @Ramazan_BIYIK

But you do need to get it working with Postgre?

Because if it is working with SQL, I just would make my solution with SQL instead of Postgre because SQL is much easier (in my opinion) than Postgre.

Please let me know if you need it to work in Postgre, I will ask for some information on Postgre how to set it up properly for PowerApps.

Kind regards

Tom

Was this reply helpful? Yes No
Ramazan_BIYIK 49 on at

Like (1)

Report
Copy link

Link copied!

My company prefers postgres (with s postgres fans are very sensitive about it :)) due to its security and cost. So i should use postgres.If I cant figure out with postgres i am allowed to use sql connection and mssql database with flow. If you can ask , it would be appreciated.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi, Could you give me a hand? I am beggining to use powerapps and CDS I have created a custom entity (CDS), the issue is that I can insert multiple rows at the entity with the same primary key! I have a field at the form (powerapps Application) to create a unique code for each item that the user will add. So, I would like to validate, that the new code does not already available at the entity. I'll appreciate any comment Regards,

Was this reply helpful? Yes No
FatimahAlQadib 20 on at

Like (1)

Report
Copy link

Link copied!

I am using MySql DB. And I do the following,
You have to make sure that your primary key value is set to Autoincrement. Then in your Patch set the primary key into 0 value. The DB will take responsibility for the sequence.

OnSelect= Patch('[database_name].[table_name]',Defaults('[database_name].[table_name]'),{column_primary_key:0, column__2: ... });

Was this reply helpful? Yes No