10

I'm trying to import data into an inventory type database. Here's what I have for the tables (truncated for brevity):

create type hostrole as enum ('Physical', 'Virtual', 'Hypervisor', 'Other');

create table host (
  id serial primary key,
  hostname varchar(40),
  role hostrole
);

create table interface (
  id serial primary key,
  name varchar(20),
  host int references host(id) on delete cascade,
  mac macaddr
);

I know I can nest a select into an insert statement, my question is whether I can nest an insert into an insert (and if so, how to do so). Here's the statement I'm trying to run:

insert into interface 
  (name, 
   mac, 
   host) 
     values 
  ('eth0', 
   '00:50:56:9d:34:d4', 
   (insert into host (hostname, hostrole) values ('foobar', 'Virtual') returning id)
);

The error comes up at the "into" of the nested insert. Can I do this in one shot, or do I have to make separate queries to do this insert?

Improve this question

2 Answers 2

Reset to default
13

You should be able to do something like this with a writable CTE:

WITH i AS (
   INSERT INTO host (hostname, hostrole) VALUES ('foobar', 'Virtual') RETURNING id
)
INSERT INTO interface (name, mac, host)
SELECT 'eth0', '00:50:56:9d:34:d4', id
FROM i

(untested, but it should be something like that)

Writable CTE is in PostgreSQL 9.1 and up.

Improve this answer
7
  • Drat. I wish I could use 9.1 or later. I'm kinda stuck with 8.4. Commented Aug 16, 2013 at 14:50
  • 1
    On 8.4 there is no way to do it, sorry. But if you're on 8.4, there are other reasons to start looking at an upgrade - postgresql.org/support/versioning - you've got less than a years "lifetime" left in that version. Commented Aug 16, 2013 at 14:52
  • Yeah, I'm already looking at upgrading the OS entirely. I'm stuck at 8.4 because I'm stuck at RHEL 5, trying to start moving to RHEL 7 in early 2014. Commented Aug 16, 2013 at 15:14
  • While I realize there can be policies, RHEL5 does i no way limit you to 8.4. See postgresql.org/download/linux/redhat in the section about yum. Commented Aug 16, 2013 at 17:06
  • 2
    No reason to use 9.1 also, as 9.2 is newer, better and stable... BTW, if you can't use repositories, install it by source code, it is really easy... Commented Aug 20, 2013 at 11:34
7

In 8.4 you could do this by wrapping the inner inserts in a function. Something like:

CREATE OR REPLACE FUNCTION insert_host(in_hostname text, in_hostrole text)
RETURNS INT LANGUAGE SQL AS
$$
   INSERT INTO host (hostname, hostrole) VALUES ($1, $2);
   SELECT id FROM host WHERE hostname = $1;
$$;

Then you can still do something like:

insert into interface 
(name, mac, host) 
 values 
('eth0', '00:50:56:9d:34:d4', insert_host('foobar', 'Virtual'));
Improve this answer

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.