Kysely•7mo ago

concat in postgres

Hey! I want to concat few columns to get address, however query is built in wrong way cuz postgres uses || to concat. What am I doing wrong?

let warehouses = await db
      .selectFrom('warehouse as w')
      .leftJoin('address as a', 'w.addressId', 'a.id')
      .select((eb) => [
          'w.id',
          'w.name',
          'w.description',
          eb.fn<string>('concat', [
              'a.city',
              eb.val(' '),
              'a.district',
              eb.val(' '),
              'a.street',
          ]).as('address')
      ])
      .execute()

let warehouses = await db
      .selectFrom('warehouse as w')
      .leftJoin('address as a', 'w.addressId', 'a.id')
      .select((eb) => [
          'w.id',
          'w.name',
          'w.description',
          eb.fn<string>('concat', [
              'a.city',
              eb.val(' '),
              'a.district',
              eb.val(' '),
              'a.street',
          ]).as('address')
      ])
      .execute()

select
    "w"."id",
    "w"."name",
    "w"."description",
    concat("a"."city",
    $1,
    "a"."district",
    $2,
    "a"."street") as "address"
from
    "warehouse" as "w"
left join "address" as "a" on
    "w"."address_id" = "a"."id"
',
  parameters: [ ' ', ' ' ]

select
    "w"."id",
    "w"."name",
    "w"."description",
    concat("a"."city",
    $1,
    "a"."district",
    $2,
    "a"."street") as "address"
from
    "warehouse" as "w"
left join "address" as "a" on
    "w"."address_id" = "a"."id"
',
  parameters: [ ' ', ' ' ]

I made it with raw sql for now

sql<string>`concat(a.city || ' ' || a.street)`.as('address')

sql<string>`concat(a.city || ' ' || a.street)`.as('address')

but I wonder what is proper way

1 Reply

koskimas•7mo ago

You are literally asking Kysely to create a function call to concat function. Why are you surprised it does that? Postgres concat is done using the || operator. The call to concat is useless there. Also, postgres DOES have a concat function And functions always take comma separated arguments. The following two queries are identical:

SELECT 'a' || 'b' || 'c' AS x;

SELECT 'a' || 'b' || 'c' AS x;

SELECT concat('a', 'b', 'c') AS x;

SELECT concat('a', 'b', 'c') AS x;

Gaming

Programming

concat in postgres