Cannot generate from pg_dump output: schema "public" already exists

[jose-zenledger]

Version

Other

What happened?

After I run migrations I run pg_dump to create the schema file (I tried adding a bunch of flags to reduce what gets put in there):

PGPASSWORD=postgres pg_dump -U postgres -h db -p 5432 --schema public --schema-only --no-tablespaces --no-subscriptions --no-security-labels --no-publications -x currencydb > database/structure.sql

Which has the following line that breaks generate

CREATE SCHEMA public;

Relevant log output

database/structure.sql:1:1: schema "public" already exists

Database schema

--
-- PostgreSQL database dump
--

-- Dumped from database version 14.2
-- Dumped by pg_dump version 14.2

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: public; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA public;


ALTER SCHEMA public OWNER TO postgres;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'standard public schema';


SET default_table_access_method = heap;

--
-- Name: prices; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.prices (
    currency_id bigint NOT NULL,
    price_usd numeric NOT NULL,
    is_validated boolean DEFAULT false NOT NULL,
    "timestamp" timestamp without time zone NOT NULL,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);


ALTER TABLE public.prices OWNER TO postgres;

--
-- Name: currencies; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.currencies (
    id bigint NOT NULL,
    code text NOT NULL,
    display_name text NOT NULL,
    platform text,
    contract text,
    is_ambiguous boolean NOT NULL,
    tracking_begin timestamp without time zone,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);


ALTER TABLE public.currencies OWNER TO postgres;

--
-- Name: currencies_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.currencies_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.currencies_id_seq OWNER TO postgres;

--
-- Name: currencies_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.currencies_id_seq OWNED BY public.currencies.id;


--
-- Name: schema_migrations; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.schema_migrations (
    version bigint NOT NULL,
    dirty boolean NOT NULL
);


ALTER TABLE public.schema_migrations OWNER TO postgres;

--
-- Name: slugs; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.slugs (
    id bigint NOT NULL,
    currency_id bigint NOT NULL,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);


ALTER TABLE public.slugs OWNER TO postgres;

--
-- Name: slugs_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.slugs_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.slugs_id_seq OWNER TO postgres;

--
-- Name: slugs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.slugs_id_seq OWNED BY public.slugs.id;


--
-- Name: currencies id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.currencies ALTER COLUMN id SET DEFAULT nextval('public.currencies_id_seq'::regclass);


--
-- Name: slugs id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.slugs ALTER COLUMN id SET DEFAULT nextval('public.slugs_id_seq'::regclass);


--
-- Name: currencies currencies_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.currencies
    ADD CONSTRAINT currencies_pkey PRIMARY KEY (id);


--
-- Name: schema_migrations schema_migrations_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.schema_migrations
    ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (version);


--
-- Name: slugs slugs_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.slugs
    ADD CONSTRAINT slugs_pkey PRIMARY KEY (id);


--
-- Name: prices_currency_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX prices_currency_id_idx ON public.prices USING btree (currency_id);


--
-- Name: prices_currency_id_timestamp_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX prices_currency_id_timestamp_idx ON public.prices USING btree (currency_id, "timestamp" DESC);


--
-- Name: prices_timestamp_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX prices_timestamp_idx ON public.prices USING btree ("timestamp" DESC);


--
-- Name: slugs_currency_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX slugs_currency_id_idx ON public.slugs USING btree (currency_id);


--
-- Name: prices ts_insert_blocker; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER ts_insert_blocker BEFORE INSERT ON public.prices FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker();


--
-- Name: prices prices_currency_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.prices
    ADD CONSTRAINT prices_currency_id_fkey FOREIGN KEY (currency_id) REFERENCES public.currencies(id);


--
-- Name: slugs slugs_currency_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.slugs
    ADD CONSTRAINT slugs_currency_id_fkey FOREIGN KEY (currency_id) REFERENCES public.currencies(id);


--
-- PostgreSQL database dump complete
--

SQL queries

-- name: ListCurrencies :many
SELECT * FROM currencies
ORDER BY id;

Configuration

version: 1
packages:
  - path: "db"
    name: "db"
    engine: "postgresql"
    schema: "database/structure.sql"
    queries: "database/query.sql"

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[kyleconroy]

Very interesting. For PostgreSQL, sqlc initializes the catalog with a default schema so that you don't need to include CREATE SCHEMA public in your DDL statements. The work around is to comment out the CREATE SCHEMA public line (or change it to CREATE SCHEMA IF NOT EXISTS public), but sqlc should be smart enough to handle this case, especially since pg_dump is such a common tool.

In the long term, I think we should treat CEATE SCHEMA public as CREATE SCHEMA IF NOT EXISTS public, but only for the public schema.

[PlatosCodes]

Hello, I've submitted a pull request #2495 to address this issue. In the pull request, I've updated the code to treat CREATE SCHEMA public as CREATE SCHEMA IF NOT EXISTS public for the public schema. Please review and let me know if there are any concerns or feedback. Thanks!