Combine two columns and add into one new column

Question

In PostgreSQL  I want to use an SQL statement to combine two columns and create a new column from them   I m thinking about using concat       but is there a better way  What s the best way to do this

User · Answer

Did you check the string concatenation function  Something like   update table c set column a   column b    column c    should work  More here

User · Answer

Generally  I agree with  kgrittn s advice  Go for it   But to address your basic question about concat    The new function concat   is useful if you need to deal with null values - and null has neither been ruled out in your question nor in the one you refer to   If you can rule out null values  the good old  SQL standard  concatenation operator    is still the best choice  and  luis  answer is just fine   SELECT col a    col b    If either of your columns can be null  the result would be null in that case  You could defend with COALESCE   SELECT COALESCE col a         COALESCE col b         But that get tedious quickly with more arguments  That s where concat   comes in  which never returns null  not even if all arguments are null  Per documentation      NULL arguments are ignored    SELECT concat col a  col b     The remaining corner case for both alternatives is where all input columns are null in which case we still get an empty string     but one might want null instead  at least I would   One possible way   SELECT CASE           WHEN col a IS NULL THEN col b           WHEN col b IS NULL THEN col a           ELSE col a    col b        END    This gets more complex with more columns quickly  Again  use concat   but add a check for the special condition   SELECT CASE WHEN  col a  col b  IS NULL THEN NULL             ELSE concat col a  col b  END    How does this work   col a  col b  is shorthand notation for a row type expression ROW  col a  col b   And a row type is only null if all columns are null  Detailed explanation    NOT NULL constraint over a set of columns   Also  use concat ws   to add separators between elements  ws for  with separator       An expression like the one in Kevin s answer   SELECT  1 zipcode      -       1 city             1 state    is tedious to prepare for null values in PostgreSQL 8 3  without concat     One way  of many    SELECT COALESCE           CASE             WHEN  1 zipcode IS NULL THEN  1 city             WHEN  1 city    IS NULL THEN  1 zipcode             ELSE  1 zipcode      -       1 city          END                COALESCE          1 state         Function volatility is only STABLE  concat   and concat ws   are STABLE functions  not IMMUTABLE because they can invoke datatype output functions  like timestamptz out  that depend on locale settings  Explanation by Tom Lane   This prohibits their direct use in index expressions  If you know that the result is actually immutable in your case  you can work around this with an IMMUTABLE function wrapper  Example here    Does PostgreSQL support  quot accent insensitive quot  collations

User · Answer

You don t need to store the column to reference it that way   Try this   To set up   CREATE TABLE tbl    zipcode text NOT NULL  city text NOT NULL  state text NOT NULL   INSERT INTO tbl VALUES   10954    Nanuet    NY      We can see we have  the right stuff     pset border 2 SELECT   FROM tbl      --------- -------- -------    zipcode    city    state    --------- -------- -------    10954     Nanuet   NY       --------- -------- -------    Now add a function with the desired  column name  which takes the record type of the table as its only parameter   CREATE FUNCTION combined rec tbl    RETURNS text   LANGUAGE SQL AS      SELECT  1 zipcode      -       1 city             1 state        This creates a function which can be used as if it were a column of the table  as long as the table name or alias is specified  like this   SELECT    tbl combined FROM tbl    Which displays like this     --------- -------- ------- --------------------    zipcode    city    state        combined         --------- -------- ------- --------------------    10954     Nanuet   NY      10954 - Nanuet  NY    --------- -------- ------- --------------------    This works because PostgreSQL checks first for an actual column  but if one is not found  and the identifier is qualified with a relation name or alias  it looks for a function like the above  and runs it with the row as its argument  returning the result as if it were a column   You can even index on such a  generated column  if you want to do so   Because you re not using extra space in each row for the duplicated data  or firing triggers on all inserts and updates  this can often be faster than the alternatives

[sql] Combine two columns and add into one new column

Examples related to sql

Examples related to postgresql

Examples related to null

Examples related to concatenation