GoogleCloudPlatform / GoogleCloudPlatform/cloud-spanner-emulator

ON CONFLICT does not match production for PostgresSQL in latest release

Open
#294 2 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.