Pivotal Knowledge Base


Column Name Changed after Creating a Schema into PostgreSQL by SQLF Command

Applies To:

SQLFire 1.0.x to 1.1.x


This document contains a solution to an issue where a table's column name is changed after creating a schema in a PostgreSQL (or vPostgreSQL) database using the [sqlf write-schema-to-db] command with a schema XML file.


A column name changes unexpectedly after creating a schema into a PostgreSQL (or vPostgreSQL) database using the [sqlf write-schema-to-db] command with a schema XML file.

For example:

a. Schema XML file--sampletable.schema.txt
<?xml version='1.0' encoding='UTF-8'?>
<database xmlns="http://db.apache.org/ddlutils/schema/1.1" name="unnamed">
<table name="SQLFIRE.SAMPLETABLE" description="">
<column name="SAMPLETABLE_CODE" primaryKey="true" required="true" type="VARCHAR" size="20" autoIncrement="false" />
<column name="CM123456789012345678901234567890" primaryKey="false" required="false" type="VARCHAR" size="20" autoIncrement="false" />

b. [sqlf write-schema-to-db] command to create the sampletable schema in a vPostgreSQL database.
sqlf write-schema-to-db \
-files=./sql/sampletable.schema.txt \
-database-type=postgresql \
-driver-class=org.postgresql.Driver \
-url=jdbc:postgresql://postgreshostname:5432/sqlfire \
-do-drops=false \
-user=postgresuser1 \

c. After running the above sqlf command, confirmed the column name in vPostgreSQL changed:

Root Cause:

In PostgreSQL (or vPostgreSQL), NAMEDATALEN is 64, so the maximum identifier length is 63 bytes by default. When a column name is over 32 characters, it hits this limitation of NAMEDATALEN and PostgreSQL changes the column name to be less than 32 characters automatically.


To avoid this issue, you should adjust column names to be less than 32 characters. Alternately, you could change the NAMEDATALEN constant in src/include/pg_config_manual.h and recompile the PostgreSQL(or vPostgreSQL) and allow for longer table names.


1. http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical...

2. http://sqlfire.docs.gopivotal.com/userguide/...sqlf-write-schema-to-db...


Powered by Zendesk