PostgreSQL 10.0 – How to Resolve Upsert Syntax Error

postgresqlsyntaxupsert

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';

Best Answer

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.

`DISTINCT ON()`

Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

So if you were to write,

SELECT myFirstAgg(z)
FROM foo
GROUP BY x,y;

It's effectively

SELECT DISTINCT ON(x,y) z
FROM foo;
-- ORDER BY z;

In that it takes the first z. There are two important differences,

You can also select other columns at no cost of further aggregation..

SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
-- ORDER BY z, k, r, t, v;

Because there is no GROUP BY you can not use (real) aggregates with it.

CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,2,3),
  (1,2,4),
  (1,2,5)
) AS t(x,y,z);

SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- fails, as you should expect.
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- would not otherwise fail.
SELECT myFirstAgg(z), sum(z)
FROM foo
GROUP BY x,y;

Don't forget `ORDER BY`

Also, while I didn't bold it then I will now

Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

Always use an ORDER BY with DISTINCT ON

Using an Ordered-Set Aggregate Function

I imagine a lot of people are looking for first_value, Ordered-Set Aggregate Functions. Just wanted to throw that out there. It would look like this, if the function existed:

SELECT a, b, first_value() WITHIN GROUP (ORDER BY z)    
FROM foo
GROUP BY a,b;

But, alas you can do this.

SELECT a, b, percentile_disc(0) WITHIN GROUP (ORDER BY z)   
FROM foo
GROUP BY a,b;

Postgresql – Improve performance on concurrent UPDATEs for a timestamp column in Postgres

Would a different kind of column be faster? For example an integer

No. timestamp and timestamptz are just unsigned 64-bit integers internally anyway.

Is there some way to not lock the column?

It doesn't lock the column. It takes weak table lock that doesn't really block anything except DDL, and takes a row level lock on the row you're updating.

There is no way to prevent the row level lock. It exists because without it behaviour and ordering concurrent updates would be undefined. We don't like undefined behaviour in RDBMSs.

It only blocks concurrent updates of the same row anyway.

Any other tips to improve this?

Not with the detail provided. There's likely a better way to do what you're trying to do, but it'll probably involve taking a few steps back and looking for a different strategy for solving the underlying problem.

In the specific case of cache invalidation I think you might want to look into LISTEN and NOTIFY. Again though, there just isn't enough info here to go on.

Best Answer

Related Solutions

PostgreSQL – Is There a Type-Safe first() Aggregate Function?

DISTINCT ON()

Don't forget ORDER BY

Using an Ordered-Set Aggregate Function

Postgresql – Improve performance on concurrent UPDATEs for a timestamp column in Postgres

Related Question

`DISTINCT ON()`

Don't forget `ORDER BY`