like '%M' not working for character datatype PostgreSQL

Question

like '%' works as expected for varchar datatype and not works for char datatype in PostgreSQL.

Created a table as below in PostgreSQL: create table movie(movie_name varchar(20), director char(20)); [Note: movie_name is varchar type and director is char type] and inserted few rows using the below query.

insert into movie values('JaiBhim', 'Gnanavel');

insert into movie values('Dharbar', 'ARM');

After this, I used the below select query with '%'.

select * 
from movie 
where movie_name like '%m';

I got correct row details. [JaiBhim and Gnanavel].

I used another select query with '%' as below.

select * 
from movie 
where director like '%M';

For this I expected one row should be returned but it returned 0 rows.

For varchar, 'like %' gives correct result and for char datatype it is not giving.

Is it the expected behaviour?

Yes. Read Character types: character(n), char(n) fixed-length, blank padded. The %M is not taking into account the spaces after the M , try %M% or `select * from movie where trim(director) like '%M' — Adrian Klaver
– Adrian Klaver, Commented Apr 5, 2023 at 16:24

Laurenz Albe · Accepted Answer · 2023-04-06 06:19:53Z

0

character pads the values with blanks, so a pattern '%M' will only match a character(10) if the tenth character is an M.

The best solution is to avoid character and use character varying instead, like the documentation recommends. Other than that, you could use a regular expression:

WHERE movie_name ~ 'M *$'

answered Apr 6, 2023 at 6:19

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

like '%M' not working for character datatype PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related