CODEWITHSUNDEEP
postgresqlunionpsql
rovyko
rovyko

Reputation: 4587

PostgreSQL - Append a table to another and add a field without listing all fields

I have two tables:

I need to write a SELECT query that adds the rows of table_b to table_a but with rank set to a specific value, let's say 4.

Currently I have:

SELECT * FROM table_a
UNION
SELECT item_id, 4 rank, field_1, field_2, ...

How can I join the two tables together without writing out all of the fields and without using an INSERT query?

EDIT:

My idea is to join table_b to table_a somehow with the rank field remaining empty, then simply replace the null rank fields. The rank field is never null, but item_id can be duplicated and table_a may have item_id values that are not in table_b, and vice-versa.

Upvotes: 0

Views: 414

Answers (2)

rovyko
rovyko

Reputation: 4587

I'm pretty sure I've got it. The predefined rank column can be inserted into table_b by joining to the subset of itself with only the columns left of the column behind which you want to insert.

WITH
    _leftcols AS ( SELECT item_id, 4 rank FROM table_b ),
    _combined AS ( SELECT * FROM table_b JOIN _leftcols USING (item_id) )
SELECT * FROM _combined
UNION
SELECT * FROM table_a

Upvotes: 0

klin
klin

Reputation: 121654

I am not sure I understand why you need this, but you can use jsonb functions:

select (jsonb_populate_record(null::table_a, row)).*
from (
    select to_jsonb(a) as row
    from table_a a
    union 
    select to_jsonb(b) || '{"rank": 4}'
    from table_b b
) s
order by item_id;

Working example in rextester.

Upvotes: 1

Related Questions