Error in POSTGRESQL Database Creation

Asked

Viewed 516 times

-1

I am having the following error when creating a POSTGRESQL database:

SS

SS

SQL CODE:

CREATE DATABASE pesadaum;

CREATE TABLE user(
    user_id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
    email varchar(255) NOT NULL,
    heavy_pass varchar(255) NOT NULL
);

CREATE TABLE admin_user(
    admin_user_id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
    email varchar(255) NOT NULL,
    heavy_pass varchar(255) NOT NULL,
    admin_name varchar(255) NOT NULL,
    rg varchar(15) NOT NULL,
    cpf varchar(15) NOT NULL,
    access_lvl boolean NOT NULL,
);

CREATE TABLE problem(
    problem_id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
    address point,
    description text
    user_id int NOT NULL REFERENCES user(user_id)
);

CREATE TABLE img(
    img_id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
    url varchar(255) NOT NULL,
    status_acc boolean,
    problem_id int NOT NULL REFERENCES problem_id(problem_id)
);

CREATE TABLE error(
    error_id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
    error_name varchar(255),
    error_description TEXT,
);

CREATE TABLE error_problem_plug(
    problem_id int NOT NULL REFERENCES problem(problem_id),
    error_id int NOT NULL REFERENCES error(error_id)
);
sql postgresql

  • Only one detail not explicitly mentioned: USER is a reserved keyword in both Postgresql and SQL:2016, SQL:2011 and SQL-92. The way around this is to use "Quoted Identifiers", for example: "user" but in this case you will always have to use quotation marks when referencing this field. Note that for Quoted "user", "USER" and "User" are distinct identifiers, which do not happen with common identifiers: foo, FOO or Foo refer to the same identifier.

3 answers

3

This syntax is not from Postgresql, it is from the SQL of another database.

Begins with the AUTO_INCREMENT. The correct would be the use of SERIAL.

There is error that I did not identify, may have occurred as a result of these. You need to learn to interpret the errors on your own, several will happen.

2


Follows his converted scritp, but in an ill-accustomed no :D

CREATE DATABASE pesadaum;

CREATE TABLE "user"(
    user_id  serial NOT NULL ,
    email varchar(255) NOT NULL,
    heavy_pass varchar(255) NOT NULL,
     PRIMARY KEY (user_id)
);

CREATE TABLE admin_user(
    admin_user_id serial  NOT NULL ,
    email varchar(255) NOT NULL,
    heavy_pass varchar(255) NOT NULL,
    admin_name varchar(255) NOT NULL,
    rg varchar(15) NOT NULL,
    cpf varchar(15) NOT NULL,
    access_lvl boolean NOT NULL,
    PRIMARY KEY(admin_user_id)
);

CREATE TABLE problem(
    problem_id serial NOT NULL ,
    address point,
    description text,
    user_id int ,
    PRIMARY KEY(problem_id),
    FOREIGN KEY (user_id) REFERENCES "user" (user_id)
);

CREATE TABLE img(
    img_id serial NOT NULL ,
    url varchar(255) NOT NULL,
    status_acc boolean,
    problem_id int NOT NULL REFERENCES "problem"(problem_id),
    PRIMARY KEY(img_id)
);

CREATE TABLE error(
    error_id serial NOT NULL ,
    error_name varchar(255),
    error_description TEXT,
    PRIMARY KEY (error_id)
);

CREATE TABLE error_problem_plug(
    problem_id int NOT NULL REFERENCES "problem"(problem_id),
    error_id int NOT NULL REFERENCES "error"(error_id)
);

  • haha mto thanks! Really was picking up. I’ve even bought a book of Postgresql :D

1

Error script looks like Mysql.

The first error is syntax error at or near "user" because there is a user object called in portgres.

If you run the "select user" query it will return the connected user.

When you set a table attribute as serial a Quence is created and the default value of the attribute becomes nextval()

The user table script would look like this:

  CREATE TABLE "user"(
user_id  serial NOT NULL ,
email varchar(255) NOT NULL,
heavy_pass varchar(255) NOT NULL,
PRIMARY KEY (user_id));

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.