PostgreSQL 9.1 – How to Fix Trim Not Working for Some Strings

postgresql-9.1string

I want to trim dot (.) from a column value. Just say string is "Mr." and I need to trim the dot from the string. I am expecting "Mr"

Lets say,

I have two fields in my table

 id   |  value
 1       Mr.
 2       Ms.
 11      Mr.
 12      Ms.

But while running below query, not getting as i expected.

 select trim(value, '.') from mytable

 value
 (text)
 Mr
 Ms
 Mr.
 Ms.

And noticed, for id 1 & 2 values is coming as expected. but for id 11 & 12 still the dot is coming. I really could not understand what is the issue.

(Using Postgresql 9.1)

Best Answer

I looked at the trim function and it removes the character from the beginning or the end of the string. I'd bet that the two where it's not working have spaces after the ".". Try trimming spaces off the end first then trim the "."

select trim(trim(value,' '), '.') from mytable

Related Solutions

Postgresql – Users groups/permissions when working with PostgreSQL

There are two basic approaches to users and groups. I will also describe what approach I favor here and why.

The first approach is to use login roles to describe what software is logging on and restrict rights accordingly. This has the advantage of being relatively simple to maintain and a number of disadvantages as well, but it is probably the most common approach because of its administrative simplicity.

The second approach, which I favor, is to give every user his or her own role. DB permissions are then attached to groups (i.e. nologin inherit roles) which are granted to individual users. This is more complex to administer. Keep in mind that unlike Linux, PostgreSQL groups can be nested and unlike Windows, there is no deny acl, so permissions are always fully cumulative. In some cases where someone needs a restricted account we can create a separate user. This adds complexity to the administration process but it also helps mitigate security issues in the application because something like a sql injection attack is less likely to have privilege escalation possibilities, and it essentially allows outsourcing of authentication to the database, and PostgreSQL can outsource this to a variety of sources (RADIUS, PAM, KRB5, GSSAPI, SSPI, etc).

PostgreSQL insert into table (not origin) based on a condition on fields on different tables

You seem to be under the impression that some kind of automatic "row number" would exist. That is not the case. Unlike rows in a spreadsheed, tables in a relational database have no natural order.

This query should do the job, but it relies on the the contents of name_1 and name_2 to make the connection. If you rely on a row number, you have to add an actual column for that.

INSERT INTO table2 (id1, id2, score1, score2, comment_string)
SELECT t1.id1, t1.id2, t1.score1, t1.score2
      ,CASE WHEN t3.name_1 IS NULL
            AND  t4.name_2 IS NULL THEN 'removed_because ...'
       ELSE END AS comment_string
FROM   table_1 t1
LEFT   JOIN table_3 t3 USING (name_1)
LEFT   JOIN table_4 t4 USING (name_2)
ORDER  BY id1; -- undeclared in Q

Based on the assumption that table_3.name_1 and table_4.name_2 are unique. Else, the query could create a "proxy cross join", possibly multiplying rows, if there are several matches.
More about this caveat in this related answer on SO.

Best Answer

Related Solutions

Postgresql – Users groups/permissions when working with PostgreSQL

PostgreSQL insert into table (not origin) based on a condition on fields on different tables

Related Question