Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Upsert in postgresql

  1. How-to
0
Votes
Undo
  1. Sivaprakash BKR
    Sivaprakash BKR
  2. PowerBuilder
  3. Friday, 21 September 2018 11:35 AM UTC

Hello,

Need to execute 'update .... ' statement, when data found while 'inserting data...', from datawindow

PB 2017, Postgresql 10.

Giving facility to import records from XML and Excel.  While importing records, few records might already available in database and need to be either excluded or updated.   This query is available in Postgres.

Can we use this 

INSERT ... ON CONFLICT DO NOTHING/UPDATE

Support for UPSERT functionality which allows either no action to be taken when a conflict is encountered, or convert the INSERT to an UPDATE.

facility from datawindow ?

https://www.postgresql.org/about/featurematrix/detail/275/

Happiness Always
BKR Sivaprakash

 

  1. PostgreSQL
  2. PowerBuilder
Powerbuilder upgrade
ODBC Progess Database question
Responses (4)
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Tuesday, 31 December 2019 20:54 PM UTC
  2. PowerBuilder
  3. # 1

Many (most?) modern SQL dialects include the MERGE statement for this purpose:

  • I have used: MS-SQL, SAP ASE, Oracle PL/SQL, and SQL Anywhere (Watcom-SQL)
  • Verified online: IBM DB/2 for Linux/UNIX/Windows, PostgreSQL and MySQL

I import to staging table using DataWindow. Then MERGE statement in a stored procedure to transfer into app table.  Performs brilliantly because no data traffic on network moving from staging table into app tables.

HTH /Michael

Comment
There are no comments made yet.
michael zuntag
michael zuntag Accepted Answer Pending Moderation
  1. Tuesday, 31 December 2019 17:17 PM UTC
  2. PowerBuilder
  3. # 2

I will be facing a similar situation. 

Was thinking about using a Datapipeline, using its Refresh, i think, property for dealing with existing rows. 

Just a thought. 

Mikael Hannes

Comment
There are no comments made yet.
Kevin Ridley
Kevin Ridley Accepted Answer Pending Moderation
  1. Wednesday, 26 September 2018 12:22 PM UTC
  2. PowerBuilder
  3. # 3

You can always do a loop through the dw and do a Select on the key values.  If they are found, mark the row as DataModified!, otherwise NewModified!, then call Update.

Comment
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Thursday, 27 September 2018 06:30 AM UTC
Thanks Kevin Ridley.

Yes, it works. Sometimes we need to import records ranging from 5000 to 25000 numbers. it's time consuming to check all records first and then to update. Should find other [faster] ways to import.

  1. Helpful
Kevin Ridley
  1. Kevin Ridley
  2. Thursday, 27 September 2018 14:17 PM UTC
You can try a stored proc or some ETL tool instead that is more geared toward that, or maybe just run overnight batch.
  1. Helpful
There are no comments made yet.
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Monday, 24 September 2018 05:33 AM UTC
  2. PowerBuilder
  3. # 4

Seems this facility is not available.   Is there any workaround that I can insert or update (if record found in db) from records in a datawindow ?

 

Happiness Always
BKR Sivaprakash

 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.