# Adding column generates error

**URL:** <https://community.cratedb.com/t/adding-column-generates-error/1104>\
**Category:** SQL\
**Created:** [May 10, 2022, 8:21am UTC](https://community.cratedb.com/t/adding-column-generates-error/1104 "2022-05-10T08:21:30Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![wvdamme](https://avatars.discourse-cdn.com/v4/letter/w/db5fbb/32.png) [@wvdamme](https://community.cratedb.com/u/wvdamme)\
**Post date:** [May 10, 2022, 8:21am UTC](https://community.cratedb.com/t/adding-column-generates-error/1104/1 "2022-05-10T08:21:30Z")

</div>

The instruction: ALTER TABLE {tablename} ADD {column name} double precision; generates the error: mapper has different length\_limit settings, current [512], merged [null]. What does this mean?

The instruction works in our development environment but fails in production.

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [May 10, 2022, 8:24am UTC](https://community.cratedb.com/t/adding-column-generates-error/1104/2 "2022-05-10T08:24:24Z")

</div>

Hi @wvdamme

Could I ask you provide some more information?

- CrateDB Version
- table schema (i.e. `SHOW CREATE TABLE <table_name>`)

---

<div class="post-metadata">

**Author:** ![wvdamme](https://avatars.discourse-cdn.com/v4/letter/w/db5fbb/32.png) [@wvdamme](https://community.cratedb.com/u/wvdamme)\
**Post date:** [May 10, 2022, 10:14am UTC](https://community.cratedb.com/t/adding-column-generates-error/1104/3 "2022-05-10T10:14:46Z")

</div>

CrateDB version:

```auto
CrateDB 4.7.0 (built 033afc5/2022-01-26T08:07:43Z
Linux 4.19.0-18-amd64 amd64
OpenJDK 64-Bit Server VM 17.0.1+12)

```

#### Table script

```sql
CREATE TABLE IF NOT EXISTS "data"."meterreading" (
   "servicepointassetenergytixcode" VARCHAR(512),
   "datetime" TIMESTAMP WITH TIME ZONE,
   "recalculatedconsumption1" DOUBLE PRECISION,
   "recalculatedconsumption2" DOUBLE PRECISION,
   "recalculatedinjection1" DOUBLE PRECISION,
   "recalculatedinjection2" DOUBLE PRECISION,
   "recalculatedactualconsumption" DOUBLE PRECISION,
   "recalculatedactualinjection" DOUBLE PRECISION,
   "recalculatedtotalapparentpower" DOUBLE PRECISION,
   "recalculatedtotalreactivepower" DOUBLE PRECISION,
   "recalculatedactivepowerphase1" DOUBLE PRECISION,
   "recalculatedactivepowerphase2" DOUBLE PRECISION,
   "recalculatedactivepowerphase3" DOUBLE PRECISION,
   "recalculatedvoltagel1l2" DOUBLE PRECISION,
   "recalculatedvoltagel2l3" DOUBLE PRECISION,
   "recalculatedvoltagel3l1" DOUBLE PRECISION,
   "recalculatedvoltagellavg" DOUBLE PRECISION,
   "recalculatedvoltagel1n" DOUBLE PRECISION,
   "recalculatedvoltagel2n" DOUBLE PRECISION,
   "recalculatedvoltagel3n" DOUBLE PRECISION,
   "recalculatedvoltagelnavg" DOUBLE PRECISION,
   "recalculatedcurrentphase1" DOUBLE PRECISION,
   "recalculatedcurrentphase2" DOUBLE PRECISION,
   "recalculatedcurrentphase3" DOUBLE PRECISION,
   "recalculatedcurrentavg" DOUBLE PRECISION,
   "recalculatedtotalpowerfactor" DOUBLE PRECISION,
   "recalculatedfrequency" DOUBLE PRECISION,
   "recalculateddatagramdatetime" TIMESTAMP WITH TIME ZONE,
   "recalculatedproduction1" DOUBLE PRECISION,
   "recalculatedproduction2" DOUBLE PRECISION,
   "recalculatedactualproduction" DOUBLE PRECISION,
   "recalculatedpulsequantity2" DOUBLE PRECISION,
   PRIMARY KEY ("servicepointassetenergytixcode", "datetime")
)
CLUSTERED INTO 6 SHARDS
WITH (
   "allocation.max_retries" = 5,
   "blocks.metadata" = false,
   "blocks.read" = false,
   "blocks.read_only" = false,
   "blocks.read_only_allow_delete" = false,
   "blocks.write" = false,
   codec = 'default',
   column_policy = 'strict',
   "mapping.total_fields.limit" = 1000,
   max_ngram_diff = 1,
   max_shingle_diff = 3,
   number_of_replicas = '0-1',
   "routing.allocation.enable" = 'all',
   "routing.allocation.total_shards_per_node" = -1,
   "store.type" = 'fs',
   "translog.durability" = 'REQUEST',
   "translog.flush_threshold_size" = 536870912,
   "translog.sync_interval" = 5000,
   "unassigned.node_left.delayed_timeout" = 60000,
   "write.wait_for_active_shards" = '1'
)

```

i have seen that the datatype of the field `servicepointassetenergytixcode` is `varchar(512)` in our development is this `Text`. Could this be the reason? How can we change this datatype?

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [May 10, 2022, 11:23am UTC](https://community.cratedb.com/t/adding-column-generates-error/1104/4 "2022-05-10T11:23:30Z")

</div>

> [@wvdamme](#):
>
> `"datetime" TIMESTAMP WITH TIME ZONE,`

Hi @wvdamme

This unfortunately seems to be a bug with CrateDB, when **VARCHARs** are used as **PRIMARY KEY**.

I created an [issue](https://github.com/crate/crate/issues/12492) in the `crate/crate` repository:

Thank you for reporting this.

* * *

**VARCHAR** as primary key leads to an exception

```sql
CREATE TABLE tab ( 
        x VARCHAR(512), 
        PRIMARY KEY(x) 
        );                                                                                                                                                                                  
--CREATE OK, 1 row affected (2.033 sec)
ALTER TABLE tab ADD COLUMN y DOUBLE;                                                                                                                                                    
--SQLParseException[mapper [x] has different length_limit settings, current [512], merged [null]]

```

* * *

**TEXT** as primary key works as expected

```sql
CREATE TABLE tab ( 
        x TEXT, 
        PRIMARY KEY(x) 
        );                                                                                                                                                                                  
--CREATE OK, 1 row affected (1.806 sec)
ALTER TABLE tab ADD COLUMN y DOUBLE;                                                                                                                                                    
--ALTER OK, -1 rows affected (0.218 sec)

```