Import Error world.sql

[strongui]

Trying to import the world.sql file into my PSQL DB and I get this:

ERROR:  relation "public.subregions" does not exist 

SQL state: 42P01

[dosubot]

🤖

The error message you're encountering suggests that the subregions table does not exist in your PostgreSQL database. This error typically occurs when the SQL script tries to insert data into a table that hasn't been created yet.

To resolve this issue, you need to ensure that the subregions table is created before you try to import data into it. Here are the steps you can follow:

1. Create the Table Structure: Before importing data, you need to have the table structure in place. This means you should have a SQL statement that creates the subregions table with all the necessary columns and data types that match the data you're trying to import.

2. Run the Creation Script Before Importing Data: Execute the SQL script that creates the subregions table in your PostgreSQL database before running the import script that inserts data into it.

Here is an example of how you might create the subregions table based on the data snippet you provided. This example assumes the data types based on the provided values but you might need to adjust them according to your full dataset:

CREATE TABLE public.subregions (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    postal_code INT,
    state_code VARCHAR(10),
    country_code VARCHAR(10),
    latitude NUMERIC(10, 8),
    longitude NUMERIC(11, 8),
    created_at TIMESTAMP WITH TIME ZONE,
    updated_at TIMESTAMP WITH TIME ZONE,
    is_active BOOLEAN,
    wikidata_id VARCHAR(255)
);

Please adjust the data types and column names as necessary to match your full dataset requirements. After creating the table with the above SQL command (or a modified version that suits your data), you should be able to import your world.sql file without encountering the "relation does not exist" error.

However, it's important to note that the structure of the subregions table in the world.sql file of the countries-states-cities-database repository is as follows:

CREATE TABLE `subregions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `country_id` int(11) NOT NULL,
  `code` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `region_id` int(11) NOT NULL,
  `iso2` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `latitude` decimal(10,8) NOT NULL,
  `longitude` decimal(11,8) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `flag` tinyint(1) NOT NULL DEFAULT '1',
  `wikiDataId` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

You can find this structure in the sql/world.sql file. Please ensure that the table structure you create matches this structure to avoid any import errors.

Sources

• psql/world.sql
• sql/world.sql

About Dosu

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

[rhnatiuk]

@dosu-bot The answer is not helpful.

The problem is that psql/world.sql was dumped using pg_dump as a text dump, and it can be restored using psql only, not with pg_restore.

In the current psql/world.sql all tables/constraints/etc. are handled in alphabetic order (furthermore, the script tries to delete them without even checking if they exist, for example), and all tables are populated in alphabetic order (cities first) too. But those tables have foreign key constraints, so it won't work.

A possible solution would be to produce a psql/world.dump file using pg_dump instead of a textual dump so that it could be used with pg_restore. But then database version compatibility might become a problem...