databasepostgresqldockerdockerfilepostgresql-9.4

ERROR: type "..." does not exist in Postgresql

I get this error:

Caused by: org.postgresql.util.PSQLException: ERROR: type 
"tool_parse_numbers_record" does not exist
Where: compilation of PL/pgSQL function "tool_parse_numbers" near line 2

I am restoring my database in a docker container like this:

FROM postgres:9.4
ENV POSTGRES_USER iwb
ENV POSTGRES_PASSWORD 1907
ENV POSTGRES_DB iwb

ADD ./1_schema.sql /docker-entrypoint-initdb.d/
ADD ./2_data.sql /docker-entrypoint-initdb.d/

Here's type definition in schema.sql file:

CREATE TYPE tool_parse_numbers_record AS (
  satir character varying(4000)
);

Here's top part of function definition in schema.sql:

CREATE FUNCTION tool_parse_numbers(pstr text, pdelimeter text DEFAULT 
'|'::text) RETURNS SETOF tool_parse_numbers_record
LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE

And this is how database is restored:

CREATE FUNCTION tool_parse_numbers(pstr text, pdelimeter text DEFAULT 
'|'::text) RETURNS SETOF tool_parse_numbers_record
LANGUAGE plpgsql SECURITY DEFINER
AS $BODY$
DECLARE

EDIT: I changed dockerfile to create types before functions:

ADD ./1_type.sql /docker-entrypoint-initdb.d/
ADD ./2_table.sql /docker-entrypoint-initdb.d/
ADD ./3_func.sql /docker-entrypoint-initdb.d/
ADD ./4_rest_table.sql /docker-entrypoint-initdb.d/
ADD ./5_data.sql /docker-entrypoint-initdb.d/

How do I fix it?

Solution

  • When I extracted the database to schema.sql file, owner was set to postgres:

        ALTER FUNCTION iwb.tool_parse_numbers(pstr text, pdelimeter text) OWNER TO postgres;

    So I changed owner to iwb since it's like that in docker file

        ENV POSTGRES_USER iwb

    And it worked!