Wie generiert man die SQL-Anweisung "create table" für eine bestehende Tabelle in postgreSQL

Question

Ich habe eine Tabelle in postgreSQL erstellt. Ich möchte mir die SQL-Anweisung ansehen, die zum Erstellen der Tabelle verwendet wurde, kann sie aber nicht herausfinden.

Wie erhalte ich die create table SQL-Anweisung für eine bestehende Tabelle in Postgres über Kommandozeile oder SQL-Anweisung?

Answer 1

Wie in den anderen Antworten erwähnt, gibt es keine eingebaute Funktion, die dies tut.

Hier ist eine Funktion, die versucht, alle Informationen zu erhalten, die benötigt werden, um die Tabelle zu replizieren - oder um eingesetzte und eingecheckte ddl zu vergleichen.

Diese Funktion gibt aus:

• Spalten (mit Genauigkeit, null/nicht-null, Standardwert)
• Zwänge

• Indizes

  CREATE OR REPLACE FUNCTION public.show_create_table( in_schema_name varchar, in_table_name varchar ) RETURNS text LANGUAGE plpgsql VOLATILE AS $$ DECLARE -- the ddl we're building v_table_ddl text;

  -- data about the target table
  v_table_oid int;
  
  -- records for looping
  v_column_record record;
  v_constraint_record record;
  v_index_record record;

  BEGIN -- grab the oid of the table; https://www.postgresql.org/docs/8.3/catalog-pg-class.html SELECT c.oid INTO v_table_oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE 1=1 AND c.relkind = 'r' -- r = ordinary table; https://www.postgresql.org/docs/9.3/catalog-pg-class.html AND c.relname = in_table_name -- the table name AND n.nspname = in_schema_name; -- the schema

  -- throw an error if table was not found
  IF (v_table_oid IS NULL) THEN
    RAISE EXCEPTION 'table does not exist';
  END IF;
  
  -- start the create definition
  v_table_ddl := 'CREATE TABLE ' || in_schema_name || '.' || in_table_name || ' (' || E'\n';
  
  -- define all of the columns in the table; https://stackoverflow.com/a/8153081/3068233
  FOR v_column_record IN
    SELECT
      c.column_name,
      c.data_type,
      c.character_maximum_length,
      c.is_nullable,
      c.column_default
    FROM information_schema.columns c
    WHERE (table_schema, table_name) = (in_schema_name, in_table_name)
    ORDER BY ordinal_position
  LOOP
    v_table_ddl := v_table_ddl || '  ' -- note: two char spacer to start, to indent the column
      || v_column_record.column_name || ' '
      || v_column_record.data_type || CASE WHEN v_column_record.character_maximum_length IS NOT NULL THEN ('(' || v_column_record.character_maximum_length || ')') ELSE '' END || ' '
      || CASE WHEN v_column_record.is_nullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
      || CASE WHEN v_column_record.column_default IS NOT null THEN (' DEFAULT ' || v_column_record.column_default) ELSE '' END
      || ',' || E'\n';
  END LOOP;
  
  -- define all the constraints in the; https://www.postgresql.org/docs/9.1/catalog-pg-constraint.html && https://dba.stackexchange.com/a/214877/75296
  FOR v_constraint_record IN
    SELECT
      con.conname as constraint_name,
      con.contype as constraint_type,
      CASE
        WHEN con.contype = 'p' THEN 1 -- primary key constraint
        WHEN con.contype = 'u' THEN 2 -- unique constraint
        WHEN con.contype = 'f' THEN 3 -- foreign key constraint
        WHEN con.contype = 'c' THEN 4
        ELSE 5
      END as type_rank,
      pg_get_constraintdef(con.oid) as constraint_definition
    FROM pg_catalog.pg_constraint con
    JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
    JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
    WHERE nsp.nspname = in_schema_name
    AND rel.relname = in_table_name
    ORDER BY type_rank
  LOOP
    v_table_ddl := v_table_ddl || '  ' -- note: two char spacer to start, to indent the column
      || 'CONSTRAINT' || ' '
      || v_constraint_record.constraint_name || ' '
      || v_constraint_record.constraint_definition
      || ',' || E'\n';
  END LOOP;
  
  -- drop the last comma before ending the create statement
  v_table_ddl = substr(v_table_ddl, 0, length(v_table_ddl) - 1) || E'\n';
  
  -- end the create definition
  v_table_ddl := v_table_ddl || ');' || E'\n';
  
  -- suffix create statement with all of the indexes on the table
  FOR v_index_record IN
    SELECT indexdef
    FROM pg_indexes
    WHERE (schemaname, tablename) = (in_schema_name, in_table_name)
  LOOP
    v_table_ddl := v_table_ddl
      || v_index_record.indexdef
      || ';' || E'\n';
  END LOOP;
  
  -- return the ddl
  RETURN v_table_ddl;

  END; $$;

Beispiel

SELECT * FROM public.show_create_table('public', 'example_table');

produziert

CREATE TABLE public.example_table (
  id bigint NOT NULL DEFAULT nextval('test_tb_for_show_create_on_id_seq'::regclass),
  name character varying(150) NULL,
  level character varying(50) NULL,
  description text NOT NULL DEFAULT 'hello there!'::text,
  CONSTRAINT test_tb_for_show_create_on_pkey PRIMARY KEY (id),
  CONSTRAINT test_tb_for_show_create_on_level_check CHECK (((level)::text = ANY ((ARRAY['info'::character varying, 'warn'::character varying, 'error'::character varying])::text[])))
);
CREATE UNIQUE INDEX test_tb_for_show_create_on_pkey ON public.test_tb_for_show_create_on USING btree (id);

Answer 2

DataGrip hat die gleiche Funktionalität wie pgAdmin. Sie können mit der rechten Maustaste auf eine Tabelle klicken und sehen dann die Option zum automatischen Erzeugen einer Tabellenanweisung.

Answer 3

Verwenden Sie dies und erhalten Sie Ihre Ausgabe in ddl.out archivo

~/bin/pg_dump -p 30000 -d <db_name> -U <db_user> --schema=<schema_name> -t <table_name> --schema-only >> /tmp/ddl.out

Dies erzeugt also DDL im Pfad: /tmp/ddl.out

Answer 4

Eine einfache Lösung, in reinem Single-SQL. Sie verstehen die Idee, Sie können sie auf weitere Attribute, die Sie anzeigen möchten, erweitern.

with c as (
SELECT table_name, ordinal_position, 
 column_name|| ' ' || data_type col
, row_number() over (partition by table_name order by ordinal_position asc) rn
, count(*) over (partition by table_name) cnt
FROM information_schema.columns
WHERE table_name   in ('pg_index', 'pg_tables')
order by table_name, ordinal_position
)
select case when rn = 1 then 'create table ' || table_name || '(' else '' end
 || col 
 || case when rn < cnt then ',' else '); ' end
from c 
order by table_name, rn asc;

Ausgabe:

create table pg_index(indexrelid oid,
 indrelid oid,
 indnatts smallint,
 indisunique boolean,
 indisprimary boolean,
 indisexclusion boolean,
 indimmediate boolean,
 indisclustered boolean,
 indisvalid boolean,
 indcheckxmin boolean,
 indisready boolean,
 indislive boolean,
 indisreplident boolean,
 indkey ARRAY,
 indcollation ARRAY,
 indclass ARRAY,
 indoption ARRAY,
 indexprs pg_node_tree,
 indpred pg_node_tree);

 create table pg_tables(schemaname name,
 tablename name,
 tableowner name,
 tablespace name,
 hasindexes boolean,
 hasrules boolean,
 hastriggers boolean,
 rowsecurity boolean);

Answer 5

Eine weitere einfache Möglichkeit war die Verwendung von [HeidiSQL client][1] for PostgreSQL Datenbank.

So gelangen Sie auf die Registerkarte Datenbank, auf der alle Datenbanken und Tabellen aufgelistet sind.

Klicken Sie auf eine beliebige Tabelle/Ansicht, für die Sie die DDL/Anweisung der jeweiligen Tabelle anzeigen möchten.

Dieser Client erledigt nun für Sie die folgenden Aufgaben für diese Tabelle auf der rechten Seite des Fensters:

Das erste Fenster wäre für die Daten der Tabelle

Zweitens für Ihre SQL-Host-Informationen

Drittens für Informationen auf Datenbankebene, z. B. welche Tabellen und wie groß sie sind

---

Forth, die wir mehr Sorge über Tabelle / Ansicht Informationen Registerkarte wird die Tabelle erstellen Anweisung leicht verfügbar für Sie haben.

---

Ich kann Ihnen nicht zeigen, in den Schnappschuss als die Arbeit mit vertraulichen Daten, versuchen Sie es mit sich selbst und lassen Sie mich wissen, wenn irgendwelche Probleme, die Sie Jungs gefunden.