"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 · 2018-05-06T05:19:10.683

The error message indicates that you run the statement as plain SQL. But it only makes sense inside a PL/pgSQL block. You explicitly stated that it's for:

assigning to multiple variables

which only makes sense inside procedural language code as there are no variable assignments in plain SQL. SELECT INTO inside PL/pgSQL has a different meaning than SQL SELECT INTO - the use of which 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.

There's nothing wrong with your placement of the INTO clause - when used in PL/pgSQL like you tagged.

SELECT INTO with more than one attribution

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

2 Answers2