synthax error near or at ':' when doing slice indexing

[TheophileLaPelouse]

Here is a sql query :

declare 
    tab integer[] := array[1, 2, 3, 4, 5] ;
    tab2 integer[] := array[1, 2, 3, 4, 5] ;
    i integer := 1 ;
    j integer := 2 ;
begin 
    raise notice 'tab %', tab[1:5] ;
    tab2[i:j] := tab[i:j] ;
end ;
$$ ;

When I input this on my psql it works well, here is what it returns :

NOTICE:  tab {1,2,3,4,5}
DO

But when I try the same query using psycopg2 with :

query = ""\ndo $$\ndeclare \n    tab integer[] := array[1, 2, 3, 4, 5] ;\n    tab2 integer[] := array[1, 2, 3, 4, 5] ;\n    i integer := 1 ;\n    j integer := 2 ;\nbegin \n    raise notice 'tab %', tab[1:5] ;\n    tab2[i:j] := tab[i:j] ;\nend ;\n$$ ;"
cur.execute(query)

I get :

Traceback (most recent call last):
  File "C:\PROGRA~1\QGIS33~1.9\apps\Python312\Lib\code.py", line 90, in runcode
    exec(code, self.locals)
  File "<input>", line 1, in <module>
psycopg2.errors.SyntaxError: ERREUR:  erreur de syntaxe sur ou près de « : »
LINE 10:     tab2[i:j] := tab[i:j] ;

I feel like this should be a bug, is there a work around ? If not I know I could just replace every slice indexing in my functions by loops but I would appreciate to not have to do this.

Thank you in advance for your answers !

---

edit

My bad, the problem was not about psycopg2. You just cannot affect a value to an array this way in the version of postgresql I am using with psycopg2, and I was doing test with psql on an other version of postgresql.

[dvarrazzo]

Because in psycopg placeholders are expressed with %s, you have to use `%%` in order to represent a literal % in your query.

…

On Fri, 25 Oct 2024, 17:14 TheophileLaPelouse, ***@***.***> wrote: Here is a sql query : declare tab integer[] := array[1, 2, 3, 4, 5] ; tab2 integer[] := array[1, 2, 3, 4, 5] ; i integer := 1 ; j integer := 2 ; begin raise notice 'tab %', tab[1:5] ; tab2[i:j] := tab[i:j] ; end ; $$ ; When I input this on my psql it works well, here is what it returns : NOTICE: tab {1,2,3,4,5} DO But when I try the same query using psycopg2 with : query = ""\ndo $$\ndeclare \n tab integer[] := array[1, 2, 3, 4, 5] ;\n tab2 integer[] := array[1, 2, 3, 4, 5] ;\n i integer := 1 ;\n j integer := 2 ;\nbegin \n raise notice 'tab %', tab[1:5] ;\n tab2[i:j] := tab[i:j] ;\nend ;\n$$ ;" cur.execute(query) I get : Traceback (most recent call last): File "C:\PROGRA~1\QGIS33~1.9\apps\Python312\Lib\code.py", line 90, in runcode exec(code, self.locals) File "<input>", line 1, in <module> psycopg2.errors.SyntaxError: ERREUR: erreur de syntaxe sur ou près de « : » LINE 10: tab2[i:j] := tab[i:j] ; I feel like this should be a bug, is there a work around ? If not I know I could just replace every slice indexing in my functions by loops but I would appreciate to not have to do this. Thank you in advance for your answers ! — Reply to this email directly, view it on GitHub <#1743>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABQWBPJ5HIONRLZ2EYTTHDZ5JN6FAVCNFSM6AAAAABQTP3OGGVHI2DSMVQWIX3LMV43ERDJONRXK43TNFXW4OZXGM3DQNJZG4> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>