PostgreSQL - Create or replace view
In this article, we would like to show you how to create and update a view using CREATE OR REPLACE VIEW in PostgreSQL.
Quick solution:
CREATE OR REPLACE VIEW "view_name" AS
SELECT "column1", "column2", "columnN"
FROM "table_name"
WHERE condition;
Practical example
To show how to create or replace a view, we will use the following table:
Note:
At the end of this article you can find database preparation SQL queries.
Example 1 - create view
In this example, we will create a view using CREATE OR REPLACE VIEW statement.
Query:
CREATE OR REPLACE VIEW "spain_users" AS
SELECT *
FROM "users"
WHERE "country" = 'Spain';
Result:
Example 2 - update view
In this example, we will remove the country column from the view created in Example 1. We will remove the column by updating the existing view using CREATE OR REPLACE VIEW statement.
Query:
CREATE OR REPLACE VIEW "spain_users" AS
SELECT "id", "name", "email"
FROM "users"
WHERE "country" = 'Spain';
Result:
Database preparation
create_tables.sql file:
CREATE TABLE "users" (
"id" SERIAL,
"name" VARCHAR(100) NOT NULL,
"email" VARCHAR(100) NOT NULL,
"country" VARCHAR(15) NOT NULL,
PRIMARY KEY ("id")
)
ENGINE=InnoDB;
insert_data.sql file:
INSERT INTO "users"
("name", "email", "country")
VALUES
('Tom', 'tom@email.com', 'Poland'),
('Chris','chris@email.com', 'Spain'),
('Jack','jack@email.com', 'Spain'),
('Kim','kim@email.com', 'Vietnam'),
('Marco','marco@email.com', 'Italy'),
('Kate','kate@email.com', 'Spain'),
('Nam','nam@email.com', 'Vietnam');
create_view.sql file:
CREATE VIEW "spain_users" AS
SELECT *
FROM "users"
WHERE "country" = 'Spain';
Quick solution:
CREATE TABLE "table_name" (
"column1" DATA_TYPE,
"column2" DATA_TYPE,
"column3" DATA_TYPE,
...
);
Note:
Go to the official documentation to see available
DATA_TYPES.
Practical example
In this example, we create users table with the following columns and types:
id- INT,name- VARCHAR,role- VARCHAR.
Query:
CREATE TABLE "users" (
id SERIAL,
name VARCHAR(100) NOT NULL,
role VARCHAR(15) NOT NULL,
PRIMARY KEY (id)
);
Database:
