Whitespace in returned query with Python and py-postgresql

Question

I'm using the py-postgresql driver. Whenever I do a SELECT it works correctly, but I get the full width of the column returned, with whitespace. So if I have a field of character(25) which holds "Name", and do:

SELECT name WHERE name = 'Name'

I get

"Name                     " << "Name" followed by 21 spaces

instead of just Name

Example Code that I've run straight from IDLE

db = posgresql.driver.connect(user     = [USERNAME],
                              password = [PASSWORD],
                              host     = [HOST],
                              port     = [PORT]
)

db.query("SELECT [FIELD] FROM [TABLE] WHERE [TRUE]")

No issues with it returning, and yes, I know how to strip the whitespace off the end, but it may need to be there, so I'm really trying to figure out how to get things to return without excess whitespace.

Alaric

Can you change column type to varchar? – Boschvark

Answer 1

Your column is probably defined as a CHAR(n) type, and the database has to honor that, so it is adding spaces at the end. You should switch your column type to VARCHAR(n), which is a variable length field with maximum length of n, and there will be no more trailing spaces.

Photoactive

That's it. I had misunderstood something I had read before, which I thought had said varchar and character are the same. Actually said varchar and varying are the same. – Alaric

It's all in the documentation: postgresql.org/docs/current/interactive/datatype-character.html – Alcibiades