0

EDIT: This was an issue of pgAdmin4 displaying I could change my previously declared column data type of character varying [] to character varying (when the user could never do that), leading me to believe I was querying for a varchar, when in reality I was querying a varchar[].

Why does querying a varchar column require curly braces like so?

SELECT * FROM users WHERE email = '{test@test.com}'

Removing the curly braces prompts ERROR: malformed array literal...

I'm using (PostgreSQL) 10.4 and pgAdmin 4 as an interface. The email column is of type character varying [], pgAdmin4 won't let me change it to character varying.

Dylan Landry
  • 1,150
  • 11
  • 27

1 Answers1

2

The reason is simple - this is not a varchar column.

Notice square brackets? character varying []
That means it's array of strings.

You can read more about arrays here: https://www.postgresql.org/docs/current/static/arrays.html

By using curly brackets you create an array of one element: '{test@test.com}'

And now you're comparing apples to apples (array to array)

pgAdmin doesn't allow you to convert the column because it doesn't "know" how exactly you want to convert array of strings to a single string.

You can check which functions are available on arrays in PostgreSQL here: https://www.postgresql.org/docs/current/static/functions-array.html

mu is too short
  • 426,620
  • 70
  • 833
  • 800
Alexey Soshin
  • 16,718
  • 2
  • 31
  • 40
  • Thanks for the answer. I believe the column's datatype menu in pgAdmin4 encountered an error because it showed I could change the datatype, throwing a wrench in my troubleshooting. Refreshing the page showed the column locked to character varying []. – Dylan Landry Aug 06 '18 at 20:26