1.1 常用SQL。以表test1为例

-- 生成完整的建表语句（包含所有列和约束）
WITH table_info AS (
    -- 获取表的列定义
    SELECT 
        'CREATE TABLE ' || n.nspname || '.' || c.relname || ' (' ||
        string_agg(
            '    ' || a.attname || ' ' || 
            pg_catalog.format_type(a.atttypid, a.atttypmod) ||
            CASE 
                WHEN a.attnotnull THEN ' NOT NULL'
                ELSE ''
            END ||
            COALESCE(' DEFAULT ' || pg_get_expr(d.adbin, d.adrelid), ''),
            ',' || E'\n'
            ORDER BY a.attnum
        ) AS columns
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_attribute a ON a.attrelid = c.oid
    LEFT JOIN pg_attrdef d ON (a.attrelid = d.adrelid AND a.attnum = d.adnum)
    WHERE c.relname = 'test1'
      AND n.nspname = 'public'
      AND a.attnum > 0 
      AND NOT a.attisdropped
    GROUP BY n.nspname, c.relname
),
constraint_info AS (
    -- 获取表的约束定义（主键、外键、唯一约束等）
    SELECT 
        string_agg(
            '    CONSTRAINT ' || conname || ' ' || 
            pg_get_constraintdef(oid),
            ',' || E'\n'
        ) AS constraints
    FROM pg_constraint
    WHERE conrelid = 'public.test1'::regclass
)
SELECT 
    columns || 
    CASE 
        WHEN constraints IS NOT NULL THEN ',' || E'\n' || constraints
        ELSE ''
    END || E'\n);' AS create_table_statement
FROM table_info, constraint_info;

2.1 win10绿色安装postgresql18

2.1.1 下载

绿色版下载页：

https://www.enterprisedb.com/download-postgresql-binaries?utm_source=chatgpt.com

绿色版下载链接：

https://get.enterprisedb.com/postgresql/postgresql-17.8-1-windows-x64-binaries.zip

pgadmin下载页： https://www.pgadmin.org/download/

pgadmin下载链接  ：  https://pgadmin-archive.postgresql.org/pgadmin4/v9.9/windows/pgadmin4-9.9-x64.exe

2.1.2 安装脚本

@echo off
rem Assuming the installation path of PostgreSQL 18 is: D:\datum\program\database\pgsql\, the data path is: D:\datum\appdata\pg18\data, and the password is stored in pg_password.txt
rem 

set PATH=D:\datum\program\database\pgsql\bin;%SystemRoot%\System32;

cd /d D:\datum\program\database\pgsql\bin
initdb.exe -D D:\datum\appdata\pg18\data -U postgres --pwfile=D:\datum\program\database\pgsql\pg_password.txt
cd /d D:\datum\program\database\pgsql

2.1.3 安装脚本

pg_ctl.exe -D D:\datum\appdata\pg18\data -l D:\datum\appdata\pg18\logfile start

2.1.4 安装过程中可能的问题

如果安装的过程中报错： 

initdb: error: program "postgres" was found by "D:/datum/program/database/pgsql/bin/initdb.exe" but was not the same version as initdb

解决： 更改注册表里的相关值，就是65001之类的那玩意。 只要把这个值清空就行了。
注册表位置： KEY_LOCAL_MACHINE\Software\Microsoft\Command Processor\Autorun

参考： https://www.cnblogs.com/wang2650/p/17758667.html

2.2 ubuntu绿色安装postgresql18

安装依赖：

sudo apt update

sudo apt install -y build-essential libreadline-dev zlib1g-dev flex bison libssl-dev libssl3 libicu74 libreadline8t64 zlib1g wget libpq5

安装程序：

apt install -y postgresql postgresql-contrib