Question 89379 in PostgreSQL Question posted in
The official documentation can be found here.

Syntax error when using WITH…AS statement – Postgresql

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?

Tags:

2

Answers


  1. 0

    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!

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search