Alternative word delimiter than underscore (_) for PostgreSQL objects (fields, tables, etc.) which doesn't require me to use quotes ("") to query?

Question

Is there any alternative word delimiter other than underscore (_) for PostgreSQL objects (fields, tables, etc.) which doesn't require me to use quotes ("") to query?

The special delimiter will be used to separate identically named field names based on their source table. I don't want to use schemas or different tables for this. Some dummy example field names would be:

product_line^product_name
product_line^product_description
service_station^station_name

I've tested out hat (^) and pipe (|), and they don't work.

CREATE TABLE "my^test^table" ( "my^test^field" text )

(I used a GUI to build the tables, and I just now notice that you have to quote the names to even create the table/field.)

Here is what I have to do to query it:

SELECT "my^test^field" FROM "my^test^table"

... while I'd like to do:

SELECT my^test^field FROM my^test^table

... which is possible if I've used underscores (_):

SELECT my_test_field FROM my_test_table

Are there any alternative delimiters other than underscore (_) which could work? Preferably a normal ASCII character which isn't alphanumeric.

Edit: My best alternative at the moment is to use double underscore. But, I'd prefer something else.

Answer 1

Based on the answers here I've decided not to use a different character as a prefix delimiter. I'm going with a double underscore (__), which is nicely parsable.

I should have been more clear on why I ultimately wanted this. The various comments I've left here explain it.

Here is an example which is pretty close to what we're probably ending at:

CREATE VIEW flat_data AS
SELECT 
    hub.id,
    idx_dly.ror_sd AS idx_dly__ror_sd,
    fx_dly.ror_sd AS fx_dly__ror_sd
FROM 
    hub
JOIN
    index_daily_movement AS idx_dly ON hub.id = idx_dly.hub_id
JOIN
    forex_daily_movement AS fx_dly ON hub.id = fx_dly.hub_id
...

In the application layer it is now possible to do things like

field_name = ... e.g. 'idx_dly__ror_sd'
table_abr, base_field_name = field_name.split('__')

Answer 2

Please, dont take this answer as advice for action. I think you are doing things that will make your life more complicated. But yet what you are looking for is doable. To get the list of accepted not quoted chars, I run:

t=# create schema s;
CREATE SCHEMA
Time: 0.931 ms
t=# do
$$
declare i record;
begin
for i in 1..255 loop
  begin
  execute format ('create table s.%s(i int)',chr(i)); --I deliberately don't use %I - so it would not add dblquotes for Identifier
  exception when others then NULL;
  end;
end loop;
end;
$$
;
DO
Time: 49.443 ms
t=# select tablename from pg_tables where tablename !~ '[aA-zZ]';

this will give you the list of characters accepted. Based on which you can experiment, eg:

t=# create table a©b (i int);
CREATE TABLE
Time: 2.920 ms
t=# select * from a©b;
 i
---
(0 rows)

or

t=# create table a­-b (i int);
CREATE TABLE
Time: 1.707 ms
t=# select * from a-­b;
 i
---
(0 rows)

Mind it is no hyphen (45), it is (173) one:

t=# select ascii('­-'),ascii('-');
 ascii | ascii
-------+-------
   173 |    45
(1 row)

So any of ÷, ® © ¤ £ ¡¦ and so on would do the trick. But now look at them and think - maybe you should solve different task?..

Answer 3

You could use dollar signs. From the documentation:

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable.

I do not think it is a good idea.

I often use thematic schemas. Gather objects (tables, views, functions etc) belonging to specific submodels in separate schemas, e.g.:

inventory.items
inventory.get_items()
inventory.document_workflows
sale.contractors
sale.local_orders
etc

where inventory and sale are schemas.