Doubt in SELECT when creating a VIEW

Question

Good afternoon, everyone,

I need to create a VIEW in POSTGRESQL, example below:

CREATE OR REPLACE VIEW test.nomedaview AS SELECT Tabela1.Cpf
FROM test.Tabela1
UNION SELECT table2.cpfcnpj FROM test.table 2; ALTER TABLE test.nomedaview

My question is this::

In my Tabela1 there are 2 columns, the Cpf column that has only Cpf and the cnpj column that has only cnpj;

In my table2 there is a single column called cpfcnpj that has either Cpf or cnpj;

In this view above, how is being used the UNION, so that this information comes in a single column, has any way to unify this information from table 1 (Cpf with cnpj)? type when the Cpf column is filled in it takes her information and if the cnpj is filled in it takes her information

OBS 1: in the example above (in bold) when I put only one of the information (Cpf or cnpj), the view is generated, but in it will only appear the cpfs of Tabela1, but in the case I wanted to appear cnpj also; OBS 2: in Tabela1, when the column Cpf is filled, it means that the column cnpj will be null, and vice versa; OBS 3: in both tables these columns are of type "Character Varying".

someone can help me?

Answer 1

Good afternoon,

I believe you can use the function Coalesce to get the value of the column that is populated between columns Cpf and cnpj:

SELECT coalesce(tabela1.cpf, tabela1.cnpj) as cpfcnpj 
FROM teste.tabela1

UNION 

SELECT tabela2.cpfcnpj 
FROM teste.tabela2

I hope it helps