![]() ![]() For completeness, let’s test it:ĭid you spot the mistake? Here’s the error: cannot extract elements from an object. Note the use of the ? operator (as in NEW.characteristics ? 'enemies') to see if a JSON object has the enemies key in it. ![]() Apply the trigger to execute BEFORE changes to the people table:.Make a trigger function that handles DELETE, UPDATE, and INSERT (see the docs for more information).To provide this mix of performance and flexibility we will instead create an enemy_association table and put a trigger function on the people table to maintain the enemy_association table. Suppose we determined that our enemy_association view was not fast enough for our project, but we like or don’t want to change how we’re manipulating the data in the people table with JSON. Trigger-maintained tables – where triggers are used on tables or views to supplement or convert inserts and updates to changes to one or more other tables.If the view is only used for occasional reporting, or the source tables rarely change, then this may be the ideal option, as there’s no overhead per insert or update to the backing table. Instead, they are updated with the REFRESH MATERIALIZED VIEW command, which can be quite expensive, time- and resource-wise. However, you can’t manipulate them directly. Materialized Views – while still views populated by a given expression, these are “full tables” that use storage space and can be indexed.If you need better performance without changing the tables there are two main options: That may be good enough for your application, depending on your usage patterns. Trigger Functions to Get Performance and ExpressivityĬareful application of indexing will improve the performance of tables that contain JSON columns (and the views that refer to them). However, we can provide that functionality with INSTEAD OF triggers. ![]() Note that Postgres does have the ability to update and insert through views, but only in very strict circumstances that generally rule out manipulation of data inside JSON columns. ![]() Further, with scalar-valued columns, foreign-key constraints can be used to ensure data integrity across data changes. Views are not indexed, and JSON column indexes are not quite as good as indexed scalar-valued columns. Now the enemy_association view can be used in joins without directly referring to the JSON content.Īlthough using this view is syntactically simpler than directly joining into the JSON, it will not be as performant or robust as an actual table that holds this information. Here we’ll make a view that shows all enemies list changes in the characteristics column of the people table, according to the change_log table (note that the referenced tables are defined in Part 1):Īnother option is to make a view that expresses the person table with the enemies list as an Associative Table: Alternatively, you could store columnar data (possibly across many tables) and create views that express the data in JSON format. This means that you could store your data in JSON format and make one or more views that break the JSON data out into columnar format. Views work with JSON columns, just like any other columns, and can be used to simplify queries involving JSON. There are minor advantages in compiled expression optimization, but mostly the value of views lies in simplifying the syntax of a hairy expression, particularly when it’s used in a join. They are typically read-only and are roughly as performant as the same expression raw. Views are essentially virtual tables that are populated dynamically from an expression given at the time of view creation. In this article, we’ll dive deeper into ways to use tables with JSONb columns, discussing constraints and triggers to guarantee data integrity, and show how to get full JSON expressivity while keeping most of the performance. In Part 1 we explained why you absolutely need JSONb columns in your PostgreSQL tables, and how they simplify the expression of complex data, including complex references and relationships. ![]()
0 Comments
Leave a Reply. |