postgresqlsql-insertstring-concatenation

Can I reference concatenated columns in another column during the same INSERT?

I intend to insert a row in table apartments, however this row contains a column full_address which is a combination of some text with the house number in apartments.house_no and the street name in apartments.house_street all of which are being inserted simultaneously. E.g

House-no house_street full_address
1 Shiba No 1, Shiba Street
2 Lancel No 2, Lancel street

I successfully concatenated these columns AS f_address output i.e from the statement: SELECT CONCAT('No '||apartments.house_no ||', '||apartments.house_street ||' Street.') AS f_address E.g

f_address
No 1, Shiba street

but inserting this output into the full_address column creates an error. I'm currently using postgres 16 with the pgadmin 4.7 client. Thank you experts for your anticipated response.

Solution

  • I think your issue is in the diffrent way you handle the column names. They are both "First char capital" and lowercase, and the wordbreaks are handled wit spaces, underscores and scores. These need to be handled when you insert, but double quoting the ones which are a problem:

    INPUT:

    enter image description here

    QUERY:

    UPDATE public.apartments
    SET "Full address"= 'No '||"House-no"||', '||house_street||' Street';

    RESULT:

    enter image description here

    My best Guess is you either miss the double quotes on "Full address" to maintain the space or on "House_no" to maintain the capitalized H. Also make sure you also created the column before you update...