"Syntax error at or near ' , '" while trying to SELECT INTO

Question

The query for selecting multiple values and assigning to multiple variables in a single SELECT query leads to an error. My Postgres version is 9.5.
The query is:

 SELECT INTO region_id ,doc_type,tax_amt fk_bint_supplier_tax_region_id,chr_supporting_document_type,
dbl_base_currency_client_net-dbl_base_currency_market_fare-dbl_base_currency_cc_charge_collected+
dbl_base_currency_vat_in+dbl_base_currency_cc_charge_collected+(19*(dbl_base_currency_tax))*5/10   
FROM tbl_sales_details   WHERE chr_document_status='N' AND vchr_document_no='INV/47922/01/18'
AND vchr_supporting_document_no='5111143004'

The error is:

ERROR:  syntax error at or near ","
LINE 1:  SELECT INTO region_id ,doc_type,tax_amt fk_bint_supplier_ta...
                               ^

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

ERROR: syntax error at or near ","
SQL state: 42601

score 3 · Answer 1 · answered May 05 '18 at 03:07

You put the into after the column list:

SELECT region_id, doc_type,tax_amt fk_bint_supplier_tax_region_id, chr_supporting_document_type,
       (dbl_base_currency_client_net - dbl_base_currency_market_fare - 
        dbl_base_currency_cc_charge_collected + 
        dbl_base_currency_vat_in + dbl_base_currency_cc_charge_collected + 19 * dbl_base_currency_tax
       ) * 5/10   
INTO . . . 
FROM tbl_sales_details
WHERE chr_document_status = 'N' AND
      vchr_document_no = 'INV/47922/01/18' AND
      vchr_supporting_document_no = '5111143004';

I don't know what the variable names are, but the go after the INTO and there must be one for each expression in the SELECT.

The placement of `INTO` would be correct - in PL/pgSQL `SELECT INTO`. Seems like the OP and you both are confusing that with SQL `SELECT INTO`. Granted - a bit of a trap. — Erwin Brandstetter, May 06 '18 at 05:14

Erwin Brandstetter · Accepted Answer · 2023-02-14T23:18:35.807

SELECT INTO in PL/pgSQL has a different meaning from
SELECT INTO in SQL. The latter is generally discouraged. The manual:

CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO.

The error message indicates you tried to run the statement as plain SQL.

There's nothing wrong with your placement of the INTO clause when used in PL/pgSQL like you tagged. You also stated that it's for:

assigning to multiple variables

That, too, only makes sense inside procedural language code as there are no variable assignments in plain SQL.

SELECT INTO with more than one attribution

"Syntax error at or near ' , '" while trying to SELECT INTO

2 Answers2