This way we don't have to drop and recreate columns nor modify system tables. Instead, lets create a new type with the correct enum values and swap out the old one. No! hacking the catalogs is dangerous, unpredictable, and almost always a bad idea. "Modify the system tables with DELETE FROM pg_enum." This has some (slight) disadvantages and there is a sleeker solution. This solution alone is no good if the type is already in use as you would have to drop any columns that use the type or temporarily change the column type to TEXT. The most popular solutions I found for this problem were always one of these two:
#UPDATE POSTGRESQL EXAMPLE UPDATE#
Unfortunately, there is no way to remove values in any version (as of 12) and no way to update values in 9.6 and bellow. Version 10 introduced an easy way to update existing values: ALTER TYPE name RENAME VALUE. PSQL provides an easy way to add new values to an enum: ALTER TYPE name ADD VALUE. To remove a value in any version or update a value in version 9.6 and bellow: # rename the existing typeĪLTER TYPE status_enum RENAME TO status_enum_old # create the new typeĬREATE TYPE status_enum AS ENUM ( 'queued', 'running', 'done' ) # update the columns to use the new typeĪLTER TABLE job ALTER COLUMN job_status TYPE status_enum USING job_status:: text::status_enum # if you get an error, see bottom of post To update a value in version 10 and up ( thanks Vlad for the heads up): ALTER TYPE status_enum RENAME VALUE 'waiting' TO 'blocked'
Side note: that solution came out of a pair programming session with Lucas Cegatti.Īre you looking for a creative company to implement your next idea? Check out LNA Systems and let’s talk.Yo1dog blog Updating Enum Values in PostgreSQL - The Safe and Easy Way Mar 29th, 2017 That brings a cost that you have to consider when deciding which tools you pick to use. But keep in mind that you also need to query and update this kind of data. JSONB is a great and valuable tool to resolve a lot of problems. The above selects will return: because that’s the type expected on the jsonb_path function. Given a jsonb column, you can set a new value on the specified path: Reference: PostgreSQL Json functions Jsonb_set(target jsonb, path text, new_value jsonb)
#UPDATE POSTGRESQL EXAMPLE HOW TO#
Pretty easy right? But how can you update a specific contact for a specific customer? How to change Jimi's email or Janis’ phone?įortunately, PostgreSQL is your friend and provides the jsonb_set function: Then you create a customers table with a JSONB contacts column and insert some data into it: Then you come up with the idea of storing the contacts as a JSONB column because they’re dynamic, and thus using a not relational data structure makes sense. Suppose you’re implementing a customer screen to store dynamic contacts for each customer.
TL DR: the final query is at the end of the article, and you can check out a live example at DB Fiddle to copy & paste and play with. In this article let’s see how to update a specific value of an object inside an array with one query. But, you just created another problem: performance bottlenecks and resource waste. JSONB is a powerful tool, but it comes at some cost because you need to adapt the way you query and handle the data.Īnd it’s not rare to load the entire jsonb object into memory, transform it using your preferred programming language, and then saving it back to the database. Let’s say you decided to store data in the database as json or jsonb and discovered that you just created new problems for yourself that you didn’t have before. By Leandro Cesquini Pereira How to update objects inside JSONB arrays with PostgreSQL Photo by Jan Antonin Kolar on Unsplash How to update a specific value on a JSONB array
0 kommentar(er)
