PostgreSQL - Working with a virtual/generated column

PostgreSQL - Working with a virtual/generated column

To create a table in PostgreSQL with a virtual/generated column derived from JSON data, you can use the jsonb data type along with a generated column. Here is an example:

Sample Json:

{ "firstname":"John", "lastName":"Sri" }

  1. Create the table: Define the table with a jsonb column and a generated column that extracts a value from the JSON data.

  2. Insert data & Query data: Insert JSON data into the table and Query the table to see the generated column in action.

Here is a step-by-step guide:

Step 1: Create the Table

In this example:

  • id: A serial primary key column.
  • data: A JSONB column to store the JSON data.
  • first_name: A generated column that extracts the first_name from the JSON data.
  • last_name: A generated column that extracts the last_name from the JSON data.

Here’s how you might use this table:

Step 2: Insert Data & Query Data



This would result in the following output:



In this way, first_name and last_name columns are automatically populated based on the values extracted from the data JSON column.

Comments

Post a Comment

Popular posts from this blog

FastEndpoint vs Minimal APIs vs Controller

Here's a tabular comparison between FastEndpoints, Minimal APIs, and Controllers in the context of ASP.NET Core: Feature/Aspect FastEndpoints Minimal APIs Controllers Design Philosophy Focuses on performance and simplicity by providing a fast, endpoint-centric approach Prioritizes minimalism and simplicity, with an emphasis on lightweight, functional programming Based on the MVC pattern, providing a structured, feature-rich approach Performance Optimized for performance with minimal overhead High performance due to minimal abstractions Slightly more overhead due to MVC and routing abstractions Setup Complexity Requires additional setup and configuration but offers more granular control Minimal setup, quickly defined routes, and handlers Requires more setup and configuration with Controllers, Actions, and Routes Routing Explicit routing defined per endpoint, more control over routes Inline routing with lambda expressions, less verbose Attribute-base...
Read more

Workflow Orchestration Tools (Stateless vs Workflow Core vs Elsa vs Step Functions vs Hangfire)

Here's a tabular comparison of Stateless , Workflow Core , Elsa , Step Functions , and Hangfire in the context of building an orchestrator pipeline in C#: Feature/Aspect Stateless Workflow Core Elsa Step Functions Hangfire Primary Focus State machine library Workflow engine Workflow engine and orchestrator Serverless orchestration of workflows Background job processing Architecture In-memory state machine In-memory and persistent workflows In-memory and persistent workflows Cloud-native (AWS) serverless In-memory and persistent job storage Workflow Model State transitions Steps and transitions Steps, activities, triggers Tasks, states, and transitions Background jobs and recurring jobs Persistence No native persistence (requires custom implementation) Supports multiple persistence providers (e.g., EF Core, MongoDB) Supports multiple persistence providers (e.g., EF Core, MongoDB) Managed by AWS Supports multiple storage options (e.g., SQL ...
Read more

AutoMapper vs FastMapper

Here is a tabular comparison between AutoMapper and FastMapper: Feature/Aspect AutoMapper FastMapper Library Type Object-to-object mapping Object-to-object mapping Performance Slower, especially with large and complex mappings Faster, optimized for performance Setup Complexity Requires configuration for mapping profiles, but highly flexible Minimal setup, focuses on simplicity Custom Mapping Supports custom mapping rules and converters Supports basic custom mapping with less flexibility Inheritance Support Well-supported, can map between base and derived classes Limited inheritance support Null Handling Customizable null handling options Basic null handling Mapping Collections Supports mapping collections with built-in configuration Supports collections, faster due to lightweight processing Flattening and Projection Supports flattening complex objects and nested properties Limited support for flattening Assemblies and Dependencies He...
Read more