Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Table name in Firebird has a lot of extra space

Tags:

sql

firebird

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"?

like image 689
Jacobian Avatar asked May 17 '16 10:05

Jacobian


People also ask

Can table name have space?

Blanks spaces are restricted in the naming convention of the database object's name and column name of the table.

How to SELECT a column name with space in SQL?

To select a column name with spaces, use the back tick symbol with column name. The symbol is ( ` `). Back tick is displayed in the keyboard below the tilde operator ( ~).

Can column name contain space in SQL?

Column names can contain any valid characters (for example, spaces).


1 Answers

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/

like image 135
Val Marinov Avatar answered Sep 18 '22 13:09

Val Marinov