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 — fmsf
– fmsf, Commented Jan 2, 2020 at 9:09

user330315 · Accepted Answer · 2018-01-30 18:21:27Z

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, 2018 at 18:21

user330315

Sign up to request clarification or add additional context in comments.

Comments

Jack · Accepted Answer · 2018-01-30 17:52:03Z

1

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
$$;

answered Jan 30, 2018 at 17:52

Jack

671 silver badge7 bronze badges

Collectives™ on Stack Overflow

Check if table exists in postgres

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related