Postgresql syntax error at or near where

Python Error Handling with the Psycopg2 PostgreSQL Adapter 645

Introduction

This article will provide a brief overview of how you can better handle PostgreSQL Python exceptions while using the psycopg2 adapter in your code. Make sure that the psycopg2 package is installed on your machine using the PIP3 package manager for Python 3 using the following command:

We’ll also be building a function from scratch that prints detailed information about the psycopg2 exceptions by accessing several of its exception library attributes. It should be noted, however, that this is mostly for educational and debugging purposes, and it should be noted that, in the implementation phase your website or application, you may want to handle them less explicitly.

Catching and handling exceptions in Python

A Python script will terminate as soon as an exception or error is raised, but there is a try-except block (that works in a similar fashion to the try <> catch(err) <> code block in PHP or JavaScript) that will allow you to catch the exception, handle it, and then respond to it with more code within the except: part of the indentation block.

The following code allows you to catch all exceptions, as a wildcard, and print them out without having to explicitly mention the exact exception:

the except Exception as error: bit will allow you to handle any exception, and return the exception information as a TypeError class object using Python’s as keyword.

Exception libraries for the psycopg2 Python adapter

Some of the two most commonly occurring exceptions in the psycopg2 library are the OperationalError and ProgrammingError exception classes.

An OperationalError typically occurs when the parameters passed to the connect() method are incorrect, or if the server runs out of memory, or if a piece of datum cannot be found, etc.

A ProgrammingError happens when there is a syntax error in the SQL statement string passed to the psycopg2 execute() method, or if a SQL statement is executed to delete a non-existent table, or an attempt is made to create a table that already exists, and exceptions of that nature.

Complete list of the psycopg2 exception classes

Here’s the complete list of all of psycopg2 exception classes:

InterfaceError , DatabaseError , DataError , OperationalError , IntegrityError , InternalError , ProgrammingError , and NotSupportedError .

Brief overview of PostgreSQL Error Codes

There is an extensive list of over 200 error codes on the postgresql.org website that describes, in detail, each five-character SQL exception.

In the psycopg2 adapter library you can return the code by accessing the exception’s pgcode attribute. It should be an alpha-numeric string, five characters in length, that corresponds to an exception in the PostgreSQL Error Codes table.

Here’s some example code showing how one can access the attribute for the PostgreSQL error code:

Import the exception libraries for the psycopg2 Python adapter

You’ll need to import the following libraries at the beginning of your Python script:

# import sys to get more detailed Python exception info
import sys

# import the connect library for psycopg2
from psycopg2 import connect

# import the error handling libraries for psycopg2
from psycopg2 import OperationalError , errorcodes , errors

Get the psycopg2 version string

Older versions of the psycopg2 adapter may handle some exceptions differently. Here’s some code that imports the __version__ attribute string for the psycopg2 library and prints it:

# import the psycopg2 library’s __version__ string
from psycopg2 import __version__ as psycopg2_version

# print the version string for psycopg2
print ( «psycopg2 version:» , psycopg2_version , » n » )

Define a Python function to handle and print psycopg2 SQL exceptions

The code in this section will define a Python function that will take a Python TypeError object class and parse, both the psycopg2 and native Python, exception attributes from it in order to print the details of the exception:

Define the ‘print_psycopg2_exception()’ Python function

Use Python’s def keyword to define a new function and make it accept a TypeError Python object class as its only parameter:

Use Python’s built-in ‘sys’ library to get more detailed exception information

The next bit of code grabs the traceback information for the exception, including the line number in the code that the error occurred on, by calling the sys library’s exc_info() method:

# get details about the exception
err_type , err_obj , traceback = sys . exc_info ( )

# get the line number when exception occured
line_num = traceback . tb_lineno

Print the details for the psycopg2 exception

Use Python’s print() function to print the details of the psycopg2 exception that was passed to the function call:

# print the connect() error
print ( » n psycopg2 ERROR:» , err , «on line number:» , line_num )
print ( «psycopg2 traceback:» , traceback , «— type:» , err_type )

# psycopg2 extensions.Diagnostics object attribute
print ( » n extensions.Diagnostics:» , err. diag )

# print the pgcode and pgerror exceptions
print ( «pgerror:» , err. pgerror )
print ( «pgcode:» , err. pgcode , » n » )

Handle psycopg2 exceptions that occur while connecting to PostgreSQL

Now that the function has been defined it’s time to test it out by running some psycopg2 code. The following Python code attempts to make a connection to PostgreSQL in a try-except indentation block, and, in the case of an exception, passes the TypeError Python object to the print_psycopg2_exception() function defined earlier:

# declare a new PostgreSQL connection object
try :
conn = connect (
dbname = «python_test» ,
user = «WRONG_USER» ,
host = «localhost» ,
password = «mypass»
)
except OperationalError as err:
# pass exception to function
print_psycopg2_exception ( err )

# set the connection to ‘None’ in case of error
conn = None

NOTE: The above code will give the connection object a value of None in the case of an exception.

If the username string, passed to the user parameter, doesn’t match any of the users for the PostgreSQL server then the function should print something that closely resembles the following:

psycopg2 ERROR: FATAL: password authentication failed for user «WRONG_USER»
FATAL: password authentication failed for user «WRONG_USER»
on line number: 43
psycopg2 traceback: — type:

pgerror: None
pgcode: None

Handle psycopg2 exceptions that occur while executing SQL statements

If the code to connect to PostgreSQL didn’t have any problems, and no exceptions were raised, then test out the function again. The following code purposely attempts to use a cursor object to execute() a SQL statement with bad syntax:

# if the connection was successful
if conn != None :

# declare a cursor object from the connection
cursor = conn. cursor ( )
print ( «cursor object:» , cursor , » n » )

# catch exception for invalid SQL statement
try :
cursor. execute ( «INVALID SQL STATEMENT» )
except Exception as err:
# pass exception to function
print_psycopg2_exception ( err )

# rollback the previous transaction before starting another
conn. rollback ( )

The print_psycopg2_exception() function should print a response that resembles the following:

psycopg2 ERROR: syntax error at or near «INVALID»
LINE 1: INVALID SQL STATEMENT
^
on line number: 90
psycopg2 traceback: — type:

pgerror: ERROR: syntax error at or near «INVALID»
LINE 1: INVALID SQL STATEMENT
^

The 42601 PostgreSQL code indicates that the exception resulted from a syntax error in the SQL statement:

Catch ‘InFailedSqlTransaction’ psycopg2 exceptions

This last bit of Python code will raise a InFailedSqlTransaction exception if the last PostgreSQL transaction, with the bad SQL statement, wasn’t rolled back using the connection object’s rollback() method:

Conclusion

The psycopg2 library adapter for PostgreSQL has an extensive list of exception Python classes, and this article only covered a few of them just to give a general idea of how you can handle such exceptions in your own Python script.

Just the Code

# import sys to get more detailed Python exception info
import sys

# import the connect library for psycopg2
from psycopg2 import connect

# import the error handling libraries for psycopg2
from psycopg2 import OperationalError , errorcodes , errors

# import the psycopg2 library’s __version__ string
from psycopg2 import __version__ as psycopg2_version
«`python
#!/usr/bin/python3
# -*- coding: utf-8 -*-

# import sys to get more detailed Python exception info
import sys

# import the connect library for psycopg2
from psycopg2 import connectDoes NOT need TextBroker to re-write

# import the error handling libraries for psycopg2
from psycopg2 import OperationalError , errorcodes , errors

# import the psycopg2 library’s __version__ string
from psycopg2 import __version__ as psycopg2_version

# print the version string for psycopg2
print ( «psycopg2 version:» , psycopg2_version , » n » )

# define a function that handles and parses psycopg2 exceptions
def print_psycopg2_exception ( err ) :
# get details about the exception
err_type , err_obj , traceback = sys . exc_info ( )

# get the line number when exception occured
line_num = traceback . tb_lineno

# print the connect() error
print ( » n psycopg2 ERROR:» , err , «on line number:» , line_num )
print ( «psycopg2 traceback:» , traceback , «— type:» , err_type )

# psycopg2 extensions.Diagnostics object attribute
print ( » n extensions.Diagnostics:» , err. diag )

# print the pgcode and pgerror exceptions
print ( «pgerror:» , err. pgerror )
print ( «pgcode:» , err. pgcode , » n » )

try :
conn = connect (
dbname = «python_test» ,
user = «objectrocket» ,
host = «localhost» ,
password = «mypass»
)
except OperationalError as err:
# pass exception to function
print_psycopg2_exception ( err )

# set the connection to ‘None’ in case of error
conn = None

# if the connection was successful
if conn != None :

# declare a cursor object from the connection
cursor = conn. cursor ( )
print ( «cursor object:» , cursor , » n » )

# catch exception for invalid SQL statement
try :
cursor. execute ( «INVALID SQL STATEMENT» )
except Exception as err:
# pass exception to function
print_psycopg2_exception ( err )

# rollback the previous transaction before starting another
conn. rollback ( )

# execute a PostgreSQL command to get all rows in a table
# returns ‘psycopg2.errors.InFailedSqlTransaction’ if rollback() not called
try :
cursor. execute ( «SELECT * FROM some_table;» )
except errors. InFailedSqlTranroughsaction as err:
# pass exception to function
print_psycopg2_exception ( err )

# close the cursor object to avoid memory leaks
cursor. close ( )

# close the connection object also
conn. close ( )

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Источник

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

Источник

Приложение A. Коды ошибок PostgreSQL

Всем сообщениям, которые выдаёт сервер PostgreSQL , назначены пятисимвольные коды ошибок, соответствующие кодам «SQLSTATE» , описанным в стандарте SQL. Приложения, которые должны знать, какое условие ошибки имело место, обычно проверяют код ошибки и только потом обращаются к текстовому сообщению об ошибке. Коды ошибок, скорее всего, не изменятся от выпуска к выпуску PostgreSQL , и они не меняются при локализации как сообщения об ошибках. Заметьте, что отдельные, но не все коды ошибок, которые выдаёт PostgreSQL , определены стандартом SQL; некоторые дополнительные коды ошибок для условий, не описанных стандартом, были добавлены независимо или позаимствованы из других баз данных.

Согласно стандарту, первые два символа кода ошибки обозначают класс ошибок, а последние три символа обозначают определённое условие в этом классе. Таким образом, приложение, не знающее значение определённого кода ошибки, всё же может понять, что делать, по классу ошибки.

В Таблице A-1 перечислены все коды ошибок, определённые в PostgreSQL 9.4.1. (Некоторые коды в настоящее время не используются, хотя они определены в стандарте SQL.) Также показаны классы ошибок. Для каждого класса ошибок имеется «стандартный» код ошибки с последними тремя символами 000. Этот код выдаётся только для таких условий ошибок, которые относятся к определённому классу, но не имеют более определённого кода.

Символ, указанный в колонке «Имя условия» , определяет условие в PL/pgSQL . Имена условий могут записываться в верхнем или нижнем регистре. (Заметьте, что PL/pgSQL , в отличие от ошибок, не распознаёт предупреждения; то есть классы 00, 01 и 02.)

Для некоторых типов ошибок сервер сообщает имя объекта базы данных (таблица, колонка таблицы, тип данных или ограничение), связанного с ошибкой; например, имя уникального ограничения, вызвавшего ошибку unique_violation. Такие имена передаются в отдельных полях сообщения об ошибке, чтобы приложениям не пришлось извлекать его из возможно локализованного текста ошибки для человека. На момент выхода PostgreSQL 9.3 полностью охватывались только ошибки класса SQLSTATE 23 (нарушения ограничений целостности), но в будущем должны быть охвачены и другие классы.

Источник

error: syntax error at or near 
length: 95,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '122',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1180',
  routine: 'scanner_yyerror'

const createInitialTables = `
        CREATE TABLE authors (
             id UUID NOT NULL,
             author_name VARCHAR(100) NOT NULL UNIQUE CHECK (author_name <> ''),
             author_slug VARCHAR(100) NOT NULL UNIQUE CHECK (author_slug <> ''),
             PRIMARY KEY (id)
        );

        CREATE TABLE posts (
             id UUID NOT NULL,
             post VARCHAR(500) NOT NULL CHECK (post<> ''),
             post_slug VARCHAR(500) NOT NULL CHECK (post_slug <> ''),
             author_id UUID NOT NULL,
             PRIMARY KEY (id),
             CONSTRAINT fk_authors FOREIGN KEY(author_id) REFERENCES authors(id)
        );

        CREATE TABLE categories (
             id UUID NOT NULL,
             category_name VARCHAR(50) NOT NULL CHECK (category_name <> ''),
             category_slug VARCHAR(50) NOT NULL CHECK (category_slug <> ''),
             post_id UUID NOT NULL,
             PRIMARY KEY (id),
             CONSTRAINT fk_posts FOREIGN KEY(post_id) REFERENCES posts(id)
        );

`;

const insertAuthor = async() => {

    try {

        const data       = await fs.readFile( path.join( __dirname + '../../data/data.json' ) );
        const parsedData = JSON.parse( data.toString() );

        const authorID   = short.generate();
        const authorName = parsedData[ 0 ].author;
        const authorSlug = slugify( parsedData[ 0 ].author, {
            strict: true,
            lower: true
        } );

        const insertData = `
            INSERT INTO authors (id, author_name, author_slug) 
            VALUES 
            (${authorID}, ${authorName}, ${authorSlug});
        `;

        await pool.query( insertData );

        console.log( 'Data inserted successfully!' );

    } catch ( e ) {
        console.log( e );
    }
};

insertAuthor();

2021-10-18 01:23:16.885 +06 [5964] ERROR:  syntax error at or near "Paton" at character 122
2021-10-18 01:23:16.885 +06 [5964] STATEMENT:  
                INSERT INTO authors (id, author_name, author_slug) 
                VALUES 
                (an3cxZh8ZD3tdtqG4wuwPR, Alan Paton, alan-paton);

2 ответа

Лучший ответ

INSERT INTO authors (id, author_name, author_slug) 
VALUES 
(an3cxZh8ZD3tdtqG4wuwPR, Alan Paton, alan-paton);

INSERT INTO authors (id, author_name, author_slug) 
VALUES 
('an3cxZh8ZD3tdtqG4wuwPR', 'Alan Paton', 'alan-paton');

const insertSQL = `
  INSERT INTO authors (id, author_name, author_slug) 
  VALUES ($1, $2, $3);
`;
await pool.query( insertSQL, [authorID, authorName, authorSlug] );

create extension "uuid-ossp";

create table authors (
  id uuid primary key default uuid_generate_v4(),

  -- There's no point in arbitrarily limiting the size of your text fields.
  -- They will only use as much space as they need.
  author_name text not null unique check (author_name <> ''),
  author_slug text not null unique check (author_slug <> '')
);

insert into authors (author_name, author_slug) 
values ('Alan Paton', 'alan-paton');

const insertData = `
    INSERT INTO authors (id, author_name, author_slug) 
    VALUES 
    ('${authorID}', '${authorName}', '${authorSlug}');`;  // added the quotes