Adding column default to child inheritance table does not generate DDL for default definition when using Create Script command

From: Michael Rasmussen <michaelr(at)porch(dot)com>
To: "pgadmin-support(at)postgresql(dot)org" <pgadmin-support(at)postgresql(dot)org>
Subject: Adding column default to child inheritance table does not generate DDL for default definition when using Create Script command
Date: 2015-01-15 19:55:31
Message-ID: F126AFBD-99C6-4671-8DC6-DC5D3A1D0E89@porch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support

Platform: Mac OS X Yosemite
PostgreSQL Server Version: 9.2.9
Language: English
pgAdmin III Version: 1.20.0 (Dec 19 2014, rev: REL-1_20_0)
pgAdmin III Distribution: Binary from http://www.postgresql.org/ftp/pgadmin3/release/v1.20.0/osx/

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
--

--
Michael Rasmussen
Sr. Data Engineer
Porch

Browse pgadmin-support by date

  From Date Subject
Next Message hushthatbush 2015-01-17 20:13:42 Is the "x ms" info reliable?
Previous Message ced75 2015-01-13 09:02:23 Re: PgAdmin III 1.20 error on Windows 7 (64 bits)