0

I'd like to create a new column in my table if that column doesn't exist. But if the column exists, I would like to update it.

I haven't found a suitable answer yet. Can anyone help me?

  • Update it how? Change its type? – Kayaman Aug 22 at 14:12
  • I'd like to modify the value of that column for some of the records in the table. Something like UPDATE table_name SET column_name = xx – MLD Aug 22 at 14:20
  • You have a curious use case in that you want to update column values, but you're not sure if the column exists. How's that possible? If the column doesn't exist and you add it, will you update all its values? Only some and leave the others to null? – Kayaman Aug 22 at 14:25
  • Actually I have to connect to the database from a program written in Java. I have a method within a class to update an existing table. The new column should be created when the method is called for the first time. During the next calls the column should be updated. Thanks to your questions I realise, that what I'm trying to do, it doesn't make any sense from a sql point of view. I think I should create two different methods, one to create the column (which will be called just once) and the other one to update it (which will be called several times). – MLD Aug 22 at 14:42
  • Not from a programming point of view either. Incremental schema changes are best done with tools like Liquibase or Flyway. That way you don't need to put DDL in your program, and you know what your schema looks like at all times. You could check if the column exists inside your program, and then decide what to do. There's really no "add column or update some values" idiom in the db world, so better to keep this on the application side. – Kayaman Aug 22 at 14:47
0

You can use INFORMATION_SCHEMA.COLUMNS to check if the column exists or not.

CREATE TABLE t(rid int, foo int);

INSERT INTO t VALUES (1,1),(2,2),(3,3);

CREATE FUNCTION fnn(id int, new_val int) RETURNS void AS $$
BEGIN

  IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't' AND COLUMN_NAME = 'bar') THEN
      ALTER TABLE t
        ADD bar int;
  END IF;

 UPDATE t
 SET    bar = new_val
 WHERE  id = rid;

END; $$
LANGUAGE plpgsql;


SELECT fnn(1, 15);

SELECT * FROM t;

rid | foo |  bar
--: | --: | ---:
  2 |   2 | <em>null</em>
  3 |   3 | <em>null</em>
  1 |   1 |   15

db<>fiddle here

  • Thanks a lot! That's exactly what I need. – MLD Aug 23 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.