DDL from psql console?

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:

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. :-)

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

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