Postgres | how to combine from two or more table into single table

Question

i have lots of data which is divided into two or more tables.Then how to fetch the data from all the table.

First table is point table which is divided into three parts

first table = ab_p1_point
second table = ab_p2_point
third table = ab_p3_point

Another table is line table which is again divided into three parts

first table = bc_p1_line
second table = bc_p2_line
third table = bc_p3_line

How to combine all the parts of point tables into single point table and all the parts of line tables into single line table than perform operation between both the tables.

d512 · Accepted Answer · 2018-04-09 05:20:47Z

1

Assuming that all three tables have the same structure, you could potentially use union all:

select * from (
    select f1, f2, f3 from ab_p1_point
    union all
    select f1, f2, f3 from ab_p2_point
    union all
    select f1, f2, f3 from ab_p3_point
) t
where f1 = 'abc'

You could make it simpler to work with by putting the query into a view.

But why are you breaking up your rows across multiple tables?

answered Apr 9, 2018 at 5:20

d512

34.6k31 gold badges91 silver badges120 bronze badges

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

Comments

Turophile · Accepted Answer · 2018-04-09 05:23:34Z

1

You can use a CTE (WITH statement):

WITH points AS (
    SELECT * FROM ab_p1_point
    UNION
    SELECT * FROM ab_p2_point
    UNION
    SELECT * FROM ab_p3_point
    )
,  lines AS (
    SELECT * FROM bc_p1_line
    UNION
    SELECT * FROM bc_p2_line
    UNION
    SELECT * FROM bc_p3_line
    )
SELECT * 
FROM points
INNER JOIN lines 
ON points.column = lines.column

answered Apr 9, 2018 at 5:23

Turophile

3,4151 gold badge15 silver badges22 bronze badges

Collectives™ on Stack Overflow

Postgres | how to combine from two or more table into single table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related