Question 340828 in Debian Question posted in
The oficial documentation can be found here.

Debian – How can union two tables with their own order?

postgresql version info:

psql -V
psql (PostgreSQL) 13.11 (Debian 13.11-0+deb11u1)

Create table :

create table sample1(ticker text,pe float,grow float,peg float);
create table sample2(ticker text,pe float,grow float,peg float);

Insert data for test in table sample1:

insert into sample1 (ticker,pe,grow,peg) 
values 
('dxp',16.8,2.809,0.07),
('vhm',5.4,0.656,0.08);

Insert data for test in table sample2:

insert into sample2 (ticker,pe,grow,peg) 
values 
('vig',11.1,-0.425,-0.26),
('lhc',9.1,-0.209,-0.43);

Data in two tables:

select * from sample1;
 ticker |  pe  | grow  | peg  
--------+------+-------+------
 dxp    | 16.8 | 2.809 | 0.07
 vhm    |  5.4 | 0.656 | 0.08
(2 rows)

Time: 0.749 ms
select * from sample2;
 ticker |  pe  |  grow  |  peg  
--------+------+--------+-------
 vig    | 11.1 | -0.425 | -0.26
 lhc    |  9.1 | -0.209 | -0.43
(2 rows)

Union them into one:

select * from sample1 union select * from sample2;
 ticker |  pe  |  grow  |  peg  
--------+------+--------+-------
 vig    | 11.1 | -0.425 | -0.26
 dxp    | 16.8 |  2.809 |  0.07
 lhc    |  9.1 | -0.209 | -0.43
 vhm    |  5.4 |  0.656 |  0.08
(4 rows)

Can union them with order in their own tables such as below?

 ticker |  pe  |  grow  |  peg  
--------+------+--------+-------
 dxp    | 16.8 |  2.809 |  0.07
 vhm    |  5.4 |  0.656 |  0.08
 vig    | 11.1 | -0.425 | -0.26
 lhc    |  9.1 | -0.209 | -0.43
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The simplest way is implement union all:

    select * from sample1 union all select * from sample2;

  2. 0

    The order in your both tables is not deterministic, However, if you prefer the data from table1 to appear first, you can include a static column in both SELECT statements to define the order.

    select *
from (
  select *, 1 as rn
  from sample1 
  union 
  select *, 2
  from sample2
) as s
order by rn, pe desc

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search