Getting error while importing PostgreSQL database?

Question

CREATE TABLE IF NOT EXISTS "access_log" (
    "id" BIGINT NOT NULL DEFAULT 'nextval(''access_log_id_seq''::regclass)',
    "user_id" BIGINT NOT NULL,
    "platform" SMALLINT NOT NULL,
    "created_at" TIMESTAMP NULL DEFAULT NULL,
    "updated_at" TIMESTAMP NULL DEFAULT NULL,
    "device_id" VARCHAR(255) NULL DEFAULT NULL,
    "location" VARCHAR(255) NULL DEFAULT NULL,
    "ip" VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY ("id"),
    CONSTRAINT "access_log_user_id_foreign" FOREIGN KEY ("user_id") REFERENCES "public"."users" ("id") ON UPDATE NO ACTION ON DELETE NO ACTION
);

I am trying to restore my PostgreSQL using HeidiSQL. It is showing invalid input syntax for type bigint.

By the way, you have the wrong type for your created and updated columns. Should be timestamp with time zone. — Basil Bourque
– Basil Bourque, Commented Jan 5, 2023 at 7:43
Too many quotes ' in your statement, nextval() is a function and not a piece of text. — Frank Heikens
– Frank Heikens, Commented Jan 5, 2023 at 8:17
Unrelated, but: an IP address should be stored in a column defined as inet . Btw: there is no magic optimization when using 255 as the length limit for a varchar compared to e.g. 260 or 274` — user330315
– user330315, Commented Jan 5, 2023 at 8:46

user330315 · Accepted Answer · 2023-01-05 08:46:07Z

1

The call of the nextval() function must not be quoted.

So this:

default 'nextval(''access_log_id_seq''::regclass)'

needs to be:

default nextval('access_log_id_seq'::regclass)

answered Jan 5, 2023 at 8:46

user330315

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

2 Comments

Kapil Dev Singh Over a year ago

I used this default nextval('access_log_id_seq'::regclass) but still not working.

user330315 Over a year ago

@KapilDevSingh: "sill not working" is not a valid Postgres error message. The CREATE TABLE will work just fine if the sequence (and the table users) are created.

Collectives™ on Stack Overflow

Getting error while importing PostgreSQL database?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related