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 https://stackoverflow.com/questions/10639963/postgres-dynamic-query-function — Juan Carlos Oropeza, Aug 07 '18 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. https://www.postgresql.org/docs/9.1/static/app-pgdump.html — Victor Di Leo, Aug 07 '18 at 15:33

score 0 · Answer 1 · answered Aug 07 '18 at 15:29

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 07 '18 at 15:29

Little Santi

8,563
2
18
46

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. – MatBailie Aug 07 '18 at 15:48

score 0 · Answer 2 · answered Aug 08 '18 at 08:40

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.

PostgreSQL - dynamically script table definition

2 Answers2