Having an issue converting SQL to postgreSQL with ''' making huge strings

jack gell 0
Viewed 29
CONCAT('ERROR - Portfolio '', ''' not found in br_portfolio and composite_member table')

My issue is that ''' is creating a huge string that goes on for many lines after it is, so I guess the problem is how do I create ' char as a string that is wrapped in ''?

Thanks

sql postgresql
1 Answers

Maybe this is more of a comment, but for what it's worth some additional options for you.

If you have a long string that includes return characters and single quotes, you can always use string literals using $$:

select $ONE$I'm a dog$ONE$, $FOOT$Hello,
Hambone's Dog$FOOT$

Hopefully you can see what goes inside the $$ doesn't matter -- it just has to match the beginning and end of the literal.

Second comment: I really like the format function in Pg. It's cleaner, in my opinion, than doing concat or multiple concat operators:

select format ('Eating too much %s is dangerous', 'cake')

So, combining these two, I wonder if this will help make your code cleaner/easier:

create table test (portfolio varchar(20));

insert into test values ('foot'), ('ball');

select
  format ($EE$ERROR - Portfolio '%s' not found in br_portfolio$EE$, portfolio)
from test;
Hambone0
Related