Sql UNION same tables with additional origin column

Question

I have two same tables. Each for each year.

• 15_docs:

  id, name, org, slo, dok

• 16_docs:

  id, name, org, slo, dok

Now I want to create view with UNION of both, but I need to know the rows origin.

Output VIEW:

id, name, org, slo, dok, year

How can I achieve that? I am using postgreSQL database.

Answer 1

 SELECT id, name, org, slo, dok, 'year 2015' FROM 15_docs
UNION
 SELECT id, name, org, slo, dok, 'year 2016' FROM 16_docs

Edit

This surely works for MS SQL server, have not test it on postgresql

Answer 2

you can use UNION & UNION ALL in this case , both give the same result because UNION clause give distinct rows at the end & UNION ALL clause combine all rows of both query result .But in this case ,this 'year' must create a uniqueness in each row . Try this query !

 SELECT 
        id,
        name,
        org,
        slo,
        dok,
        'year_15 ' as year
    FROM 
       15_docs

    UNION

    SELECT 
        id,
        name,
        org,
        slo,
        dok,
        'year_16 ' as year
    FROM 
       16_docs

or

SELECT 
        id,
        name,
        org,
        slo,
        dok,
        'year_15 ' as year
    FROM 
       15_docs

    UNION ALL

    SELECT 
        id,
        name,
        org,
        slo,
        dok,
        'year_16 ' as year
    FROM 
       16_docs