Ошибка синтаксиса примерное положение into

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”;
^

Источник

@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');

Tim245, читайте документацию внимательнее. Для sqlite и psycopg разные формат передачи параметров.
Для pg по-простому просто вместо ? указывайте %s
т.е. в вашем случае

curr.execute("""INSERT INTO users_id (user_id, ) VALUES (%s);""", (user_id_v, ))

Если же использовать именованные переменные, то в данных надо передавать словарь с этими же ключами (это обычный синтаксис форматирования строки)

curr.execute("""INSERT INTO users_id (user_id, ) VALUES (%(str)s);""", {"str": user_id_v})

Вот пример из документации

QSqlQuery query;
query.prepare("INSERT INTO person (id, forename, surname) "
              "VALUES (:id, :forename, :surname)");
query.bindValue(":id", 1001);
query.bindValue(":forename", "Bart");
query.bindValue(":surname", "Simpson");
query.exec();


  Если вы пытаетесь сделать bind, то вам нужно правильно сформировать строку запроса, плюс указать bind привязки.
 

  В данном случае наличие кавычек уже не нужно будет.
 
bool DataBase::inserIntoTable(const QVariantList &data)
{

    QSqlQuery query;

   query.prepare("INSERT INTO public.NameTable (FirstName, SurName, Nik) VALUES (:db, 'cc', 'bb');");
   query.bindValue(":db",       data[0].toString());

    if(!query.exec()){
        qDebug() << "error insert into " << TABLE;
        qDebug() <<  query.lastQuery();
        qDebug() << query.lastError().text();
        return false;
    } else {
        return true;
    }
    return false;
}


  В статье, по которой вы это делаете были макросы, поэтому для конкатенации строк нужно было делать разделение кавычками. А вы очевидно формируете запрос сразу без макросов.