Postgresql sql error 42601 error syntax error at or near

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.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

PostgreSQL — SQL state: 42601 syntax error

I would like to know how to use a dynamic query inside a function. I’ve tried lots of ways, however, when I try to compile my function a message SQL 42601 is displayed.

The code that I use:

Error message I receive:

What is wrong? How can I solve this problem?

1 Answer 1

Your function would work like this:

You cannot mix plain and dynamic SQL the way you tried to do it. The whole statement is either all dynamic or all plain SQL. So I am building one dynamic statement to make this work. You may be interested in the chapter about executing dynamic commands in the manual.

The aggregate function count() returns bigint , but you had rowcount defined as integer , so you need an explicit cast ::int to make this work.

I use dollar quoting to avoid quoting hell.

However, is this supposed to be a honeypot for SQL injection attacks or are you seriously going to use it? For your very private and secure use, it might be ok-ish — though I wouldn’t even trust myself with a function like that. If there is any possible access for untrusted users, such a function is a loaded footgun. It’s impossible to make this secure.

Craig (a sworn enemy of SQL injection) might get a light stroke when he sees what you forged from his answer to your preceding question. 🙂

The query itself seems rather odd, btw. The two SELECT terms might be merged into one. But that’s beside the point here.

Источник

PostgreSQL error 42601- How we fix it

by Sijin George | Sep 12, 2019

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,

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

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,

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.

SELECT * FROM long_term_prediction_anomaly WHERE + “‘Timestamp’” + ‘”BETWEEN ‘” +
2019-12-05 09:10:00+ ‘”AND’” + 2019-12-06 09:10:00 + “‘;”)

Hello Joe,
Do you still get PostgreSQL errors? If you need help, we’ll be happy to talk to you on chat (click on the icon at right-bottom).

У меня ошибка drop table exists “companiya”;

CREATE TABLE “companiya” (
“compania_id” int4 NOT NULL,
“fio vladelca” text NOT NULL,
“name” text NOT NULL,
“id_operator” int4 NOT NULL,
“id_uslugi” int4 NOT NULL,
“id_reklama” int4 NOT NULL,
“id_tex-specialist” int4 NOT NULL,
“id_filial” int4 NOT NULL,
CONSTRAINT “_copy_8” PRIMARY KEY (“compania_id”)
);

CREATE TABLE “filial” (
“id_filial” int4 NOT NULL,
“street” text NOT NULL,
“house” int4 NOT NULL,
“city” text NOT NULL,
CONSTRAINT “_copy_5” PRIMARY KEY (“id_filial”)
);

CREATE TABLE “login” (
“id_name” int4 NOT NULL,
“name” char(20) NOT NULL,
“pass” char(20) NOT NULL,
PRIMARY KEY (“id_name”)
);

CREATE TABLE “operator” (
“id_operator” int4 NOT NULL,
“obrabotka obrasheniya” int4 NOT NULL,
“konsultirovanie” text NOT NULL,
“grafick work” date NOT NULL,
CONSTRAINT “_copy_2” PRIMARY KEY (“id_operator”)
);

CREATE TABLE “polsovateli” (
“id_user” int4 NOT NULL,
“id_companiya” int4 NOT NULL,
“id_obrasheniya” int4 NOT NULL,
“id_oshibka” int4 NOT NULL,
CONSTRAINT “_copy_6” PRIMARY KEY (“id_user”)
);

CREATE TABLE “reklama” (
“id_reklama” int4 NOT NULL,
“tele-marketing” text NOT NULL,
“soc-seti” text NOT NULL,
“mobile” int4 NOT NULL,
CONSTRAINT “_copy_3” PRIMARY KEY (“id_reklama”)
);

CREATE TABLE “tex-specialist” (
“id_tex-specialist” int4 NOT NULL,
“grafik” date NOT NULL,
“zarplata” int4 NOT NULL,
“ispravlenie oshibok” int4 NOT NULL,
CONSTRAINT “_copy_7” PRIMARY KEY (“id_tex-specialist”)
);

CREATE TABLE “uslugi” (
“id_uslugi” int4 NOT NULL,
“vostanavlenia parola” int4 NOT NULL,
“poterya acaunta” int4 NOT NULL,
CONSTRAINT “_copy_4” PRIMARY KEY (“id_uslugi”)
);

ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_operator_1” FOREIGN KEY (“id_operator”) REFERENCES “operator” (“id_operator”);
ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_uslugi_1” FOREIGN KEY (“id_uslugi”) REFERENCES “uslugi” (“id_uslugi”);
ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_filial_1” FOREIGN KEY (“id_filial”) REFERENCES “filial” (“id_filial”);
ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_reklama_1” FOREIGN KEY (“id_reklama”) REFERENCES “reklama” (“id_reklama”);
ALTER TABLE “companiya” ADD CONSTRAINT “fk_companiya_tex-specialist_1” FOREIGN KEY (“id_tex-specialist”) REFERENCES “tex-specialist” (“id_tex-specialist”);
ALTER TABLE “polsovateli” ADD CONSTRAINT “fk_polsovateli_companiya_1” FOREIGN KEY (“id_companiya”) REFERENCES “companiya” (“compania_id”);

ERROR: ОШИБКА: ошибка синтаксиса (примерное положение: “”companiya””)
LINE 1: drop table exists “companiya”;
^

Источник

SQL state: 42601 syntax error at or near «11»

I have a table address_all and it is inherited by several address tables. address_history inherits from parent table history_all and keeps current address information. I am creating new table which inherits address_all table and copies information from address_history to new table.

My stored procedure is like this below. I am having some error when I call it. To better explain error I am using line number.

I get this error:

3 Answers 3

Try this largely simplified form:

Major points:

You can assign variables in plpgsql at declaration time. Simplifies code.

Use to_char() to format your date. Much simpler.

now() and CURRENT_TIMESTAMP do the same.

Don’t quote ‘now()’ , use now() (without quotes) if you want the current timestamp.

Use the USING clause with EXECUTE , so you don’t have to convert the timestamp to text and back — possibly running into quoting issues like you did. Faster, simpler, safer.

In LANGUAGE plpgsql , plpgsql is a keyword and should not be quoted.

You may want to check if the table already exists with CREATE TABLE IF NOT EXISTS , available since PostgreSQL 9.1.

Apparently you need to quote backupdays, or it is not seen as a string from where to parse a timestamp.

You’re building SQL using string manipulation so you have to properly quote everything just like in any other language. There are a few functions that you’ll want to know about:

• quote_ident : quote an identifier such as a table name.
• quote_literal : quote a string to use as a string literal.
• quote_nullable : as quote_literal but properly handles NULLs as well.

Something like this will server you better:

The quote_ident calls aren’t necessary in your case but they’re a good habit.

Источник

Postgres: Error [42601] Error: Syntax error at or near «$2». Error while executing the Query

In the above query, In the where clause, (the first line: (messages. TIME > TIMESTAMP ? — ’30 day’::INTERVAL) AND ) when parameter is typecasted (i.e when I put TIMESTAMP before ?) I get the following error

Which is not true for the text data in the second line (messages. TIME ? ::TIMESTAMP — ’30 day’::INTERVAL) AND ) ( i.e I put the TIMESTAMP after ? ) Can anybody throw some light on what is happening? Thanks!!

Note: PostGresVersion — 9.6 OdBC driver — 32 bit driver (located at C:Program Files (x86)psqlODBC905binpsqlodbc30a.dll.) Same is true with Postgres 8.4 as well.

2 Answers 2

A constant of an arbitrary type can be entered using any one of the following notations:

The string constant’s text is passed to the input conversion routine for the type called type. The result is a constant of the indicated type.

The :: , CAST() , and function-call syntaxes can also be used to specify run-time type conversions of arbitrary expressions, as discussed in Section 4.2.9. To avoid syntactic ambiguity, the type ‘string‘ syntax can only be used to specify the type of a simple literal constant.

So you can use this syntax only with a string literal and not with a parameter as you are trying to do.

Источник

Состояние SQL: синтаксическая ошибка 42601 на уровне или около «11»

У меня есть таблица address_all и наследуется несколькими адресными таблицами. address_history наследуется от родительской таблицы history_all и сохраняет информацию о текущем адресе. Я создаю новую таблицу, которая наследует address_all таблицы и копирует информацию из address_history к новой таблице.

Моя хранимая процедура выглядит следующим образом. У меня какая-то ошибка при вызове. Чтобы лучше объяснить ошибку, я использую номер строки.

Я получаю эту ошибку:

3 ответы

Попробуйте эту в значительной степени упрощенную форму:

Основные моменты:

Вы можете назначать переменные в plpgsql во время объявления. Упрощает код.

Используйте to_char() для форматирования даты. Гораздо проще.

now() и CURRENT_TIMESTAMP сделать то же самое.

Не цитировать ‘now()’ , Используйте now() (без кавычек), если вам нужна текущая метка времени.

Использовать USING пункт с EXECUTE , так что вам не нужно конвертировать timestamp в text и обратно — возможно наткнувшись квотирование вопросы, как вы сделали. Быстрее, проще, безопаснее.

In LANGUAGE plpgsql , plpgsql является ключевым словом и не должен заключаться в кавычки.

Вы можете проверить, существует ли уже таблица с CREATE TABLE IF NOT EXISTS , доступный начиная с PostgreSQL 9.1.

Источник

@YohDeadfall — I understand that part about it, but this is not script that I am creating or even code that I am creating. This is all created under the hood by Npsql/EntityFramework. My quick guess is that I am extending my DbContext from IdentityDbContext<IdentityUser> which wants to create all of the tables for roles, users, claims, etc. If I change this to just extend from DbContext, then everything works as advertised.

Below is the script that EF is trying to use created from dotnet ef migrations script — please be aware that I have removed my custom part of the script for brevity.

You can see there are two specific calls that are being made where [NormalizedName] and [NormalizedUserName] are being used.

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" varchar(150) NOT NULL,
    "ProductVersion" varchar(32) NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);

CREATE TABLE "AspNetRoles" (
    "Id" text NOT NULL,
    "ConcurrencyStamp" text NULL,
    "Name" varchar(256) NULL,
    "NormalizedName" varchar(256) NULL,
    CONSTRAINT "PK_AspNetRoles" PRIMARY KEY ("Id")
);

CREATE TABLE "AspNetUsers" (
    "Id" text NOT NULL,
    "AccessFailedCount" int4 NOT NULL,
    "ConcurrencyStamp" text NULL,
    "Email" varchar(256) NULL,
    "EmailConfirmed" bool NOT NULL,
    "LockoutEnabled" bool NOT NULL,
    "LockoutEnd" timestamptz NULL,
    "NormalizedEmail" varchar(256) NULL,
    "NormalizedUserName" varchar(256) NULL,
    "PasswordHash" text NULL,
    "PhoneNumber" text NULL,
    "PhoneNumberConfirmed" bool NOT NULL,
    "SecurityStamp" text NULL,
    "TwoFactorEnabled" bool NOT NULL,
    "UserName" varchar(256) NULL,
    CONSTRAINT "PK_AspNetUsers" PRIMARY KEY ("Id")
);

CREATE TABLE "AspNetRoleClaims" (
    "Id" int4 NOT NULL,
    "ClaimType" text NULL,
    "ClaimValue" text NULL,
    "RoleId" text NOT NULL,
    CONSTRAINT "PK_AspNetRoleClaims" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_AspNetRoleClaims_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserClaims" (
    "Id" int4 NOT NULL,
    "ClaimType" text NULL,
    "ClaimValue" text NULL,
    "UserId" text NOT NULL,
    CONSTRAINT "PK_AspNetUserClaims" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_AspNetUserClaims_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserLogins" (
    "LoginProvider" text NOT NULL,
    "ProviderKey" text NOT NULL,
    "ProviderDisplayName" text NULL,
    "UserId" text NOT NULL,
    CONSTRAINT "PK_AspNetUserLogins" PRIMARY KEY ("LoginProvider", "ProviderKey"),
    CONSTRAINT "FK_AspNetUserLogins_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserRoles" (
    "UserId" text NOT NULL,
    "RoleId" text NOT NULL,
    CONSTRAINT "PK_AspNetUserRoles" PRIMARY KEY ("UserId", "RoleId"),
    CONSTRAINT "FK_AspNetUserRoles_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_AspNetUserRoles_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE TABLE "AspNetUserTokens" (
    "UserId" text NOT NULL,
    "LoginProvider" text NOT NULL,
    "Name" text NOT NULL,
    "Value" text NULL,
    CONSTRAINT "PK_AspNetUserTokens" PRIMARY KEY ("UserId", "LoginProvider", "Name"),
    CONSTRAINT "FK_AspNetUserTokens_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
);

CREATE INDEX "IX_AspNetRoleClaims_RoleId" ON "AspNetRoleClaims" ("RoleId");

CREATE UNIQUE INDEX "RoleNameIndex" ON "AspNetRoles" ("NormalizedName") WHERE [NormalizedName] IS NOT NULL;

CREATE INDEX "IX_AspNetUserClaims_UserId" ON "AspNetUserClaims" ("UserId");

CREATE INDEX "IX_AspNetUserLogins_UserId" ON "AspNetUserLogins" ("UserId");

CREATE INDEX "IX_AspNetUserRoles_RoleId" ON "AspNetUserRoles" ("RoleId");

CREATE INDEX "EmailIndex" ON "AspNetUsers" ("NormalizedEmail");

CREATE UNIQUE INDEX "UserNameIndex" ON "AspNetUsers" ("NormalizedUserName") WHERE [NormalizedUserName] IS NOT NULL;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20180514204732_initial', '2.0.3-rtm-10026');

1

29.03.2017, 23:23. Показов 11487. Ответов 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE OR REPLACE FUNCTION public."sp_CreateQuestionary"(
    IN _name CHARACTER VARYING,
    IN _family_name CHARACTER VARYING,
    IN _father_name CHARACTER VARYING,
    IN _num_phone BIGINT,
    OUT _pk_id_klient INTEGER)
  RETURNS INTEGER AS
$BODY$
BEGIN 
INSERT INTO QuestionaryTABL (Name, Family_name, Father_name, Num_phone) VALUES (_name, _family_name, _father_name, _num_phone) returning Pk_ID_Klient INTO _pk_ID_Klient; 
END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public."sp_CreateQuestionary"(CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, BIGINT)
  OWNER TO postgres;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

using System;
using Npgsql;
using System.Data.Common;
using System.Windows.Forms;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
 
namespace WindowsFormsApplication7
{
    public partial class Form1 : Form
    {
        private DataSet ds = new DataSet();
        private DataTable dt = new DataTable();
 
        NpgsqlConnection con = new NpgsqlConnection("Server=localhost;Port=5432;User=postgres;Password=Leon7;Database=Questionary;");
            string sql = ("SELECT "Pk_ID_Klient", "Name" , "Family_name", "Father_name", "Num_phone"  FROM "QuestionaryTABL"");
        NpgsqlDataAdapter adapter ;
        NpgsqlCommandBuilder commandBuilder;
 
        public Form1()
        {
            InitializeComponent();
            dataGridView1.AllowUserToAddRows = false;
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            con.Open();
            NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(sql, con);
            con.Close();
            ds.Reset();
            adapter.Fill(ds);
            dt = ds.Tables[0];
            dataGridView1.DataSource = dt;
    
            dataGridView1.Columns["Pk_ID_Klient"].ReadOnly = true;
        }
 
        private void button2_Click(object sender, EventArgs e)
        {
            
            // добавим новую строку
            DataRow newRow = dt.NewRow();
            newRow["Name"] = textBox1.Text.ToString();
            newRow["Family_name"] = textBox2.Text.ToString();
            newRow["Father_name"] = textBox3.Text.ToString();
            newRow["Num_phone"] = UInt64.Parse(textBox4.Text);
            dt.Rows.Add(newRow);
 
            con.Open();
            adapter = new NpgsqlDataAdapter(sql, con);
            commandBuilder = new NpgsqlCommandBuilder(adapter);
 
 
            // устанавливаем команду на вставку
            adapter.InsertCommand = new NpgsqlCommand("sp_CreateQuestionary(character varying, character varying, character varying, bigint)", con);
            // это будет зранимая процедура
            adapter.InsertCommand.CommandType = CommandType.StoredProcedure;
            // добавляем параметр для name
            adapter.InsertCommand.Parameters.Add(new NpgsqlParameter("_name", NpgsqlTypes.NpgsqlDbType.Char, 50, "Name"));
 
            adapter.InsertCommand.Parameters.Add(new NpgsqlParameter("_family_name", NpgsqlTypes.NpgsqlDbType.Char, 50, "Family_name"));
 
            adapter.InsertCommand.Parameters.Add(new NpgsqlParameter("_father_name", NpgsqlTypes.NpgsqlDbType.Char, 50, "Father_name"));
 
            adapter.InsertCommand.Parameters.Add(new NpgsqlParameter("_num_phone", NpgsqlTypes.NpgsqlDbType.Bigint, 0, "Num_phone"));
 
            // добавляем выходной параметр для id
            NpgsqlParameter parameter = adapter.InsertCommand.Parameters.Add("_pk_ID_Klient", NpgsqlTypes.NpgsqlDbType.Integer, 0, "Pk_ID_Klient");
            parameter.Direction = ParameterDirection.Output;
 
            adapter.Update(ds);
 
        }
 
 
    }
 
}

__________________
Помощь в написании контрольных, курсовых и дипломных работ, диссертаций здесь

0