12312

MIgrating Virtual Columns from oracle to postgres

Question:

What options does one have to deal with virtual columns when migrating from Oracle 11 to Postgres 9.5 - without having to change database related code in an application (which means functions and views are out of the picture and triggers are way too expensive as dealing with large data sets)?

A similar question exists : <a href="https://stackoverflow.com/questions/8250389/computed-calculated-columns-in-postgresql/8250729?noredirect=1" rel="nofollow">Computed / calculated columns in PostgreSQL</a> but the solutions do not help with the migration scenario.

Answer1:

If you use a BEFORE INSERT trigger, you can modify the values inserted before they actually are written. That shouldn't be very expensive. If cutting edge performance is required, write the trigger function in C.

But I think that a view is the best solution. You can use an updatable view, that way you wouldn't have to change the application code:

CREATE TABLE data( id integer PRIMARY KEY, factor1 integer NOT NULL, factor2 integer NOT NULL ); CREATE VIEW interface AS SELECT id, factor1, factor2, factor1 * factor2 AS product FROM data; test=> INSERT INTO interface VALUES (1, 6, 7), (2, 3, 14); INSERT 0 2 test=> UPDATE interface SET factor1 = 7 WHERE id = 1; UPDATE 1 test=> DELETE FROM interface WHERE id = 1; DELETE 1 test=> SELECT * FROM interface; ┌────┬─────────┬─────────┬─────────┐ │ id │ factor1 │ factor2 │ product │ ├────┼─────────┼─────────┼─────────┤ │ 2 │ 3 │ 14 │ 42 │ └────┴─────────┴─────────┴─────────┘ (1 row)

Recommend

  • Django implementation of default value in database
  • Django Rest Framework - Best way to deal with API parameter validation errors?
  • insert into a MySQL database on a different server
  • GData Youtube : obtaining thumbnails
  • Cache is client side or server side?
  • Table field that holds row count from another table
  • Exception handling as per java coding standards
  • Regarding Static class in c# [duplicate]
  • Entity Framework Core 1.0.1 add-migration
  • iOS 9 custom transition - animationControllerForDismissedController not called
  • jQuery Mobile - Dialogs without changing hash
  • Binary Tree Traversal Sum Of Each Depth
  • Show records ordered with maximum price first in PHP & MySQL
  • Small video playback
  • How to initialize context? [closed]
  • Type safe keys with Entity Framework model
  • Selecting a subset of data in ServiceStack.OrmLite
  • Granting permissions to Azure Active Directory Web Application automatically
  • Is there any way to call saveCurrentTurnWithMatchData without sending a push notification?
  • how to populate a SQLite database and use that database in phonegap?
  • Compare struct to a constant in C
  • In matplotlib, how do you change the fontsize of a single figure?
  • Creating a DropDownList
  • Authentication in Play! and RestEasy
  • Entity Framework Code First TPC Inheritance Self-Referencing Child Class
  • how to avoid repetitive constructor in children
  • How to match http request and response using Jersey ContainerRequestFilter and ContainerResponseFilt
  • Spark fat jar to run multiple versions on YARN
  • Ajax Loaded meta Tags
  • Xamarin Forms - UWP Fonts
  • What is Eclipse's Declaration View used for?
  • Arrow is showed instead of the material design version hamburger icon. Why doesn't syncState in
  • ActionScript 2 vs ActionScript 3 performance
  • Arrays break string types in Julia
  • json Serialization in asp
  • embed rChart in Markdown
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app
  • git trying to push non-existent file … after clearing cache