How can I generate the DDL of a table programmatically on Postgresql? Is there a system query or command to do it? Googling the issue returned no pointers.
The answer is to check the source code for pg_dump and follow the switches it uses to generate the DDL. Somewhere inside the code there s a number of queries used to retrieve the metadata used to generate the DDL.
Use pg_dump with this options:
pg_dump -U user_name -h host database -s -t table_or_view_names -f table_or_view_names.sql
Description:
-s or --schema-only : Dump only ddl / the object definitions (schema), without data.
-t or --table Dump : Dump only tables (or views or sequences) matching table
Examples:
-- dump each ddl table elon build.
$ pg_dump -U elon -h localhost -s -t spacex -t tesla -t solarcity -t boring > companies.sql
Sorry if out of topic. Just wanna help who googling "psql dump ddl" and got this thread.
You can use the pg_dump command to dump the contents of the database (both schema and data). The --schema-only switch will dump only the DDL for your table(s).
Why would shelling out to psql not count as "programmatically?" It ll dump the entire schema very nicely.
Anyhow, you can get data types (and much more) from the information_schema (8.4 docs referenced here, but this is not a new feature):
=# select column_name, data_type from information_schema.columns
-# where table_name = config ;
column_name | data_type
--------------------+-----------
id | integer
default_printer_id | integer
master_host_enable | boolean
(3 rows)
Here is a good article on how to get the meta information from information schema, http://www.alberton.info/postgresql_meta_info.html.
I saved 4 functions to mock up pg_dump -s behaviour partially. Based on d+ metacommand. The usage would be smth alike:
pset format unaligned
select get_ddl_t(schemaname,tablename) as "--" from pg_tables where tableowner <> postgres ;
Of course you have to create functions prior.
我如何将Excel板的数据输入我的Django应用? I m将PosgreSQL数据库作为数据库。
On production our database is a few hundred gigabytes in size. For development and testing, we need to create snapshots of this database that are functionally equivalent, but which are only 10 or 20 ...
I ll take the simplest of the SQL functions as an example: CREATE OR REPLACE FUNCTION skater_name_match(INTEGER,VARCHAR) RETURNS BOOL AS $$ SELECT $1 IN (SELECT skaters_skater.competitor_ptr_id ...
I m 在当地网络上运行几台机器,在Salgresql8.3处撰写一份申请。
I want to join few attributes in select statement as one for example select id, (name + + surname + + age) as info from users this doesn t work, how to do it? I m using postgreSQL.
Hey, I have 2 tables in PostgreSql: 1 - documents: id, title 2 - updates: id, document_id, date and some data: documents: | 1 | Test Title | updates: | 1 | 1 | 2006-01-01 | | 2 | 1 | 2007-01-01 |...
I need to setup my PostgreSQL DB s text encoding to handle non-American English characters that you d find showing up in languages such as German, Spanish, and French. What character encoding should ...
I am using a set of SQL LIKE conditions to go through the alphabet and list all items beginning with the appropriate letter, e.g. to get all books where the title starts with the letter "A": SELECT * ...