Postgresql – Insert multiples rows/columns – PostgreSQL 9.2

insertpostgresql

I've got a table that has several columns, But I need to insert data into two columns of that table.
I need to add data into those columns, from a select statement.

Would it be something like:

INSERT INTO dm.billables_links (billable_id, mobiuser_id)
SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike '%Alisha%'
SELECT id FROM public.ja_mobiusers WHERE name_first LIKE 'Alisha%' AND name_last LIKE 'Dson%'

It has to be in the same transaction because the mobiuser_id must go to the selected billable_id on the first select.

How can I do that?

1 – Select billable_id from dm.billable

2 – Select mobiuser_id from
ja_mobiusers

3 – Insert the billable_id and the mobiuser_id to the
dm.billables_links table.

I've a CONSTRAINT on the target table: (That's why I need to get it done at the same time. So I won't get errors like:

ERROR: new row for relation "billables_links" violates check
constraint "cc_one_and_only_one_target"

ALTER TABLE dm.billables_links
  ADD CONSTRAINT cc_one_and_only_one_target CHECK ((("customer_id" IS NOT NULL)::integer + ("role_id" IS NOT NULL)::integer + ("mobiuser_id" IS NOT NULL)::integer) = 1);

This is related to my previous question – Which I've asked to be deleted because have done the wrong question and it has been confused.
INSERT INTO + Selects – PostgreSQL 9.2

UPDATE 1: This is an example of what I need

1 – select the billable_id: (SELECT1)

SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike '%Alisha%'

2 – select the mobiuser_id: (SELECT2)

SELECT id FROM public.ja_mobiusers WHERE name_first LIKE 'Alisha%' AND name_last LIKE 'Dadryl%'

3 – Insert those two data into the dm.billables_links table (EXAMPLE):

INSERT INTO dm.billables_links (billable_id, mobiuser_id) VALUES (**SELECT1**, **SELECT2**);

Best Answer

If it's guaranteed (like you commented on your previous question) that each SELECT returns exactly 1 row, the simplest way to combine both would be to just nest one SELECT within the other:

INSERT INTO dm.billables_links (billable_id, mobiuser_id)
SELECT billable_id
    , (SELECT id
       FROM   public.ja_mobiusers
       WHERE  name_first LIKE 'Alisha%'
       AND    name_last LIKE 'Dson%') AS foo  -- alias irrelevant
FROM   junk.wm_260_billables2
WHERE  info ILIKE '%Alisha%';

If there can be 0 - n results per SELECT, it gets more complex. You would have to define what the result should look like for each possible combination.

Note that you get no row (nothing inserted) if the outer SELECT finds nothing, but a NULL value for the second column if the nested SELECT finds nothing.

Or you could just use two subqueries:

INSERT INTO dm.billables_links (billable_id, mobiuser_id)
VALUES (
  (SELECT billable_id
   FROM   junk.wm_260_billables2
   WHERE  info ILIKE '%Alisha%')
, (SELECT id
   FROM   public.ja_mobiusers
   WHERE  name_first LIKE 'Alisha%'
   AND    name_last LIKE 'Dson%')
   );

Now you get NULL for each column where nothing is found, and an error if more than one row is found.

I seriously doubt that an ILIKE expression can guarantee a single result row.

Aside: your constraint would be faster and cleaner this way - though more verbose:

ALTER TABLE dm.billables_links ADD CONSTRAINT cc_one_and_only_one_target
CHECK (customer_id IS NOT NULL AND role_id IS     NULL AND mobiuser_id IS     NULL
    OR customer_id IS     NULL AND role_id IS NOT NULL AND mobiuser_id IS     NULL
    OR customer_id IS     NULL AND role_id IS     NULL AND mobiuser_id IS NOT NULL)
Related Question