How to select from an array column without getting malformed array errors

Question

I've spent a good hour or so googling and trying various combinations but without success.

I wish to select from a table where one of the columns is single dimensional array of varchar(255).

In normal SQL I use the following query:

SELECT * FROM customers WHERE email_domains @> '{"@google.com"}';

That works perfectly. But now I want to do the same from code. So I have tried this:

domain = '@google.com'
sql = "SELECT * FROM customers WHERE email_domains @> '{%s}';"

cursor.execute( sql, [domain] )
result = cursor.fetchall()

and a whole load of various combinations of escaped ' and " but I cannot get it to work.

The error I get is this:

ERROR: malformed array literal: "{"
LINE 1: ... * FROM customers WHERE email_domains @> '{'@goo....
                                                         ^
DETAIL:  Unexpected end of input.

All help appreciated :)

Looks like the @g in '@google' is being interrupted as an beginning of an array operator. I don't have an environment at the moment to test but two options come to mind. 1) try escaping the @ in domain "domain='\@google' 2) Use the array constructor "WHERE email_domains @> ARRAY ['%s'];" — Belayer
– Belayer, Commented Jun 17, 2019 at 18:23
I tried changing the @google.com to anything.com without the @ just to see what error message I got. It was this ERROR:root:Failed to process Customer: syntax error at or near "anything" LINE 1: ...FROM customers WHERE email_domains @> '{'anything.com'... — The Welsh Dragon
– The Welsh Dragon, Commented Jun 17, 2019 at 18:36
but escaping it gave this Failed to process Customer: syntax error at or near "\" LINE 1: ...FROM customers WHERE email_domains @> '{'\@anything.co.. — The Welsh Dragon
– The Welsh Dragon, Commented Jun 17, 2019 at 18:39

The Welsh Dragon · Accepted Answer · 2019-06-17 19:06:28Z

2

Ok. At times I amaze myself with my stupidity...

The solution (for me) was to simply restructure my sql to this:

SELECT * FROM customers WHERE %s  = ANY(email_domains);

Thanks to everyone who offered suggestions.

answered Jun 17, 2019 at 19:06

The Welsh Dragon

5291 gold badge7 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeremy · Accepted Answer · 2019-06-17 19:00:28Z

1

psycopg2 converts lists to arrays for you. I can't test this easily at the moment, but I believe this should work:

domain = ['@google.com']
# Let psycopg2 do the escaping for you, don't put quotes in there
sql = "SELECT * FROM customers WHERE email_domains @> %s;"

cursor.execute( sql, [domain] )
result = cursor.fetchall()

answered Jun 17, 2019 at 19:00

Jeremy

6,77322 silver badges21 bronze badges

5 Comments

The Welsh Dragon Over a year ago

Thanks but it didnt work.. it gave this error malformed array literal: "@google.com" with the sql looking like this * FROM customers WHERE email_domains @> '@google.c...

Jeremy Over a year ago

Hmm, strange. You did change domain to a list of (one) domains, right?

The Welsh Dragon Over a year ago

No, you are correct. I missed that! But.. the domain is a string '@google.com' so if i use that like this cursor.execute( sql, [domain]) I get this error: malformed array literal: "@google.com" if i use it like this cursor.execute( sql, [[domain]] ) I get this error: operator does not exist: character varying[] @> text[]

The Welsh Dragon Over a year ago

I have found a solution though and answered my own question accordingly.

Abelisto Over a year ago

@TheWelshDragon Yes in PostgreSQL character varying (varchar) and text are two different types. You need explicit type casting like ... WHERE email_domains @> %s::varchar[];

Collectives™ on Stack Overflow

How to select from an array column without getting malformed array errors

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related