Comment générer l'instruction sql "create table" pour une table existante dans postgreSQL ?

Question

J'ai créé une table dans postgreSQL. Je souhaite consulter l'instruction SQL utilisée pour créer la table, mais je n'y arrive pas.

Comment puis-je obtenir le create table Instruction SQL pour une table existante dans Postgres via ligne de commande ou instruction SQL ?

Answer 1

pg_dump -t 'schema-name.table-name' --schema-only database-name

Plus d'informations - dans le manuel .

Answer 2

(AVIS - cette solution ne fonctionne pas avec PostgreSQL v12+)

---

Ma solution est de me connecter à la base de données postgres en utilisant psql avec l'option -E comme suit :

psql -E -U username -d database   

Dans psql, exécutez les commandes suivantes pour voir le sql que postgres utilise pour générer
l'énoncé de la table de description :

-- List all tables in the schema (my example schema name is public)
\dt public.*
-- Choose a table name from above
-- For create table of one public.tablename
\d+ public.tablename  

En me basant sur le sql obtenu après l'exécution de ces commandes, j'ai pu rassembler les éléments suivants
la fonction plpgsql suivante :

CREATE OR REPLACE FUNCTION generate_create_table_statement(p_table_name varchar)
  RETURNS text AS
$BODY$
DECLARE
    v_table_ddl   text;
    column_record record;
BEGIN
    FOR column_record IN 
        SELECT 
            b.nspname as schema_name,
            b.relname as table_name,
            a.attname as column_name,
            pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
            CASE WHEN 
                (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                 FROM pg_catalog.pg_attrdef d
                 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
                'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                              FROM pg_catalog.pg_attrdef d
                              WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
            ELSE
                ''
            END as column_default_value,
            CASE WHEN a.attnotnull = true THEN 
                'NOT NULL'
            ELSE
                'NULL'
            END as column_not_null,
            a.attnum as attnum,
            e.max_attnum as max_attnum
        FROM 
            pg_catalog.pg_attribute a
            INNER JOIN 
             (SELECT c.oid,
                n.nspname,
                c.relname
              FROM pg_catalog.pg_class c
                   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
              WHERE c.relname ~ ('^('||p_table_name||')$')
                AND pg_catalog.pg_table_is_visible(c.oid)
              ORDER BY 2, 3) b
            ON a.attrelid = b.oid
            INNER JOIN 
             (SELECT 
                  a.attrelid,
                  max(a.attnum) as max_attnum
              FROM pg_catalog.pg_attribute a
              WHERE a.attnum > 0 
                AND NOT a.attisdropped
              GROUP BY a.attrelid) e
            ON a.attrelid=e.attrelid
        WHERE a.attnum > 0 
          AND NOT a.attisdropped
        ORDER BY a.attnum
    LOOP
        IF column_record.attnum = 1 THEN
            v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
        ELSE
            v_table_ddl:=v_table_ddl||',';
        END IF;

        IF column_record.attnum <= column_record.max_attnum THEN
            v_table_ddl:=v_table_ddl||chr(10)||
                     '    '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null;
        END IF;
    END LOOP;

    v_table_ddl:=v_table_ddl||');';
    RETURN v_table_ddl;
END;
$BODY$
  LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;

Voici l'utilisation de la fonction :

SELECT generate_create_table_statement('tablename');

Et voici la déclaration d'abandon si vous ne voulez pas que cette fonction persiste en permanence :

DROP FUNCTION generate_create_table_statement(p_table_name varchar);

Answer 3

Générer l'instruction create table pour une table dans postgresql à partir de la ligne de commande linux :

Créez un tableau pour une démonstration :

CREATE TABLE your_table(
    thekey   integer NOT NULL,  ticker   character varying(10) NOT NULL,
    date_val date,              open_val numeric(10,4) NOT NULL
); 

pg_dump peut sortir la psql de création de table :

pg_dump -U your_user your_database -t your_table --schema-only

Qui imprime :

-- pre-requisite database and table configuration omitted
CREATE TABLE your_table (                                                                                                      
    thekey integer NOT NULL,                                                                                                   
    ticker character varying(10) NOT NULL,                                                                                     
    date_val date,                                                                                                             
    open_val numeric(10,4) NOT NULL                                                                                            
);                                                                                                                   
-- post-requisite database and table configuration omitted

Explication :

pg_dump nous aide à obtenir des informations sur la base de données elle-même. -U est le nom d'utilisateur. Mon utilisateur pgadmin n'a pas de mot de passe défini, je n'ai donc pas besoin d'en mettre un. Le site -t signifie spécifier pour une seule table. --schema-only signifie qu'il faut imprimer uniquement les données relatives à la table, et non les données de la table. Voici la commande exacte que j'utilise :

Obtenez le nom de la table et les informations sur le type de données de postgresql avec SQL :

CREATE TABLE your_table(  thekey integer NOT NULL,
                          ticker character varying(10) NOT NULL,
                          date_val date,
                          open_val numeric(10,4) NOT NULL
); 

SELECT table_name, column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'your_table'; 

Qui imprime :

 table_name  column_name      data_type      

 your_table  thekey       integer            
 your_table  ticker       character varying  
 your_table  date_val     date               
 your_table  open_val     numeric

Answer 4

La méthode la plus simple à laquelle je pense est d'installer pgAdmin 3 ( trouvé ici ) et utilisez-le pour visualiser votre base de données. Il générera automatiquement une requête qui créera la table en question.

Answer 5

Si vous voulez faire cela pour plusieurs tables à la fois, vous devez utiliser le commutateur -t plusieurs fois (il m'a fallu un certain temps pour comprendre pourquoi les listes séparées par des virgules ne fonctionnaient pas). Il peut également être utile d'envoyer les résultats vers un fichier de sortie ou un tube vers un serveur postgres sur une autre machine.

pg_dump -t table1 -t table2 database_name --schema-only > dump.sql

pg_dump -t table1 -t table2 database_name --schema-only | psql -h server_name database_name