0

I have a table1 created in the prod environment. I have to add column to an existing table1. Im using the function create of replace to create the table with the suitable ddl. To add the column shall i add it manually on the prod machine and change the ddl. Or changing only the ddl will do it?

Thank you

Jappa
  • 101
  • 8

1 Answers1

0

As far as i know there is no such thing as a create or replace table syntax. that should only work for functions or views.

Use the ALTER TABLE Table1 ADD NewColumn {DATA_TYPE} command.

The way you phrased your question makes me think you use a 3rd party database version control like liquibase. If that is the case leave the initial create DDL alone and just add it as a separate change. If you change the initial ddl it will only affect new databases.

Gabriel Durac
  • 2,610
  • 1
  • 12
  • 13
  • Thank you for your response, so i shall do both. first add the column on table1 of prod machine, second change the ddl to include the new column. Is it what you mean @Gabriel Durac ? https://www.postgresql.org/message-id/1046283935.1014.502.camel%40camel – Jappa Aug 31 '20 at 09:19
  • So are you using liquibase or some other db versioning tool ? – Gabriel Durac Aug 31 '20 at 09:21
  • im using postgresql – Jappa Aug 31 '20 at 09:22
  • `ALTER TABLE` is a DDL command so I am a bit confused – Gabriel Durac Aug 31 '20 at 09:22
  • 1
    what I am suggesting is just run the `ALTER TABLE` in prod and that will add the column. Also run the the same alter command on any other environments you want to add the column on. If you are running automatic db deploys using code, then you need to add a new changeset using the ALTER command. – Gabriel Durac Aug 31 '20 at 09:26