Error import postgres database

Question

I have a databse dump db.sql.schema I import it using psql -U user dbname < ~/db.sql.schema After import in my DB I see table mytable but not mytable_id_seq.

# \d mytable
 Table mytable
 Column | Type | Collation | Nullable | Default 
-------------------+--------------+-----------+----------+---------
 id | integer | | not null | 
 field1 | integer | | not null | 
 field2 | integer | | not null | 
 field3 | integer | | not null | 

I got the follwong error:

# INSERT INTO mytable (field1, field2, field3) VALUES (1, 1, 1) RETURNING mytable.id;
ERROR: null value in column "id" violates not-null constraint
DETAIL: Failing row contains (null, 1, 1, 1).

My postgres version is 10.12 It looks like auto-increment of id doesn't works. Is it a postgres issue or issue with my dump? Is it loo old or too new?

• sluge: I got an error because of CREATE SEQUENCE qqq AS smallint; ERROR: syntax error at or near "AS" LINE 1: CREATE SEQUENCE qqq AS smallint; But my postgres10.12 should support AS data_type!

Answer 1

You are trying to import a dump from a recent PostgreSQL version into an older PostgreSQL server. That is not supported.

Your only option is to manually edit the dump and fix any syntax that the older server doesn't understand.

Check for the first error message you get when restoring the dump, later ones can be consequences of the first one.

• sluge: Yes, you are right. I previously I checked the postgres version by psql -V but in my case psql was 10.12, but postgres itself was 9.5!