PostgreSQL criar uma tabela se não existir
num script MySQL podes escrever:
CREATE TABLE IF NOT EXISTS foo ...;
... coisa ...
e então você pode executar o script muitas vezes sem recriar a tabela.
Como se faz isso no PostgreSQL?6 answers
Este recurso foi implementado em Postgres 9.1:
CREATE TABLE IF NOT EXISTS myschema.mytable (i integer);
Para versões mais antigas, aqui está uma função para trabalhar em torno dela:
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;
Ligue para:
SELECT create_mytable(); -- call as many times as you want.
Notas:
-
As colunas
schemaname
etablename
empg_tables
são sensíveis à capitalização. Se você citar dois identificadores na declaraçãoCREATE TABLE
, você precisa usar a mesma ortografia exata. Se não o fizeres, tens de usar cordas de minúsculas. Ver: -
pg_tables
apenas contém quadros reais. O identificador pode ainda ser ocupado por objetos relacionados. Ver: Se a função a execução esta função não tem os privilégios necessários para criar a tabela, você poderá querer usar
SECURITY DEFINER
para o função e torná-lo propriedade por outra função com os privilégios necessários. Esta versão é suficientemente segura.
Tenta isto:
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)
)
Criei uma solução genérica a partir das respostas existentes que pode ser reutilizada para qualquer tabela:
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;
Utilização:
select create_if_not_exists('my_table', 'CREATE TABLE my_table (id integer NOT NULL);');
Poderia ser simplificado ainda mais para tomar apenas um parâmetro se se extraísse o nome da tabela do parâmetro da consulta. Também deixei de fora os esquemas. Sinta - se livre para estender a minha solução se você sabe como fazer isso-eu não sou tão profundo em plpgsql ainda (esta é a primeira vez que estou lidando com isso).
Não existe nenhuma tabela de criação se não existir... mas você pode escrever um procedimento simples para isso, algo como: [[3]}
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;
Esta solução é um pouco semelhante à resposta de Erwin Brandstetter, mas usa apenas a linguagem sql.
Nem todas as instalações do PostgreSQL têm a linguagem plpqsql por omissão, o que significa que poderá ter de ligar para CREATE LANGUAGE plpgsql
Antes de criar a função, e depois ter de remover a linguagem de novo, para deixar a base de dados no mesmo estado que era antes (mas só se a base de dados não tivesse a linguagem plpgsql para começar). Vês como a complexidade cresce?
Adicionando o plpgsql pode não ser problema se você estiver executando seu script localmente, no entanto, se o script é usado para configurar o esquema em um cliente pode não ser desejável deixar alterações como esta no banco de dados dos clientes.
Esta solução é inspirada por um post de 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();
Não existe nenhuma tabela de criação se não existir... mas você pode escrever um procedimento simples para isso, algo como: [[3]}
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'
);