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]] *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].
> 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/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].
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/391faad9-696a-437d-af80-dcd453225dff%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
