insert.md

# 插入

现有一个表：

```postgresql
create table book(
    id serial primary key ,
    title text not null ,
    meta jsonb default '{}'::jsonb,
    price money,
    isbn text not null ,
    publish_at date not null 
);
create unique index on book(isbn);
create index on book using gin(meta);
```

那么下列哪个选项的代码可以执行成功？

## 答案

```postgresql
insert into book(title, price, isbn, publish_at) select 'a book title', 25.4, 'xx-xxxx-xxxx', '2019-12-1'::date;
insert into book(title, price, isbn, publish_at) select 'a other book title', 25.4, 'yy-yyyy-xxxx', '2019-12-1'::date; 
```

## 选项

### 唯一键冲突

```postgresql
insert into book(title, price, isbn, publish_at) select 'a book title', 25.4, 'xx-xxxx-xxxx', '2019-12-1'::date;
insert into book(title, price, isbn, publish_at) select 'a other book title', 35.4, 'xx-xxxx-xxxx', '2019-12-1'::date; 
```

### 缺少必要的列

```postgresql
insert into book(price, isbn, publish_at) select 25.4, 'xx-xxxx-xxxx', '2019-12-1'::date;
insert into book(price, isbn, publish_at) select 35.4, 'yy-yyyy-xxxx', '2019-12-1'::date;
```

### 类型错误

```postgresql
insert into book(title, price, isbn, publish_at) select 'a book title', 'unknown', 'xx-xxxx-xxxx', '2019-12-1'::date;
insert into book(title, price, isbn, publish_at) select 'a other book title', 'unknown', 'xx-xxxx-xxxx', '2019-12-1'::date;
```

### 违反非空约束

```postgresql
insert into book(title, price, isbn, publish_at) select null, 'unknown', 'xx-xxxx-xxxx', '2019-12-1'::date;
insert into book(title, price, isbn, publish_at) select null, 'unknown', 'xx-xxxx-xxxx', '2019-12-1'::date;
```