How to insert into two tables in Postgres using CTE in one SQL query

Question

I would like to add data to two tables in Postgres with one query using CTE. After a user submit data from a form in the frontend for table2, I want my SQL query to insert id, value1 in table1. Then the same id in table one will be used to create data of table2. When I tried the hard-coded value below in pgAdmin, the code works as the id was generated for table1 and used to create table2 data.

WITH ins AS (
    INSERT INTO table1 
     (post_type, created_on) 
    VALUES 
     ('keyword', 'NOW()')
    RETURNING pid)
    INSERT INTO table2 
    (pid, author, title, description, body, category, search_volume, is_deleted, created_on)
    VALUES
    ((SELECT pid FROM ins), 'jet12', 'Head', 'Head is on top', 'Head is the most important part of the body', 'Head', '10000', 'false', 'NOW()')

However, since I'll be using a form to populate the data, hard-coding will surely not work. I tried the code below but I can't seem to get around it.

WITH ins AS (
    INSERT INTO table1 
     (post_type, created_on) 
    VALUES 
     ('keyword', 'NOW()')
    RETURNING pid)
    INSERT INTO table2 
    (pid, author, title, description, body, category, search_volume, is_deleted, created_on)
    VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)

Please how can I write the query to accept parameters as values? Is there another way to go about it? See my full function below:

const keywordHandler = (req, res) => {
  const values = [req.body.pid, req.body.username, req.body.title, req.body.description, req.body.body, req.body.category, req.body.search_volume, req.body.is_deleted, req.body.created_on]
pool.query(`WITH ins AS (
    INSERT INTO table1 
     (post_type, created_on) 
    VALUES 
     ('keyword', 'NOW()')
    RETURNING pid)
    INSERT INTO table2 
    (pid, author, title, description, body, category, search_volume, is_deleted, created_on)
    VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`, 
               values, (k_err, k_res) => {
                if (k_err) {
                    return console.error('Error executing query', k_err.stack)
                  }
           res.json({
                    status: 'Keyword submitted successfully',
                    data: k_res.rows
    });
     })
};

Answer 1

The following should work.

WITH ins AS (
  INSERT INTO table1
    (post_type, created_on) 
  VALUES
    ('keyword', now())
  RETURNING pid
)
INSERT INTO table2 
  (pid, author, title, description, body, category, search_volume, is_deleted, created_on)
SELECT
  ins.pid, $1, $2, $3, $4, $5, $6, $7, $8
FROM ins;

An alternative—which I would heartily recommend if it's an option for you—is to use UUIDs for ids. Then you wouldn't need a CTE to pass values between the two statements at all; just generate the UUID at the app level and include it in both of your insert statements. Since the likelihood of generating a duplicate UUID is somewhere around "winning the lottery jackpot every day for a year" slim to none during your lifetime, it should be considered a safe bet with additional benefits.

Answer 2

Don't pass pid- it's generated by the first CTE, that's the whole point.

You could use a prepared statement (most programming languages have libraries wrapping this functionality) or create a server-side function to take parameters.

Demonstrating a simple SQL function returning void (nothing):

CREATE OR REPLACE FUNCTION f_my_insert(_username text
                                     , _title text
                                     , _description text
                                     , _body text
                                     , _category text
                                     , _search_volume text
                                     , _is_deleted bool       -- type?
                                     , _created_on timestamp) -- type?
  RETURNS void AS  --  or what do you want to return?
$func$
   WITH ins AS (
      INSERT INTO table1 
             (post_type, created_on) 
      VALUES ('keyword', now())  --  'keyword' hard-coded, now() hard-coded, too?
      RETURNING pid
      )
   INSERT INTO table2 
         (pid, author, title, description, body, category, search_volume, is_deleted, created_on)
   SELECT pid, $1    , $2   , $3         , $4  , $5      , $6           , $7        , $8           -- use ordinal references
-- SELECT pid,_author,_title,_description,_body,_category,_search_volume,_is_deleted,_created_on)  -- ... or parameter names
   FROM   ins
$func$  LANGUAGE sql;

Adapt data types in the function declaration to your actual needs.

Call:

SELECT f_my_insert('username1', 'title2', 'description3', 'body4', 'category5', 'search_volume6', false, now());

There are various ways to call a Postgres function:

• Functions with variable number of input parameters

Using a SELECT for the 2nd INSERT instead of a VALUES expression only inserts if the first INSERT actually returned a row.

It's odd that you use a hard-coded 'NOW()' for the first created_on column and pass a parameter for second. Do the same for both instead?

'NOW()' is just a noisy, misleading way to spell the constant 'now', btw. I guess you want to actually use the function now() (transaction timestamp) instead, which can be subtly different in certain situations. See:

• Difference between now() and current_timestamp

Answer 3

If you are doing this because you are using JPA and designed your entities to inherit a shared base class, then my suggestion would be to rethink your entity definitions.

Consider using Embedded classes rather than inheritance - embedding will give you the shared definition, without having every entity class sharing the same table - each resulting entity will be in its own table.

Being able to work with your table definitions easily in SQL should always be a design consideration.