My query is something like this. I try to get a status for a list of ids.
select order_number, order_status_name
from data.order_fact s
join data.order_status_dim l
on s.order_status_key = l.order_status_key
where
order_number in (1512011196169,1512011760019,1512011898493,1512011972111)
I get an error though that says:
ERROR: operator does not exist: character varying = bigint
LINE 6: order_number in (1512011196169,1512011760019,1512011898493,1...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Do you have any clue on how I should reform the ids to get it work?
Thanks a lot!
asked Jan 12, 2016 at 10:06
If you can’t change the type of numbers within in
, you could use cast
:
select * from numbers_as_string
where cast(my_numbers_as_string as int) in (1,2,3)
answered Jun 6, 2020 at 1:05
This happen too when you are using native query in sprinboot and you are passing a parameter as string but that field is a (integer or long) in your model/entity, also when you are comparing a string with a integer due that param is used like string without casting.
so you should cast it as integer in the native query like this
x::integer
for example:
@Query(value="
......
.....
inner join tablex t on t.x\:\:integer = pv.id n"+
....
....
")
List<Object> getMyQuery(@Param("x") String x)
answered Dec 5, 2022 at 21:24
My query is something like this. I try to get a status for a list of ids.
select order_number, order_status_name
from data.order_fact s
join data.order_status_dim l
on s.order_status_key = l.order_status_key
where
order_number in (1512011196169,1512011760019,1512011898493,1512011972111)
I get an error though that says:
ERROR: operator does not exist: character varying = bigint
LINE 6: order_number in (1512011196169,1512011760019,1512011898493,1...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Do you have any clue on how I should reform the ids to get it work?
Thanks a lot!
asked Jan 12, 2016 at 10:06
If you can’t change the type of numbers within in
, you could use cast
:
select * from numbers_as_string
where cast(my_numbers_as_string as int) in (1,2,3)
answered Jun 6, 2020 at 1:05
This happen too when you are using native query in sprinboot and you are passing a parameter as string but that field is a (integer or long) in your model/entity, also when you are comparing a string with a integer due that param is used like string without casting.
so you should cast it as integer in the native query like this
x::integer
for example:
@Query(value="
......
.....
inner join tablex t on t.x\:\:integer = pv.id n"+
....
....
")
List<Object> getMyQuery(@Param("x") String x)
answered Dec 5, 2022 at 21:24
I’m trying to interact with the Twitter API to show a user_timeline on my website.
I followed the railscasts.com video for Twitter integration: http://railscasts.com/episodes/359-twitter-integration
I am interacting with the API just fine, pulling the information into my application and it is displaying and working in development.
My code is as follows:
Model — timeline.rb
class Timeline < ActiveRecord::Base
attr_accessible :content, :screen_name, :tweet_id
def self.pull_tweets
Twitter.user_timeline("{username_goes_here}", since_id: maximum(:tweet_id)).each do |tweet|
unless exists?(tweet_id: tweet.id)
create!(
tweet_id: tweet.id,
content: tweet.text,
screen_name: tweet.user.screen_name,
)
end
end
end
end
Here’s the migration:
class CreateTimelines < ActiveRecord::Migration
def change
create_table :timelines do |t|
t.string :tweet_id
t.string :screen_name
t.text :content
t.timestamps
end
end
end
And to display the Tweets:
<div id="timeline">
<% Timeline.order("tweet_id desc").limit(3).each do |timeline| %>
<h3><%= timeline.content %></h3>
<p class="pull-right">
~ @<%= timeline.screen_name %>
</p>
<% end %>
</div>
The idea is to store the tweets in the database so that even if Twitter is down, this won’t affect the users seeing the most recent tweets.
Anyway, when I run the command Timeline.pull_tweets
in the console it works fine.
It’s when I push to heroku, migrate the db, and attempt to run the same command.
Then I get this error:
PGError: ERROR: operator does not exist: character varying = bigint
LINE 1: ...ne FROM "timelines" WHERE "timelines"."tweet_id" = 21919081...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Any help with what is going on?
I’ve also tried to run the migration so that :tweet_id
is an integer, but I got another error on heroku as well.
Thanks.
Перейти к контенту
My query is something like this. I try to get a status for a list of ids.
select order_number, order_status_name
from data.order_fact s
join data.order_status_dim l
on s.order_status_key = l.order_status_key
where
order_number in (1512011196169,1512011760019,1512011898493,1512011972111)
I get an error though that says:
ERROR: operator does not exist: character varying = bigint
LINE 6: order_number in (1512011196169,1512011760019,1512011898493,1...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Do you have any clue on how I should reform the ids to get it work?
Thanks a lot!
asked Jan 12, 2016 at 10:06
If you can’t change the type of numbers within in
, you could use cast
:
select * from numbers_as_string
where cast(my_numbers_as_string as int) in (1,2,3)
answered Jun 6, 2020 at 1:05
This happen too when you are using native query in sprinboot and you are passing a parameter as string but that field is a (integer or long) in your model/entity, also when you are comparing a string with a integer due that param is used like string without casting.
so you should cast it as integer in the native query like this
x::integer
for example:
@Query(value="
......
.....
inner join tablex t on t.x::integer = pv.id n"+
....
....
")
List<Object> getMyQuery(@Param("x") String x)
answered Dec 5, 2022 at 21:24
I am trying to run this query:
select *
from my_table
where column_one=${myValue}
I get the following error in Datagrip:
[42883] ERROR: operator does not exist: character varying = bigint Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Now, I have found this question, and I can fix the error by putting a string like this:
select *
from my_table
where column_one='123'
What I need is a way to pass in the ‘123’ as a parameter. I usually do this ${myValue}
and it works, but I am not sure how to keep my variable there as an input so I can run dynamic queries in code and let Postgres understand I want to pass in a string and not a number.
Any suggestions?
Here’s a screenshot of how I am putting the parameter value in DataGrip…:
Ok, so, I just tried to put quotes in the data grip parameters input field for myValue @thirumal’s answer things work. I didn’t know I have to quote the value for it to work.
This is what it looks like:
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
vjykumar opened this issue
Jul 13, 2018
· 15 comments
Assignees
Comments
When trying to use Spring Security ACL, I am facing error: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = character varying
Below is the error stack:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select acl_object_identity.object_id_identity, acl_entry.ace_order, acl_object_identity.id as acl_id, acl_object_identity.parent_object, acl_object_identity.entries_inheriting, acl_entry.id as ace_id, acl_entry.mask, acl_entry.granting, acl_entry.audit_success, acl_entry.audit_failure, acl_sid.principal as ace_principal, acl_sid.sid as ace_sid, acli_sid.principal as acl_principal, acli_sid.sid as acl_sid, acl_class.class from acl_object_identity left join acl_sid acli_sid on acli_sid.id = acl_object_identity.owner_sid left join acl_class on acl_class.id = acl_object_identity.object_id_class left join acl_entry on acl_object_identity.id = acl_entry.acl_object_identity left join acl_sid on acl_entry.sid = acl_sid.id where ( (acl_object_identity.object_id_identity = ? and acl_class.class = ?)) order by acl_object_identity.object_id_identity asc, acl_entry.ace_order asc]; nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 781
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:620)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:657)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:688)
at org.springframework.security.acls.jdbc.BasicLookupStrategy.lookupObjectIdentities(BasicLookupStrategy.java:384)
at org.springframework.security.acls.jdbc.BasicLookupStrategy.readAclsById(BasicLookupStrategy.java:339)
at org.springframework.security.acls.jdbc.JdbcAclService.readAclsById(JdbcAclService.java:130)
at org.springframework.security.acls.jdbc.JdbcAclService.readAclById(JdbcAclService.java:112)
at org.springframework.security.acls.jdbc.JdbcAclService.readAclById(JdbcAclService.java:120)
I traced the issue till this line:
ps.setString((2 * i) + 1, identifier); |
I am wondering if we have decided that Spring Security ACL will always support Long SID Identifiers, why are we converting the identifier to String and setting the parameter as String.
Any pointers in that direction?
Hi,
I face the same issue. As recommended in the current Spring.io documentation i’ve setup the acl tables in my PostgreSQL database, with column acl_object_identity.object_id_identity
of type varchar(36)
.
IMHO the args
parameter in queryForObject
method call is not properly specified.
It is new Object[]{oid.getType(), oid.getIdentifier()}
, but it must be new Object[]{oid.getType(), "" + oid.getIdentifier()}
as shown in the example below:
public class PostgresJdbcMutableAclService extends JdbcMutableAclService { //copy of this JdbcMutableAclService.selectObjectIdentityPrimaryKey private String selectObjectIdentityPrimaryKey = "select acl_object_identity.id from acl_object_identity, acl_class " + "where acl_object_identity.object_id_class = acl_class.id and acl_class.class=? " + "and acl_object_identity.object_id_identity = ?"; public PostgresJdbcMutableAclService(DataSource dataSource, LookupStrategy lookupStrategy, AclCache aclCache) { super(dataSource, lookupStrategy, aclCache); } @Override protected Long retrieveObjectIdentityPrimaryKey(ObjectIdentity oid) { try { return (Long) this.jdbcTemplate.queryForObject(this.selectObjectIdentityPrimaryKey, Long.class, new Object[]{oid.getType(), "" + oid.getIdentifier()}); } catch (DataAccessException var3) { return null; } } @Override public List<ObjectIdentity> findChildren(ObjectIdentity parentIdentity) { Object[] args = new Object[]{"" + parentIdentity.getIdentifier(), parentIdentity.getType()}; List<ObjectIdentity> objects = this.jdbcTemplate.query(this.findChildrenSql, args, new RowMapper<ObjectIdentity>() { public ObjectIdentity mapRow(ResultSet rs, int rowNum) throws SQLException { String javaType = rs.getString("class"); Serializable identifier = (Serializable) rs.getObject("obj_id"); // identifier = JdbcAclService.this.aclClassIdUtils.identifierFrom(identifier, rs); return new ObjectIdentityImpl(javaType, identifier); } }); return objects.size() == 0 ? null : objects; } }
Further references
- Postgresql release notes: on-character data types are no longer automatically cast to TEXT
Any other ideas?
There is support for non int identifiers in Spring Security now. If someone wants to setup a sample to reproduce this then we can try and go from there.
@rwinch
Where to setup a PostgreSQL sample / is there any reference for other databases?
Unfortuantely, the support of non-integer identifiers does not fix my Postgres issue
@nenaraab There isn’t a sample with Postgres. The contact sample is the reference for ACL support. However, I would caution you that we don’t typically recommend using ACL support because it requires doing in memory joins. If you have a million records and the user only is able to access 2, then you must process all the records in memory.
Instead, we recommend using the Spring Data support.
I see you were able to make some progress on this in #6050 Does that resolve your issue?
In addition to my changes above, I had to adjust two more queries, to make it work with the correct types:
@Bean public JdbcMutableAclService aclService() { JdbcMutableAclService jdbcMutableAclService = new JdbcMutableAclService(dataSource, lookupStrategy(), aclCache()); // from documentation jdbcMutableAclService.setClassIdentityQuery("select currval(pg_get_serial_sequence('acl_class', 'id'))"); jdbcMutableAclService.setSidIdentityQuery("select currval(pg_get_serial_sequence('acl_sid', 'id'))"); // additional adjustments jdbcMutableAclService.setObjectIdentityPrimaryKeyQuery("select acl_object_identity.id from acl_object_identity, acl_class where acl_object_identity.object_id_class = acl_class.id and acl_class.class=? and acl_object_identity.object_id_identity = cast(? as varchar)"); jdbcMutableAclService.setFindChildrenQuery("select obj.object_id_identity as obj_id, class.class as class from acl_object_identity obj, acl_object_identity parent, acl_class class where obj.parent_object = parent.id and obj.object_id_class = class.id and parent.object_id_identity = cast(? as varchar) and parent.object_id_class = (select id FROM acl_class where acl_class.class = ?)"); return jdbcMutableAclService; }
@mangei — your workaround looks much nicer than mine, leveraging the new setters
Anyhow, this PR will hopefully make your additional adjustments obsolete.
@rwinch thanks for the sample and the hint with ACL support… for the reasons you’ve mentioned we don’t use it for mass selects (like findChildren…) and we also don’t use @PostAuthorize
for paginated REST calls.
Still, for single inserts / deletions we make
rwinch
changed the title
Error with postgres
Spring Security ACL Error with postgres
Nov 29, 2018
rwinch
changed the title
Spring Security ACL Error with postgres
Spring Security ACL: No operator matches the given name and argument type
Nov 29, 2018
This comment has been minimized.
This comment has been minimized.
@nenaraab & @rwinch: Thanks for your time and work to fix this issue! I appreciate this a lot =)
We are using Spring-Security since acegi times, always with acl implementation. We updated to every release since. After trying 5.1.6 to 5.2.0 we reverted and tried again with 5.2.1 but have the same error. I wonder if it could be the aftermath of this change. Maybe someone have a clue?
We’are running on Oracle 12 and our error is
org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [java.math.BigDecimal] to type [java.lang.Long] at org.springframework.core.convert.support.GenericConversionService.handleConverterNotFound(GenericConversionService.java:321) at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:194) at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:174) at org.springframework.security.acls.jdbc.AclClassIdUtils.convertToLong(AclClassIdUtils.java:122) at org.springframework.security.acls.jdbc.AclClassIdUtils.identifierFrom(AclClassIdUtils.java:71) at org.springframework.security.acls.jdbc.BasicLookupStrategy$ProcessResultSet.convertCurrentResultIntoObject(BasicLookupStrategy.java:634) at org.springframework.security.acls.jdbc.BasicLookupStrategy$ProcessResultSet.extractData(BasicLookupStrategy.java:583) at org.springframework.security.acls.jdbc.BasicLookupStrategy$ProcessResultSet.extractData(BasicLookupStrategy.java:558) at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:679) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) at org.springframework.security.acls.jdbc.BasicLookupStrategy.lookupObjectIdentities(BasicLookupStrategy.java:381) at org.springframework.security.acls.jdbc.BasicLookupStrategy.readAclsById(BasicLookupStrategy.java:336) at org.springframework.security.acls.jdbc.JdbcAclService.readAclsById(JdbcAclService.java:129) at org.springframework.security.acls.jdbc.JdbcAclService.readAclById(JdbcAclService.java:111) at org.springframework.security.acls.jdbc.JdbcAclService.readAclById(JdbcAclService.java:119) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:205) at com.sun.proxy.$Proxy172.readAclById(Unknown Source)
@fprumbau I think the one you are looking for is #4814
AclClassIdUtils
has a default ConversionService
that it uses now. As a workaround, you might consider configuring a GenericConversionService
for BasicLookupStrategy
. Otherwise, I’d recommend that you log an issue in case there is a way for the framework to take care of the concern.
@jzheaux Thanx a lot, you made my day. After registering a proper implementation converting BigDecimal to long everything is good again.
Hi @fprumbau — I am using spring-security-acl 5.2.2-RELEASE and facing same issue while converting from Integer to Long. I have my object_identity_id column defined as int in acl_object_identity table. Could you please explain me how you added your custom converter in GenericConversionService and then injected this instance to AclClassIdUtils. As I see AclClassIdUtils is using new instance of GenericConversionService.
I’ve updated paragraphs to the latest version (1.9) and tried to run the database updates.
(Sidenote — I’m using PostgreSQL as database system)
However, trying to run the post update function ‘paragraphs_post_update_rebuild_parent_fields’ results in a crash with the following error:
> [notice] Update started: paragraphs_post_update_rebuild_parent_fields
> [error] SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: character varying <> bigint
> LINE 5: WHERE (((p.parent_id <> f.entity_id)) OR (p.parent_type <> '...
> ^
> HINT: No operator matches the given name and argument types. You might need to add explicit type casts.: SELECT f.entity_id AS entity_id, f.field_paragraph_target_revision_id AS field_paragraph_target_revision_id, p.revision_id AS prevision_id
> FROM
> {paragraph_revision__field_paragraph} f
> INNER JOIN {paragraphs_item_revision_field_data} p ON f.field_paragraph_target_revision_id = p.revision_id
> WHERE (((p.parent_id <> f.entity_id)) OR (p.parent_type <> :db_condition_placeholder_0) OR (p.parent_field_name <> :db_condition_placeholder_1)) AND ((p.langcode = f.langcode))
> ORDER BY p.revision_id ASC NULLS FIRST
> LIMIT 100 OFFSET 0; Array
> (
> [:db_condition_placeholder_0] => paragraph
> [:db_condition_placeholder_1] => field_paragraph
> )
>
> [error] Update failed: paragraphs_post_update_rebuild_parent_fields
> [error] Update aborted by: paragraphs_post_update_rebuild_parent_fields
In my understanding this is caused by the type difference of the values that are being compared: ‘p.parent_id’ being ‘varying‘ and ‘f.entity_id’ being ‘bigint‘. What worked for me, was explicitly casting the value of type ‘bigint‘ to ‘varying (varchar)‘, so that the types are the same. The other way around would work too, but I thought it would be safer to cast a ‘bigint‘ to ‘varying (varchar)‘, in case the ‘p.parent_id’ wouldn’t contain a numerical value.
I’ve created a patch containing the change I did to solve this issue for me.
Tag1 supports the Drupal Project.
ERROR: Operator does not exist: character varying = integer
Getting an error Operator does not exist: character varying = integer when executing an insight
Written by Boyan Barnev
Updated over a week ago
Problem
You created an SQL insight and when you execute it (or save it and view it) you get an error message stating: ERROR: Operator does not exist: character varying = integer
Cause
The above error means that somewhere in your insight you are trying to compare a text string to a numeric value. SQL can’t directly recognize the text as number, thus the comparison is not possible.
You can easily identify if that’s the case if you do a SELECT statement for the fields you want to use in your insight and observe their output by clicking the Execute button in your insight editor. If the value is surrounded in quotation marks, then it’s coming through as a text. If it’s not, then it’s numeric.
Here are two examples:
-
In the below example, the values of the id and week fields are coming through as text:
{
"data_source_id": "5ec29e7c53e7610001d5f1be",
"id": "4",
"sync_date": "2020-06-25T00:00:00",
"week": "3"
}
2. In this example, the values of the id and week fields are coming through as numeric:
{
"data_source_id": "5ec29e7c53e7610001d5f1be",
"id": 4,
"sync_date": "2020-06-25T00:00:00",
"week": 3
}
Solution
If you must compare the values you’re extracting against numeric values, you have to cast your fields and specify the desired format. For example:
SELECT week FROM my_table WHERE id::int=4
instructs SQL to interpret the value in the id field as integer, and enables you to compare it to an integer value.
Additional information
-
In this article we use the :: operator to cast to a numeric type. You can read more about it here: PostgreSQL numeric data types
Problem Description:
My query is something like this. I try to get a status for a list of ids.
select order_number, order_status_name
from data.order_fact s
join data.order_status_dim l
on s.order_status_key = l.order_status_key
where
order_number in (1512011196169,1512011760019,1512011898493,1512011972111)
I get an error though that says:
ERROR: operator does not exist: character varying = bigint
LINE 6: order_number in (1512011196169,1512011760019,1512011898493,1...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Do you have any clue on how I should reform the ids to get it work?
Thanks a lot!
Solution – 1
Your order_number
is a varchar, you can’t compare that to a number (123
is a number in SQL, '123'
is a string constant)
You need to use string literals:
order_number in ('1512011196169','1512011760019','1512011898493','1512011972111')
More details in the manual:
http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
Solution – 2
If you can’t change the type of numbers within in
, you could use cast
:
select * from numbers_as_string
where cast(my_numbers_as_string as int) in (1,2,3)
Solution – 3
This happen too when you are using native query in sprinboot and you are passing a parameter as string but that field is a (integer or long) in your model/entity, also when you are comparing a string with a integer due that param is used like string without casting.
so you should cast it as integer in the native query like this
x::integer
for example:
@Query(value="
......
.....
inner join tablex t on t.x::integer = pv.id n"+
....
....
")
List<Object> getMyQuery(@Param("x") String x)