Ygthkb

Is there any way to play D&D without a DM?

Converting numbers to words - Python

Short story about a man betting a group he could tell a story, and one of them would disappear and the others would not notice

Taking an academic pseudonym?

Two oatmeal pies a day keep the doctor away?

What does "south of due west" mean?

Crack the bank account's password!

Can someone explain European graduate programs in STEM fields?

Will the duration of traveling to Ceres using the same tech developed for going to Mars be proportional to the distance to go to Mars or not?

Disk space full during insert, what happens?

How do I fight with Heavy Armor as a Wizard with Tenser's Transformation?

Why is it that Bernie Sanders always called a "socialist"?

How can I differentiate duration vs starting time

What could cause an entire planet of humans to become aphasic?

Does Plato's "Ring of Gyges" have a corrupting influence on its wearer?

If I tried and failed to start my own business, how do I apply for a job without job experience?

Is it possible to narrate a novel in a faux-historical style without alienating the reader?

What does @ mean in a hostname in DNS configuration?

Is there a way to pause a running process on Linux systems and resume later?

How can I prep for the Curse of Strahd adventure effectively?

What does an unprocessed RAW file look like?

Sets that are both Sum-free and Product-free

How does holding onto an active but un-used credit card affect your ability to get a loan?

Can someone explain what a key is?

Merge ignoring type errors: length, and type mismatch

PostgreSQL data type text vs varchar without lengthHuge mismatch between reported index size and number of buffers in execution planhow can we merge sum and count?errors install and start postgresql-9.5What indexing to be used over string columns(text type) with string length around 3000 charactersPostgres: SELECTing bytea data partially with offset and lengthDatabase Wrapper Design issue: No operator matches the given name and argument type(s)Does changing the length limit (type-modifier) of varchar() result in a table or index rewrite?Difference between UPSERT and MERGE?Merge 2 columns and replace with specific output

0

My query is an extension of the below thread

PostgreSQL equivalent of Oracle LOG ERRORS clause in MERGE statement

I understand that Postgressql does not provide an option to route the errors during a massive DML, but is there an alternate solution to handle the errors.

I have a requirement to INSERT/UPDATE 100k records into a table and if there are errors with one record, then the whole transaction gets rolled back. I just need to skip the error records(ex: datatype mismatch or data length exceeds the target column length) and continue with the rest of the load. I considered LOOP and its an expensive operation.

postgresql postgresql-9.4

share|improve this question

edited Jan 2 '18 at 22:19

Evan Carroll

32.5k970221

asked Jan 2 '18 at 21:40

Harinath Arasu

bumped to the homepage by Community♦ 3 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

migrated from stackoverflow.com Jan 2 '18 at 21:46

This question came from our site for professional and enthusiast programmers.

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Upgrade to >=9.5 and you have UPSERT in the form of INSERT … ON CONFLICT
  
  – Jack Douglas♦
  Jan 2 '18 at 22:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @a_horse_with_no_name insert on conflict doesn't handle this afaik from what the op is talking about. he specifically mentions datatype mismatch or data length exceeds, ON CONFLICT is about UNIQUENESS collisions.
  
  – Evan Carroll
  Jan 2 '18 at 22:11
  
  
  

  
  
  
  

add a comment | 

0

My query is an extension of the below thread

PostgreSQL equivalent of Oracle LOG ERRORS clause in MERGE statement

I understand that Postgressql does not provide an option to route the errors during a massive DML, but is there an alternate solution to handle the errors.

I have a requirement to INSERT/UPDATE 100k records into a table and if there are errors with one record, then the whole transaction gets rolled back. I just need to skip the error records(ex: datatype mismatch or data length exceeds the target column length) and continue with the rest of the load. I considered LOOP and its an expensive operation.

postgresql postgresql-9.4

share|improve this question

edited Jan 2 '18 at 22:19

Evan Carroll

32.5k970221

asked Jan 2 '18 at 21:40

Harinath Arasu

bumped to the homepage by Community♦ 3 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

migrated from stackoverflow.com Jan 2 '18 at 21:46

This question came from our site for professional and enthusiast programmers.

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Upgrade to >=9.5 and you have UPSERT in the form of INSERT … ON CONFLICT
  
  – Jack Douglas♦
  Jan 2 '18 at 22:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @a_horse_with_no_name insert on conflict doesn't handle this afaik from what the op is talking about. he specifically mentions datatype mismatch or data length exceeds, ON CONFLICT is about UNIQUENESS collisions.
  
  – Evan Carroll
  Jan 2 '18 at 22:11
  
  
  

  
  
  
  

add a comment | 

My query is an extension of the below thread

PostgreSQL equivalent of Oracle LOG ERRORS clause in MERGE statement

I understand that Postgressql does not provide an option to route the errors during a massive DML, but is there an alternate solution to handle the errors.

I have a requirement to INSERT/UPDATE 100k records into a table and if there are errors with one record, then the whole transaction gets rolled back. I just need to skip the error records(ex: datatype mismatch or data length exceeds the target column length) and continue with the rest of the load. I considered LOOP and its an expensive operation.

postgresql postgresql-9.4

share|improve this question

edited Jan 2 '18 at 22:19

Evan Carroll

32.5k970221

asked Jan 2 '18 at 21:40

Harinath Arasu

share|improve this question

edited Jan 2 '18 at 22:19

Evan Carroll

32.5k970221

asked Jan 2 '18 at 21:40

Harinath Arasu

share|improve this question

edited Jan 2 '18 at 22:19

Evan Carroll

32.5k970221

asked Jan 2 '18 at 21:40

Harinath Arasu

edited Jan 2 '18 at 22:19

Evan Carroll

32.5k970221

edited Jan 2 '18 at 22:19

Evan Carroll

32.5k970221

2 Answers
2

active

oldest

votes

0

route the errors

Not sure what you even mean there.

I have a requirement to INSERT/UPDATE 100k records into a table and if there are errors with one record, then the whole transaction gets rolled back. I just need to skip the error records(ex: datatype mismatch or data length exceeds the target column length) and continue with the rest of the load. I considered LOOP and its an expensive operation.

There is no other way to work with such data other than with a loop,

• On the server, that loop can be written with a cursor that catches errors. You can run RAISE [debug|notice] as permitted in plpgsql. Or, you can call NOTIFY and alert a daemon.


• On the client, you can verify that the data meets your constraints and stream it in with an appropriate COPY FROM STDIN. Because you're writing the code to skip invalid rows you can handle the error however you want.

share|improve this answer

answered Jan 2 '18 at 22:01

Evan CarrollEvan Carroll

32.5k970221

add a comment | 

0

If applicable, you can create a function and use EXCEPTION control statement. Once we had an issue like "log any error in particular log table while executing a statement and continue without error" so we used something like this;

DO

$body$

DECLARE

  l_pg_exception_detail text;

  l_pg_exception_hint text;

  l_pg_exception_context text;

BEGIN

  --insert / update / delete statement here

  INSERT INTO ... VALUES ...;



  EXCEPTION

    WHEN OTHERS THEN

      GET STACKED DIAGNOSTICS l_pg_exception_detail = PG_EXCEPTION_DETAIL;

      GET STACKED DIAGNOSTICS l_pg_exception_hint = PG_EXCEPTION_HINT;

      GET STACKED DIAGNOSTICS l_pg_exception_context = PG_EXCEPTION_CONTEXT;



      RAISE NOTICE 'something happened: DETAIL: % , HINT: % , CONTEXT: % ', 

        l_pg_exception_detail,

        l_pg_exception_hint,

        l_pg_exception_context;

END;

$body$

Documentation reference is here

share|improve this answer

answered Jan 3 '18 at 13:58

Sahap AsciSahap Asci

1,188412

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f194420%2fmerge-ignoring-type-errors-length-and-type-mismatch%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

route the errors

Not sure what you even mean there.

I have a requirement to INSERT/UPDATE 100k records into a table and if there are errors with one record, then the whole transaction gets rolled back. I just need to skip the error records(ex: datatype mismatch or data length exceeds the target column length) and continue with the rest of the load. I considered LOOP and its an expensive operation.

There is no other way to work with such data other than with a loop,

• On the server, that loop can be written with a cursor that catches errors. You can run RAISE [debug|notice] as permitted in plpgsql. Or, you can call NOTIFY and alert a daemon.


• On the client, you can verify that the data meets your constraints and stream it in with an appropriate COPY FROM STDIN. Because you're writing the code to skip invalid rows you can handle the error however you want.

share|improve this answer

answered Jan 2 '18 at 22:01

Evan CarrollEvan Carroll

32.5k970221

add a comment | 

0

route the errors

Not sure what you even mean there.

I have a requirement to INSERT/UPDATE 100k records into a table and if there are errors with one record, then the whole transaction gets rolled back. I just need to skip the error records(ex: datatype mismatch or data length exceeds the target column length) and continue with the rest of the load. I considered LOOP and its an expensive operation.

There is no other way to work with such data other than with a loop,

• On the server, that loop can be written with a cursor that catches errors. You can run RAISE [debug|notice] as permitted in plpgsql. Or, you can call NOTIFY and alert a daemon.


• On the client, you can verify that the data meets your constraints and stream it in with an appropriate COPY FROM STDIN. Because you're writing the code to skip invalid rows you can handle the error however you want.

share|improve this answer

answered Jan 2 '18 at 22:01

Evan CarrollEvan Carroll

32.5k970221

add a comment | 

route the errors

Not sure what you even mean there.

I have a requirement to INSERT/UPDATE 100k records into a table and if there are errors with one record, then the whole transaction gets rolled back. I just need to skip the error records(ex: datatype mismatch or data length exceeds the target column length) and continue with the rest of the load. I considered LOOP and its an expensive operation.

There is no other way to work with such data other than with a loop,

• On the server, that loop can be written with a cursor that catches errors. You can run RAISE [debug|notice] as permitted in plpgsql. Or, you can call NOTIFY and alert a daemon.


• On the client, you can verify that the data meets your constraints and stream it in with an appropriate COPY FROM STDIN. Because you're writing the code to skip invalid rows you can handle the error however you want.

share|improve this answer

answered Jan 2 '18 at 22:01

Evan CarrollEvan Carroll

32.5k970221

share|improve this answer

answered Jan 2 '18 at 22:01

Evan CarrollEvan Carroll

32.5k970221

answered Jan 2 '18 at 22:01

Evan CarrollEvan Carroll

32.5k970221

answered Jan 2 '18 at 22:01

Evan CarrollEvan Carroll

32.5k970221

0

If applicable, you can create a function and use EXCEPTION control statement. Once we had an issue like "log any error in particular log table while executing a statement and continue without error" so we used something like this;

DO

$body$

DECLARE

  l_pg_exception_detail text;

  l_pg_exception_hint text;

  l_pg_exception_context text;

BEGIN

  --insert / update / delete statement here

  INSERT INTO ... VALUES ...;



  EXCEPTION

    WHEN OTHERS THEN

      GET STACKED DIAGNOSTICS l_pg_exception_detail = PG_EXCEPTION_DETAIL;

      GET STACKED DIAGNOSTICS l_pg_exception_hint = PG_EXCEPTION_HINT;

      GET STACKED DIAGNOSTICS l_pg_exception_context = PG_EXCEPTION_CONTEXT;



      RAISE NOTICE 'something happened: DETAIL: % , HINT: % , CONTEXT: % ', 

        l_pg_exception_detail,

        l_pg_exception_hint,

        l_pg_exception_context;

END;

$body$

Documentation reference is here

share|improve this answer

answered Jan 3 '18 at 13:58

Sahap AsciSahap Asci

1,188412

add a comment | 

0

If applicable, you can create a function and use EXCEPTION control statement. Once we had an issue like "log any error in particular log table while executing a statement and continue without error" so we used something like this;

DO

$body$

DECLARE

  l_pg_exception_detail text;

  l_pg_exception_hint text;

  l_pg_exception_context text;

BEGIN

  --insert / update / delete statement here

  INSERT INTO ... VALUES ...;



  EXCEPTION

    WHEN OTHERS THEN

      GET STACKED DIAGNOSTICS l_pg_exception_detail = PG_EXCEPTION_DETAIL;

      GET STACKED DIAGNOSTICS l_pg_exception_hint = PG_EXCEPTION_HINT;

      GET STACKED DIAGNOSTICS l_pg_exception_context = PG_EXCEPTION_CONTEXT;



      RAISE NOTICE 'something happened: DETAIL: % , HINT: % , CONTEXT: % ', 

        l_pg_exception_detail,

        l_pg_exception_hint,

        l_pg_exception_context;

END;

$body$

Documentation reference is here

share|improve this answer

answered Jan 3 '18 at 13:58

Sahap AsciSahap Asci

1,188412

add a comment | 

If applicable, you can create a function and use EXCEPTION control statement. Once we had an issue like "log any error in particular log table while executing a statement and continue without error" so we used something like this;

DO

$body$

DECLARE

  l_pg_exception_detail text;

  l_pg_exception_hint text;

  l_pg_exception_context text;

BEGIN

  --insert / update / delete statement here

  INSERT INTO ... VALUES ...;



  EXCEPTION

    WHEN OTHERS THEN

      GET STACKED DIAGNOSTICS l_pg_exception_detail = PG_EXCEPTION_DETAIL;

      GET STACKED DIAGNOSTICS l_pg_exception_hint = PG_EXCEPTION_HINT;

      GET STACKED DIAGNOSTICS l_pg_exception_context = PG_EXCEPTION_CONTEXT;



      RAISE NOTICE 'something happened: DETAIL: % , HINT: % , CONTEXT: % ', 

        l_pg_exception_detail,

        l_pg_exception_hint,

        l_pg_exception_context;

END;

$body$

Documentation reference is here

share|improve this answer

answered Jan 3 '18 at 13:58

Sahap AsciSahap Asci

1,188412

DO

$body$

DECLARE

  l_pg_exception_detail text;

  l_pg_exception_hint text;

  l_pg_exception_context text;

BEGIN

  --insert / update / delete statement here

  INSERT INTO ... VALUES ...;



  EXCEPTION

    WHEN OTHERS THEN

      GET STACKED DIAGNOSTICS l_pg_exception_detail = PG_EXCEPTION_DETAIL;

      GET STACKED DIAGNOSTICS l_pg_exception_hint = PG_EXCEPTION_HINT;

      GET STACKED DIAGNOSTICS l_pg_exception_context = PG_EXCEPTION_CONTEXT;



      RAISE NOTICE 'something happened: DETAIL: % , HINT: % , CONTEXT: % ', 

        l_pg_exception_detail,

        l_pg_exception_hint,

        l_pg_exception_context;

END;

$body$

share|improve this answer

answered Jan 3 '18 at 13:58

Sahap AsciSahap Asci

1,188412

answered Jan 3 '18 at 13:58

Sahap AsciSahap Asci

1,188412

answered Jan 3 '18 at 13:58

Sahap AsciSahap Asci

1,188412