Pivotal Knowledge Base

Follow

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

Applies To:

SQLFire 1.0.x to 1.1.x

Purpose:

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.

Symptom:

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" />
</table>
</database>

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 \
-password=postgresuserpass

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

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.

Solution:

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.

Reference:

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

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

Comments

Powered by Zendesk