PostgreSQL - dynamically script table definition

Question

Is there a way in which I can get the table definition in a script that I can execute?

i.e.

I have a table "cities":

CREATE TABLE public.cities
(
    name character(80) COLLATE pg_catalog."default" NOT NULL,
    location point,
    CONSTRAINT pk_city_name PRIMARY KEY (name)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.cities
    OWNER to postgres;

is there a way I can generate that with a script rather than using the GUI?

You use dynamic query. There is a command EXECUTE where you pass a string with all the instruction. see an example here stackoverflow.com/questions/10639963/… — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Aug 7, 2018 at 15:23
pg_dump with the -t switch will give you the CREATE TABLE for a specific table. Using -s will generate a script for the entire schema. postgresql.org/docs/9.1/static/app-pgdump.html — Victor Di Leo
– Victor Di Leo, Commented Aug 7, 2018 at 15:33

Little Santi · Accepted Answer · 2018-08-07 15:29:20Z

0

If you want to write your commands into a script file, and the run it from command line, you should use psql -f <filename>.

See https://www.postgresql.org/docs/9.2/static/app-psql.html

answered Aug 7, 2018 at 15:29

Little Santi

8,8532 gold badges22 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MatBailie Over a year ago

I think the OP is asking for the script to be created using the CLI (not just written out to file). For example, output the above script by specifying the table cities, but without using the GUI.

Jay · Accepted Answer · 2018-08-08 08:40:02Z

0

Thanks for the links and places to look.

for those reading what I done:

open cmd

navigate to C:\Program Files\PostgreSQL\10\bin> (or create a short cut)

pg_dump -d mydb -t cities -U postgres -h localhost > C:/test/weather.sql

enter password for postgres

file is output to directory.

answered Aug 8, 2018 at 8:40

Jay

812 silver badges10 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - dynamically script table definition

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related