1

SQL Alter script:

alter table table_name ADD COLUMN IF NOT EXISTS chk_col jsonb not null 
DEFAULT '{}'::jsonb;

In this script, I am getting "ERROR: Already present: The column already exists" but the column chk_col is not present in the table.

If I remove DB and then create again the same script is executed successfully.

How do I correct it without removing the database?

lazyakshay
  • 11
  • 2

1 Answers1

0

Can you explain more your setup ? What version are you using ? Are there needed other steps to reproduce ? I can't reproduce in 2.7.1.1:

yugabyte=# create database fshije
yugabyte=# \c fshije

fshije=# create table table_name(id bigint);
fshije=# alter table table_name ADD COLUMN IF NOT EXISTS chk_col jsonb not null DEFAULT '{}'::jsonb;
fshije=# \d table_name
               Table "public.table_name"
 Column  |  Type  | Collation | Nullable |   Default   
---------+--------+-----------+----------+-------------
 id      | bigint |           |          | 
 chk_col | jsonb  |           | not null | '{}'::jsonb
dh YB
  • 965
  • 3
  • 10
  • Version is 2.7.1.1. No other steps are required to reproduce this, we are running this alter query as a migration script in our env. Though, I got this error in local after running alter script(add column, drop column) few times. – lazyakshay Jul 29 '21 at 09:32
  • Can you send a script to reproduce this somehow ? Did you hit it once or multiple times ? Because it looks similar to this issue that was fixed in 2.7.1.1 https://github.com/yugabyte/yugabyte-db/issues/5543 – dh YB Jul 29 '21 at 15:38
  • Yes, getting the same issue that you have mentioned above. First I ran an incorrect alter script then the correct one and got this issue. How do I correct it without creating the database again? steps to reproduce: 1. alter table table_name ADD COLUMN IF NOT EXISTS chk_col2 boolean not null 2. alter table table_name ADD COLUMN IF NOT EXISTS chk_col2 boolean not null default false in 2nd step getting this error. – lazyakshay Jul 30 '21 at 06:16
  • You can try by dropping the table. What are you getting with `\d table_name` ? – dh YB Jul 30 '21 at 14:42