Import existing Postgre database with phpPgAdmin and double single quotes

Question

New to postgre here.

I need to restore a postgre database from a SQL script that has been given to me.

The beginning of the script is:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
/*!40101 SET NAMES  */
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */

-- Listage de la structure de la table public. agreements
CREATE TABLE IF NOT EXISTS "agreements" (
    "id" INTEGER NOT NULL DEFAULT "nextval('agreements_id_seq'::regclass)",
    "text" VARCHAR(255) NOT NULL,
    "created_at" TIMESTAMP NULL DEFAULT 'NULL::timestamp without time zone',
    "updated_at" TIMESTAMP NULL DEFAULT 'NULL::timestamp without time zone',
    PRIMARY KEY ("id")
);

When I try to import this script using phpPgAdmin (PostgreSQL 9.6.22, phpPgAdmin 7.13.0, PHP 8.1.27), I am getting the following error:

ERREUR: syntaxe en entrée invalide pour l'entier : « nextval('agreements_id_seq'::regclass) »

Meaning that there is an invalid input for the number nextval.

I don't understand what's going on, I already removed from the file semicolons that were at the end of the first commented lines that prevented the import too.

Did the person who create the SQL export chose a wrong format?

Answer 1

The correct syntax for nextval is without the double quotes.

Additionally you'll need to remove the single quotes in the timestamp default.

This works tested in 16.1

CREATE TABLE IF NOT EXISTS "agreements" (
    "id" INTEGER NOT NULL DEFAULT  nextval('agreements_id_seq'::regclass),
    "text" VARCHAR(255) NOT NULL,
    "created_at" TIMESTAMP NULL DEFAULT  NULL::timestamp without time zone ,
    "updated_at" TIMESTAMP NULL DEFAULT  NULL::timestamp without time zone ,
    PRIMARY KEY ("id")
);