In this article, we would like to show you how to create a view in PostgreSQL.
CREATE VIEW "view_name" AS SELECT "column1", "column2", ... FROM "table_name" WHERE condition;
To show how the
CREATE VIEW statement works, we will use the following table:
At the end of this article you can find database preparation SQL queries.
In this example, we will create a view that shows all users from Spain.
CREATE VIEW "Spain Users" AS SELECT "name", "email" FROM "users" WHERE "country" = 'Spain';
To see the view created with the query above simply use:
SELECT * FROM "Spain Users";
If you try to create view that already exist with
CREATE VIEW query you will get an error:
If you try to
CREATE VIEW IF NOT EXISTS you will get a warning instead:
CREATE TABLE "users" ( "id" SERIAL PRIMARY KEY, "name" VARCHAR(100) NOT NULL, "email" VARCHAR(100) NOT NULL, "country" VARCHAR(15) NOT NULL );
INSERT INTO "users" ("name", "email", "country") VALUES ('Tom', 'email@example.com', 'Poland'), ('Chris','firstname.lastname@example.org', 'Spain'), ('Jack','email@example.com', 'Spain'), ('Kim','firstname.lastname@example.org', 'Vietnam'), ('Marco','email@example.com', 'Italy'), ('Kate','firstname.lastname@example.org', 'Spain'), ('Nam','email@example.com', 'Vietnam');