1

I want to get "create table clause" by a sql.
Or can I use Knex.js to get "create table clause"?

I've seen this "How to generate the "create table" sql statement for an existing table in postgreSQL"
However, most of the answers uses pg_dump which I can't use.
And there're some sql answers, but:

  • lack of specifying schema
  • lack of indexes or primay key

So, I asked the question again but differently:

  • no use of pg_dump
Aliar
  • 73
  • 7

1 Answers1

1

Some sql like below can be a help:

    SELECT                                          
  'CREATE TABLE ' || relname || E'\n(\n' ||
  array_to_string(
    array_agg(
      '    ' || column_name || ' ' ||  type || ' '|| not_null
    )
    , E',\n'
  ) || E'\n);\n'
from
(
  SELECT 
    c.relname, a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) as type,
    case 
      when a.attnotnull
    then 'NOT NULL' 
    else 'NULL' 
    END as not_null 
  FROM pg_class c,
   pg_attribute a,
   pg_type t
   WHERE c.relname = 'city'
   AND a.attnum > 0
   AND a.attrelid = c.oid
   AND a.atttypid = t.oid
 ORDER BY a.attnum
) as tabledefinition
group by relname
;
SeanH
  • 538
  • 2
  • 8
  • Thank you, really. But how can I do when I need a specified schema. – Aliar Jul 01 '22 at 08:23
  • You can refer to the extension of: https://github.com/lacanoid/pgddl If you are free to control your PG database, you even can install that extension to get the detailed DDL of specified table or other object. – SeanH Jul 01 '22 at 20:54