Postgresql Syntax Error when attempting to create a type

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

asked Jan 2 at 6:23

Abrar Hossain

3472520

add a comment |

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

asked Jan 2 at 6:23

Abrar Hossain

3472520

add a comment |

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

asked Jan 2 at 6:23

Abrar Hossain

3472520

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

node.js postgresql

asked Jan 2 at 6:23

Abrar Hossain

3472520

asked Jan 2 at 6:23

Abrar Hossain

3472520

asked Jan 2 at 6:23

Abrar Hossain

3472520

asked Jan 2 at 6:23

Abrar Hossain

3472520

asked Jan 2 at 6:23

Abrar Hossain

3472520

add a comment |

1 Answer
1

active

oldest

votes

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 at 6:40

sticky bit

15.1k101632

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

– Abrar Hossain
Jan 2 at 7:20

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54002085%2fpostgresql-syntax-error-when-attempting-to-create-a-type%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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 at 6:40

sticky bit

15.1k101632

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

– Abrar Hossain
Jan 2 at 7:20

add a comment |

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 at 6:40

sticky bit

15.1k101632

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

– Abrar Hossain
Jan 2 at 7:20

add a comment |

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 at 6:40

sticky bit

15.1k101632

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 at 6:40

sticky bit

15.1k101632

answered Jan 2 at 6:40

sticky bit

15.1k101632

answered Jan 2 at 6:40

sticky bit

15.1k101632

answered Jan 2 at 6:40

sticky bit

15.1k101632

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

– Abrar Hossain
Jan 2 at 7:20

add a comment |

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

– Abrar Hossain
Jan 2 at 7:20

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

– Abrar Hossain
Jan 2 at 7:20

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Bdtjtk