Why view with column alias is not insertable?

CREATE TABLE `projects` (
  `stars` integer, 
  `title` varchar(255)

CREATE VIEW projects_view AS SELECT *, title AS name FROM projects;

When I try to insert something:

INSERT INTO `projects_view` (`name`) VALUES ('Name');

I get: ERROR 1471 (HY000): The target table projects_view of the INSERT is not insertable-into

But this works in PostgreSQL.

>Solution :

You cannot insert because by using select * you have two references in the view to a single column.

From the documentation a view is not insertable where

Multiple references to any column of a base table (fails for INSERT, okay for UPDATE, DELETE)

It should work if you name each column:

CREATE VIEW projects_view AS 
SELECT stars, title as name FROM projects;

Fiddle example

Leave a Reply