5

So, I created a table in Firebird, using Python fdb library like so:

>>> import fdb

>>> conn = fdb.connect(...)
>>> sql = "CREATE TABLE test_table(id integer not null)"
>>> cursor = conn.cursor()
>>> cursor.execute(sql)
>>> conn.commit()

However, when I list tables, I get this strange result:

>>> tables = []
>>> sql = "select rdb$relation_name from rdb$relations 
       where rdb$view_blr is null and (rdb$system_flag is null or rdb$system_flag = 0)"
>>> cursor.execute(sql)
>>> res = cursor.fetchall()
        for r in res:
            tables.append(r[0])
>>> tables
['TEST_TABLE                     ']

What the heck is going on? Where does this stupid extra space come from? Why my table is named "TEST_TABLE " and not just "TEST_TABLE"?

pilcrow
  • 56,591
  • 13
  • 94
  • 135
Jacobian
  • 10,122
  • 29
  • 128
  • 221

1 Answers1

6

Field:

RDB$RELATION_NAME is CHAR(31)

CHAR is padded with spaces.

The most important difference is that CHAR is padded with spaces and VARCHAR is not. For example, if you have:

CREATE TABLE t1 ( c1 VARCHAR(2), c2 CHAR(2) );

INSERT INTO t1 (c1,c2) VALUES ('a', 'a');

The column c1 will contain value 'a', while column c2 will contain value 'a ' with additional space. Trailing spaces are ignored when doing comparisons, so both columns would >match the

WHERE c = 'a'

clause of some query. Trailing spaces are respected by LIKE operator, which >is a source of confusion for beginners

See : http://www.firebirdfaq.org/faq237/

Community
  • 1
  • 1
Val Marinov
  • 2,705
  • 17
  • 22
  • It is one of those nasty things that you encounter in one database among dozens. Thanks! – Jacobian May 17 '16 at 13:25
  • 1
    It's worth noting that this padding, and the somewhat unexpected behavior that trailing spaces are ignored for `CHAR` equality comparisons, is mandated by standard SQL. Firebird users are well acquainted with the [`TRIM()`](http://www.firebirdsql.org/refdocs/langrefupd20-trim.html) function. – pilcrow May 17 '16 at 15:26
  • Yeah, it would be great if the metadata tables would use varchar instead, but legacy and all that... – Mark Rotteveel May 18 '16 at 13:11