PostgreSQL Table merge

Question

I have two tables of data for example:

Network Status

ipaddress       time       violation
192.168.1.1     2:00       yes
192.168.1.6     2:00       no
192.168.1.11    2:00       no
192.168.1.1     3:00       no
192.168.1.6     3:00       no
192.168.1.11    3:00       yes

and

Firewall Log

ipaddress       machinetype    location
192.168.1.1     computer       London
192.168.1.6     server         New York
192.168.1.11    server         Bejing

and I wish to merge the data from Firewall Log into the Network Status table so I will have the following:

Network Status

ipaddress       time       violation       machinetype       location
192.168.1.1     2:00       yes             computer          London
192.168.1.6     2:00       no              server            New York
192.168.1.11    2:00       no              server            Bejing
192.168.1.1     3:00       no              computer          London
192.168.1.6     3:00       no              server            New York
192.168.1.11    3:00       yes             server            Bejing

Does anyone have any ideas? I assumed there would be a command to merge tables by a specific column value but after research I have only come across the join and select into commands. But I can't figure out how to use these to achieve what I want.

Thanks!

UNION will not work, different column count

OkieOth
– OkieOth

2012-11-21 14:49:51 +00:00
Commented Nov 21, 2012 at 14:49 — OkieOth
– OkieOth, Commented Nov 21, 2012 at 14:49

OkieOth · Accepted Answer · 2012-11-21 15:34:25Z

3

What do you think about a view? Combine the two tables with a select and create a view from it

CREATE VIEW myView AS SELECT log.*,status.* FROM log, status 
WHERE log.ipaddress=status.ipaddress 

/* OR */
CREATE VIEW myView AS SELECT log.*,status.* FROM log l
INNER JOIN status s ON l.ipaddress=s.ipaddress

edited Nov 21, 2012 at 15:34

answered Nov 21, 2012 at 14:49

OkieOth

3,7642 gold badges24 silver badges30 bronze badges

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

4 Comments

user330315 Over a year ago

Please use explicit JOIN syntax instead of the implicit joins in the where clause.

OkieOth Over a year ago

you are right ... my mistake, usually I only use the join clause with numeric columns but chars will work too.

Ihor Romanchenko Over a year ago

Also never use * or table_name.*. It can cause some nasty bugs and in this particular VIEW it will duplicate ipaddress column

James Elder Over a year ago

Thanks, I opted to use the add column, update method as the create view method takes up alot of memory (I am trying to merge the firewall log containing 2.7m pieces of data with the network status containing 168m pieces of data), and the update method doesn't require new memory to be alocated for each row...It was also easier for me to understand.

Ihor Romanchenko · Accepted Answer · 2012-11-21 16:10:55Z

1

To "merge" this tables you need:

1) Add "machinetype" and "location" columns to the "Network Status" table:

ALTER TABLE "Network Status"
  ADD COLUMN "machinetype" TEXT;
ALTER TABLE "Network Status"
  ADD COLUMN "location" TEXT;

2) Populate them with data

UPDATE "Network Status" NS
SET "machinetype" = FL."machinetype",
    "location" = FL."location"
FROM "Firewall Log" FL
WHERE FL."ipaddress" = NS."ipaddress"

Or you can create a VIEW and use it to SELECT data as if it is a table:

CREATE VIEW "Status and log" AS
SELECT NS."ipaddress", NS."time", NS."violation", FL."machinetype", FL."location"
FROM "Network Status" NS
JOIN "Firewall Log" FL ON FL."ipaddress" = NS."ipaddress"

edited Nov 21, 2012 at 16:10

answered Nov 21, 2012 at 15:44

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

1 Comment

James Elder Over a year ago

Thanks, works perfectly :) see my comment above for the reason as to which I chose to use the add column/update method.

Collectives™ on Stack Overflow

PostgreSQL Table merge

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related