Search code examples
postgresqltrigger.io

Create table, starting with digit in postgresql

Can you give me suggestion to create table with starting with digits in postgresql.

Solution

  • use double quotes, eg:

    t=# create table "42 Might be not The be$t idea" (i serial);
CREATE TABLE
t=# \d+ "42 Might be not The be$t idea"
                                             Table "public.42 Might be not The be$t idea"
 Column |  Type   |                                  Modifiers                                  | Storage | Stats target | Descript
ion
--------+---------+-----------------------------------------------------------------------------+---------+--------------+---------
----
 i      | integer | not null default nextval('"42 Might be not The be$t idea_i_seq"'::regclass) | plain   |              |

    Please look close at what it leads to. Generally using mixed case, special characters and starting relation from number is kept a bad practice. Despite the fact that Postgres understands and works with such relation names, you have a risk to hit the bug with other software.

    Without an experience you most probably shoot yourself in the foot. Eg pg_dump -t "badName" won't work. Bash will understand double quotes as own - and it is meant to work this way. So you have to specify pg_dump -t '"badName"' to find the table. And if you just fail to find a table you are lucky. Disaster is when you have badname and Badname in same schema.

    The fact that it is doable does not mean you should jump into using it.