previous : next

Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

CREATE OR REPLACE VIEW

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Many dialects support a convenient OR REPLACE clause that allows for dropping any pre-existing view by the same name in a single statement. 

// Create a new view
create.createOrReplaceView("early_authors", "author_id", "first_name", "last_name")
      .as(select(AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
          .from(AUTHOR)

          // Any inserted or updated authors must continue to satisfy this condition
          .where(AUTHOR.ID.lt(200)))
      .execute();

Dialect support

This example using jOOQ:

createOrReplaceView("a", "id").as(select(AUTHOR.ID).from(AUTHOR))

Translates to the following dialect specific expressions:

ASE, Aurora Postgres, BigQuery, CockroachDB, DB2, Databricks, DuckDB, Exasol, H2, Hana, MariaDB, MySQL, Oracle, Postgres, Vertica, YugabyteDB

CREATE OR REPLACE VIEW a(id)
AS
SELECT AUTHOR.ID
FROM AUTHOR

ClickHouse

CREATE OR REPLACE VIEW a
AS
SELECT t.id
FROM (
  SELECT AUTHOR.ID id
  FROM AUTHOR
) t

Firebird, SQLServer

CREATE OR ALTER VIEW a(id)
AS
SELECT AUTHOR.ID
FROM AUTHOR

Teradata

REPLACE VIEW a(id)
AS
SELECT AUTHOR.ID
FROM AUTHOR

Trino

CREATE OR REPLACE VIEW a
AS
SELECT t.id
FROM (
  SELECT AUTHOR.ID
  FROM AUTHOR
) t (id)

Access, Aurora MySQL, Derby, HSQLDB, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLite, Snowflake, Sybase

/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website

previous : next

Feedback

Do you have any feedback about this page? We'd love to hear it!

↑ Back to top

Community

Support

Legal

Documentation

The jOOQ Logo