PostgreSQL Server Version: 9.2.9
Language: English
pgAdmin III Version: 1.20.0 (Dec 19 2014, rev: REL-1_20_0)
Example DDL:
CREATE TABLE foo (
the_key INTEGER,
the_value BOOLEAN
);
CREATE TABLE bar ()
INHERITS (foo);
ALTER TABLE bar
ALTER COLUMN the_value SET DEFAULT TRUE;
What is happening:
When I right click on table bar, and run CREATE Script in the context menu, I get the below output, which detects the new default definition but puts it in the comments. Thus the DEFAULT true gets lost.
-- Table: bar
-- DROP TABLE bar;
CREATE TABLE bar
(
-- Inherited from table foo: the_key integer,
-- Inherited from table foo: the_value boolean DEFAULT true
)
INHERITS (foo)
WITH (
OIDS=FALSE
);
ALTER TABLE bar
OWNER TO redacted;
The CREATE Script command should match the pg_dump output, as seen below, by including an alter table statement that preserves the default definition.
~]# pg_dump -s -t bar porch
--
-- PostgreSQL database dump
--
SET statement_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;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: bar; Type: TABLE; Schema: public; Owner: redacted; Tablespace:
--
CREATE TABLE bar (
)
INHERITS (foo);
ALTER TABLE public.bar OWNER TO redacted;
--
-- Name: the_value; Type: DEFAULT; Schema: public; Owner: redacted
--
ALTER TABLE ONLY bar ALTER COLUMN the_value SET DEFAULT true;
--
-- PostgreSQL database dump complete
--