Styjun

What does "autolyco-sentimental" mean?

Proof of First Difference Property for Fourier Series

Why does the friction act on the inward direction when a car makes a turn on a level road?

Why adjustbox needs a tweak of raise=-0.3ex with enumitem?

Can I say "Gesundheit" if someone is coughing?

Is there a word that describes people who are extraverted and/or energetic, but uneducated, unintelligent and/or uncreative?

Why wasn't interlaced CRT scanning done back and forth?

What does Argus Filch specifically do?

Using Forstner bits instead of hole saws

Can't split a feature as invalid geometry - but "Check Validity" says its valid

Approximating an expression for a potential

Why have both: BJT and FET transistors on IC output?

Where can I see modifications made to the PATH environment variable by the Go installer

Why isn't the new LEGO CV joint available on Bricklink or Brickowl?

Can birds evolve without trees?

Is the EU really banning "toxic propellants" in 2020? How is that going to work?

HackerRank Implement Queue using two stacks Solution

In a KP-K endgame, if the enemy king is in front of the pawn, is it always a draw?

What is the reason behind water not falling from a bucket at the top of loop?

Detect lightning:recordForm change in mode attribute

Can an unintentional murderer leave Ir Miklat for Shalosh Regalim?

Went to a big 4 but got fired for underperformance in a year recently - Now every one thinks I'm pro - How to balance expectations?

how to change ^L code in many files in ubuntu?

What is Albrecht Dürer's Perspective Machine drawing style?

Postgres update INTEGER column from JSONB

Add a column with a default value to an existing table in SQL ServerHow do I UPDATE from a SELECT in SQL Server?How to exit from PostgreSQL command line utility: psqlpostgres: upgrade a user to be a superuser?Explanation of JSONB introduced by PostgreSQLJSONB performance degrades as number of keys increasePerformance issues jsonb in PostgresOptimizing Postgres JSONB query with not null constraintAggregating values on JSONB fieldPostgres JSONB timestamp query very slow compared to timestamp column query

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I have a table cart:

 id | value | metadata
--------+-------+-------------------
 45417 | 0 | "value": "1300"
 45418 | 0 | "value": "1300"
 276021 | 0 | "value": "1300"

and I'm trying to UPDATE the value column with the value in the JSONB metadata if it exists. I come up with the following query:

UPDATE cart SET value=CAST(subquery.meta_val as INTEGER) FROM
(SELECT id, metadata->>'value' as meta_val FROM cart
WHERE value = 0 AND 
metadata->>'value' IS NOT NULL) as subquery
WHERE cart.id=subquery.id;

Now this works but it takes quite a lot of time for 4M rows I want to update on production and it looks to me like there is a lot of redundancy in the query.

I think the next step would be to wrap all this in a transaction and improve the query, is there anything that can be done to improve performance out of this query ?

sql postgresql jsonb

share|improve this question

asked Mar 27 at 1:22

PepperoniPizzaPepperoniPizza

4,03555 gold badges3838 silver badges7474 bronze badges

add a comment | 

0

I have a table cart:

 id | value | metadata
--------+-------+-------------------
 45417 | 0 | "value": "1300"
 45418 | 0 | "value": "1300"
 276021 | 0 | "value": "1300"

and I'm trying to UPDATE the value column with the value in the JSONB metadata if it exists. I come up with the following query:

UPDATE cart SET value=CAST(subquery.meta_val as INTEGER) FROM
(SELECT id, metadata->>'value' as meta_val FROM cart
WHERE value = 0 AND 
metadata->>'value' IS NOT NULL) as subquery
WHERE cart.id=subquery.id;

Now this works but it takes quite a lot of time for 4M rows I want to update on production and it looks to me like there is a lot of redundancy in the query.

I think the next step would be to wrap all this in a transaction and improve the query, is there anything that can be done to improve performance out of this query ?

sql postgresql jsonb

share|improve this question

asked Mar 27 at 1:22

PepperoniPizzaPepperoniPizza

4,03555 gold badges3838 silver badges7474 bronze badges

add a comment | 

I have a table cart:

 id | value | metadata
--------+-------+-------------------
 45417 | 0 | "value": "1300"
 45418 | 0 | "value": "1300"
 276021 | 0 | "value": "1300"

and I'm trying to UPDATE the value column with the value in the JSONB metadata if it exists. I come up with the following query:

UPDATE cart SET value=CAST(subquery.meta_val as INTEGER) FROM
(SELECT id, metadata->>'value' as meta_val FROM cart
WHERE value = 0 AND 
metadata->>'value' IS NOT NULL) as subquery
WHERE cart.id=subquery.id;

Now this works but it takes quite a lot of time for 4M rows I want to update on production and it looks to me like there is a lot of redundancy in the query.

I think the next step would be to wrap all this in a transaction and improve the query, is there anything that can be done to improve performance out of this query ?

sql postgresql jsonb

share|improve this question

asked Mar 27 at 1:22

PepperoniPizzaPepperoniPizza

4,03555 gold badges3838 silver badges7474 bronze badges

 id | value | metadata
--------+-------+-------------------
 45417 | 0 | "value": "1300"
 45418 | 0 | "value": "1300"
 276021 | 0 | "value": "1300"

UPDATE cart SET value=CAST(subquery.meta_val as INTEGER) FROM
(SELECT id, metadata->>'value' as meta_val FROM cart
WHERE value = 0 AND 
metadata->>'value' IS NOT NULL) as subquery
WHERE cart.id=subquery.id;

share|improve this question

asked Mar 27 at 1:22

PepperoniPizzaPepperoniPizza

4,03555 gold badges3838 silver badges7474 bronze badges

share|improve this question

asked Mar 27 at 1:22

PepperoniPizzaPepperoniPizza

4,03555 gold badges3838 silver badges7474 bronze badges

asked Mar 27 at 1:22

PepperoniPizzaPepperoniPizza

4,03555 gold badges3838 silver badges7474 bronze badges

asked Mar 27 at 1:22

PepperoniPizzaPepperoniPizza

4,03555 gold badges3838 silver badges7474 bronze badges

1 Answer
1

active

oldest

votes

1

Try it without a subquery.

update cart as c
set value = coalesce((c.metadata->>'value')::int, 0)

share|improve this answer

answered Mar 27 at 3:29

Rob TaylorRob Taylor

45922 silver badges88 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Or if you want to leave the value unperturbed, then instead of 0, just use c.value
  
  – Rob Taylor
  Mar 27 at 3:30
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f55368449%2fpostgres-update-integer-column-from-jsonb%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

Try it without a subquery.

update cart as c
set value = coalesce((c.metadata->>'value')::int, 0)

share|improve this answer

answered Mar 27 at 3:29

Rob TaylorRob Taylor

45922 silver badges88 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Or if you want to leave the value unperturbed, then instead of 0, just use c.value
  
  – Rob Taylor
  Mar 27 at 3:30
  

  
  
  
  

add a comment | 

1

Try it without a subquery.

update cart as c
set value = coalesce((c.metadata->>'value')::int, 0)

share|improve this answer

answered Mar 27 at 3:29

Rob TaylorRob Taylor

45922 silver badges88 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Or if you want to leave the value unperturbed, then instead of 0, just use c.value
  
  – Rob Taylor
  Mar 27 at 3:30
  

  
  
  
  

add a comment | 

Try it without a subquery.

update cart as c
set value = coalesce((c.metadata->>'value')::int, 0)

share|improve this answer

answered Mar 27 at 3:29

Rob TaylorRob Taylor

45922 silver badges88 bronze badges

update cart as c
set value = coalesce((c.metadata->>'value')::int, 0)

share|improve this answer

answered Mar 27 at 3:29

Rob TaylorRob Taylor

45922 silver badges88 bronze badges

answered Mar 27 at 3:29

Rob TaylorRob Taylor

45922 silver badges88 bronze badges

answered Mar 27 at 3:29

Rob TaylorRob Taylor

45922 silver badges88 bronze badges