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 
   (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?

2 Answers 2


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

   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

(untested, but it should be something like that)

Writable CTE is in PostgreSQL 9.1 and up.

  • Drat. I wish I could use 9.1 or later. I'm kinda stuck with 8.4.
    – John
    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. 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.
    – John
    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. 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...
    – MatheusOl
    Aug 20, 2013 at 11:34

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)
   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) 
('eth0', '00:50:56:9d:34:d4', insert_host('foobar', 'Virtual'));

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.