Getting 'error: syntax error at or near...' in Postgresql insert query

Question

I'm fairly new at Postgresql and learning new things everyday. So I have this blog project where I want to use PostgreSQL as a db. But I'm kind of stuck at the most basic insert query which is throwing an error. I have three tables, posts, authors and categories. I could create the table properly I guess but when I try to insert data I get this error:

error: syntax error at or near 
length: 95,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '122',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1180',
  routine: 'scanner_yyerror'

Now I don't know where the issue is and Postgres' errors are not that specific.

Can anyone please tell me where I could be going wrong?

Here are the tables:

const createInitialTables = `
        CREATE TABLE authors (
             id UUID NOT NULL,
             author_name VARCHAR(100) NOT NULL UNIQUE CHECK (author_name <> ''),
             author_slug VARCHAR(100) NOT NULL UNIQUE CHECK (author_slug <> ''),
             PRIMARY KEY (id)
        );

        CREATE TABLE posts (
             id UUID NOT NULL,
             post VARCHAR(500) NOT NULL CHECK (post<> ''),
             post_slug VARCHAR(500) NOT NULL CHECK (post_slug <> ''),
             author_id UUID NOT NULL,
             PRIMARY KEY (id),
             CONSTRAINT fk_authors FOREIGN KEY(author_id) REFERENCES authors(id)
        );

        CREATE TABLE categories (
             id UUID NOT NULL,
             category_name VARCHAR(50) NOT NULL CHECK (category_name <> ''),
             category_slug VARCHAR(50) NOT NULL CHECK (category_slug <> ''),
             post_id UUID NOT NULL,
             PRIMARY KEY (id),
             CONSTRAINT fk_posts FOREIGN KEY(post_id) REFERENCES posts(id)
        );

`;

Here's the async function where I'm making the insert query:

const insertAuthor = async() => {

    try {

        const data       = await fs.readFile( path.join( __dirname + '../../data/data.json' ) );
        const parsedData = JSON.parse( data.toString() );

        const authorID   = short.generate();
        const authorName = parsedData[ 0 ].author;
        const authorSlug = slugify( parsedData[ 0 ].author, {
            strict: true,
            lower: true
        } );

        const insertData = `
            INSERT INTO authors (id, author_name, author_slug) 
            VALUES 
            (${authorID}, ${authorName}, ${authorSlug});
        `;

        await pool.query( insertData );

        console.log( 'Data inserted successfully!' );

    } catch ( e ) {
        console.log( e );
    }
};

insertAuthor();

UPDATE--------------------------------------

This is how the Postgres log file looks like:

2021-10-18 01:23:16.885 +06 [5964] ERROR:  syntax error at or near "Paton" at character 122
2021-10-18 01:23:16.885 +06 [5964] STATEMENT:  
                INSERT INTO authors (id, author_name, author_slug) 
                VALUES 
                (an3cxZh8ZD3tdtqG4wuwPR, Alan Paton, alan-paton);

Postgres error messages tend to be very specific. Looks like your driver is editing detail out. Take a look at the Postgres log, it should have the information you need. If you don't understand it copy and paste the error message as text as update to your question. — Adrian Klaver
– Adrian Klaver, Commented Oct 17, 2021 at 18:13

Schwern · Accepted Answer · 2021-10-17 20:08:49Z

5

INSERT INTO authors (id, author_name, author_slug) 
VALUES 
(an3cxZh8ZD3tdtqG4wuwPR, Alan Paton, alan-paton);

Your string values are not quoted. It would have to be...

INSERT INTO authors (id, author_name, author_slug) 
VALUES 
('an3cxZh8ZD3tdtqG4wuwPR', 'Alan Paton', 'alan-paton');

You could add quotes to your query, but don't. Your query as written is insecure and vulnerable to a SQL injection attack. Do not insert values into queries with string concatenation.

Instead, use parameters.

const insertSQL = `
  INSERT INTO authors (id, author_name, author_slug) 
  VALUES ($1, $2, $3);
`;
await pool.query( insertSQL, [authorID, authorName, authorSlug] );

Postgres will handle the quoting for you. This is safer, more secure, and faster.

Note that an3cxZh8ZD3tdtqG4wuwPR is not a valid UUID. A UUID is a 128 bit integer often represented as a 32 character hex string.

Note that you also probably want to use autoincrementing primary keys instead of generating the ID yourself. For a UUID primary key, load the uuid-ossp package and use its UUID function as your default.

create extension "uuid-ossp";

create table authors (
  id uuid primary key default uuid_generate_v4(),

  -- There's no point in arbitrarily limiting the size of your text fields.
  -- They will only use as much space as they need.
  author_name text not null unique check (author_name <> ''),
  author_slug text not null unique check (author_slug <> '')
);

insert into authors (author_name, author_slug) 
values ('Alan Paton', 'alan-paton');

edited Oct 17, 2021 at 20:08

answered Oct 17, 2021 at 19:51

Schwern

167k28 gold badges225 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

s.khan Over a year ago

Hey man I'm not getting this error: error: invalid input syntax for type uuid: "hWBoUWD521QZoEUyV9KGtF". I'm using a different package to generate uuid because Postgres' native uuid is too big. I wanted something shorter. Could this be a problem?

Schwern Over a year ago

@s.khan That is not a valid UUID. Yes it is a problem. Why do you think it's too big? Do you need UUID primary keys for such a simple schema?

s.khan Over a year ago

Is it possible to make the native uuid shorter? And will native uuid solve thiis issue?

Schwern Over a year ago

@s.khan A UUID is 128 bits. If you want something shorter, you have to declare your own binary column using bytea. But why?

Schwern Over a year ago

@s.khan You need some sort of primary key, but you can just use simple integers. Postgres has the serial data type which automatically increments.

|

Vedant · Accepted Answer · 2021-10-17 19:43:32Z

1

In the INSERT query, add the string values in quotes -

const insertData = `
    INSERT INTO authors (id, author_name, author_slug) 
    VALUES 
    ('${authorID}', '${authorName}', '${authorSlug}');`;  // added the quotes

answered Oct 17, 2021 at 19:43

Vedant

1194 bronze badges

5 Comments

Schwern Over a year ago

While correct, this is insecure. en.wikipedia.org/wiki/SQL_injection

s.khan Over a year ago

I tried this before. Now I get this error: error: invalid input syntax for type uuid: "fyk6q6EsAqhwUQsXiEf6B2"

Vedant Over a year ago

@Schwern, totally agree with you. The data should be sanitized and binded.

s.khan Over a year ago

@Vedant Could this be because of author table being a foreign key in posts table?

Schwern Over a year ago

@s.khan Like it says, fyk6q6EsAqhwUQsXiEf6B2 is not a valid UUID.

Collectives™ on Stack Overflow

Getting 'error: syntax error at or near...' in Postgresql insert query

2 Answers 2

7 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related