将MySQL中的0000-00-00 00:00:00日期时间插入到PostgreSQL中。

Question

我正在尝试从MySQL数据库迁移到PostgreSQL数据库。 我创建了一个包含以下查询的脚本：

 if ($result->num_rows > 0) {
     while($row = $result->fetch_assoc()) { 

     $psql = "INSERT INTO subscriptions_sub (sub_id,cust_id,tarrif_id,des,datetime_created,datetime_modified,credit,credit_limit,status,date_start,date_end,backoffice_userref,backoffice_deleted,backoffice_createdon,backoffice_createdby, backoffice_updatedon,backoffice_updatedby,backoffice_administration,backoffice_readonly,backoffice_order,backoffice_dummy)
    VALUES('" .(int) $row["sub_id"] . "','" . (int) $row["cust_id"] . "','" . (int) $row["tarrif_id"] . "','" . $row["des"] . "','" . $row["datetime_created"] . "','" . $row["datetime_modified"] . "','" . $row["credit"] . "','" . $row["credit_limit"] . "','" . $row["status"] . "','" . $row["date_start"] . "','" . $row["date_end"] . "','" . $row["backoffice_userref"] . "','" . $row["backoffice_deleted"] . "','" . $row["backoffice_createdon"] . "','" . $row["backoffice_createdby"] . "','" . $row["backoffice_updatedon"] . "','" . (int) $row["backoffice_updatedby"] . "','" . (int) $row["backoffice_administration"] . "','" . $row["backoffice_readonly"] . "','" . (int)$row["backoffice_order"] . "','" . $row["backoffice_dummy"] . "');
    ";
    print_r($row);

    $ret = pg_query($db, $psql);
    if(!$ret){
        echo pg_last_error($db);
    } else {
        echo "Records created succesfully\n";
    }

}

这是我的订阅.sub文件

 _columns = {
    'sub_id': fields.integer('Subscription ID', size=10),
    'cust_id': fields.many2one('res.partner','customer_id', 'Customer ID'),
    'tarrif_id': fields.integer('Tariefplan ID', size=10, required=True),
    'des': fields.char('Description', size=64),
    'datetime_created': fields.datetime('Created', default='0'),
    'datetime_modified': fields.datetime('Modified', default='0'),
    'credit': fields.float('Credit', digits=(10,4)),
    'credit_limit': fields.float('Credit limit', digits=(10,4)),
    'status': fields.selection([('active','Active'),('inactive','Inactive'),('ended','Ended'),('deleted','Deleted'),('disabled','Disabled')], 'Status', default='active', required=True),
    'date_start': fields.date('Date start'),
    'date_end': fields.date('Date end'),
    'backoffice_userref': fields.integer('Backoffice_userref', size=10),
    'backoffice_deleted': fields.boolean('Backoffice_deleted', size=1, default='0', required=True),
    'backoffice_createdon': fields.datetime('Backoffice_createdon', default='0'),
    'backoffice_createdby': fields.integer('Backoffice_createdby', size=10, default='0', required=True),
    'backoffice_updatedon': fields.datetime('Backoffice_updatedon', default='0'),
    'backoffice_updatedby': fields.integer('Backoffice_updatedby', size=10, default='0', required=True),
    'backoffice_administration': fields.integer('Backoffice_administration', size=10, default='1', required=True),
    'backoffice_readonly': fields.boolean('Backoffice_readonly', size=1, default='0', required=True),
    'backoffice_order': fields.integer('Backoffice_order', size=20, default='-1', required=True),
    'backoffice_dummy': fields.boolean('Backoffice_dummy', size=1, default='0', required=True),
    #'qty_available': fields.related('customer_id','subscription_id',type='char', relation="partner.res", string="subscription", store=True)
}

例如：在mysql数据库中，datetime_created字段的类型为TIMESTAMP，并且其值为“0000-00-00 00:00:00”。当我执行脚本时，出现以下错误：

ERROR: date/time字段值超出范围：“0000-00-00 00:00:00”。

是否可以将mysql中的datetime转换为postgresql？

- ruud

---

传递NULL而不是0000-00-00 00:00:00，据我所知，PostgreSQL不接受0000-00-00 00:00:00作为时间戳字段的默认/空值。 - Vivek S.

Answer 1

create table mytbl1 (dte timestamp);

insert into mytbl1 values (now());
--Query returned successfully: one row affected

insert into mytbl1 values ('0000-00-00 00:00:00');
--ERROR:  date/time field value out of range: "0000-00-00 00:00:00"

由于PostgreSQL不接受0或0000-00-00 00:00:00，所以您可以使用NULLIF()函数，如下所示：

insert into mytbl1 
values(NULLIF('0000-00-00 00:00:00','0000-00-00 00:00:00')::timestamp);

所以您应该根据此安排插入语句。

insert into subscriptions_sub(datetime_created) 
values (NULLIF(your_field_name,'0000-00-00 00:00:00')::timestamp)  

- Vivek S.