2

I'm sure there is an easy answer to this, but my PostgreSQL isn't up to it at the moment.

I have a table hostname_facts with three columns:

hostname
name
value

hostname is a list of fully-qualified server hostnames.
name contains things like role (a TLA defining a server's role in the environment), env (part of an FQDN), memorysize, eth1_ip, fqdn, etc.
value - the actual value of role, env, memorysize, eth1_ip, fqdn, etc.

There are about 800 hostnames in this table.

From this table I've been asked to create a flat file that can be used as an inventory list for Ansible, which needs to be in the format:

[role.env]
hostname, value_of_role, value_of_env
hostname, value_of_role, value_of_env
hostname, value_of_role, value_of_env
...

So, I need need to pull out hostname sorted by value of name = 'role' as well as name = 'env'. This is stumping me!

I used:

select hostname,value from hostname_facts
where name = 'role'
union
select hostname,value from hostname_facts
where name = 'env'
order by value ASC

But that returns the 'name' values all in one long column, 'role' followed by 'env', with all hostnames repeated twice (obviously).

Can anyone suggest how to get just one set of hostnames with 'role' and 'env' as two columns alongside, rather than one underneath the other?

2 Answers 2

4

After your clarifying comment I suggest:

SELECT hostname, r.value AS role, e.value AS env 
FROM   hostname_facts r
JOIN   hostname_facts e USING (hostname)
WHERE  r.name = 'role'
AND    e.name = 'env'
ORDER  BY hostname;

It does the same as the query @zgguy already posted, just simpler.

2
  • Thanks Erwin, but zgguy's code gave me the output I needed. I needed one column of all hostnames, with another column showing the role of each host, and a third column displaying 'env' for each host.
    – HelenH
    Jan 11, 2016 at 18:37
  • @HelenH: I see, updated accordingly. Jan 11, 2016 at 19:30
2

You need to join the table to itself.

select a.hostname, a.value as role, b.value as env 
from 
  (select hostname, value from hostname_facts where name = 'role') a
  inner join 
  (select hostname, value from hostname_facts where name = 'env') b
on a.hostname = b.hostname
order by a.hostname
1
  • Fantastic, thank you SO much, that's done exactly what I needed!
    – HelenH
    Jan 11, 2016 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.