Ошибка синтаксиса примерное положение with - TopOshibok.ru - решение и исправление самых разных ошибок

Syntax errors are quite common while coding.

But, things go for a toss when it results in website errors.

PostgreSQL error 42601 also occurs due to syntax errors in the database queries.

At Bobcares, we often get requests from PostgreSQL users to fix errors as part of our Server Management Services.

Today, let’s check PostgreSQL error in detail and see how our Support Engineers fix it for the customers.

What causes error 42601 in PostgreSQL?

PostgreSQL is an advanced database engine. It is popular for its extensive features and ability to handle complex database situations.

Applications like Instagram, Facebook, Apple, etc rely on the PostgreSQL database.

But what causes error 42601?

PostgreSQL error codes consist of five characters. The first two characters denote the class of errors. And the remaining three characters indicate a specific condition within that class.

Here, 42 in 42601 represent the class “Syntax Error or Access Rule Violation“.

In short, this error mainly occurs due to the syntax errors in the queries executed. A typical error shows up as:

Here, the syntax error has occurred in position 119 near the value “parents” in the query.

How we fix the error?

Now let’s see how our PostgreSQL engineers resolve this error efficiently.

Recently, one of our customers contacted us with this error. He tried to execute the following code,

CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
WITH m_ty_person AS (return query execute sql)
select name, count(*) from m_ty_person where name like '%a%' group by name
union
select name, count(*) from m_ty_person where gender = 1 group by name;
END
$$ LANGUAGE plpgsql;

But, this ended up in PostgreSQL error 42601. And he got the following error message,

ERROR: syntax error at or near "return"
LINE 5: WITH m_ty_person AS (return query execute sql)

Our PostgreSQL Engineers checked the issue and found out the syntax error. The statement in Line 5 was a mix of plain and dynamic SQL. In general, the PostgreSQL query should be either fully dynamic or plain. Therefore, we changed the code as,

RETURN QUERY EXECUTE '
WITH m_ty_person AS (' || sql || $x$)
SELECT name, count(*)::int FROM m_ty_person WHERE name LIKE '%a%' GROUP BY name
UNION
SELECT name, count(*)::int FROM m_ty_person WHERE gender = 1 GROUP BY name$x$;

This resolved the error 42601, and the code worked fine.

[Need more assistance to solve PostgreSQL error 42601?- We’ll help you.]

Conclusion

In short, PostgreSQL error 42601 occurs due to the syntax errors in the code. Today, in this write-up, we have discussed how our Support Engineers fixed this error for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник

Хочу апдейтить число работников d.number_employees исходя из агрегированной таблицы employees, но постгрес пишет ошибку
SQL Error [42601]: ОШИБКА: ошибка синтаксиса (примерное положение: ",")

Сам запрос вот:

UPDATE departaments AS d,
    (SELECT department, COUNT(*) AS numb
     FROM employees
     GROUP BY department) AS e
SET d.number_employees = e.numb
WHERE d.departament_name = e.department;

задан 8 ноя 2022 в 13:06

У меня получилось так. Дело оказалось не во вложенном запросе, а в алиасе, по какой-то причине posgresql не взлюбил его.

UPDATE departaments 
SET number_employees = empl.cnt
FROM (SELECT departament, COUNT(*) AS cnt FROM employees GROUP BY departament) AS empl
WHERE departament_name = empl.departament;

ответ дан 8 ноя 2022 в 16:36

Источник

Алексей

@falcon_digit

Начинающий в AI

Делаю задачи по курсу SQL на Stepik’е, параллельно их же делаю в PostgreSQL.
Такой запрос UPDATE с вложенным SELECT’ом в PostgreSQL выдаёт ошибку синтаксиса

UPDATE fine,
(SELECT  name, number_plate, violation
FROM fine
GROUP BY name, number_plate, violation
HAVING count(*) > 1
    ) query_in
SET sum_fine = sum_fine * 2 
WHERE fine.name = query_in.name AND fine.number_plate = query_in.number_plate AND fine.violation = query_in.violation AND fine.date_payment IS NULL;

ERROR: ОШИБКА: ошибка синтаксиса (примерное положение: «,»)
LINE 1: UPDATE fine,
На Stepik’е этот код принимается без ошибки.
Т.е. в PostgreSQL так нельзя делать? Или это как то лечится?
P.S. В SQLiteStudio тоже ошибка синтаксиса.

Вопрос задан

04 дек. 2022
241 просмотр

Пригласить эксперта

А чего спрашивать-то? Прочитайте документацию postgresql про update — есть там подобный синтаксис или нет? Уже понятно, что нет. Возможно, Вам подойдёт with?
А что касается Степиков и подобных, то в курсах по SQL обязательно должна быть указана версия/стандарт sql на которых курс основан, чтобы подобные казусы выявлять.

Это скорее всего неправильно.

SELECT  name, number_plate, violation
FROM fine
GROUP BY name, number_plate, violation
HAVING count(*) > 1

если используется GROUP BY то в выражении SELECT должна стоять функция агрегации (count в данном случае)

Показать ещё
Загружается…

22 сент. 2023, в 21:27

300 руб./за проект

22 сент. 2023, в 19:59

5000 руб./за проект

22 сент. 2023, в 18:59

30000 руб./за проект

Минуточку внимания

Источник

Hello I’m trying to figure out why switching my compatability mode from 80 to 100 in MSSQL broke my function below?

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)   Apr 22 2011 19:23:43
Copyright (c) Microsoft Corporation  Express Edition with Advanced Services (64-bit) on
Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Here is my function:

GO
ALTER FUNCTION [dbo].[GetRoot] 
(
    @Param1 int 
)
RETURNS varchar(50)
AS
BEGIN
DECLARE @ReturnValue varchar(50)
with results as
    (
        select parentouid,net_ouid from net_ou where net_ouid=@Param1
        union all
        select t2.parentouid,t2.net_ouid from net_ou t2 
        inner join results t1 on t1.parentouid = t2.net_ouid where t2.parentouid <> t1.net_ouid
    )   
    select @ReturnValue = net_ou.displayname 
    from  NET_OU RIGHT OUTER JOIN
    results ON net_ou.net_ouid = results.ParentouID where results.parentouid=results.net_ouid

    RETURN @ReturnValue

END

asked Aug 11, 2011 at 20:30

Try throwing a semi colon in front of the with:

;with results as
    (
        select parentouid,net_ouid from net_ou where net_ouid=@Param1
        union all
        select t2.parentouid,t2.net_ouid from net_ou t2 
        inner join results t1 on t1.parentouid = t2.net_ouid where t2.parentouid <> t1.net_ouid
    )

Give this article a read to understand why you need to do that. Snipit:

However, if the CTE is not the first statement in the batch, you must
precede the WITH keyword with a semicolon. As a best practice, I
prefer to prefix all of my CTEs with a semicolon—I find this
consistent approach easier than having to remember whether I need a
semicolon or not.

Personally, I don’t do it for every CTE, but if that makes things easier for you it won’t hurt anything.

answered Aug 11, 2011 at 20:31

Abe MiesslerAbe Miessler

82.6k99 gold badges305 silver badges487 bronze badges

Add a semicolon before WITH:

;with results as
    (
        select parentouid,net_ouid from net_ou where net_ouid=@Param1
        union all
        select t2.parentouid,t2.net_ouid from net_ou t2 
        inner join results t1 on t1.parentouid = t2.net_ouid where t2.parentouid <> t1.net_ouid
    )   
    select @ReturnValue = net_ou.displayname 
    from  NET_OU RIGHT OUTER JOIN
    results ON net_ou.net_ouid = results.ParentouID where results.parentouid=results.net_ouid

    RETURN @ReturnValue

END

CTE declarations need to be the first command in the batch.

answered Aug 11, 2011 at 20:32

JNKJNK

63.4k15 gold badges122 silver badges138 bronze badges

I would suggest that you adopt the practice of ending all statements with a semicolon. This is part of the ANSI standard and will help you when need to work on another database. SQL Server are moving towards this in any case. Many more commands require semicolons now in SQL Server 2012.

E.g.

ALTER FUNCTION [dbo].[GetRoot] 
    (@Param1 int)
RETURNS varchar(50)
AS
BEGIN
    DECLARE @ReturnValue VARCHAR(50)
    ;
    WITH cteResults 
    AS (SELECT parentouid
              ,net_ouid 
          FROM net_ou 
         WHERE net_ouid=@Param1
         UNION ALL
        SELECT t2.parentouid,t2.net_ouid 
          FROM net_ou t2 
         INNER JOIN results t1 
                 ON t1.parentouid = t2.net_ouid
         WHERE t2.parentouid <> t1.net_ouid )   
    SELECT @ReturnValue = net_ou.displayname 
      FROM net_ou 
     RIGHT JOIN cteResults 
             ON net_ou.net_ouid = results.ParentouID
     WHERE results.parentouid=results.net_ouid
    ;   
    RETURN @ReturnValue
    ;   
END
;
GO

As an added bonus it makes you queries a crap load easier to read.

answered Aug 29, 2012 at 15:02

Joe HarrisJoe Harris

13.7k4 gold badges47 silver badges54 bronze badges

Источник

Hello
I got an error when I tested CTE

Exception message:
Error
Exception: Npgsql.PostgresException
Message  : 42601: ошибка синтаксиса (примерное положение: "SELECT")
Stack trace:
   в Npgsql.NpgsqlConnector.<DoReadMessage>d__148.MoveNext()
--- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
   в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   в System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   в Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
--- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
   в Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
--- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
   в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   в System.Runtime.CompilerServices.ValueTaskAwaiteR`1.GETRESULT()
   В NPGSQL.NPGSQLCONNECTOR.<READEXPECTING>D__154`1.MOVENEXT()
--- КОНЕЦ ТРАССИРОВКА СТЕКА ИЗ ПРЕДЫДУЩЕГО РАСПОЛОЖЕНИЯ, ГДЕ возникло исключение ---
   в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   в System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   в Npgsql.NpgsqlDataReader.<NextResult>d__32.MoveNext()
--- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
   в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   в Npgsql.NpgsqlDataReader.NextResult()
   в Npgsql.NpgsqlCommand.<Execute>d__71.MoveNext()
--- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
   в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   в System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   в Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__92.MoveNext()
--- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
   в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   в System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   в Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   в System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   в LinqToDB.Data.DataConnection.ExecuteReader(CommandBehavior commandBehavior) в C:\projects\linq2db\Source\LinqToDB\Data\DataConnection.cs:строка 1236

Steps to reproduce

Having studied the question, I found out that the problem is in the missing space between the CTE name and the «as» operator
The error can be reproduced by using ToCTE (name) if the name is written in lower case. in this case, the name does not turn into quotes and merges with the operator following it

var typIds = db.Devices.Select(s => s.Devtypeid).AsCte("c1");
var typId2s = typIds.Distinct().AsCte("c2");                
var qCte = db.Devtypes.Where(w => w.Devtypeid.NotIn(typId2s)).ToList();

attention to the second CTE name (c2AS):

WITH c1 ("Devtypeid")
AS
(
        SELECT
                s.devtypeid
        FROM
                billing.devices s
),
c2AS
(
        SELECT DISTINCT
                t1."Devtypeid"
        FROM
                c1 t1
)
SELECT
        w.devtypeid,
        w.typename,
        w."GlobalType"
FROM
        billing.devtypes w
WHERE
        True = w.devtypeid NOT IN (
                SELECT
                        *
                FROM
                        c2 t2
        )

Environment details

linq2db version: 2.6.2.0
Database Server: E.g. PostgreSQL 9.6.9
Database Provider: E.g. Npgsql 3.2.5.0
Operating system: E.g. Windows 10
Framework version: .NET Framework 4.5.0

Источник