# LIMIT Error in Named Query Execution **URL:** \ **Category:** Ignition\ **Tags:** database, ignition81, perspective\ **Created:** [April 10, 2024, 7:10am UTC](https://forum.inductiveautomation.com/t/limit-error-in-named-query-execution/88623 "2024-04-10T07:10:53Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Hari\_Mhasalekar](https://avatars.discourse-cdn.com/v4/letter/h/90db22/32.png) [@Hari\_Mhasalekar](https://forum.inductiveautomation.com/u/Hari_Mhasalekar)\ **Post date:** [April 10, 2024, 7:10am UTC](https://forum.inductiveautomation.com/t/limit-error-in-named-query-execution/88623/1 "2024-04-10T07:10:53Z")
Hello Team, while executing a named query with postgre database. system showing below error msg: ![NamedQueryError](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/9/89d74d202ad296d043cd53083509eb35293ec581.png) ![QueryBroweser](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/c/ec0129de239f564f4a0e83b123bd32bd5dee8e94.png) **GatewayException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "LIMIT"** \*\* Position: 2\*\* \*\* caused by Exception: org.postgresql.util.PSQLException: ERROR: syntax error at or near "LIMIT"\*\* \*\* Position: 2\*\* But after executing same named query in query browser showing a correct result. i have attached the screen shots for better understanding. Please help me out if anyone knows why the name query showing Limit error. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [April 10, 2024, 8:06am UTC](https://forum.inductiveautomation.com/t/limit-error-in-named-query-execution/88623/2 "2024-04-10T08:06:01Z")
I suspect that the error is because you are running a second query (`SELECT 1`) in the named query. I don't think Ignition can handle that. ---
**Author:** ![Nikita\_Walake](https://avatars.discourse-cdn.com/v4/letter/n/71c47a/32.png) [@Nikita\_Walake](https://forum.inductiveautomation.com/u/Nikita_Walake)\ **Post date:** [April 10, 2024, 8:54am UTC](https://forum.inductiveautomation.com/t/limit-error-in-named-query-execution/88623/3 "2024-04-10T08:54:02Z")
Is there any solution for select statement? ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [April 10, 2024, 9:06am UTC](https://forum.inductiveautomation.com/t/limit-error-in-named-query-execution/88623/4 "2024-04-10T09:06:40Z")
[Note: I thought I was responding to the OP. I wasn't.] Delete it? Why do you think you need it? * * * You have the Query Type set to Scalor. I suspect that it should be set to UpdateQuery and let it return the number of rows affected. > - **UpdateQuery** : Allows all **UPDATE** types of queries (querys that mutate or otherwise modify rows on a table) and returns the number of rows affected by the query. This type should be selected when modifying the database in some way, such as when running an INSERT, UPDATE, or DELETE query. > **[Named Query Workspace | Ignition User Manual](https://docs.inductiveautomation.com/docs/8.1/platform/sql-in-ignition/named-queries/named-query-workspace#named-query-workspace-overview)** > > Named Query Workspace Overview ---
**Author:** ![Nikita\_Walake](https://avatars.discourse-cdn.com/v4/letter/n/71c47a/32.png) [@Nikita\_Walake](https://forum.inductiveautomation.com/u/Nikita_Walake)\ **Post date:** [April 10, 2024, 9:13am UTC](https://forum.inductiveautomation.com/t/limit-error-in-named-query-execution/88623/5 "2024-04-10T09:13:02Z")
We are migrating MSSQL named query to PostgreSQL. There are stored procedures with select statement we are not able to migrate it. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [April 10, 2024, 9:59am UTC](https://forum.inductiveautomation.com/t/limit-error-in-named-query-execution/88623/6 "2024-04-10T09:59:15Z")
This thread is about named queries - not stored procedures. You should start your own thread - and provide enough information that someone could possibly help you. ---
**Author:** ![Nikita\_Walake](https://avatars.discourse-cdn.com/v4/letter/n/71c47a/32.png) [@Nikita\_Walake](https://forum.inductiveautomation.com/u/Nikita_Walake)\ **Post date:** [April 15, 2024, 5:11am UTC](https://forum.inductiveautomation.com/t/limit-error-in-named-query-execution/88623/7 "2024-04-15T05:11:38Z")
DO $$ DECLARE isExist INT; BEGIN SELECT COUNT(\*) INTO isExist FROM "Scrap" WHERE "ScrapName" = :ScrapName AND "EquipmentId" = :EquipmentId; IF isExist \<\> 1 THEN INSERT INTO Scrap("ScrapName", "Description", "EquipmentId", "ScrapTime", "IsFrozen", "CreatedBy") VALUES (:ScrapName, :Description, :EquipmentId, :ScrapTime, :IsFrozen, :CreatedBy); RETURN 1; ELSE RETURN 0; END IF; END $$; This postgre query is not working in ignition named query. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [April 15, 2024, 7:07am UTC](https://forum.inductiveautomation.com/t/limit-error-in-named-query-execution/88623/8 "2024-04-15T07:07:50Z")
Make the "ScrapName" and "EquipmentId" unique, then you can do a simple insert and it just won't work if it breaks the unique constraint. And it will be more performant AND easier to understand/maintain. ---
**Author:** ![Nikita\_Walake](https://avatars.discourse-cdn.com/v4/letter/n/71c47a/32.png) [@Nikita\_Walake](https://forum.inductiveautomation.com/u/Nikita_Walake)\ **Post date:** [April 15, 2024, 7:14am UTC](https://forum.inductiveautomation.com/t/limit-error-in-named-query-execution/88623/9 "2024-04-15T07:14:43Z")
I can use simple insert function but I need to use declare to check whether the current inserting data is already present in the Table. So that I used declare function but this query is working well in PG admin but not working in Ignition 8.1 and showing the Limit error. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [April 15, 2024, 7:20am UTC](https://forum.inductiveautomation.com/t/limit-error-in-named-query-execution/88623/10 "2024-04-15T07:20:03Z")
But why do you want to check if it already exists ? If it's just so you don't insert it again, a unique constraint of those 2 columns will do that for you. It's built-in, and it's what it's for. If there are other reasons you need to know if it exists, explain them and we'll help you figure out a saner way to do it. Also: - Don't reuse threads for something unrelated. Make a new one, with an explicit title and appropriate tags. - Use the preformatted text tool to post code: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/6/36420ae057199ad7433be68b5e62d6bd4595799d.png) Or manually wrap your code in triple backticks: > ``` > code > ``` You can even add the language to add syntax highlighting: > ```sql > sql code > ``` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 15, 2024, 12:01pm UTC](https://forum.inductiveautomation.com/t/limit-error-in-named-query-execution/88623/11 "2024-04-15T12:01:18Z")
That isn't a query. That is a script. Vanilla JDBC doesn't support scripts, and they only work accidentally in Ignition (some JDBC drivers go beyond the specification). For your stated purpose, you should be using PostgreSQL's `ON CONFLICT...` support. Superior to your script, as it is race-free.