[BUGS] BUG #14652: pg_dump: VIEW dumped as TABLE - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      14652
Logged by:          Laurent Martelli
Email address:      martellilaurent@gmail.com
PostgreSQL version: 9.4.12
Operating system:   Dedian gnu-linux Jessie
Description:

Some views of are dumped as TABLE. And this is really annoying because it
has "REPLICA IDENTITY NOTHING" which is not understood if I try to restore
in 9.1. 

Example:
coopener-v2_2017-03-16=# \d ael_invoice              View "public.ael_invoice"    Column      |          Type
|Modifiers 
 
-----------------+------------------------+-----------id              | bigint                 | old_invoice_id  |
charactervarying(255) | invoice_id      | character varying(255) | amount_wt       | numeric(20,2)          | vat
     | numeric(20,2)          | is_paid         | boolean                | due_date        | date                   |
rum            | bigint                 | invoice_date    | date                   | invoice_name    | character
varying(255)| status_name     | character varying(255) | payment_mode    | character varying(255) | reject_date     |
date                  | amount_rejected | numeric(19,2)          | contract_line   | bigint                 |
consumption_qty| numeric                | product         | bigint                 | 
 

Using pg_dump -t, all is fine:
$ pg_dump --schema-only -O -x -t ael_invoice coopener-v2_2017-03-16
--
-- PostgreSQL database dump
--

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 search_path = public, pg_catalog;

--
-- Name: ael_invoice; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW ael_invoice ASSELECT i.id,   i.old_invoice_id,   i.invoice_id,   i.ex_all_tax_total AS amount_wt,
i.vat_totalAS vat,   i.balanced_memory_ok AS is_paid,   i.due_date,   i.rum,   i.invoice_date,   i.invoice_name,
status.nameAS status_name,   pay_mode.name AS payment_mode,   i.reject_date,   i.amount_rejected,   i.contract_line,
sum(iline.qty)AS consumption_qty,   iline.product  FROM (((invoice_invoice i    LEFT JOIN administration_status status
ON((i.status = status.id)))    LEFT JOIN payment_payment_mode pay_mode ON ((i.payment_mode = 
pay_mode.id)))    LEFT JOIN invoice_invoice_line iline ON ((i.id = iline.invoice))) GROUP BY i.id, status.name,
pay_mode.name,iline.product;
 


--
-- Name: VIEW ael_invoice; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON VIEW ael_invoice IS 'Factures';


--
-- PostgreSQL database dump complete
--

But if I dump the whole schema, I get a TABLE with RULE:

$ pg_dump --schema-only -O -x --schema=public coopener-v2_2017-03-16 

[...]

--
-- Name: ael_invoice; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE ael_invoice (   id bigint,   old_invoice_id character varying(255),   invoice_id character varying(255),
amount_wtnumeric(20,2),   vat numeric(20,2),   is_paid boolean,   due_date date,   rum bigint,   invoice_date date,
invoice_namecharacter varying(255),   status_name character varying(255),   payment_mode character varying(255),
reject_datedate,   amount_rejected numeric(19,2),   contract_line bigint,   consumption_qty numeric,   product bigint
 
);

ALTER TABLE ONLY ael_invoice REPLICA IDENTITY NOTHING;


--
-- Name: TABLE ael_invoice; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE ael_invoice IS 'Factures';

[...]

--
-- Name: _RETURN; Type: RULE; Schema: public; Owner: -
--

CREATE RULE "_RETURN" AS   ON SELECT TO ael_invoice DO INSTEAD  SELECT i.id,   i.old_invoice_id,   i.invoice_id,
i.ex_all_tax_totalAS amount_wt,   i.vat_total AS vat,   i.balanced_memory_ok AS is_paid,   i.due_date,   i.rum,
i.invoice_date,  i.invoice_name,   status.name AS status_name,   pay_mode.name AS payment_mode,   i.reject_date,
i.amount_rejected,  i.contract_line,   sum(iline.qty) AS consumption_qty,   iline.product  FROM (((invoice_invoice i
LEFTJOIN administration_status status ON ((i.status = status.id)))    LEFT JOIN payment_payment_mode pay_mode ON
((i.payment_mode= 
pay_mode.id)))    LEFT JOIN invoice_invoice_line iline ON ((i.id = iline.invoice))) GROUP BY i.id, status.name,
pay_mode.name,iline.product;
 

[...]


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs