I am very new to postgres. I got this error when try to run the following script:
CREATE OR REPLACE FUNCTION xyz(text) RETURNS INTEGER AS
'DECLARE result int;
BEGIN
SELECT count(*) into result from tbldealercommissions
WHERE
txtdealercode = $1;
if result < 1 then returns 1;
else returns 2 ;
end if;
END;
'
LANGUAGE sql VOLATILE;
The error is
ERROR: syntax error at or near "int"
LINE 3: 'DECLARE result int;
not sure what cause this error. Any help is appreciated.
asked Oct 6, 2011 at 12:07
This is unsuitable:
LANGUAGE sql
use this instead:
LANGUAGE plpgsql
The syntax you are trying to use is not pure SQL language but the procedural PL/pgSQL language. In PostgreSQL you can install different languages and PL/pgSQL is only primus inter pares in that regard. This also means that you might get the error message, that this language is not installed. In that case use
CREATE LANGUAGE plpgsql;
which actives it. Depending on the version of PostgreSQL you might need superuser rights to do this step.
Have fun.
answered Oct 6, 2011 at 12:19
A.H.A.H.
63.6k14 gold badges91 silver badges125 bronze badges
5
Not only are you using the wrong language (as noted by A.H.) but there is returns keyword, you want return. You might want to use a different delimiter to avoid running into problems with string literals in your functions, $$ is pretty common. I think your function should look more like this:
CREATE OR REPLACE FUNCTION xyz(text) RETURNS INTEGER AS $$
DECLARE result int;
BEGIN
select count(*) into result
from tbldealercommissions
where txtdealercode = $1;
if result < 1 then return 1;
else return 2;
end if;
END;
$$ LANGUAGE plpgsql VOLATILE;
answered Oct 6, 2011 at 17:37
mu is too shortmu is too short
425k70 gold badges829 silver badges795 bronze badges
I am very new to postgres. I got this error when try to run the following script:
CREATE OR REPLACE FUNCTION xyz(text) RETURNS INTEGER AS
'DECLARE result int;
BEGIN
SELECT count(*) into result from tbldealercommissions
WHERE
txtdealercode = $1;
if result < 1 then returns 1;
else returns 2 ;
end if;
END;
'
LANGUAGE sql VOLATILE;
The error is
ERROR: syntax error at or near "int"
LINE 3: 'DECLARE result int;
not sure what cause this error. Any help is appreciated.
asked Oct 6, 2011 at 12:07
This is unsuitable:
LANGUAGE sql
use this instead:
LANGUAGE plpgsql
The syntax you are trying to use is not pure SQL language but the procedural PL/pgSQL language. In PostgreSQL you can install different languages and PL/pgSQL is only primus inter pares in that regard. This also means that you might get the error message, that this language is not installed. In that case use
CREATE LANGUAGE plpgsql;
which actives it. Depending on the version of PostgreSQL you might need superuser rights to do this step.
Have fun.
answered Oct 6, 2011 at 12:19
A.H.A.H.
62.8k14 gold badges91 silver badges122 bronze badges
5
Not only are you using the wrong language (as noted by A.H.) but there is returns keyword, you want return. You might want to use a different delimiter to avoid running into problems with string literals in your functions, $$ is pretty common. I think your function should look more like this:
CREATE OR REPLACE FUNCTION xyz(text) RETURNS INTEGER AS $$
DECLARE result int;
BEGIN
select count(*) into result
from tbldealercommissions
where txtdealercode = $1;
if result < 1 then return 1;
else return 2;
end if;
END;
$$ LANGUAGE plpgsql VOLATILE;
answered Oct 6, 2011 at 17:37
mu is too shortmu is too short
421k69 gold badges826 silver badges789 bronze badges
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»;
Здравствуйте! Довольно распространенный вопрос, находил решения, но все же компилятор выводит ошибку. Как-то неправильно задаю первичный ключ.
Использую Postgresql
| Java | ||
|
| SQL | ||
|
Ошибка:
org.hibernate.tool.schema.spi.CommandAcceptanceExc eption: Error executing DDL via JDBC Statement
Caused by: org.postgresql.util.PSQLException: ОШИБКА: ошибка синтаксиса (примерное положение: «auto_increment»)
Добавлено через 27 минут
SQL-запрос пишу в консоли postgresql. Таблица без записей.
Также в проекте имеется обычный конфигурационный файл для JPA. Думаю, что роли он здесь не играет
__________________
Помощь в написании контрольных, курсовых и дипломных работ, диссертаций здесь
Пытаюсь создать табличку, вот такую
CREATE TABLE screens_items ( screenitemid bigint NOT NULL, screenid bigint NOT NULL, resourcetype integer DEFAULT '0' NOT NULL, resourceid bigint DEFAULT '0' NOT NULL, width integer DEFAULT '320' NOT NULL, height integer DEFAULT '200' NOT NULL, x integer DEFAULT '0' NOT NULL, y integer DEFAULT '0' NOT NULL, colspan integer DEFAULT '0' NOT NULL, rowspan integer DEFAULT '0' NOT NULL, elements integer DEFAULT '25' NOT NULL, valign integer DEFAULT '0' NOT NULL, halign integer DEFAULT '0' NOT NULL, style integer DEFAULT '0' NOT NULL, url varchar(255) DEFAULT '' NOT NULL, dynamic integer DEFAULT '0' NOT NULL, sort_triggers integer DEFAULT '0' NOT NULL, application varchar(255) DEFAULT '' NOT NULL, PRIMARY KEY (screenitemid) );
Получаю
Error: ОШИБКА: ошибка синтаксиса (примерное положение: "application")
psql --version psql (PostgreSQL) 9.4.9
Вроде слово «application» не зарезервировано?
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»;
So, you’re creating a custom SQL query to perform a task in the database. After putting the code together and running it in PHPmyAdmin it responds with a 1064 error. It may look similar to this:
The 1064 error displays any time you have an issue with your SQL syntax, and is often due to using reserved words, missing data in the database, or mistyped/obsolete commands. So follow along and learn more about what the 1064 error is, some likely causes, and general troubleshooting steps.
Note: Since syntax errors can be hard to locate in long queries, the following online tools can often save time by checking your code and locating issues:
- PiliApp MySQL Syntax Check
- EverSQL SQL Query Syntax Check & Validator
Causes for the 1064 error
- Reserved Words
- Missing Data
- Mistyped Commands
- Obsolete Commands
This may seem cryptic since it is a general error pointing to a syntax issue in the SQL Query statement. Since the 1064 error can have multiple causes, we will go over the most common things that will result in this error and show you how to fix them. Follow along so you can get your SQL queries updated and running successfully.
Using Reserved Words
Every version of MySQL has its own list of reserved words. These are words that are used for specific purposes or to perform specific functions within the MySQL engine. If you attempt to use one of these reserved words, you will receive the 1064 error. For example, below is a short SQL query that uses a reserved word as a table name.
CREATE TABLE alter (first_day DATE, last_day DATE);
How to fix it:
Just because the word alter is reserved does not mean it cannot be used, it just has special requirements to use it as the MySQL engine is trying to call the functionality for the alter command. To fix the issue, you will want to surround the word with backticks, this is usually the button just to the left of the “1” button on the keyboard. The code block below shows how the code will need to look in order to run properly.
CREATE TABLE `alter` (first_day DATE, last_day DATE);
Missing Data
Sometimes data can be missing from the database. This causes issues when the data is required for a query to complete. For example, if a database is built requiring an ID number for every student, it is reasonable to assume a query will be built to pull a student record by that ID number. Such a query would look like this:
SELECT * from students WHERE studentID = $id
If the $id is never properly filled in the code, the query would look like this to the server:
SELECT * from students WHERE studentID =
Since there is nothing there, the MySQL engine gets confused and complains via a 1064 error.
How to fix it:
Hopefully, your application will have some sort of interface that will allow you to bring up the particular record and add the missing data. This is tricky because if the missing data is the unique identifier, it will likely need that information to bring it up, thus resulting in the same error. You can also go into the database (typically within phpMyAdmin) where you can select the particular row from the appropriate table and manually add the data.
Mistyping of Commands
One of the most common causes for the 1064 error is when a SQL statement uses a mistyped command. This is very easy to do and is easily missed when troubleshooting at first. Our example shows an UPDATE command that is accidentally misspelled.
UDPATE table1 SET id = 0;
How to fix it:
Be sure to check your commands prior to running them and ensure they are all spelled correctly.
Below is the syntax for the correct query statement.
UPDATE table1 SET id = 0;
Obsolete Commands
Some commands that were deprecated (slated for removal but still allowed for a period of time) eventually go obsolete. This means that the command is no longer valid in the SQL statement. One of the more common commands is the ‘TYPE‘ command. This has been deprecated since MySQL 4.1 but was finally removed as of version 5.1, where it now gives a syntax error. The ‘TYPE‘ command has been replaced with the ‘ENGINE‘ command. Below is an example of the old version:
CREATE TABLE t (i INT) TYPE = INNODB;
This should be replaced with the new command as below:
CREATE TABLE t (i INT) ENGINE = INNODB;
For developers or sysadmins experienced with the command line, get High-Availability and Root Access for your application, service, and websites with Cloud VPS Hosting.
Error 1064 Summary
As you can see there is more than one cause for the 1064 error within MySQL code. Now, you know how to correct the issues with your SQL Syntax, so your query can run successfully. This list will be updated as more specific instances are reported.
Пытаюсь загрузить данные в таблицу Postgres. Скрипт и данные находятся на локальном сервере Open Server, а бд на другом, удаленном серваке, к которому есть полный доступ (соединяюсь с базой через менеджер бд и все редактирую). Подключение к базе при запуске скрипта успешно происходит, но вот запрос INSERT INTO не срабатывает:
$pg = "INSERT INTO public.1253_data () VALUES " . implode(",", $temp_array);
Ошибка выдается от функции pg_query, пишет: Warning: pg_query(): Query failed: Ошибка синтаксиса (примерное положение: .1253_data).
Я запускаю функцию pg_result_query():
$result = pg_query($conn, $sql);
if ($result) {
echo "New record created successfully";
} else {
$k = pg_get_result($result);
echo 'Query failed1: ' . pg_result_error($k);
}
pg_close($conn);
В результате pg_result_error дает пустоту:
Судя по документации, pg_result_дает такое только тогда, когда ошибок никаких нет. Но тогда почему собственный дескриптор ошибки pg_query такой?
Только начинаю pg изучать, все перепробовал в поисках ошибки ( Эксперты, подскажите, в чем она может быть?
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»;
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»;
So, you’re creating a custom SQL query to perform a task in the database. After putting the code together and running it in PHPmyAdmin it responds with a 1064 error. It may look similar to this:
The 1064 error displays any time you have an issue with your SQL syntax, and is often due to using reserved words, missing data in the database, or mistyped/obsolete commands. So follow along and learn more about what the 1064 error is, some likely causes, and general troubleshooting steps.
Note: Since syntax errors can be hard to locate in long queries, the following online tools can often save time by checking your code and locating issues:
- PiliApp MySQL Syntax Check
- EverSQL SQL Query Syntax Check & Validator
Causes for the 1064 error
- Reserved Words
- Missing Data
- Mistyped Commands
- Obsolete Commands
This may seem cryptic since it is a general error pointing to a syntax issue in the SQL Query statement. Since the 1064 error can have multiple causes, we will go over the most common things that will result in this error and show you how to fix them. Follow along so you can get your SQL queries updated and running successfully.
Using Reserved Words
Every version of MySQL has its own list of reserved words. These are words that are used for specific purposes or to perform specific functions within the MySQL engine. If you attempt to use one of these reserved words, you will receive the 1064 error. For example, below is a short SQL query that uses a reserved word as a table name.
CREATE TABLE alter (first_day DATE, last_day DATE);
How to fix it:
Just because the word alter is reserved does not mean it cannot be used, it just has special requirements to use it as the MySQL engine is trying to call the functionality for the alter command. To fix the issue, you will want to surround the word with backticks, this is usually the button just to the left of the “1” button on the keyboard. The code block below shows how the code will need to look in order to run properly.
CREATE TABLE `alter` (first_day DATE, last_day DATE);
Missing Data
Sometimes data can be missing from the database. This causes issues when the data is required for a query to complete. For example, if a database is built requiring an ID number for every student, it is reasonable to assume a query will be built to pull a student record by that ID number. Such a query would look like this:
SELECT * from students WHERE studentID = $id
If the $id is never properly filled in the code, the query would look like this to the server:
SELECT * from students WHERE studentID =
Since there is nothing there, the MySQL engine gets confused and complains via a 1064 error.
How to fix it:
Hopefully, your application will have some sort of interface that will allow you to bring up the particular record and add the missing data. This is tricky because if the missing data is the unique identifier, it will likely need that information to bring it up, thus resulting in the same error. You can also go into the database (typically within phpMyAdmin) where you can select the particular row from the appropriate table and manually add the data.
Mistyping of Commands
One of the most common causes for the 1064 error is when a SQL statement uses a mistyped command. This is very easy to do and is easily missed when troubleshooting at first. Our example shows an UPDATE command that is accidentally misspelled.
UDPATE table1 SET id = 0;
How to fix it:
Be sure to check your commands prior to running them and ensure they are all spelled correctly.
Below is the syntax for the correct query statement.
UPDATE table1 SET id = 0;
Obsolete Commands
Some commands that were deprecated (slated for removal but still allowed for a period of time) eventually go obsolete. This means that the command is no longer valid in the SQL statement. One of the more common commands is the ‘TYPE‘ command. This has been deprecated since MySQL 4.1 but was finally removed as of version 5.1, where it now gives a syntax error. The ‘TYPE‘ command has been replaced with the ‘ENGINE‘ command. Below is an example of the old version:
CREATE TABLE t (i INT) TYPE = INNODB;
This should be replaced with the new command as below:
CREATE TABLE t (i INT) ENGINE = INNODB;
For developers or sysadmins experienced with the command line, get High-Availability and Root Access for your application, service, and websites with Cloud VPS Hosting.
Error 1064 Summary
As you can see there is more than one cause for the 1064 error within MySQL code. Now, you know how to correct the issues with your SQL Syntax, so your query can run successfully. This list will be updated as more specific instances are reported.
восстановить базу из дампа:
--
-- PostgreSQL database dump
--
-- Dumped from database version 10.19 (Ubuntu 10.19-0ubuntu0.18.04.1)
-- Dumped by pg_dump version 10.19 (Ubuntu 10.19-0ubuntu0.18.04.1)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
--
-- Name: attribute_id_seq; Type: SEQUENCE; Schema: public; Owner: bender
--
CREATE SEQUENCE public.attribute_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.attribute_id_seq OWNER TO bender;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: attribute; Type: TABLE; Schema: public; Owner: bender
--
CREATE TABLE public.attribute (
attribute_id integer DEFAULT nextval('public.attribute_id_seq'::regclass) NOT NULL,
name character varying(30) NOT NULL,
attribute_type_id integer NOT NULL
);
ALTER TABLE public.attribute OWNER TO bender;
--
-- Name: attribute_type_id_seq; Type: SEQUENCE; Schema: public; Owner: bender
--
CREATE SEQUENCE public.attribute_type_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.attribute_type_id_seq OWNER TO bender;
--
-- Name: attribute_type; Type: TABLE; Schema: public; Owner: bender
--
CREATE TABLE public.attribute_type (
attribute_type_id integer DEFAULT nextval('public.attribute_type_id_seq'::regclass) NOT NULL,
name character varying(50) NOT NULL
);
ALTER TABLE public.attribute_type OWNER TO bender;
--
-- Name: film_id_seq; Type: SEQUENCE; Schema: public; Owner: bender
--
CREATE SEQUENCE public.film_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.film_id_seq OWNER TO bender;
--
-- Name: film; Type: TABLE; Schema: public; Owner: bender
--
CREATE TABLE public.film (
film_id integer DEFAULT nextval('public.film_id_seq'::regclass) NOT NULL,
name character varying(50) NOT NULL
);
ALTER TABLE public.film OWNER TO bender;
--
-- Name: film_attributes_id_seq; Type: SEQUENCE; Schema: public; Owner: bender
--
CREATE SEQUENCE public.film_attributes_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.film_attributes_id_seq OWNER TO bender;
--
-- Name: film_attributes; Type: TABLE; Schema: public; Owner: bender
--
CREATE TABLE public.film_attributes (
film_attributes_id integer DEFAULT nextval('public.film_attributes_id_seq'::regclass) NOT NULL,
attribute_id integer NOT NULL,
film_id integer NOT NULL,
value_text character varying,
value_integer integer,
value_float double precision,
value_boolean boolean,
value_timestamp timestamp with time zone
);
ALTER TABLE public.film_attributes OWNER TO bender;
--
-- Name: film_attributes_values; Type: VIEW; Schema: public; Owner: bender
--
CREATE VIEW public.film_attributes_values AS
SELECT
NULL::character varying(50) AS name,
NULL::character varying(50) AS attribute_type,
NULL::character varying(30) AS attribute_name,
NULL::character varying AS attribute_value;
ALTER TABLE public.film_attributes_values OWNER TO bender;
--
-- Name: film_tasks; Type: VIEW; Schema: public; Owner: bender
--
CREATE VIEW public.film_tasks AS
SELECT
NULL::character varying(50) AS name,
NULL::character varying[] AS today_tasks,
NULL::character varying[] AS twenty_days_tasks;
ALTER TABLE public.film_tasks OWNER TO bender;
--
-- Data for Name: attribute; Type: TABLE DATA; Schema: public; Owner: bender
--
COPY public.attribute (attribute_id, name, attribute_type_id) FROM stdin;
1 Рецензии 3
3 Премия Оскар 2
4 Премия Ника 2
5 Премия Золотой Глобус 2
10 Описание фильма 3
11 Длительность (мин.) 1
12 Длительность проката (дней) 1
2 Рейтинг 7
6 Премьера в мире 6
7 Премьера в России 6
8 Старт продажи билетов 6
9 Старт проката 6
13 Окончание проката 6
.
--
-- Data for Name: attribute_type; Type: TABLE DATA; Schema: public; Owner: bender
--
COPY public.attribute_type (attribute_type_id, name) FROM stdin;
1 integer
2 boolean
3 text
4 date
5 numeric
6 timestamp
7 float
.
--
-- Data for Name: film; Type: TABLE DATA; Schema: public; Owner: bender
--
COPY public.film (film_id, name) FROM stdin;
1 Spoiler-man: No Way
2 Matrix 4
.
--
-- Data for Name: film_attributes; Type: TABLE DATA; Schema: public; Owner: bender
--
COPY public.film_attributes (film_attributes_id, attribute_id, film_id, value_text, value_integer, value_float, value_boolean, value_timestamp) FROM stdin;
1 1 1 Годный фильм, распинаюсь про сюжет, пишу про игру актеров, все круто N N N N
2 1 2 Джон Уик уже не тот, сестры Вачовски сбрендили, полная фигня N N N N
5 3 1 f N N N N
7 6 2 N N N N 2021-12-10 00:00:00+03
9 7 2 N N N N 2021-12-30 00:00:00+03
10 8 1 N N N N 2021-12-10 00:00:00+03
11 8 2 N N N N 2021-12-07 00:00:00+03
12 12 1 N 21 N N N
13 12 2 N 14 N N N
14 9 1 N N N N 2021-12-15 00:00:00+03
15 9 2 N N N N 2021-12-15 00:00:00+03
16 13 1 N N N N 2022-01-04 00:00:00+03
17 13 2 N N N N 2022-01-04 00:00:00+03
18 3 2 t N N N N
6 6 1 N N N N 2021-12-15 00:00:00+03
8 7 1 N N N N 2022-01-04 00:00:00+03
.
--
-- Name: attribute_id_seq; Type: SEQUENCE SET; Schema: public; Owner: bender
--
SELECT pg_catalog.setval('public.attribute_id_seq', 13, true);
--
-- Name: attribute_type_id_seq; Type: SEQUENCE SET; Schema: public; Owner: bender
--
SELECT pg_catalog.setval('public.attribute_type_id_seq', 6, true);
--
-- Name: film_attributes_id_seq; Type: SEQUENCE SET; Schema: public; Owner: bender
--
SELECT pg_catalog.setval('public.film_attributes_id_seq', 18, true);
--
-- Name: film_id_seq; Type: SEQUENCE SET; Schema: public; Owner: bender
--
SELECT pg_catalog.setval('public.film_id_seq', 2, true);
--
-- Name: attribute attribute_pkey; Type: CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.attribute
ADD CONSTRAINT attribute_pkey PRIMARY KEY (attribute_id);
--
-- Name: attribute_type attribute_type_name_key; Type: CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.attribute_type
ADD CONSTRAINT attribute_type_name_key UNIQUE (name);
--
-- Name: attribute_type attribute_type_pkey; Type: CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.attribute_type
ADD CONSTRAINT attribute_type_pkey PRIMARY KEY (attribute_type_id);
--
-- Name: attribute attribute_unq; Type: CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.attribute
ADD CONSTRAINT attribute_unq UNIQUE (name);
--
-- Name: film_attributes film_attributes_pkey; Type: CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.film_attributes
ADD CONSTRAINT film_attributes_pkey PRIMARY KEY (film_attributes_id);
--
-- Name: film film_pkey; Type: CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.film
ADD CONSTRAINT film_pkey PRIMARY KEY (film_id);
--
-- Name: film film_unq; Type: CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.film
ADD CONSTRAINT film_unq UNIQUE (name);
--
-- Name: attribute_index; Type: INDEX; Schema: public; Owner: bender
--
CREATE INDEX attribute_index ON public.attribute USING btree (name COLLATE "C.UTF-8" varchar_ops);
--
-- Name: film_index; Type: INDEX; Schema: public; Owner: bender
--
CREATE INDEX film_index ON public.film USING btree (name COLLATE "C.UTF-8");
--
-- Name: attribute attribute_type_fkey; Type: FK CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.attribute
ADD CONSTRAINT attribute_type_fkey FOREIGN KEY (attribute_type_id) REFERENCES public.attribute_type(attribute_type_id) NOT VALID;
--
-- Name: film_attributes film_attribute_attribute_fkey; Type: FK CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.film_attributes
ADD CONSTRAINT film_attribute_attribute_fkey FOREIGN KEY (attribute_id) REFERENCES public.attribute(attribute_id);
--
-- Name: film_attributes film_attribute_film_fkey; Type: FK CONSTRAINT; Schema: public; Owner: bender
--
ALTER TABLE ONLY public.film_attributes
ADD CONSTRAINT film_attribute_film_fkey FOREIGN KEY (film_id) REFERENCES public.film(film_id);
--
-- PostgreSQL database dump complete
--ERROR: ОШИБКА: ошибка синтаксиса (примерное положение: "1")
LINE 180: 1 Рецензии 3
^
SQL state: 42601
Character: 4115Алексей
@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
-
195 просмотров
Пригласить эксперта
А чего спрашивать-то? Прочитайте документацию 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, в 00:59
8000 руб./за проект
22 июн. 2023, в 00:56
8000 руб./за проект
22 июн. 2023, в 00:39
12000 руб./за проект
Минуточку внимания
Содержание
- PostgreSQL error 42601- How we fix it
- What causes error 42601 in PostgreSQL?
- How we fix the error?
- Conclusion
- PREVENT YOUR SERVER FROM CRASHING!
- 10 Comments
- [Error] 42601: syntax error at or near «SELECT» on pgsql 9.2.5 #813
- Comments
- PostgreSQL – SQL state: 42601 syntax error
- Solution
- 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.
Источник