Syntax error when using WITH...AS statement

Question

I'm inserting data into table and I want to get last id which will be later used for another query.

Here is table structure and query sql-fiddle

I'm receiving this error

ERROR: syntax error at end of input Position: 890

What could be wrong in insert statement?

Note that questions should be self-contained, so please [edit] your question and include the code that generates the error as [formatted text](https://meta.stackoverflow.com/a/251362) - [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) please — , Mar 15 '23 at 14:56
I fixed the query in your link, but post it here so i can answer the question — rentox98, Mar 15 '23 at 15:50

rentox98 · Accepted Answer · 2023-03-15T16:13:11.123

In all DBMS, when you declare a "view" using the with ... as () syntax, you are expected to use it immediately. If you try to execute only the with ... as () construct, you will get an error and PostgreSQL will wait for you to complete the query.

You have two options: either remove the "with" construct and obtain the result of your "returning" clause, or append a query to the "view" created through with ... as () to read its contents, as follows:

with app as (
 INSERT INTO public.tag 
 (class, name, value_type, history_type, persistent_history_limit_type,persistent_history_size, cache_size, unity, threshold_group, threshold, threshold_saving_option, threshold_time, uses_linear_regression, linear_regression_cluster_period, linear_regression_number_of_clusters, enum_mapping, site_id, as_id, use_bit_info, tag_bit, writing_only, active, detail, scanner_id, top_output, top_input, bottom_input, bottom_output, primary_rated, transducer_rated, secondary_rated, raw_upper_range, invert, scaling_type, relative_offset, absolute_offset)
 VALUES
 ('TAG', 'PLCOFFLINEx', 2, 1, 0, 1, 1, '', 'System', 1.0, 2, 0, 0, 0, 0, '', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) RETURNING oid as lastid
)
 select * from app

From Doc:

... and the WITH clause itself is attached to a primary statement that can also be a SELECT, INSERT, UPDATE, or DELETE

I hope this helps!

"*you are expected to use it immediately.*" - that's not only the case in Postgres. That's the way common table expressions work. In all DBMS — , Mar 15 '23 at 16:11

score 1 · Answer 2 · answered Mar 15 '23 at 16:04

sql-fiddle

with lastinsert AS (
INSERT INTO public.tag ("class", "name", value_type, history_type, persistent_history_limit_type, persistent_history_size, cache_size, unity, threshold_group, threshold, threshold_saving_option, threshold_time, uses_linear_regression, linear_regression_cluster_period, linear_regression_number_of_clusters, enum_mapping, site_id, as_id, use_bit_info, tag_bit, writing_only, active, detail, scanner_id, top_output, top_input, bottom_input, bottom_output, primary_rated, transducer_rated, secondary_rated, raw_upper_range, invert, scaling_type, relative_offset, absolute_offset)
VALUES('TAG', 'PLCOFFLINEx', 2, 1, 0, 1, 1, '', 'System', 1.0, 2, 0, 0, 0, 0, '', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL) 
 RETURNING oid)
 select oid as lastid from lastinsert

Syntax error when using WITH...AS statement

2 Answers2