GoogleCloudPlatform / GoogleCloudPlatform/cloud-spanner-emulator
ON CONFLICT does not match production for PostgresSQL in latest release
- Dominant language
- C++
- Stars
- 334
- Forks
- 77
- Avg merge
- 8m
- Merged PRs (30d)
- 2
Description
I believe emulator and live spanner do not match in functionality for `ON CONFLICT DO UPDATE SET`. This seems to be a problem with the latest release as this is NOT an issue with `gcr.io/cloud-spanner-emulator/emulator:1.5.43`
```sql
CREATE TABLE IF NOT EXISTS table_one (
user_id VARCHAR(36) NOT NULL,
attribute BYTEA NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
updated_at TIMESTAMPTZ NOT NULL,
PRIMARY KEY (user_id),
CONSTRAINT fk_table_one_user_id
FOREIGN KEY (user_id) REFERENCES users (id)
);
```
**This works live in GCP but will fail in the simulator.**
```sql
INSERT INTO table_one (
user_id,
attribute,
created_at,
updated_at
) VALUES (
$1, $2, $3, $4
)
ON CONFLICT (user_id) DO UPDATE SET
user_id = EXCLUDED.user_id,
attribute = EXCLUDED.attribute,
created_at = EXCLUDED.created_at,
updated_at = EXCLUDED.updated_at;
```
**This fails live in GCP but succeeds in the simulator**
```sql
INSERT INTO table_one (
user_id,
attribute,
created_at,
updated_at
) VALUES (
$1, $2, $3, $4
)
ON CONFLICT (user_id) DO UPDATE SET
attribute = EXCLUDED.attribute,
created_at = EXCLUDED.created_at,
updated_at = EXCLUDED.updated_at;
```
Contributor guide
Assessment
This issue has not been assessed yet.