CODEWITHSUNDEEP
cassandracql
alan
alan

Reputation: 31

Does it have an equivalent query in cql/cassandra for "alter table add column if column not exists"?

I need to add the column for table that if the column is not existed.

I ran the Alter Table <table> add <column_name> <type>; however, it will have this error message if the column already exists.

InvalidRequest: Error from server: code=2200 [Invalid query] message="Invalid column name <column_name> because it conflicts with an existing column"

Does it have a way to do a similar check on whether the column exists or not?

Thank

Upvotes: 3

Views: 501

Answers (2)

Tasos P.
Tasos P.

Reputation: 4114

According to the official documentation of Cassandra 4.1 you can use the IF NOT EXISTS clause, i.e.:

ALTER TABLE addamsFamily ADD IF NOT EXISTS gravesite varchar;

I quote (emphasis mine):

The ALTER TABLE statement can:

ADD a new column to a table. The primary key of a table cannot ever be altered. A new column, thus, cannot be part of the primary key. Adding a column is a constant-time operation based on the amount of data in the table. If the new column already exists, the statement will return an error, unless IF NOT EXISTS is used in which case the operation is a no-op.

[...]

For versions before 4.1, you need to use Aaron's answer based on system_schema.

Upvotes: 3

Aaron
Aaron

Reputation: 57748

So I checked Tasos's answer, and it works with Apache Cassandra 4.1. It does not work with 4.0 or any version prior to that.

If you're using an older version, you can try querying system_schema like this:

SELECT COUNT(*) FROm system_schema.columns
WHERE keyspace_name = 'nosql1' AND table_name = 'users' AND column_name='description';

If you're trying to do something programatically, you can check whether or not that query returns 0 or 1, and then apply the ALTER TABLE command.

Upvotes: 2

Related Questions