Duplicates in PK

The scenario you've described should indeed cause an error, but it's not behaving as expected. This is likely due to a misunderstanding or a potential issue with your database system.

In a properly functioning relational database system, attempting to insert a duplicate value into a column defined as a PRIMARY KEY should result in an error. The PRIMARY KEY constraint ensures that each value in that column is unique across all rows in the table.

In your example:

1. You're creating a table d_date with date_sk as the PRIMARY KEY.
2. You're inserting two rows with the same date_sk value (1).

The second INSERT statement should fail with a primary key violation error. If it's not, there could be several reasons:

1. The PRIMARY KEY constraint might not have been properly applied when creating the table.
2. There might be an issue with your database configuration or settings.
3. You might be using a database system that doesn't enforce PRIMARY KEY constraints strictly (which would be unusual for a production-grade relational database).

To troubleshoot this:

1. Double-check that the PRIMARY KEY constraint was successfully created with the table.
2. Try running a DESCRIBE or similar command on your table to verify its structure.
3. Ensure you're connected to the correct database and schema.
4. If possible, try to recreate this scenario in a different database instance to see if the behavior persists.

If the issue continues, you may need to consult your database system's documentation or support channels, as this behavior is not standard and could indicate a more serious problem with your database setup or configuration.
Sources
DROP TABLE - Amazon Redshift
Error handling for T-SQL - SQL Server to Aurora PostgreSQL Migration Playbook

here is table ddl:

CREATE TABLE IF NOT EXISTS public.d_date
(
	date_sk INTEGER NOT NULL  ENCODE az64
	,date_id DATE   ENCODE az64
	,PRIMARY KEY (date_sk)
)
DISTSTYLE AUTO
;