pgsql concat() functionality(almost)

Question

Part of the MySQL query that I'm trying to convert to pgSQL :

LEFT JOIN {$_TABLES['comments']} c ON c.sid = concat('fileid_' ,a.lid )

This got messy since it's concatenating a string with a column(a.lid), which isn't supported by the SQL 92 || operator(important!). Any idea's how to redo this part of the query for pgSQL?

I don't know where you get the idea that || operator cannot concatenate string with column. If this would be true it would make the operator pretty much useless. — user80168
– user80168, Commented Aug 13, 2009 at 10:01

ChssPly76 · Accepted Answer · 2009-08-12 23:43:27Z

5

PostgreSQL 8.3 and up supports || operator as long as at least one of the operands is a string. Concatentation of column with string literal works as well. What version are you using?

answered Aug 12, 2009 at 23:43

ChssPly76

101k25 gold badges210 silver badges196 bronze badges

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

3 Comments

stan Over a year ago

Yea I was trying it on an older version.

Bill Karwin Over a year ago

According to the manual, the || string concatenation operator is supported on older versions too, such as version 7.4 (the earliest version with online docs).

Magnus Hagander Over a year ago

You can always cast the integer one, as in: 'file_id' || CAST (a.lid AS text)

Scott Marlowe · Accepted Answer · 2010-11-16 00:23:00Z

1

Please note that if you concatenate a null to anything else, all you'll get is null.

answered Nov 16, 2010 at 0:23

Scott Marlowe

9,0403 gold badges26 silver badges21 bronze badges

1 Comment

stan Over a year ago

Correct. Not sure if that's pertinent to this question though.

Collectives™ on Stack Overflow

pgsql concat() functionality(almost)

2 Answers 2

3 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Related