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»;
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:
CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
WITH v_tb_person AS (return query execute sql)
select name, count(*) from v_tb_person where nome like '%a%' group by name
union
select name, count(*) from v_tb_person where gender = 1 group by name;
END
$$ LANGUAGE plpgsql;
Error message I receive:
ERROR: syntax error at or near "return"
LINE 5: WITH v_tb_person AS (return query execute sql)
I tried using:
WITH v_tb_person AS (execute sql)
WITH v_tb_person AS (query execute)
WITH v_tb_person AS (return query execute)
What is wrong? How can I solve this problem?
Its a question related to PostgreSQL equivalent of Oracle “bulk collect”
Solution
Your function would work like this:
CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
RETURN QUERY EXECUTE '
WITH v_tb_person AS (' || sql || $x$)
SELECT name, count(*)::int FROM v_tb_person WHERE nome LIKE '%a%' GROUP BY name
UNION
SELECT name, count(*)::int FROM v_tb_person WHERE gender = 1 GROUP BY name$x$;
END
$$ LANGUAGE plpgsql;
Call:
SELECT * FROM prc_tst_bulk($$SELECT a AS name, b AS nome, c AS gender FROM tbl$$)
-
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()
returnsbigint
, but you hadrowcount
defined asinteger
, 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
- Combining Ember Table with Ember Data
- Maven2: Missing artifact but jars are in place
- What is the worst programming language you ever worked with?
- Homebrew install specific version of formula?
- sql query to find priority jobs
- generate days from date range
- Multiple separate IF conditions in SQL Server
- How to properly do JSON API GET requests and assign output…
- SQL query return data from multiple tables
- Gradle error: Execution failed for task…
Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and
privacy statement. We’ll occasionally send you account related emails.
Already on GitHub?
Sign in
to your account
Closed
anriqing opened this issue
Jul 26, 2018
· 9 comments
Comments
Please include this information:
What Grafana version are you using?
5.2.0-pre1
What datasource are you using?
postgresql
What OS are you running grafana on?
centos 7.0
What did you do?
- Create a variable named region. It shows below values.
- Create a nested variable named component. It returns ‘message : «pq: syntax error at or near «$»»‘ .
5.2.0-pre1 is quite old version. Please try latest stable 5.2.2 or latest master (5.3.0-pre1)
marefr
added
the
needs more info
Issue needs more information, like query results, dashboard or panel json, grafana version etc
label
Jul 26, 2018
@marefr Thank you very much for your advise.
I updated my grafana to 5.2.2.1, and it still shows the same error message.
What postgres version? Seems like you have at least one null value and chinese (?) character in region which could potentially cause your problem.
Could you please include screenshots of configuration screens for rergion and component variable? Also, when this error happens what values of region have you selected?
Looks like variables are not expanded in your query.
Unable to reproduce using latest master (5.3-pre1)
I haven’t tried to update grafana to 5.3-pre1 yet.
However, I have tried to change my first variable region to below one:
I added «LIMIT 150» to the query which I’m sure the number will not exceed 150.
SELECT distinct region_name FROM sub_change_order_do LIMIT 150;
Then the nested variable component works.
It’s weired.
Do you have any null values in those 150? What if you do
select distinct region_name from sub_change_order_do where region_name is not null
Does that work?
You are right. There IS null value in those 150. Your query works too.
Closing this. Will consider reopen if you think that null values should be supported in variables.
Last tested: Feb 2021
Overview
This SQL error generally means that somewhere in the query, there is invalid syntax.
Some common examples:
- Using a database-specific SQL for the wrong database (eg BigQuery supports DATE_ADD, but Redshift supports DATEADD)
- Typo in the SQL (missing comma, misspelled word, etc)
- Missing a sql clause (missed from, join, select, etc)
- An object does not exist in the database or is not accessible from the current query (eg referencing orders.id when there is no orders table joined in the current query, etc)
In some circumstances, the database error message may display extra detail about where the error was raised, which can be helpful in narrowing down where to look.
Error Message
SQL ERROR: syntax error at or near
Troubleshooting
This should generally be the first step to troubleshoot any SQL syntax error in a large query: iteratively comment out blocks of SQL to narrow down where the problem is.
TIP: To make this process easier, change the group by clause to use position references
eg: group by 1,2,3,4,5 instead of group by orders.status, orders.date, to_char(...)...
as well as separate the where and having clauses onto multiple lines.
So for example, say we have the following query:
play_arrow
WITH cte AS (
select id, status, sales_amountfrom orders
)
select status, foo.date, sum(cte.sales_amount), count(*) from cte
join foo on cte.date = foo.date
group by status, foo.date
order by 3 desc
We could start by running just the portion in the CTE:
play_arrow
-- WITH cte AS (
select id, status, sales_amountfrom orders
-- )
-- select status, foo.date, sum(cte.sales_amount), count(*)
-- from cte
-- join foo on cte.date = foo.date
-- group by 1, 2
-- order by 3 desc
Then strip out the aggregates and portions related to them
play_arrow
WITH cte AS (
select id, status, sales_amountfrom orders
)
select status, foo.date, -- sum(cte.sales_amount), count(*)
from cte
join foo on cte.date = foo.date
-- group by 1, 2
-- order by 3 desc
Iteratively stripping out / adding back in portions of the query until you find the minimum query to trigger the error.
-
Lookup functions and syntax If the query is small enough, or if we’ve narrowed the scope enough with 1, google all the functions used in the query and verify that they exist and are being used correctly.
-
Verify all objects exist Verify that you’ve joined all tables used in the select, where, and having clause, and that those tables exist in the db. Once we’ve narrowed things down from 1, also check that each column exists in the table specified.