PostgreSQL crée une table si elle n’existe pas

Dans un script MySQL, vous pouvez écrire:

CREATE TABLE IF NOT EXISTS foo ...; 

… d’autres choses …

et puis vous pouvez exécuter le script plusieurs fois sans recréer la table.

Comment faites-vous cela dans PostgreSQL?

    Cette fonctionnalité a été implémentée dans Postgres 9.1 :

     CREATE TABLE IF NOT EXISTS myschema.mytable (i integer); 


    Pour les anciennes versions , voici une fonction pour le contourner:

     CREATE OR REPLACE FUNCTION create_mytable () RETURNS void AS $func$ BEGIN IF EXISTS (SELECT 1 FROM pg_catalog.pg_tables WHERE schemaname = 'myschema' AND tablename = 'mytable') THEN RAISE NOTICE 'Table myschema.mytable already exists.'; ELSE CREATE TABLE myschema.mytable (i integer); END IF; END $func$ LANGUAGE plpgsql; 

    Appel:

     SELECT create_mytable(); -- call as many times as you want. 

    Remarques:

    • Les colonnes schemaname et tablename dans pg_tables sont sensibles à la casse. Si vous doublez les identifiants dans l’instruction CREATE TABLE , vous devez utiliser exactement la même orthographe. Si vous ne le faites pas, vous devez utiliser des chaînes minuscules. Voir:

      • Les noms de colonne PostgreSQL sont-ils sensibles à la casse?
    • pg_tables ne contient que des tables réelles. L’identifiant peut encore être occupé par des objects associés. Voir:

      • Comment vérifier si une table existe dans un schéma donné
    • Si le rôle exécutant cette fonction ne dispose pas des privilèges nécessaires pour créer la table, vous pouvez utiliser SECURITY DEFINER pour la fonction et la faire appartenir à un autre rôle disposant des privilèges nécessaires. Cette version est suffisamment sûre.

    Essaye ça :

     CREATE TABLE IF NOT EXISTS app_user ( username varchar(45) NOT NULL, password varchar(450) NOT NULL, enabled integer NOT NULL DEFAULT '1', PRIMARY KEY (user_id) ) 

    J’ai créé une solution générique à partir des réponses existantes qui peuvent être réutilisées pour n’importe quelle table:

     CREATE OR REPLACE FUNCTION create_if_not_exists (table_name text, create_stmt text) RETURNS text AS $_$ BEGIN IF EXISTS ( SELECT * FROM pg_catalog.pg_tables WHERE tablename = table_name ) THEN RETURN 'TABLE ' || '''' || table_name || '''' || ' ALREADY EXISTS'; ELSE EXECUTE create_stmt; RETURN 'CREATED'; END IF; END; $_$ LANGUAGE plpgsql; 

    Usage:

     select create_if_not_exists('my_table', 'CREATE TABLE my_table (id integer NOT NULL);'); 

    On pourrait simplifier davantage pour ne prendre qu’un seul paramètre si l’on voulait extraire le nom de la table du paramètre de requête. Aussi j’ai laissé de côté les schémas. N’hésitez pas à étendre ma solution si vous savez comment le faire – je ne suis pas encore très au fait de plpgsql (c’est la première fois que je le fais).

    Il n’y a pas de CREATE TABLE si ce n’est EXISTS … mais vous pouvez écrire une procédure simple pour cela, quelque chose comme:

     CREATE OR REPLACE FUNCTION prc_create_sch_foo_table() RETURNS VOID AS $$ BEGIN EXECUTE 'CREATE TABLE /* IF NOT EXISTS add for PostgreSQL 9.1+ */ sch.foo ( id serial NOT NULL, demo_column varchar NOT NULL, demo_column2 varchar NOT NULL, CONSTRAINT pk_sch_foo PRIMARY KEY (id)); CREATE INDEX /* IF NOT EXISTS add for PostgreSQL 9.5+ */ idx_sch_foo_demo_column ON sch.foo(demo_column); CREATE INDEX /* IF NOT EXISTS add for PostgreSQL 9.5+ */ idx_sch_foo_demo_column2 ON sch.foo(demo_column2);' WHERE NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_schema = 'sch' AND table_name = 'foo'); EXCEPTION WHEN null_value_not_allowed THEN WHEN duplicate_table THEN WHEN others THEN RAISE EXCEPTION '% %', SQLSTATE, SQLERRM; END; $$ LANGUAGE plpgsql; 

    Cette solution est quelque peu similaire à la réponse d’Erwin Brandstetter, mais n’utilise que le langage SQL.

    Toutes les installations de PostgreSQL n’ont pas le langage plpqsql par défaut, cela signifie que vous devrez peut-être appeler CREATE LANGUAGE plpgsql avant de créer la fonction, puis supprimer à nouveau le langage pour laisser la firebase database dans le même état que précédemment (mais seulement si la firebase database n’avait pas le langage plpgsql pour commencer). Voyez comment la complexité grandit?

    L’ajout de plpgsql peut ne pas être un problème si vous exécutez votre script localement, cependant, si le script est utilisé pour configurer un schéma chez un client, il n’est peut-être pas souhaitable de laisser des modifications de ce type dans la firebase database clients.

    Cette solution s’inspire d’ un article d’Andreas Scherbaum .

     -- Function which creates table CREATE OR REPLACE FUNCTION create_table () RETURNS TEXT AS $$ CREATE TABLE table_name ( i int ); SELECT 'extended_recycle_bin created'::TEXT; $$ LANGUAGE 'sql'; -- Test if table exists, and if not create it SELECT CASE WHEN (SELECT true::BOOLEAN FROM pg_catalog.pg_tables WHERE schemaname = 'public' AND tablename = 'table_name' ) THEN (SELECT 'success'::TEXT) ELSE (SELECT create_table()) END; -- Drop function DROP FUNCTION create_table(); 

    Il n’y a pas de CREATE TABLE si ce n’est EXISTS … mais vous pouvez écrire une procédure simple pour cela, quelque chose comme:

     CREATE OR REPLACE FUNCTION execute(TEXT) RETURNS VOID AS $$ BEGIN EXECUTE $1; END; $$ LANGUAGE plpgsql; SELECT execute($$ CREATE TABLE sch.foo ( i integer ) $$) WHERE NOT exists ( SELECT * FROM information_schema.tables WHERE table_name = 'foo' AND table_schema = 'sch' );