Check if table exists in postgres

Question

In Firebird I have the following code to check if table exists:

SET TERM ^ ;
EXECUTE BLOCK AS
BEGIN
  IF (NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'TESTE')) THEN
BEGIN
  EXECUTE STATEMENT
    'CREATE TABLE TESTE( ' ||
      'CDTESTE VARCHAR(7) NOT NULL, ' ||
      'FATURA VARCHAR(7)); ';
  EXECUTE STATEMENT 'ALTER TABLE TESTE ADD CONSTRAINT PK_TESTE PRIMARY KEY (CDTESTE)';
END
END^
SET TERM ; ^

How can I do that in Postgres using anonymous block? Sorry my english.

Does this answer your question? [How to check if a table exists in a given schema](https://stackoverflow.com/questions/20582500/how-to-check-if-a-table-exists-in-a-given-schema) — fmsf, Jan 02 '20 at 09:09

score 8 · Answer 1 · answered Jan 30 '18 at 18:21

8

No need for a check:

CREATE TABLE IF NOT EXISTS TESTE(
   CDTESTE VARCHAR(7) NOT NULL, 
   FATURA VARCHAR(7)
);

ALTER TABLE TESTE ADD CONSTRAINT PK_TESTE PRIMARY KEY (CDTESTE);

answered Jan 30 '18 at 18:21

score 0 · Answer 2 · answered Jan 30 '18 at 17:52

I found the solution:

DO $$     
begin 
  if not (SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE 
  upper(table_name) = 'TB_INGREDIENTE')) then
  CREATE TABLE tb_rep_ingredientes
  (
  cod_rep_ingrediente serial PRIMARY KEY,
  cod_ingrediente bigint,
  cod_filial bigint,
  nome character varying(30),
  preco_compra money,
  saldo real    
  );
end if;  
end
$$;

Check if table exists in postgres

2 Answers2