Postgresql Syntax Error when attempting to create a type

Question

I am trying to create the following type in postgresql using pg nodejs package. I have written a function that queries the pool and attempts to create this type as follows:

return pool.query(
   `
        CREATE TYPE grade_sheet AS (
            subjectName VARCHAR(100),
            teacherName VARCHAR(100),
            uti VARCHAR(32),
            markAllocated REAL CHECK (markAllocated >= 0.0 AND markAllocated <= 100.00),
            markObtained REAL CHECK (markObtained >= 0.0 AND markObtained <= 100.00),
            gradeObtained CHAR(2),
            dateTaken TIMESTAMP
        );
   `
);

When I am trying to run the script, I get the following syntax error:

{ error: syntax error at or near "CHECK"
at Connection.parseE (/home/zerocool/myschool/node_modules/pg/lib/connection.js:554:11)
at Connection.parseMessage (/home/zerocool/myschool/node_modules/pg/lib/connection.js:379:19)
at Socket.<anonymous> (/home/zerocool/myschool/node_modules/pg/lib/connection.js:119:22)
at Socket.emit (events.js:127:13)
at addChunk (_stream_readable.js:269:12)
at readableAddChunk (_stream_readable.js:256:11)
at Socket.Readable.push (_stream_readable.js:213:10)
at TCP.onread (net.js:590:20)
name: 'error',
length: 95,
severity: 'ERROR',
code: '42601',
detail: undefined,
hint: undefined,
position: '195',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'scan.l',
line: '1087',
routine: 'scanner_yyerror' }

sticky bit · Accepted Answer · 2019-01-02 06:40:50Z

1

Constraints cannot be used in types. But in domains the can. Domains however cannot have multiple attributes. But you can solve your problem by using both:

create a domain including your check constraint
create a type an use the domain

It could look like:

CREATE DOMAIN grade_sheet_real
              real 
              CHECK (value >= 0.0
                     AND value <= 100.00);

CREATE TYPE grade_sheet AS
            (subjectname varchar(100),
             teachername varchar(100),
             uti varchar(32),
             markallocated grade_sheet_real,
             markobtained grade_sheet_real,
             gradeobtained char(2),
             datetaken timestamp);

answered Jan 2, 2019 at 6:40

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Abrar Hossain Over a year ago

Thanks for your answer. Cleared up my doubts and worked perfectly too.

Collectives™ on Stack Overflow

Postgresql Syntax Error when attempting to create a type

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related