PostgreSQL UUID Syntax Error – How to Resolve

postgresqlsyntaxuuid

Whever I use a UUID as a literal, I get

SELECT a82857b6-e336-4c6c-8499;
ERROR:  syntax error at or near "-"

I thought it might be an encoding issue, but that doesn't seem to be it.

Best Answer

As documented in the manual, string constants (or in general: anything that is not a number) need to be enclosed in single quotes:

ALTER TABLE newarts ALTER COLUMN jurisdiction_id 
   SET DEFAULT 'a82857b6-e336-4c6c-8499-4242';

Related Solutions

PostgreSQL CREATE TABLE error

You can use reserved words by quoting them:

CREATE TABLE mail_user (
   "user" char(50) NOT NULL,
   domain char(50) NOT NULL,
   password char(50) NOT NULL
);

PostgreSQL – How to Index a UUID

Use PostgreSQL's built-in uuid data type, and create a regular b-tree index on it.

There is no need to do anything special. This will result in an optimal index, and will also store the uuid field in as compact a form as is currently practical.

(Hash indexes in PostgreSQL prior to version 10 were not crash-safe and were really a historical relic that tended to perform no better than a b-tree anyway. Avoid them. On PostgreSQL 10 they've been made crash-safe and had some performance improvements made so you may wish to consider them.)

If for some reason you could not use the uuid type, you would generally create a b-tree on the text representation or, preferably, a bytea representation of the uuid.

Best Answer

Related Solutions

PostgreSQL CREATE TABLE error

PostgreSQL – How to Index a UUID

Related Question