Why am I getting syntax error at or near "@"

Question

When trying to join two tables and update one of them, I'm receiving an unexpected error from this function right here:

CREATE OR REPLACE FUNCTION tsi.update_data(_creation_time int)
RETURNS VOID
AS $$
BEGIN
    EXECUTE format($sql$
        UPDATE tsi.forecasts_%s a SET
        a."incidents @ 01:00" = b.n_incid,
        a."road @ 01:00" = b.n_roads
        FROM tgi_tmp b WHERE a.link_ix = b.link_id;
  $sql$,_creation_time);
END $$ LANGUAGE plpgsql;

It gives me this error message:

syntax error at or near "@"
cidents @ 01:00" = n_incid,
        ^

Do anyone know why I'm getting this error? The tables do contain the mentioned columns, so that is not the problem. Is postgres having a hard time dealing with string-columns in an Execute-format?

Postgres version: 10.5 Simplified table structure:

CREATE TABLE tsi.forecasts_%s (
    link_ix int PRIMARY KEY,
    "slipincidents @ 00:00" SMALLINT NOT NULL,
    "roadcoverage @ 00:00" SMALLINT NOT NULL,
);

and tgi_tmp:

CREATE TEMPORARY TABLE tgi_tmp (
    link_id TEXT,
    n_road SMALLINT,
    n_incid SMALLINT
    CONSTRAINT tgi_tmp_pkey PRIMARY KEY (link_id)
);

@a_horse_with_no_name saving space and no time to reformat to a better structure — Jesper, Sep 11 '18 at 12:03
@Jesper: Obviously, you are losing time this way. Very error prone. Consider legal, lower-case, unquoted names everywhere. *That* saves time. As for your question, missing minimum requirements: table definition, Postgres version. — Erwin Brandstetter, Sep 11 '18 at 12:11
@ErwinBrandstetter Added requirements. Had had a discussion about the structure before: https://stackoverflow.com/questions/51893728/size-of-postgresql-table-increased-on-table-row-or-column — Jesper, Sep 11 '18 at 12:21

score 3 · Answer 1 · answered Sep 11 '18 at 12:01

Weird it complaints about the @ doesn't do that for me. What however is wrong is specifying the table (alias) for the columns you are assigning to in the set. You should only specify the column names.

CREATE OR REPLACE FUNCTION tsi.update_data(_creation_time int)
RETURNS VOID
AS $$
BEGIN
    EXECUTE format($sql$
        UPDATE tsi.forecasts_%s a SET
          "incidents @ 01:00" = b.n_incid,
          "road @ 01:00" = b.n_roads
        FROM tgi_tmp b WHERE a.link_ix = b.link_id;
  $sql$,_creation_time);
END $$ LANGUAGE plpgsql;

Erwin Brandstetter · Answer 2 · 2018-09-11T15:33:29.460

Trying to debug your function, I get these error messages, one after the other:

ERROR:  operator does not exist: integer = text
ERROR:  column b.n_roads does not exist
ERROR:  column "a" of relation "tsi_forecasts_1" does not exist
ERROR:  column "incidents @ 01:00" of relation "tsi_forecasts_1" does not exist

Each after fixing the previous error.
I arrive at this working version:

CREATE OR REPLACE FUNCTION tsi_update_data(_creation_time int)
  RETURNS VOID AS
$func$
BEGIN
    EXECUTE format($sql$
       UPDATE tsi_forecasts_%s a
       SET    "slipincidents @ 00:00" = b.n_incid  -- don't table-qualify target cols
            , "roadcoverage @ 00:00"  = b.n_road   -- col names in q don't match
       FROM   tgi_tmp b
       WHERE  a.link_ix = b.link_id::int; -- your db design forces a cast
  $sql$, _creation_time);
END
$func$  LANGUAGE plpgsql;

But I cannot reproduce your error:

syntax error at or near "@"
cidents @ 01:00" = n_incid,
        ^

Which must be invoked by something that's not in the question, like outer double-quoting or special meaning of characters in your unnamed client program.

All that aside, it might pay to reconsider your naming convention and your db design. Use legal, lower-case, unquoted identifiers and matching data types (link_ix is int while link_ix is text).

It's probably true that there's some unknown configurations that's causing the problems. The db design is fortunately not my making, and is currently being restructured, but needed a quick patch :) — Jesper, Sep 11 '18 at 15:33

score 0 · Accepted Answer · answered Sep 11 '18 at 12:30

Works for some reason when I'm not specifying the offset. Like this:

 CREATE OR REPLACE FUNCTION tsi.update_data(_creation_time int)
 RETURNS VOID

    AS $$
    BEGIN
        EXECUTE format($sql$
            UPDATE tsi.forecasts_%s a SET
            "incidents @ %s" = b.n_incid,
            "road @ %s" = b.n_roads
            FROM tgi_tmp b WHERE a.link_ix = b.link_id;
      $sql$,_creation_time, '01:00', '01:00');
    END $$ LANGUAGE plpgsql;

Why am I getting syntax error at or near "@"

3 Answers3