creating a table with column names having brackets

Question

I tried to create a table with postgresql query:

CREATE TABLE customer_account(ID_account integer primary key, customer_name (lastname) text);

but it gives an error message:

ERROR:  syntax error at or near "("
LINE 5: customer_name (lastname) text,

Probably the problem comes from the bracket, and I already tried like

CREATE TABLE customer_account("ID_account" primary key, "customer_name (lastname)" text);

But it also gave me a similar error message.

How to correct the query? I really need to use bracket.

@a_horse_with_no_name I already tried to use " but it didn't work. Or should I add " on the different position? — Dong-gyun Kim, Aug 10 '20 at 13:29

score 1 · Accepted Answer · answered Aug 10 '20 at 13:29

1

Using " will work, but you are missing the data type for your primary key:

CREATE TABLE customer_account
(
  "ID_account" integer primary key, 
             --^ here
  "customer_name (lastname)" text
);

But I strongly suggest you do not use quoted identifiers.

They will give you much more trouble in the long run then they are worth it.

I would recommend to use something like this:

CREATE TABLE customer_account
(
  account_id        integer primary key, 
  customer_lastname text
);

("ID" as a prefix sounds quite strange in English)

answered Aug 10 '20 at 13:29

Thanks. I forgot the add integer in the primary key. I already added the data type and used "", but it doesn't work. I know using quotation but if I have to, what should I do? – Dong-gyun Kim Aug 10 '20 at 13:37
1

Well, apparently you are not showing us the code you are running. As I have show in the [online example](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=ca0dc44b4634f05fe521681d6fc31c0a) my code works. But seriously: don't try any harder, do not use quoted identifiers. Period. – Aug 10 '20 at 13:40

1 Answers1