Syntax error using column names with spaces in Postgres

Question

I am trying to create tables under a schema. However, I am getting following error.

[Code: 0, SQL State: 42601] ERROR: syntax error at or near "VARCHAR" Position: 120 [Script position: 120 - 123]

I am not able to debug where is the syntax error. Below you can find the postgres query.

CREATE SCHEMA IF NOT EXISTS ebi_mut_db;

        CREATE TABLE IF NOT EXISTS ebi_mut_db.version_info (
        version INT, 
        download_date DATE, 
        download_url TEXT, 
        responsible TEXT);

        CREATE TABLE IF NOT EXISTS ebi_mut_db.mutations_affecting_interactions (
        Feature AC NOT NULL VARCHAR (20), 
        Feature short label VARCHAR (50), 
        Feature range(s) VARCHAR (50), 
        Original sequence CHAR (50), 
        Resulting sequence CHAR (50), 
        Feature type VARCHAR (100), 
        Feature annotation VARCHAR (255), 
        Affected protein AC VARCHAR (100), 
        Affected protein symbol VARCHAR (100), 
        Affected protein full name VARCHAR (100), 
        Affected protein organism VARCHAR (100), 
        Interaction participants VARCHAR (255), 
        PubMedID VARCHAR (100), 
        Figure legend VARCHAR (50), 
        Interaction AC VARCHAR (50),
        PRIMARY KEY (Feature AC);

Thanks

Unrelated to your problem, but: don't use the char data type — user330315
– user330315, Commented Jan 14, 2020 at 15:08

user330315 · Accepted Answer · 2020-01-14 15:09:47Z

You can't have column names with spaces in them. If you really want to make your life harder then it needs to be you have to quote them, e.g. "Feature AC" instead of Feature AC

And the NOT NULL attributes goes after the datatype,

"Feature AC" VARCHAR (20) NOT NULL,

You are also missing a closing ) at the end of the CREATE TABLE Statement:

And please forget that the char type exists. It has absolutely no advantages whatsoever but has some nasty surprises.

So the whole statement should be:

CREATE TABLE IF NOT EXISTS ebi_mut_db.mutations_affecting_interactions 
(
  "Feature AC" VARCHAR (20) NOT NULL, 
  "Feature short label" VARCHAR (50), 
  "Feature range(s)" VARCHAR (50), 
  "Original sequence" VARCHAR (50), 
  "Resulting sequence" VARCHAR (50), 
  "Feature type" VARCHAR (100), 
  "Feature annotation" VARCHAR (255), 
  "Affected protein AC" VARCHAR (100), 
  "Affected protein symbol" VARCHAR (100), 
  "Affected protein full name" VARCHAR (100), 
  "Affected protein organism" VARCHAR (100), 
  "Interaction participants" VARCHAR (255), 
  "PubMedID" VARCHAR (100), 
  "Figure legend" VARCHAR (50), 
  "Interaction AC" VARCHAR (50),
  PRIMARY KEY (Feature AC)
);

But you should, really, really re-think your column names. I would strongly recommend using something like this:

CREATE TABLE IF NOT EXISTS ebi_mut_db.mutations_affecting_interactions 
(
  feature_ac varchar (20) not null, 
  feature_short_label varchar (50), 
  feature_range varchar (50), 
  original_sequence varchar (50), 
  resulting_sequence varchar (50), 
  feature_type varchar (100), 
  feature_annotation varchar (255), 
  affected_protein_ac varchar (100), 
  affected_protein_symbol varchar (100), 
  affected_protein_full_name varchar (100), 
  affected_protein_organism varchar (100), 
  interaction participants varchar (255), 
  pub_med_id varchar (100), 
  figure_legend varchar (50), 
  interaction_ac varchar (50),
  primary key (feature ac)
);

Collectives™ on Stack Overflow

Syntax error using column names with spaces in Postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related