Thread: DDL from psql console?
Hello, I was curious if there was a way to get the DDL for a particular table from the psql client console? I have two postgres boxes (development and production) and would like to copy & paste the DDL "CREATE TABLE" statements from the development console to the production console when I'm moving a particular table definition over. I tried \dt+ but it didn't appear to show it. Any thoughts?
Any reason not to use pg_dump -s? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Apr 25, 2005, at 10:29 AM, John Browne wrote: > Hello, > > I was curious if there was a way to get the DDL for a particular table > from the psql client console? I have two postgres boxes (development > and production) and would like to copy & paste the DDL "CREATE TABLE" > statements from the development console to the production console when > I'm moving a particular table definition over. I tried \dt+ but it > didn't appear to show it. > > Any thoughts?
On Mon, 2005-04-25 at 10:29, John Browne wrote: > Hello, > > I was curious if there was a way to get the DDL for a particular table > from the psql client console? I have two postgres boxes (development > and production) and would like to copy & paste the DDL "CREATE TABLE" > statements from the development console to the production console when > I'm moving a particular table definition over. I tried \dt+ but it > didn't appear to show it. I don't think you can get it from within psql, but you can get it with pg_dump from the command line: pg_dump -st tablename dbname
Yeah, I know about pg_dump. I just was curious if there was another way, since I always have two psql consoles already open at all times anyway. :-) On 4/25/05, John Browne <jkbrowne@gmail.com> wrote: > Hello, > > I was curious if there was a way to get the DDL for a particular table > from the psql client console? I have two postgres boxes (development > and production) and would like to copy & paste the DDL "CREATE TABLE" > statements from the development console to the production console when > I'm moving a particular table definition over. I tried \dt+ but it > didn't appear to show it. > > Any thoughts? >
John Browne <jkbrowne@gmail.com> writes:
> I was curious if there was a way to get the DDL for a particular table
> from the psql client console?
No. Try
pg_dump -s -t tablename dbname
regards, tom lane
On Mon, Apr 25, 2005 at 10:44:14AM -0500, John Browne wrote: > > Yeah, I know about pg_dump. I just was curious if there was another > way, since I always have two psql consoles already open at all times > anyway. :-) You could do "\!pg_dump ..." -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Tom Lane wrote:
> John Browne <jkbrowne@gmail.com> writes:
> > I was curious if there was a way to get the DDL for a particular table
> > from the psql client console?
>
> No. Try
> pg_dump -s -t tablename dbname
Oh, from psql: :-) (We really should have an easier way of show all
information from psql)
test=> CREATE TABLE test (x SERIAL);
NOTICE: CREATE TABLE will create implicit sequence "test_x_seq" for
serial column "test.x"
CREATE TABLE
test=> \! pg_dump -s -t test test
--
-- PostgreSQL database dump
--
SET client_encoding = 'SQL_ASCII';
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: test; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--
CREATE TABLE test (
x serial NOT NULL
);
ALTER TABLE public.test OWNER TO postgres;
--
-- PostgreSQL database dump complete
--
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Actually, that's a thought.. I could even create a bash wrapper script so I wouldn't have to type the database name each time. Will give it a shot. Thanks On 4/25/05, Michael Fuhr <mike@fuhr.org> wrote: > On Mon, Apr 25, 2005 at 10:44:14AM -0500, John Browne wrote: > > > > Yeah, I know about pg_dump. I just was curious if there was another > > way, since I always have two psql consoles already open at all times > > anyway. :-) > > You could do "\!pg_dump ..." > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ >
John Browne wrote: > Actually, that's a thought.. I could even create a bash wrapper > script so I wouldn't have to type the database name each time. Will > give it a shot. > > Thanks > > On 4/25/05, Michael Fuhr <mike@fuhr.org> wrote: > > On Mon, Apr 25, 2005 at 10:44:14AM -0500, John Browne wrote: > > > > > > Yeah, I know about pg_dump. I just was curious if there was another > > > way, since I always have two psql consoles already open at all times > > > anyway. :-) > > > > You could do "\!pg_dump ..." Also, what is it you want to see that \d doesn't give you? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073