How can I run the SQL like "Insert into table_A where columnA = temp_table

I have two temp_tables defined as:

with temp_table_1 as (
  select id from table_A where fname="john" limit 1
),
with temp_table_2 as (
  select id from table_B where lname="smith" limit 1
)

These tables returns 1 row with just the id. I want to Insert a row into table_C where the column col_1 is equal to the result of temp_table_1 and the column col_2 is equal to the result of temp_table_2, and the third column (created_at) set to now:

The following fails for me:

insert into table_c (col_1, col_2, created_at) 
values (temp_table_1.id, temp_table_2.id, current_timestamp at time zone 'UTC');

CodePudding user response：

BEGIN;
CREATE temp TABLE table_a (
    id bigint,
    fname text
);
CREATE temp TABLE table_b (
    id bigint,
    lname text
);
CREATE temp TABLE table_c (
    col_1 bigint,
    col_2 bigint,
    created_at timestamp
);
INSERT INTO table_a
    VALUES (1, 'john');
INSERT INTO table_a
    VALUES (1, 'john');
INSERT INTO table_b
    VALUES (2, 'smith');
INSERT INTO table_b
    VALUES (2, 'smith');
COMMIT;

WITH temp_table_1 AS (
    SELECT
        a.id AS a_id,
        b.id AS b_id
    FROM
        table_a a,
        table_b b
    WHERE
        a.fname = 'john'
        AND b.lname = 'smith'
    LIMIT 1)
INSERT INTO table_c (col_1, col_2, created_at)
SELECT
    temp_table_1.a_id,
    temp_table_1.b_id,
    CURRENT_TIMESTAMP at time zone 'UTC'
FROM
    temp_table_1
RETURNING
    *;

CodePudding user response：

That should be fairly straightforward with a cross join:

WITH temp_table_1 AS (... LIMIT 1),
WITH temp_table_2 AS (... LIMIT 1)
INSERT INTO table_c (col_1, col_2, created_at)
SELECT temp_table_1.id,
       temp_table_2.id,
       current_timestamp AT TIME ZONE 'UTC'
FROM temp_table_1 CROSS JOIN temp_table_2;