PostgreSQL – How to Combine Two Columns into One

postgresqlupdate

I have a table in PostgreSQL 11 with two varchar columns like this:

 A | B  
---+---
 m | n 
 x | y

Now I want to add a column C which should contain the contents of A and B (concat(A, B)):

 A | B | C 
---+---+---
 m | n | mn
 x | y | xy

After that I want to drop A and B so that I effectively replace the two original columns.

How can I set the value of C to the concatenated values of A and B?

Best Answer

You seem to have already the solution yourself:

UPDATE thetable SET c = CONCAT(a, b)

Related Solutions

PostgreSQL – String Replace Using Concatenated Columns

Depending on how you would want to deal with possible NULL values, concat_ws() is probably your safest and simplest way to go:

UPDATE tbl
SET filed1 = replace(field1, concat_ws(' ', field2, field3, field4), '')
WHERE filed1 IS DISTINCT FROM replace(field1, concat_ws(' ', field2, field3, field4), '')

concat_ws() ignores NULL values. With plain concatenation (||), one NULL field would make the whole pattern NULL. It was introduced with Postgres 9.1.

The added WHERE clause prevents empty updates. This enhances performance a lot if many rows wouldn't change anyway.

Postgresql – Removing all columns with given name

For a one-time use, you don't need to persist a function. Use a DO statement:

DO
$do$
DECLARE
   rec record;
BEGIN
FOR rec IN
   SELECT a.attrelid::regclass::text AS tbl, a.attname
   FROM   pg_class c
   JOIN   pg_attribute a ON a.attrelid = c.oid
   WHERE  c.relkind = 'r'
   AND    a.attname = 'timestamp'
   AND    a.atttypid = 'timestamp'::regtype
   AND    a.attnum > 0
   AND    NOT a.attisdropped
LOOP
   RAISE NOTICE '%', format('ALTER TABLE %s DROP COLUMN %I', rec.tbl, rec.attname);
   -- Check test output before uncommenting EXECUTE!
   -- EXECUTE format('ALTER TABLE %s DROP COLUMN %I', rec.tbl, rec.attname);
END LOOP;
END
$do$;

Major points

As mentioned at the top, probably no need for a function. The body of a DO statement is identical, default language is plpgsql.
Use the implicit cursor of a FOR loop. Simpler and faster. Explicit cursors are rarely necessary in plpgsql.
Table names are not unique in a Postgres database. There can be any number of tables with the same name in multiple schemas.
Avoid SQL injection and basic exceptions with non-standard (double quoted) identifiers.
- A cast to regclass takes care of the table name. At the same time, table names are schema-qualified automatically where necessary (taking the current search_path into account).
- format() with %I sanitizes the column names.
Details:
- SQL injection in Postgres functions vs prepared queries
I assume you are aware of timestamp and timstamptz data types? This only removes columns with data type timestamp [without time zone].
Identify the data type with a.atttypid = 'timestamp'::regtype, so you don't have to join to pg_type. Simpler.
Exclude system columns and dead columns:
AND a.attnum > 0 AND NOT a.attisdroppedand

Best Answer

Related Solutions

PostgreSQL – String Replace Using Concatenated Columns

Postgresql – Removing all columns with given name

Major points

Related Question