Rails syntax error after schema load with PostgreSQL

Question

i have a problem and i am not able to understand neither to find a solution elsewhere. The thing is that, when i am trying to load the schema with

load "#{Rails.root}/db/sctructure.sql"

errors appear. The postgresql version is 9.5.1 and the pg gem is 0.8.1. As i can understand pg gem cannot recognise the comments of the dump file. Have you ever run into something similar?

    SyntaxError: /Users/user/Documents/workspace/app/db/structure.sql:2: syntax error, unexpected tIDENTIFIER, expecting keyword_do or '{' or '('
-- PostgreSQL database dump
                      ^
/Users/user/Documents/workspace/app/db/structure.sql:5: syntax error, unexpected tIDENTIFIER, expecting keyword_do or '{' or '('
-- Dumped from database version 9.5.1
              ^
/Users/user/Documents/workspace/app/db/structure.sql:5: unexpected fraction part after numeric literal
-- Dumped from database version 9.5.1
                                   ^
/Users/user/Documents/workspace/app/db/structure.sql:6: syntax error, unexpected tIDENTIFIER, expecting keyword_do or '{' or '('
-- Dumped by pg_dump version 9.5.1
            ^
/Users/user/Documents/workspace/app/db/structure.sql:6: unexpected fraction part after numeric literal
-- Dumped by pg_dump version 9.5.1
                                ^
/Users/user/Documents/workspace/app/db/structure.sql:17: syntax error, unexpected tLABEL
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
        ^
/Users/user/Documents/workspace/app/db/structure.sql:17: syntax error, unexpected ':', expecting end-of-input
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -

a portion of the structure.sql file:

-- PostgreSQL database dump
--

-- Dumped from database version 9.5.1
-- Dumped by pg_dump version 9.5.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: accounts; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE accounts (
    id integer NOT NULL,
    name character varying,
    logo character varying,
    website character varying,
    subdomain character varying,
    active boolean,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);


--
-- Name: accounts_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE accounts_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: accounts_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE accounts_id_seq OWNED BY accounts.id;


--
-- Name: addresses; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE addresses (
    id integer NOT NULL,
    location_id integer,
    location_type character varying,
    street character varying,
    number character varying,
    zip character varying,
    city character varying,
    country character varying,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);


--
-- Name: addresses_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE addresses_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: addresses_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE addresses_id_seq OWNED BY addresses.id;


--
-- Name: administrators; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE administrators (
    id integer NOT NULL,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);


--
-- Name: administrators_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE administrators_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: administrators_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE administrators_id_seq OWNED BY administrators.id;


--
-- Name: agreements; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE agreements (
    id integer NOT NULL,
    employee_id integer,
    client_id integer,
    contract_id integer,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);


--
-- Name: agreements_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE agreements_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: agreements_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE agreements_id_seq OWNED BY agreements.id;


--
-- Name: api_authorizations; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE api_authorizations (
    id integer NOT NULL,
    authorizable_id integer,
    authorizable_type character varying,
    client_id character varying,
    client_secret character varying,
    access_token character varying,
    access_expires_at timestamp without time zone,
    download_token character varying,
    token_type character varying,
    scope character varying,
    refresh_token character varying,
    refresh_expires_at timestamp without time zone,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);


--
-- Name: api_authorizations_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE api_authorizations_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: api_authorizations_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE api_authorizations_id_seq OWNED BY api_authorizations.id;


--
-- Name: billings; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE billings (
    id integer NOT NULL,
    user_id integer,
    bank_account_number character varying,
    bic character varying,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);


--
-- Name: billings_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE billings_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: billings_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE billings_id_seq OWNED BY billings.id;


--
-- Name: clients; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE clients (
    id integer NOT NULL,
    external_id character varying,
    url_digest character varying,
    employee_id integer,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);


--
-- Name: clients_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE clients_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: clients_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE clients_id_seq OWNED BY clients.id;


--
-- Name: companies; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE companies (
    id integer NOT NULL,
    name character varying,
    revenue numeric(15,2),
    employee_number integer,
    sector character varying,
    foundation_date date,
    tax_number character varying,
    vat_id character varying,
    total_salaries numeric(15,2),
    client_id integer,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);


--
-- Name: companies_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE companies_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: companies_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE companies_id_seq OWNED BY companies.id;


--
-- Name: contacts; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE contacts (
    id integer NOT NULL,
    honorific integer,
    title character varying,
    first_name character varying,
    last_name character varying,
    birth_date date,
    telephone character varying,
    user_id integer,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);


--
-- Name: contacts_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE contacts_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: contacts_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE contacts_id_seq OWNED BY contacts.id;


--
-- Name: contract_categories; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE contract_categories (
    id integer NOT NULL,
    name character varying,
    contract_group_id integer,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);


--
-- Name: contract_categories_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE contract_categories_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: contract_categories_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE contract_categories_id_seq OWNED BY contract_categories.id;


--
-- Name: contract_groups; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE contract_groups (
    id integer NOT NULL,
    name character varying,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);


--
-- Name: contract_groups_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE contract_groups_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: contract_groups_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

score 1 · Accepted Answer · answered Mar 09 '16 at 12:15

1

You are seeing this error because you can't load sql files. load is only used for loading ruby files.

answered Mar 09 '16 at 12:15

Josh Deeden

1,660
1
12
14

Is there any way that i can load db/structure.sql file in my model? – marinoszak Mar 09 '16 at 12:54
I have tried before but i think db:schema:load does not load structure.sql file. It is only for schema.rb, or am i wrong? In application that i am writing i have multiple tenants and when i create a new tenant i want to change the search_path and load the structure.sql file. – marinoszak Mar 09 '16 at 13:16
Oops, you're right. What I meant was `rake db:structure:load` -- as far as making this work in a multitenant environment, that should be a separate question. that being said, maybe check out https://github.com/influitive/apartment for clues on how it handles changing the search path when loading structures/schemas. – Josh Deeden Mar 09 '16 at 14:26
Thank you for your resource. I found a lot of stuff and finally i solved the problem. – marinoszak Mar 09 '16 at 16:47

Rails syntax error after schema load with PostgreSQL

1 Answers1