Sql error 42601 error syntax error at or near select

PostgreSQL error 42601 mainly occurs due to the syntax errors in the code. Proper syntax check and code correction will fix it up.

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»;

Источник

Содержание

  1. PostgreSQL error 42601- How we fix it
  2. What causes error 42601 in PostgreSQL?
  3. How we fix the error?
  4. Conclusion
  5. PREVENT YOUR SERVER FROM CRASHING!
  6. 10 Comments
  7. [Error] 42601: syntax error at or near «SELECT» on pgsql 9.2.5 #813
  8. Comments
  9. PostgreSQL – SQL state: 42601 syntax error
  10. Solution
  11. Related Articles

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

Источник

[Error] 42601: syntax error at or near «SELECT» on pgsql 9.2.5 #813

When trying to remove or update an object from DB i get this exception on pgsql 9.2.5. On 9.4 it works fine.

Npgsql: npgsql-3.1-alpha0058
EF: 6.1.3
posgresql: postgresql92-9.2.5-1PGDG.rhel6.x86_64

[Error] 42601: syntax error at or near «SELECT» — at Npgsql.NpgsqlConnector.DoReadSingleMessage (DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage, Boolean isPrependedMessage) [0x00000] in :0
at Npgsql.NpgsqlConnector.ReadSingleMessageWithPrepended (DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage) [0x00000] in :0

2015-10-07 19:13:17.082 CEST [9318]: LOG: statement: DISCARD ALL
2015-10-07 19:13:17.083 CEST [9318]: ERROR: syntax error at or near «SELECT» at character 4857
2015-10-07 19:13:17.083 CEST [9318]: STATEMENT: SELECT «Project4».»Id1″ AS «Id», «Project4″.»Id» AS «Id1», «Project4″.»Name», «Project4″.»DatastoreRef», «Project4″.»DatastoreName», «Project4″.»TestNetworkName», «Project4″.»TestNetworkRef», «Project4″.»TestNetworkVLanId», «Project4″.»RecoveryNetworkName», «Project4″.»RecoveryNetworkRef», «Project4″.»RecoveryNetworkVLanId», «Project4″.»ResourcePoolName», «Project4″.»ResourcePoolRef», «Project4″.»ComputeResourceName», «Project4″.»ComputeResourceRef», «Project4″.»FolderName», «Project4″.»FolderRef», «Project4″.»PowerOnTimeoutMin», «Project4″.»DestinationPath», «Project4″.»Status», «Project4″.»MaxRecoveryPoint», «Project4″.»ServerId», «Project4″.»Enabled», «Project4″.»StartDateTime», «Project4″.»DaysOfWeek», «Project4″.»Interval», «Project4».»C1″, «Project4″.»IntervalUnit», «Project4″.»EndDateTime», «Project4″.»DateTimeFormat_DateFormat», «Project4″.»DateTimeFormat_TimeFormat», «Project4″.»DateTimeFormat_TimeZoneId», «Project4».»C35″ AS «C2», «Project4».»C3″, «Project4».»C4″, «Project4».»C5″, «Project4».»C6″, «Project4».»C7″, «Project4».»C8″, «Project4».»C9″, «Project4».»C10″, «Project4».»C11″, «Project4».»C12″, «Project4».»C13″, «Project4».»C14″, «Project4».»C2″ AS «C15», «Project4».»C15″ AS «C16», «Project4».»C16″ AS «C17», «Project4».»C17″ AS «C18», «Project4».»C18″ AS «C19», «Project4».»C19″ AS «C20», «Project4».»C20″ AS «C21», «Project4».»C21″ AS «C22», «Project4».»C22″ AS «C23», «Project4».»C23″ AS «C24», «Project4».»C24″ AS «C25», «Project4».»C25″ AS «C26», «Project4».»C26″ AS «C27», «Project4».»C27″ AS «C28», «Project4».»C28″ AS «C29», «Project4».»C29″ AS «C30», «Project4».»C30″ AS «C31», «Project4».»C31″ AS «C32», «Project4».»C32″ AS «C33», «Project4».»C33″ AS «C34», «Project4».»C34″ AS «C35» FROM (SELECT «Alias1″.»Id», «Alias1″.»Name», «Alias1″.»DatastoreRef», «Alias1″.»DatastoreName», «Alias1″.»TestNetworkName», «Alias1″.»TestNetworkRef», «Alias1″.»TestNetworkVLanId», «Alias1″.»RecoveryNetworkName», «Alias1″.»RecoveryNetworkRef», «Alias1″.»RecoveryNetworkVLanId», «Alias1″.»ResourcePoolName», «Alias1″.»ResourcePoolRef», «Alias1″.»ComputeResourceName», «Alias1″.»ComputeResourceRef», «Alias1″.»FolderName», «Alias1″.»FolderRef», «Alias1″.»PowerOnTimeoutMin», «Alias1″.»DestinationPath», «Alias1″.»Status», «Alias1″.»MaxRecoveryPoint», «Alias1″.»ServerId», «Alias1».»Id1″, «Alias1″.»Enabled», «Alias1″.»StartDateTime», «Alias1″.»DaysOfWeek», «Alias1″.»Interval», «Alias1″.»IntervalUnit», «Alias1″.»EndDateTime», «Alias1″.»DateTimeFormat_DateFormat», «Alias1″.»DateTimeFormat_TimeFormat», «Alias1″.»DateTimeFormat_TimeZoneId», «Alias1».»C1″, «UnionAll1».»C1″ AS «C2», «UnionAll1″.»Id» AS «C3», «UnionAll1».»Id1″ AS «C4», «UnionAll1″.»InstanceId» AS «C5», «UnionAll1″.»Name» AS «C6», «UnionAll1″.»Ref» AS «C7», «UnionAll1″.»IsPhysical» AS «C8», «UnionAll1″.»BackupId» AS «C9», «UnionAll1″.»JobId» AS «C10», «UnionAll1″.»OSUsername» AS «C11», «UnionAll1″.»OSUserPwd» AS «C12», «UnionAll1″.»OSType» AS «C13», «UnionAll1″.»SnapshotRef» AS «C14», «UnionAll1».»Id2″ AS «C15», «UnionAll1».»Id3″ AS «C16», «UnionAll1″.»MacAdresss» AS «C17», «UnionAll1″.»NicLabel» AS «C18», «UnionAll1″.»NetworkConfigId» AS «C19», «UnionAll1″.»VirtualMachine_Id» AS «C20», «UnionAll1».»C2″ AS «C21», «UnionAll1».»C3″ AS «C22», «UnionAll1».»C4″ AS «C23», «UnionAll1».»C5″ AS «C24», «UnionAll1».»C6″ AS «C25», «UnionAll1».»C7″ AS «C26», «UnionAll1».»C8″ AS «C27», «UnionAll1».»C9″ AS «C28», «UnionAll1».»C10″ AS «C29», «UnionAll1».»C11″ AS «C30», «UnionAll1».»C12″ AS «C31», «UnionAll1».»C13″ AS «C32», «UnionAll1».»C14″ AS «C33», «UnionAll1».»C15″ AS «C34», CASE WHEN («UnionAll1″.»Id» IS NULL) THEN (CAST (NULL AS int4)) ELSE (1) END AS «C35» FROM (SELECT «Extent1″.»Id», «Extent1″.»Name», «Extent1″.»DatastoreRef», «Extent1″.»DatastoreName», «Extent1″.»TestNetworkName», «Extent1″.»TestNetworkRef», «Extent1″.»TestNetworkVLanId», «Extent1″.»RecoveryNetworkName», «Extent1″.»RecoveryNetworkRef», «Extent1″.»RecoveryNetworkVLanId», «Extent1″.»ResourcePoolName», «Extent1″.»ResourcePoolRef», «Extent1″.»ComputeResourceName», «Extent1″.»ComputeResourceRef», «Extent1″.»FolderName», «Extent1″.»FolderRef», «Extent1″.»PowerOnTimeoutMin», «Extent1″.»DestinationPath», «Extent1″.»Status», «Extent1″.»MaxRecoveryPoint», «Extent1″.»ServerId», «Extent2″.»Id» AS «Id1», «Extent2″.»Enabled», «Extent2″.»StartDateTime», «Extent2″.»DaysOfWeek», «Extent2″.»Interval», «Extent2″.»IntervalUnit», «Extent2″.»EndDateTime», «Extent2″.»DateTimeFormat_DateFormat», «Extent2″.»DateTimeFormat_TimeFormat», «Extent2″.»DateTimeFormat_TimeZoneId», CASE WHEN («Extent2″.»Id» IS NULL) THEN (CAST (NULL AS int2)) ELSE (CAST («Extent2″.»IntervalValue» AS int2)) END AS «C1» FROM «public».»jobs» AS «Extent1» LEFT OUTER JOIN «public».»schedules» AS «Extent2» ON «Extent1″.»Id» = «Extent2″.»Id» WHERE «Extent1″.»Id» = $1 LIMIT 1) AS «Alias1» LEFT OUTER JOIN LATERAL (SELECT «UnionAll1».»C1″, «UnionAll1″.»Id», «UnionAll1».»Id1″, «UnionAll1″.»InstanceId», «UnionAll1″.»Name», «UnionAll1″.»Ref», «UnionAll1″.»IsPhysical», «UnionAll1″.»BackupId», «UnionAll1″.»JobId», «UnionAll1″.»OSUsername», «UnionAll1″.»OSUserPwd», «UnionAll1″.»OSType», «UnionAll1″.»SnapshotRef», «UnionAll1».»Id2″, «UnionAll1».»Id3″, «UnionAll1″.»MacAdresss», «UnionAll1″.»NicLabel», «UnionAll1″.»NetworkConfigId», «UnionAll1″.»VirtualMachine_Id», «UnionAll1».»C2″, «UnionAll1».»C3″, «UnionAll1».»C4″, «UnionAll1».»C5″, «UnionAll1».»C6″, «UnionAll1».»C7″, «UnionAll1».»C8″, «UnionAll1».»C9″, «UnionAll1».»C10″, «UnionAll1».»C11″, «UnionAll1».»C12″, «UnionAll1».»C13″, «UnionAll1».»C14″, «UnionAll1».»C15″ FROM ((SELECT CASE WHEN («Extent4″.»Id» IS NULL) THEN (CAST (NULL AS int4)) ELSE (1) END AS «C1», «Extent3″.»Id», «Extent3″.»Id» AS «Id1», «Extent3″.»InstanceId», «Extent3″.»Name», «Extent3″.»Ref», «Extent3″.»IsPhysical», «Extent3″.»BackupId», «Extent3″.»JobId», «Extent3″.»OSUsername», «Extent3″.»OSUserPwd», «Extent3″.»OSType», «Extent3″.»SnapshotRef», «Extent4″.»Id» AS «Id2», «Extent4″.»Id» AS «Id3», «Extent4″.»MacAdresss», «Extent4″.»NicLabel», «Extent4″.»NetworkConfigId», «Extent4″.»VirtualMachine_Id», CAST (NULL AS int4) AS «C2», CAST (NULL AS int4) AS «C3», CAST (NULL AS text) AS «C4», CAST (NULL AS text) AS «C5», CAST (NULL AS bool) AS «C6», CAST (NULL AS int4) AS «C7», CAST (NULL AS int4) AS «C8», CAST (NULL AS int4) AS «C9», CAST (NULL AS int4) AS «C10», CAST (NULL AS int4) AS «C11», CAST (NULL AS int4) AS «C12», CAST (NULL AS text) AS «C13», CAST (NULL AS text) AS «C14», CAST (NULL AS int4) AS «C15» FROM «public».»vms» AS «Extent3» LEFT OUTER JOIN «public».»vm_net» AS «Extent4» ON «Extent3″.»Id» = «Extent4″.»VirtualMachine_Id» WHERE «Alias1″.»Id» = «Extent3″.»JobId») UNION ALL (SELECT 2 AS «C1», «Extent5″.»Id», «Extent5″.»Id» AS «Id1», «Extent5″.»InstanceId», «Extent5″.»Name», «Extent5″.»Ref», «Extent5″.»IsPhysical», «Extent5″.»BackupId», «Extent5″.»JobId», «Extent5″.»OSUsername», «Extent5″.»OSUserPwd», «Extent5″.»OSType», «Extent5″.»SnapshotRef», CAST (NULL AS int4) AS «C2», CAST (NULL AS int4) AS «C3», CAST (NULL AS text) AS «C4», CAST (NULL AS text) AS «C5», CAST (NULL AS int4) AS «C6», CAST (NULL AS int4) AS «C7», «Extent6″.»Id» AS «Id2», «Extent6″.»Id» AS «Id3», «Extent6″.»Name» AS «Name1», «Extent6″.»Command», «Extent6″.»IsCustom», «Extent6″.»TimeoutMin», «Extent6″.»Priority», «Extent6″.»VirtualMachine_Id», CASE WHEN («Extent7″.»Id» IS NULL) THEN (CAST (NULL AS int4)) ELSE (1) END AS «C8», «Extent7″.»Id» AS «Id4», «Extent7″.»Id» AS «Id5», «Extent7″.»Name» AS «Name2», «Extent7″.»Value», «Extent7″.»VirtualMachineApplicationTest_Id» FROM «public».»vms» AS «Extent5» INNER JOIN «public».»vm_test» AS «Extent6» LEFT OUTER JOIN «public».»vm_testparam» AS «Extent7» ON «Extent6″.»Id» = «Extent7″.»VirtualMachineApplicationTest_Id» ON «Extent5″.»Id» = «Extent6″.»VirtualMachine_Id» WHERE «Alias1″.»Id» = «Extent5″.»JobId»)) AS «UnionAll1») AS «UnionAll1» ON TRUE) AS «Project4» ORDER BY «Project4″.»Id1″ ASC ,»Project4″.»Id» ASC ,»Project4″.»C35″ ASC ,»Project4″.»C4″ ASC ,»Project4″.»C2″ ASC ,»Project4″.»C22″ ASC ,»Project4″.»C29″ ASC
2015-10-07 19:13:18.221 CEST [9318]: LOG: statement: DISCARD ALL
2015-10-07 19:13:18.221 CEST [9318]: ERROR: syntax error at or near «SELECT» at character 4857

The text was updated successfully, but these errors were encountered:

Источник

PostgreSQL – SQL state: 42601 syntax error

Posted By: Anonymous

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?

Solution

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 piece of code in the answer to your preceding question. 🙂

The query itself seems rather odd, btw. But that’s beside the point here.

Answered By: Anonymous

Related Articles

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Источник

Источник

Содержание

  1. PostgreSQL error 42601- How we fix it
  2. What causes error 42601 in PostgreSQL?
  3. How we fix the error?
  4. Conclusion
  5. Related posts:
  6. PREVENT YOUR SERVER FROM CRASHING!
  7. 10 Comments
  8. Приложение A. Коды ошибок PostgreSQL

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 (нарушения ограничений целостности), но в будущем должны быть охвачены и другие классы.

Источник

Источник

When trying to remove or update an object from DB i get this exception on pgsql 9.2.5. On 9.4 it works fine.

Npgsql: npgsql-3.1-alpha0058
EF: 6.1.3
posgresql: postgresql92-9.2.5-1PGDG.rhel6.x86_64

Exception

[Error] 42601: syntax error at or near «SELECT» — at Npgsql.NpgsqlConnector.DoReadSingleMessage (DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage, Boolean isPrependedMessage) [0x00000] in :0
at Npgsql.NpgsqlConnector.ReadSingleMessageWithPrepended (DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage) [0x00000] in :0

log_statment=all

QUERY:

2015-10-07 19:13:17.082 CEST [9318]: LOG: statement: DISCARD ALL
2015-10-07 19:13:17.083 CEST [9318]: ERROR: syntax error at or near «SELECT» at character 4857
2015-10-07 19:13:17.083 CEST [9318]: STATEMENT: SELECT «Project4».»Id1″ AS «Id», «Project4″.»Id» AS «Id1», «Project4″.»Name», «Project4″.»DatastoreRef», «Project4″.»DatastoreName», «Project4″.»TestNetworkName», «Project4″.»TestNetworkRef», «Project4″.»TestNetworkVLanId», «Project4″.»RecoveryNetworkName», «Project4″.»RecoveryNetworkRef», «Project4″.»RecoveryNetworkVLanId», «Project4″.»ResourcePoolName», «Project4″.»ResourcePoolRef», «Project4″.»ComputeResourceName», «Project4″.»ComputeResourceRef», «Project4″.»FolderName», «Project4″.»FolderRef», «Project4″.»PowerOnTimeoutMin», «Project4″.»DestinationPath», «Project4″.»Status», «Project4″.»MaxRecoveryPoint», «Project4″.»ServerId», «Project4″.»Enabled», «Project4″.»StartDateTime», «Project4″.»DaysOfWeek», «Project4″.»Interval», «Project4».»C1″, «Project4″.»IntervalUnit», «Project4″.»EndDateTime», «Project4″.»DateTimeFormat_DateFormat», «Project4″.»DateTimeFormat_TimeFormat», «Project4″.»DateTimeFormat_TimeZoneId», «Project4».»C35″ AS «C2», «Project4».»C3″, «Project4».»C4″, «Project4».»C5″, «Project4».»C6″, «Project4».»C7″, «Project4».»C8″, «Project4».»C9″, «Project4».»C10″, «Project4».»C11″, «Project4».»C12″, «Project4».»C13″, «Project4».»C14″, «Project4».»C2″ AS «C15», «Project4».»C15″ AS «C16», «Project4».»C16″ AS «C17», «Project4».»C17″ AS «C18», «Project4».»C18″ AS «C19», «Project4».»C19″ AS «C20», «Project4».»C20″ AS «C21», «Project4».»C21″ AS «C22», «Project4».»C22″ AS «C23», «Project4».»C23″ AS «C24», «Project4».»C24″ AS «C25», «Project4».»C25″ AS «C26», «Project4».»C26″ AS «C27», «Project4».»C27″ AS «C28», «Project4».»C28″ AS «C29», «Project4».»C29″ AS «C30», «Project4».»C30″ AS «C31», «Project4».»C31″ AS «C32», «Project4».»C32″ AS «C33», «Project4».»C33″ AS «C34», «Project4».»C34″ AS «C35» FROM (SELECT «Alias1″.»Id», «Alias1″.»Name», «Alias1″.»DatastoreRef», «Alias1″.»DatastoreName», «Alias1″.»TestNetworkName», «Alias1″.»TestNetworkRef», «Alias1″.»TestNetworkVLanId», «Alias1″.»RecoveryNetworkName», «Alias1″.»RecoveryNetworkRef», «Alias1″.»RecoveryNetworkVLanId», «Alias1″.»ResourcePoolName», «Alias1″.»ResourcePoolRef», «Alias1″.»ComputeResourceName», «Alias1″.»ComputeResourceRef», «Alias1″.»FolderName», «Alias1″.»FolderRef», «Alias1″.»PowerOnTimeoutMin», «Alias1″.»DestinationPath», «Alias1″.»Status», «Alias1″.»MaxRecoveryPoint», «Alias1″.»ServerId», «Alias1».»Id1″, «Alias1″.»Enabled», «Alias1″.»StartDateTime», «Alias1″.»DaysOfWeek», «Alias1″.»Interval», «Alias1″.»IntervalUnit», «Alias1″.»EndDateTime», «Alias1″.»DateTimeFormat_DateFormat», «Alias1″.»DateTimeFormat_TimeFormat», «Alias1″.»DateTimeFormat_TimeZoneId», «Alias1».»C1″, «UnionAll1».»C1″ AS «C2», «UnionAll1″.»Id» AS «C3», «UnionAll1».»Id1″ AS «C4», «UnionAll1″.»InstanceId» AS «C5», «UnionAll1″.»Name» AS «C6», «UnionAll1″.»Ref» AS «C7», «UnionAll1″.»IsPhysical» AS «C8», «UnionAll1″.»BackupId» AS «C9», «UnionAll1″.»JobId» AS «C10», «UnionAll1″.»OSUsername» AS «C11», «UnionAll1″.»OSUserPwd» AS «C12», «UnionAll1″.»OSType» AS «C13», «UnionAll1″.»SnapshotRef» AS «C14», «UnionAll1».»Id2″ AS «C15», «UnionAll1».»Id3″ AS «C16», «UnionAll1″.»MacAdresss» AS «C17», «UnionAll1″.»NicLabel» AS «C18», «UnionAll1″.»NetworkConfigId» AS «C19», «UnionAll1″.»VirtualMachine_Id» AS «C20», «UnionAll1».»C2″ AS «C21», «UnionAll1».»C3″ AS «C22», «UnionAll1».»C4″ AS «C23», «UnionAll1».»C5″ AS «C24», «UnionAll1».»C6″ AS «C25», «UnionAll1».»C7″ AS «C26», «UnionAll1».»C8″ AS «C27», «UnionAll1».»C9″ AS «C28», «UnionAll1».»C10″ AS «C29», «UnionAll1».»C11″ AS «C30», «UnionAll1».»C12″ AS «C31», «UnionAll1».»C13″ AS «C32», «UnionAll1».»C14″ AS «C33», «UnionAll1».»C15″ AS «C34», CASE WHEN («UnionAll1″.»Id» IS NULL) THEN (CAST (NULL AS int4)) ELSE (1) END AS «C35» FROM (SELECT «Extent1″.»Id», «Extent1″.»Name», «Extent1″.»DatastoreRef», «Extent1″.»DatastoreName», «Extent1″.»TestNetworkName», «Extent1″.»TestNetworkRef», «Extent1″.»TestNetworkVLanId», «Extent1″.»RecoveryNetworkName», «Extent1″.»RecoveryNetworkRef», «Extent1″.»RecoveryNetworkVLanId», «Extent1″.»ResourcePoolName», «Extent1″.»ResourcePoolRef», «Extent1″.»ComputeResourceName», «Extent1″.»ComputeResourceRef», «Extent1″.»FolderName», «Extent1″.»FolderRef», «Extent1″.»PowerOnTimeoutMin», «Extent1″.»DestinationPath», «Extent1″.»Status», «Extent1″.»MaxRecoveryPoint», «Extent1″.»ServerId», «Extent2″.»Id» AS «Id1», «Extent2″.»Enabled», «Extent2″.»StartDateTime», «Extent2″.»DaysOfWeek», «Extent2″.»Interval», «Extent2″.»IntervalUnit», «Extent2″.»EndDateTime», «Extent2″.»DateTimeFormat_DateFormat», «Extent2″.»DateTimeFormat_TimeFormat», «Extent2″.»DateTimeFormat_TimeZoneId», CASE WHEN («Extent2″.»Id» IS NULL) THEN (CAST (NULL AS int2)) ELSE (CAST («Extent2″.»IntervalValue» AS int2)) END AS «C1» FROM «public».»jobs» AS «Extent1» LEFT OUTER JOIN «public».»schedules» AS «Extent2» ON «Extent1″.»Id» = «Extent2″.»Id» WHERE «Extent1″.»Id» = $1 LIMIT 1) AS «Alias1» LEFT OUTER JOIN LATERAL (SELECT «UnionAll1».»C1″, «UnionAll1″.»Id», «UnionAll1».»Id1″, «UnionAll1″.»InstanceId», «UnionAll1″.»Name», «UnionAll1″.»Ref», «UnionAll1″.»IsPhysical», «UnionAll1″.»BackupId», «UnionAll1″.»JobId», «UnionAll1″.»OSUsername», «UnionAll1″.»OSUserPwd», «UnionAll1″.»OSType», «UnionAll1″.»SnapshotRef», «UnionAll1».»Id2″, «UnionAll1».»Id3″, «UnionAll1″.»MacAdresss», «UnionAll1″.»NicLabel», «UnionAll1″.»NetworkConfigId», «UnionAll1″.»VirtualMachine_Id», «UnionAll1».»C2″, «UnionAll1».»C3″, «UnionAll1».»C4″, «UnionAll1».»C5″, «UnionAll1».»C6″, «UnionAll1».»C7″, «UnionAll1».»C8″, «UnionAll1».»C9″, «UnionAll1».»C10″, «UnionAll1».»C11″, «UnionAll1».»C12″, «UnionAll1».»C13″, «UnionAll1».»C14″, «UnionAll1».»C15″ FROM ((SELECT CASE WHEN («Extent4″.»Id» IS NULL) THEN (CAST (NULL AS int4)) ELSE (1) END AS «C1», «Extent3″.»Id», «Extent3″.»Id» AS «Id1», «Extent3″.»InstanceId», «Extent3″.»Name», «Extent3″.»Ref», «Extent3″.»IsPhysical», «Extent3″.»BackupId», «Extent3″.»JobId», «Extent3″.»OSUsername», «Extent3″.»OSUserPwd», «Extent3″.»OSType», «Extent3″.»SnapshotRef», «Extent4″.»Id» AS «Id2», «Extent4″.»Id» AS «Id3», «Extent4″.»MacAdresss», «Extent4″.»NicLabel», «Extent4″.»NetworkConfigId», «Extent4″.»VirtualMachine_Id», CAST (NULL AS int4) AS «C2», CAST (NULL AS int4) AS «C3», CAST (NULL AS text) AS «C4», CAST (NULL AS text) AS «C5», CAST (NULL AS bool) AS «C6», CAST (NULL AS int4) AS «C7», CAST (NULL AS int4) AS «C8», CAST (NULL AS int4) AS «C9», CAST (NULL AS int4) AS «C10», CAST (NULL AS int4) AS «C11», CAST (NULL AS int4) AS «C12», CAST (NULL AS text) AS «C13», CAST (NULL AS text) AS «C14», CAST (NULL AS int4) AS «C15» FROM «public».»vms» AS «Extent3» LEFT OUTER JOIN «public».»vm_net» AS «Extent4» ON «Extent3″.»Id» = «Extent4″.»VirtualMachine_Id» WHERE «Alias1″.»Id» = «Extent3″.»JobId») UNION ALL (SELECT 2 AS «C1», «Extent5″.»Id», «Extent5″.»Id» AS «Id1», «Extent5″.»InstanceId», «Extent5″.»Name», «Extent5″.»Ref», «Extent5″.»IsPhysical», «Extent5″.»BackupId», «Extent5″.»JobId», «Extent5″.»OSUsername», «Extent5″.»OSUserPwd», «Extent5″.»OSType», «Extent5″.»SnapshotRef», CAST (NULL AS int4) AS «C2», CAST (NULL AS int4) AS «C3», CAST (NULL AS text) AS «C4», CAST (NULL AS text) AS «C5», CAST (NULL AS int4) AS «C6», CAST (NULL AS int4) AS «C7», «Extent6″.»Id» AS «Id2», «Extent6″.»Id» AS «Id3», «Extent6″.»Name» AS «Name1», «Extent6″.»Command», «Extent6″.»IsCustom», «Extent6″.»TimeoutMin», «Extent6″.»Priority», «Extent6″.»VirtualMachine_Id», CASE WHEN («Extent7″.»Id» IS NULL) THEN (CAST (NULL AS int4)) ELSE (1) END AS «C8», «Extent7″.»Id» AS «Id4», «Extent7″.»Id» AS «Id5», «Extent7″.»Name» AS «Name2», «Extent7″.»Value», «Extent7″.»VirtualMachineApplicationTest_Id» FROM «public».»vms» AS «Extent5» INNER JOIN «public».»vm_test» AS «Extent6» LEFT OUTER JOIN «public».»vm_testparam» AS «Extent7» ON «Extent6″.»Id» = «Extent7″.»VirtualMachineApplicationTest_Id» ON «Extent5″.»Id» = «Extent6″.»VirtualMachine_Id» WHERE «Alias1″.»Id» = «Extent5″.»JobId»)) AS «UnionAll1») AS «UnionAll1» ON TRUE) AS «Project4» ORDER BY «Project4″.»Id1″ ASC ,»Project4″.»Id» ASC ,»Project4″.»C35″ ASC ,»Project4″.»C4″ ASC ,»Project4″.»C2″ ASC ,»Project4″.»C22″ ASC ,»Project4″.»C29″ ASC
2015-10-07 19:13:18.221 CEST [9318]: LOG: statement: DISCARD ALL
2015-10-07 19:13:18.221 CEST [9318]: ERROR: syntax error at or near «SELECT» at character 4857

Источник

Понравилась статья? Поделить с друзьями:
  • Sql error 42601 error syntax error at or near merge
  • Sql error 42601 error syntax error at or near end
  • Sql error 42601 error syntax error at end of input
  • Sql error 42601 error query has no destination for result data
  • Sql error 42601 error multiple decimal points