create-table

Is it possible to roll back CREATE TABLE and ALTER TABLE statements in major SQL databases?

by

http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis provides an overview of this issue from PostgreSQL’s perspective. Is DDL transactional according to this document? PostgreSQL – yes MySQL – no; DDL causes an implicit commit Oracle Database 11g Release 2 and above – by default, no, but an alternative called edition-based redefinition exists Older versions of Oracle – no; DDL causes an … Read more

PostgreSQL create table if not exists

by

This feature has been implemented in Postgres 9.1: CREATE TABLE IF NOT EXISTS myschema.mytable (i integer); For older versions, here is a function to work around it: CREATE OR REPLACE FUNCTION create_mytable() RETURNS void LANGUAGE plpgsql AS $func$ BEGIN IF EXISTS (SELECT FROM pg_catalog.pg_tables WHERE schemaname=”myschema” AND tablename=”mytable”) THEN RAISE NOTICE ‘Table myschema.mytable already exists.’; … Read more

Create a temporary table in a SELECT statement without a separate CREATE TABLE

by

CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1) From the manual found at http://dev.mysql.com/doc/refman/5.7/en/create-table.html You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed. This means that two different sessions can use … Read more