Query failed: ERROR: invalid byte sequence for encoding "UTF8": 0x91

Question

I've been trying to create this table but keep getting the following error

Query failed: ERROR: invalid byte sequence for encoding "UTF8": 0x91

The code I've been working with is:

CREATE TABLE test (
  clientNo     CHAR(4) NOT NULL,
  onDate       DATE,
  atTime       TIME,
  instructorID CHAR(4) NOT NULL,    
  centreID     CHAR(4) NOT NULL,
  status       CHAR(4) CHECK ( status IN ('Fail', ‘Pass’, ‘null’) ),
  reason       VARCHAR(30),
       PRIMARY KEY (clientNo, onDate, atTime),       
       FOREIGN KEY (clientNo) REFERENCES client (clientNo)
                ON UPDATE CASCADE
                ON DELETE CASCADE,
       FOREIGN KEY (instructorID) REFERENCES instructor (instructorID)
                ON UPDATE CASCADE
                ON DELETE CASCADE,
       FOREIGN KEY (centreID) REFERENCES centre (centreID)
                ON UPDATE CASCADE,
       CONSTRAINT testConstraints UNIQUE (instructorID, onDate, atTime)
);

FWIW: `‘Pass’` looks very wrong. Use a real *text* editor, not a word/document editor (as these like to play fancy tricks). Since the error itself complains about the encoding I'd first scrub the text by saving it as ASCII (a real text editor will allow this as well) and reloading the file before pasting it into PG. — user2864740, Mar 24 '15 at 20:35

score 1 · Answer 1 · answered Mar 24 '15 at 21:14

You have two problems here:

You're sending PostgreSQL Windows CP-1252 encoded text when it is expecting UTF-8, hence the "invalid byte sequence" error.
You're trying to quote a string literal with "fancy" quotes rather than the ' that SQL requires. Note that ‘ is 0x91 in Windows CP-1252 encoding.

Both errors are in the same place and can be solved by replacing:

status IN ('Fail', ‘Pass’, ‘null’)

with

status IN ('Fail', 'Pass', 'null')

That assumes, of course, that 'null' is a valid value for your status column. If you really mean null then you'd want:

status is null or status in ('Fail', 'Pass')

Thanks! rectifying the two problems you mentioned definitely solved the error. Two things to learn would be never code this in word and two careful with the quotes. — Bosssuperod, Mar 25 '15 at 08:21

Erwin Brandstetter · Answer 2 · 2017-07-26T03:11:40.400

Aside from the obvious typos and encoding problems pointed out by @user286 and @mu, your use of char(4) and spurious capitalization are suspicious.

About string data types:

Any downsides of using data type "text" for storing strings?

To fix the encoding problem try in your session:

SET client_encoding TO WIN1252;

Make the setting permanent in one or the other way. Depends on your needs. This answer demonstrates a couple of possibilities:

How does the search_path influence identifier resolution and the "current schema"

A proper CREATE TABLE script could look something like this:

CREATE TABLE test (
   test_id       serial PRIMARY KEY
,  client_no     int NOT NULL  -- or varchar(4)?
,  ts            timestamp
,  instructor_id int NOT NULL    
,  centre_id     int NOT NULL
,  status        boolean
,  reason        varchar(30)
,  CONSTRAINT test_client_ts_uni UNIQUE (client_no, ts)
,  FOREIGN KEY (client_no) REFERENCES client (client_no)
            ON UPDATE CASCADE ON DELETE CASCADE
,  FOREIGN KEY (instructor_id) REFERENCES instructor (instructor_id)
            ON UPDATE CASCADE ON DELETE CASCADE
,  FOREIGN KEY (centre_id) REFERENCES centre (centre_id)
            ON UPDATE CASCADE
,  CONSTRAINT test_instr_ts_uni UNIQUE (instructor_id, ts)
);

Query failed: ERROR: invalid byte sequence for encoding "UTF8": 0x91

2 Answers2