Question 113672 in PostgreSQL Question posted in
The official documentation can be found here.

Can't create table with PostgreSQL including "#" (npgsql.NET)

So I’ve actually got two issues with PostgreSQL. I actually use npgsql.NET to create queries, connections, other with PostgreSQL, however I am new to this database software.

First Issue

I got the error:

Npgsql.PostgresException: '42601: syntax error at or near "#"

POSITION: 16'

after using the script:

CREATE TABLE {textBox1.Text} (
    user_id serial PRIMARY KEY,
    username VARCHAR ( 50 ) UNIQUE NOT NULL,
    password VARCHAR ( 50 ) NOT NULL,
    email VARCHAR ( 255 ) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
        last_login TIMESTAMP
);

The textbox1.Text included: Pronner#2223.

Second Issue

When creating a table with the name PRONNER for example, it shows up as pronner. What’s wrong with the capitalization system? Or can it possibly be because I’m using pgAdmin 4 so I just see it as lower case there?

I’m quite new to this like I mentioned at the beginning of the issue, and I used to use MySQL so the syntax is a teensy bit different, but the system is very different.

Tags:

2

Answers


  1. 0

    As documented in the manual the # sign is not allowed in a SQL identifier:

    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,

    The lower case names are also documented in the chapter:

    Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)

    Login or Signup to reply.
  2. 0

    The doc says

    There is a second kind of identifier: the delimited identifier or
    quoted identifier. It is formed by enclosing an arbitrary sequence of
    characters in double-quotes ("). A delimited identifier is always an
    identifier, never a key word.

    So if you want to use uppercase and any special character, you must (always) double quote the name:

    create table "TE#st" (id integer);
select * from "TE#st";
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search