Geek Answers Handbook

How to insert one row in a parent table and then several rows in a child table in one SQL in PostgreSQL?

Please find below my diagram:

CREATE TABLE reps (
  id    SERIAL PRIMARY KEY,
  rep   TEXT NOT NULL UNIQUE
);

CREATE TABLE terms (
  id    SERIAL PRIMARY KEY,
  terms TEXT NOT NULL UNIQUE
);

CREATE TABLE shipVia (
  id        SERIAL PRIMARY KEY,
  ship_via  TEXT NOT NULL UNIQUE
);

CREATE TABLE invoices (
  id                    SERIAL PRIMARY KEY,
  customer              TEXT NOT NULL CONSTRAINT customerNotEmpty CHECK(customer <> ''),
  term_id               INT REFERENCES terms,
  rep_id                INT NOT NULL REFERENCES reps,
  ship_via_id           INT REFERENCES shipVia,
  ...
  item_count            INT NOT NULL,
  modified              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  version               INT NOT NULL DEFAULT 0
);

CREATE TABLE invoiceItems (
  id            SERIAL PRIMARY KEY,
  invoice_id    INT NOT NULL REFERENCES invoices ON DELETE CASCADE,
  name          TEXT NOT NULL CONSTRAINT nameNotEmpty CHECK(name <> ''),
  description   TEXT,
  qty           INT NOT NULL CONSTRAINT validQty CHECK (qty > 0),
  price         DOUBLE PRECISION NOT NULL
);

I am trying to insert an invoice along with its invoice elements in one SQL using a rewritable CTE. Currently, I am sticking with the following SQL statement:

WITH new_invoice AS (
    INSERT INTO invoices (id, customer, term_id, ship_via_id, rep_id, ..., item_count)
    SELECT $1, $2, t.id, s.id, r.id, ..., $26
    FROM reps r
    JOIN terms t ON t.terms = $3
    JOIN shipVia s ON s.ship_via = $4
    WHERE r.rep = $5
    RETURNING id
) INSERT INTO invoiceItems (invoice_id, name, qty, price, description) VALUES
 (new_invoice.id,$27,$28,$29,$30)
,(new_invoice.id,$31,$32,$33,$34)
,(new_invoice.id,$35,$36,$37,$38);

Of course, this SQL is incorrect, here is what PostgreSQL 9.2 says:

ERROR:  missing FROM-clause entry for table "new_invoice"
LINE 13:  (new_invoice.id,$27,$28,$29,$30)
           ^


********** Error **********

ERROR: missing FROM-clause entry for table "new_invoice"
SQL state: 42P01
Character: 704

Is it possible at all?

EDIT 1

I am trying the following version:

PREPARE insert_invoice_3 AS WITH 
new_invoice AS (
    INSERT INTO invoices (id, customer, term_id, ship_via_id, rep_id, ..., item_count) 
    SELECT $1, $2, t.id, s.id, r.id, ..., $26
    FROM reps r
    JOIN terms t ON t.terms = $3
    JOIN shipVia s ON s.ship_via = $4
    WHERE r.rep = $5
    RETURNING id
),
v (name, qty, price, description) AS (
    VALUES ($27,$28,$29,$30)
          ,($31,$32,$33,$34)
          ,($35,$36,$37,$38)
) 
 INSERT INTO invoiceItems (invoice_id, name, qty, price, description)
 SELECT new_invoice.id, v.name, v.qty, v.price, v.description
 FROM v, new_invoice;

And here is what I get in return:

ERROR:  column "qty" is of type integer but expression is of type text
LINE 19:  SELECT new_invoice.id, v.name, v.qty, v.price, v.descriptio...
                                         ^
HINT:  You will need to rewrite or cast the expression.

********** Error **********

ERROR: column "qty" is of type integer but expression is of type text
SQL state: 42804
Hint: You will need to rewrite or cast the expression.
Character: 899

I think v (name, qty, price, description)not enough, data types need to be specified. However, it v (name, qty INT, price, description)does not work - a syntax error.

EDIT 2

Next, I just tried the second version:

PREPARE insert_invoice_3 AS WITH 
new_invoice AS (
        INSERT INTO invoices (id, customer, term_id, ship_via_id, rep_id, ..., item_count) 
        SELECT $1, $2, t.id, s.id, r.id, ..., $26
        FROM reps r
        JOIN terms t ON t.terms = $3
        JOIN shipVia s ON s.ship_via = $4
        WHERE r.rep = $5
        RETURNING id
) 
 INSERT INTO invoiceItems (invoice_id, name, qty, price, description)
(
 SELECT i.id, $27, $28, $29, $30 FROM new_invoice i
 UNION ALL
 SELECT i.id, $31, $32, $33, $34 FROM new_invoice i
 UNION ALL
 SELECT i.id, $35, $36, $37, $38 FROM new_invoice i
);

Here is what I get:

ERROR:  column "qty" is of type integer but expression is of type text
LINE 15:  SELECT i.id, $27, $28, $29, $30 FROM new_invoice i
                            ^
HINT:  You will need to rewrite or cast the expression.

********** Error **********

ERROR: column "qty" is of type integer but expression is of type text
SQL state: 42804
Hint: You will need to rewrite or cast the expression.
Character: 759

Sounds like the same error. Interestingly, if I delete everything UNION ALLand leave only one statement SELECT, it works!

3

? CTE?

+4
2

PostgreSQL ​​ VALUES, .

, :

WITH new_invoice AS (
    INSERT INTO ...
    RETURNING id
),
v(a,b,c,d) AS (values
  ($27,$28,$29,$30),
  ($31,$32,$33,$34),
  ...
)
INSERT INTO invoiceItems (invoice_id, name, qty, price, description)
 SELECT new_invoice.id, a,b,c,d FROM v, new_invoice;

, new_invoice , , new_invoice - .

+5

insert... values ​​...., use insert... select...:

) INSERT INTO invoiceItems (invoice_id, name, qty, price, description)
SELECT new_invoice.id,$27,$28,$29,$30 FROM new_invoice
UNION ALL
...
+1

More articles:


All Articles