Identifier Hash Character Support

1. Purpose

In Oracle compatibility mode, IvorySQL allows unquoted identifiers to contain #. This supports migration of Oracle SQL using names such as STATISTIC# and reduces the need to add double quotes around table and column names.

2. Function Description

2.1. Identifier Rules

# can appear within or at the end of an unquoted identifier, but cannot be its first character. Existing rules for the first character remain unchanged; for example, a name can start with an ASCII letter or underscore.

Example Description

statistic#

Column name ending with #

col#1, my#column

Column names containing # within the name

test#table

Table name containing #

col_$#4

Combination of an underscore, $, #, and a digit

$ was already allowed after the first character; this feature adds #. Including # does not change identifier case handling or length rules. Quoted identifiers still follow the existing double-quote and case-conversion rules; see Quoted Identifier Case Conversion.

2.2. Coexistence with Operators

# remains a bitwise XOR operator, and #>, #>>, and #- remain JSON path extraction or deletion operators. The lexer distinguishes these operators from # within names, allowing forms without spaces such as jsonb#>>'{b}'.

If an identifier ending with # is immediately followed by > or -, the trailing # is interpreted as the start of an operator. Write comparisons and subtraction as statistic# > 0 and statistic# - 1, using spaces to separate the tokens. Similarly, a#b is one identifier; write a # b to apply XOR to a and b.

3. Examples

Run the following examples in a psql session in Oracle compatibility mode.

3.1. Create and Query Columns Containing #

CREATE TABLE test_hash_ident (
    statistic# INT,
    col#1 INT,
    my#column INT
);
INSERT INTO test_hash_ident VALUES (1, 2, 3);
SELECT statistic#, col#1, my#column FROM test_hash_ident;
 statistic# | col#1 | my#column
------------+-------+-----------
          1 |     2 |         3
(1 row)

Subsequent references do not require double quotes solely because of #. Use spaces to separate column names from operators in comparisons and arithmetic expressions:

SELECT statistic# - 1 AS previous_statistic
FROM test_hash_ident
WHERE statistic# > 0;

The query returns one row with previous_statistic equal to 0.

3.2. Create a Table Name and a Mixed-Character Column Name Containing #

CREATE TABLE test#table (col_$#4 INT);
INSERT INTO test#table VALUES (100);
SELECT col_$#4 FROM test#table;

The query returns one row with col_$#4 equal to 100. These names can be used directly in table creation, inserts, and queries.

3.3. Use XOR and JSON Operators

SELECT 5 # 3 AS xor_result;
SELECT 5#3 AS xor_no_spaces;
SELECT B'1010' # B'1100' AS bit_xor;

SELECT '{"a":1}'::json#>'{a}' AS json_path_no_space;
SELECT '{"b":2}'::jsonb#>>'{b}' AS jsonb_path_no_space;
SELECT '{"a":1,"b":2}'::jsonb#-'{a}' AS jsonb_delete_no_space;

The XOR results are 6, 6, and 0110, respectively. The JSON operations return JSON value 1, text 2, and {"b": 2}, respectively. Allowing # within identifiers preserves the meaning of these operators.

DROP TABLE test#table;
DROP TABLE test_hash_ident;

4. Usage Notes

  • This feature applies to identifiers handled by the Oracle compatibility parser. It does not imply that PostgreSQL mode accepts the same unquoted syntax.

  • A # within a name is part of the identifier and must be retained when referencing the object.

  • When a name ending with # is adjacent to an operator, use spaces to make the boundary explicit and avoid interpreting part of the name as a JSON operator.