1

Postgres 10.0

table:

pcr=> \d tableFindOld
                               Table "public.tablefindold"
  Column  |            Type             | Collation | Nullable |         Default
----------+-----------------------------+-----------+----------+-------------------------
 hostname | character varying(16)       |           | not null |
 lastmiss | timestamp without time zone |           |          |
 lastfind | timestamp without time zone |           |          |
 uname    | character varying(64)       |           |          | NULL::character varying
Indexes:
    "tablefindold_pkey" PRIMARY KEY, btree (hostname)

upsert statement:

INSERT INTO tableFindOld(hostname, lastMiss, lastFind, uname) 
VALUES('pcname', DEFAULT, '2019-03-04 11:38:04', 'billy') 
ON CONFLICT (tableFindOld.hostname) 
    DO UPDATE SET lastFind='2019-03-04 11:38:04', uname='billy' 
    WHERE tableFindOld.hostname='pcname';

error:

ERROR:  syntax error at or near ")"
LINE 1: ...8:04', 'billy') ON CONFLICT (tableFindOld.hostname) DO UPDAT...
                                                             ^

I have tried googling this to no avail. A few questions:

  • What is the specific error here? (Really asking Why am I getting this error?)
  • In upsert statements, for a column conflict condition (in this example it is the primary key conflict) is the column name supposed to be enclosed in parantheses or not? I have seen some inconsistent info on this.
  • In an upsert statment with an update, are we supposed to include the DO keyword or not? Also have seen inconsistent info on this.

Also, I created the table with DEFAULT NULL on all columns except the primary key, so I'm not sure why that's not reflected in the description.

Thanks in advance for your help.

EDIT: Here is my table creation code:

CREATE TABLE tableFindOld(
    hostname VARCHAR(16) PRIMARY KEY,
    lastMiss TIMESTAMP DEFAULT NULL,
    lastFind TIMESTAMP DEFAULT NULL,
    uname VARCHAR(64) DEFAULT NULL
);

SOLUTION: Thanks to McNets for this answer. For reference, the working statement:

INSERT INTO tableFindOld(hostname, lastMiss, lastFind, uname) VALUES('pcname', DEFAULT, '2019-03-04 12:30:04', 'billy') ON CONFLICT (hostname) DO UPDATE SET lastFind='2019-03-04 12:30:04', uname='billy' WHERE tableFindOld.hostname='pcname';
0

Remove table name in:

ON CONFLICT (hostname)

db<>fiddle here

Quoted from docs:

ON CONFLICT Clause

[ ON CONFLICT [ conflict_target ] conflict_action ]

conflict_target can perform unique index inference. When performing inference, it consists of one or more index_column_name columns and/or index_expression expressions, and an optional index_predicate. All table_name unique indexes that, without regard to order, contain exactly the conflict_target-specified columns/expressions are inferred (chosen) as arbiter indexes.

  • McNets, thanks so much. I tried without table name before but I must have had a separate error. Thanks also for the dbfiddle link. I will bookmark that. – Justin Adams Mar 4 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.