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 - no screen shots please — user330315
– user330315, Commented Mar 15, 2023 at 14:56
I fixed the query in your link, but post it here so i can answer the question — rentox98
– rentox98, Commented Mar 15, 2023 at 15:50

rentox98 · Accepted Answer · 2023-03-15 16:13:11Z

1

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!

edited Mar 15, 2023 at 16:13

answered Mar 15, 2023 at 16:00

rentox98

5191 gold badge6 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

"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

Dmitry Demin · Accepted Answer · 2023-03-15 16:04:33Z

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

Collectives™ on Stack Overflow

Syntax error when using WITH...AS statement

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related