Yeah, that makes sense now that I think about it. You can combine the Cast in
the first annotate method.
Conference.objects.annotate(year_string=Cast(ExtractYear('start_date'),
CharField())).filter(website__contains=F('year_string')).values_list('website',
flat=True)
From: [email protected] [mailto:[email protected]] On
Behalf Of Jakob Karstens
Sent: Tuesday, October 9, 2018 11:58 AM
To: Django users
Subject: Re: psycopg2 Substr SQL generator bug?
Unfortunately, using the `output_field` keyword argument leads to the same
error:
>>> Conference.objects.annotate(year=ExtractYear('start_date',
>>> output_field=CharField())).filter(website__contains=F('year'))
Traceback (most recent call last):
File
"/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/backends/utils.py",
line 85, in _execute
return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: function replace(double precision, unknown, unknown)
does not exist
LINE 1: ...nces"."website"::text LIKE '%' || REPLACE(REPLACE(REPLACE((E...
^
HINT: No function matches the given name and argument types. You might need to
add explicit type casts.
I wonder if that is part of the same issue?
One way I got it working was using an explicit cast in another annotation:
Conference.objects.annotate(year=ExtractYear('start_date')).annotate(year_string=Cast('year',
CharField())).filter(website__contains=F('year_string')).values_list('website',
flat=True)
I wonder why this works and the other method don't work? It seems I don't
really understand the inner workings of casting and filtering between Django
and Postgres.
Thanks again for your help
On Tuesday, October 9, 2018 at 9:48:22 AM UTC-7, Matthew Pava wrote:
Oh, I see.
Then just use Cast, or the output_field argument .
Conference.objects.annotate(year=ExtractYear('start_date',
output_field=CharField())).filter(website__contains=F('year'))
From: [email protected]<javascript:>
[mailto:[email protected]<javascript:>] On Behalf Of Jakob Karstens
Sent: Tuesday, October 9, 2018 11:43 AM
To: Django users
Subject: Re: psycopg2 Substr SQL generator bug?
Thanks Matthew, I'll submit a ticket.
Unfortunately, ExtractYear does not return a string, so it seems like I would
still need to explicitly cast the year annotation to a string:
>>> Conference.objects.annotate(year=ExtractYear('start_date')).filter(website__contains=F('year'))
Traceback (most recent call last):
File
"/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/backends/utils.py",
line 85, in _execute
return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: function replace(double precision, unknown, unknown)
does not exist
LINE 1: ...nces"."website"::text LIKE '%' || REPLACE(REPLACE(REPLACE((E...
^
HINT: No function matches the given name and argument types. You might need to
add explicit type casts.
On Tuesday, October 9, 2018 at 6:45:32 AM UTC-7, Matthew Pava wrote:
I would submit a ticket for that issue.
Also, instead of using string functions to solve your problem, I would use the
ExtractYear function.
Conference.objects.annotate(year=ExtractYear('start_date')).filter(website__contains=F('year'))
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Jakob Karstens
Sent: Tuesday, October 9, 2018 1:47 AM
To: Django users
Subject: psycopg2 Substr SQL generator bug?
I have a model named `Conference` which has two fields: a DateField
`start_date` and a URLField `website`.
I want to filter the rows to return all Conference's c such that: c.website
contains c.start_date__year. I want to perform this query db-side, not Python
side (for memory reasons), and I don't want to use a raw query (for portability
reasons). I believe that Django does not support using field lookups on the
right side of the equals sign in a filter clause, so my idea was to create an
annotation to pull out the year (e.g. '2018') from the `start_date` field and
check if the `website` field contains this annotation.
To verify that I could get the year strings correctly as an annotation, I ran
this statement:
Conference.objects.annotate(year_string=Substr(Cast('start_date', CharField()),
1, length=4)).values_list('year_string', flat=True)
It successfully returned:
<PolymorphicQuerySet ['2018', '2018', '2018', '2018', '2018', '2018', '2018',
'2018', '2018', '2018', '2018', '2018', '2018', '2018', '2019', '2018', '2018',
'2018', '2018', '2018', '...(remaining elements truncated)...']>
However, when I tried to filter by website containing year (replacing the
`values_list` call with the `filter` call):
Conference.objects.annotate(year_string=Substr(Cast('start_date', CharField()),
1, length=4)).filter(website__contains=F('year_string'))
a strange exception occurred, complaining about %1% in the generated SQL (I am
using Postgres and the psycopg2 Python library for my Django project)
>>> Conference.objects.annotate(year_string=Substr(Cast('start_date',
>>> CharField()), 1, length=4)).filter(website__contains=F('year_string'))
Traceback (most recent call last):
File
"/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/backends/utils.py",
line 85, in _execute
return self.cursor.execute(sql, params)
psycopg2.DataError: invalid input syntax for integer: "%1%"
LINE 1: ...E((SUBSTRING("conferences"."start_date"::varchar, '%1%', 4))...
^
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "<console>", line 1, in <module>
File
"/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/polymorphic/query.py",
line 456, in __repr__
return super(PolymorphicQuerySet, self).__repr__(*args, **kwargs)
File
"/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/models/query.py",
line 248, in __repr__
data = list(self[:REPR_OUTPUT_SIZE + 1])
File
"/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/models/query.py",
line 272, in __iter__
self._fetch_all()
File
"/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/models/query.py",
line 1179, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File
"/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/polymorphic/query.py",
line 56, in _polymorphic_iterator
o = next(base_iter)
File
"/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/models/query.py",
line 53, in __iter__
results = compiler.execute_sql(chunked_fetch=self.chunked_fetch,
chunk_size=self.chunk_size)
File
"/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/models/sql/compiler.py",
line 1064, in execute_sql
cursor.execute(sql, params)
File
"/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/backends/utils.py",
line 100, in execute
return super().execute(sql, params)
File
"/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/backends/utils.py",
line 68, in execute
return self._execute_with_wrappers(sql, params, many=False,
executor=self._execute)
File
"/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/backends/utils.py",
line 77, in _execute_with_wrappers
return executor(sql, params, many, context)
File
"/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/backends/utils.py",
line 85, in _execute
return self.cursor.execute(sql, params)
File
"/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/utils.py",
line 89, in __exit__
raise dj_exc_value.with_traceback(traceback) from exc_value
File
"/Users/jakobkarstens/confir/virt/lib/python3.6/site-packages/django/db/backends/utils.py",
line 85, in _execute
return self.cursor.execute(sql, params)
django.db.utils.DataError: invalid input syntax for integer: "%1%"
LINE 1: ...E((SUBSTRING("conferences"."start_date"::varchar, '%1%', 4))...
What is going on? Why is the generated SQL "%1%" and not "1"? Is this a
psycopg2 bug? Am I missing something?
Thanks in advance
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected]<mailto:[email protected]>.
To post to this group, send email to
[email protected]<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/083dae62-9ad7-484c-b10b-d6d1c3c7cb52%40googlegroups.com<https://groups.google.com/d/msgid/django-users/083dae62-9ad7-484c-b10b-d6d1c3c7cb52%40googlegroups.com?utm_medium=email&utm_source=footer>.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected]<javascript:>.
To post to this group, send email to [email protected]<javascript:>.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/8326add2-3815-425a-bbaa-8bcdf80cd0a5%40googlegroups.com<https://groups.google.com/d/msgid/django-users/8326add2-3815-425a-bbaa-8bcdf80cd0a5%40googlegroups.com?utm_medium=email&utm_source=footer>.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
[email protected]<mailto:[email protected]>.
To post to this group, send email to
[email protected]<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/391faad9-696a-437d-af80-dcd453225dff%40googlegroups.com<https://groups.google.com/d/msgid/django-users/391faad9-696a-437d-af80-dcd453225dff%40googlegroups.com?utm_medium=email&utm_source=footer>.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/e8e29d82f37d49f98bea210ca512c036%40ISS1.ISS.LOCAL.
For more options, visit https://groups.google.com/d/optout.
