Postgresql Error Syntax Error At Or Near $1
Contents |
Documentation Prev Fast Backward Chapter 38. PL/pgSQL - SQL Procedural Language Fast Forward Next 38.10. PL/pgSQL Under
Org.postgresql.util.psqlexception: Error: Syntax Error At Or Near "$1"
the Hood This section discusses some implementation details that are frequently org.postgresql.util.psqlexception: error: syntax error at or near ")" hibernate important for PL/pgSQL users to know. 38.10.1. Variable Substitution When PL/pgSQL prepares a SQL statement or expression pq syntax error at or near $1 for execution, any PL/pgSQL variable name appearing in the statement or expression is replaced by a parameter symbol, $n. The current value of the variable is then provided as
Syntax Error At Or Near Insert
the value for the parameter whenever the statement or expression is executed. As an example, consider the function CREATE FUNCTION logfunc(logtxt text) RETURNS void AS $$ DECLARE curtime timestamp := now(); BEGIN INSERT INTO logtable VALUES (logtxt, curtime); END; $$ LANGUAGE plpgsql; The INSERT statement will effectively be processed as PREPARE statement_name(text, timestamp) AS INSERT INTO logtable
Sql State: 42601
VALUES ($1, $2); followed on each execution by EXECUTE with the current actual values of the two variables. (Note: here we are speaking of the main SQL engine's EXECUTE command, not PL/pgSQL's EXECUTE.) The substitution mechanism will replace any token that matches a known variable's name. This poses various traps for the unwary. For example, it is a bad idea to use a variable name that is the same as any table or column name that you need to reference in queries within the function, because what you think is a table or column name will still get replaced. In the above example, suppose that logtable has column names logtxt and logtime, and we try to write the INSERT as INSERT INTO logtable (logtxt, logtime) VALUES (logtxt, curtime); This will be fed to the main SQL parser as INSERT INTO logtable ($1, logtime) VALUES ($1, $2); resulting in a syntax error like this: ERROR: syntax error at or near "$1" LINE 1: INSERT INTO logtable ( $1 , logti
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers plpgsql or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up golang syntax error at or near “$1” in postgres up vote 3 down vote favorite I am trying to execute a query in go using the sql module. var from string = "2015-03-01 00:00:00" https://www.postgresql.org/docs/8.3/static/plpgsql-implementation.html rows, err := db.Query("select time, val from table where " + "time >= extract(epoch from timestamp with time zone $1)::int4 " + "and time < extract(epoch from timestamp with time zone '2015-03-01 00:15:10')::int4 " + "order by time asc",from) However I get the error pq: syntax error at or near "$1" If I put in the epoch values directly then the query will work and the query works when I try it without any variables i.e. with the query hardcoded. http://stackoverflow.com/questions/29007972/golang-syntax-error-at-or-near-1-in-postgres So what is the problem? postgresql go share|improve this question asked Mar 12 '15 at 11:01 robochat 61259 have you tried ? and not $1 –Not_a_Golfer Mar 12 '15 at 11:58 1 It gives the same error (i.e. syntax error at or near "?"). Anyway, I thought that the pq module used $1 ? –robochat Mar 12 '15 at 12:02 hmmm... can you try and make your query a single multi-line string using backticks? Not that I think it would matter but otherwise things seem okay, and it's a better practice in terms of GC pressure and cpu performance anyway. –Not_a_Golfer Mar 12 '15 at 12:13 add a comment| 1 Answer 1 active oldest votes up vote 3 down vote accepted You're right about $1 and ?. The reason why it complains about invalid syntax with $1 is because of type cast. Put it like this: rows, err := db.Query("select time, val from table where " + "time >= extract(epoch from $1::timestamp with time zone)::int4 " + "and time < extract(epoch from timestamp with time zone '2015-03-01 00:15:10')::int4 " + "order by time asc",from) share|improve this answer answered Mar 12 '15 at 13:34 Tino 34625 thanks, that was the problem. –robochat Mar 12 '15 at 13:49 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up u
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any http://dba.stackexchange.com/questions/103980/syntax-error-using-preparedstatements-with-postgresql questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site syntax error for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Syntax error using preparedstatements syntax error at with postgresql up vote 1 down vote favorite I have a Java application connecting to a PostgreSQL database. Using straightforward code like: ps = con.prepareStatement(sql); if (dataTypes != null && dataTypes.size() > 0) { for (int i = 0; i < dataTypes.size(); ++i) { if (dataTypes.get(i) == DataTypes.NUMERIC) ps.setLong((i + 1), Long.parseLong(values.get(i))); else ps.setString((i + 1), values.get(i)); } } rs = ps.executeQuery(); and with a query like select ? from dummy it just works fine. In the postgres logs what shows is: 2015-06-13 12:48:30 EEST [28294-3] xx LOG: execute