GitHub
07/10/2025, 7:18 PMcreate_empty_table
Issue created by edgarrmondragon
### Singer SDK Version
0.47.4
### Is this a regression?
• Yes
### Python Version
NA
### Bug scope
Targets (data type handling, batching, SQL object generation, etc.)
### Operating System
No response
### Description
## Summary
The create_empty_table method doesn’t maintain the order of columns specified in the primary_keys sequence when creating the primary key constraint. Instead, it adheres to the order of properties as they are listed in the schema, which can result in erroneous primary key definitions.
## Description
In the current implementation of create_empty_table
sdk/singer_sdk/connectors/sql.py
Line 1279 in</meltano/sdk/commit/bba4d8818fcf77a015437ee785713aa50b1559fe|bba4d88>
| def create_empty_table( |
| ----------------------- |
(downstream too: https://github.com/MeltanoLabs/target-postgres/blob/3d16266bcc1be6d01f09a90607d5288bbfeb3374/target_postgres/connector.py#L421)
the method iterates through schema['properties'].items() and adds columns, setting the primary key flag if a column exists the primary_keys sequence.
This means the primary key columns will be ordered according to how properties are defined in the schema dictionary, not according to the explicit order specified in the primary_keys sequence.
This does not allow you to rely on the primary key column order that clients specify. Primary key order matters for database performance and indexing strategies. Additionally, the same schema with different property ordering could result in different primary key definitions.
## Expected Behavior
The primary key columns should be created in the exact order specified in the primary_keys sequence parameter.
## Suggested Fix
Do not set the primary_key property on the column and use PrimaryKeyConstraint.
def create_empty_table( # noqa: PLR0913
self,
table_name: str,
meta: sa.MetaData,
schema: dict[str, Any],
connection: sa.engine.Connection,
primary_keys: t.Sequence[str] | None = None,
partition_keys: list[str] | None = None,
as_temp_table: bool = False,
) -> sa.Table:
"""Create an empty target table.
Args:
table_name: the target table name.
meta: the SQLAlchemy metadata object.
schema: the JSON schema for the new table.
connection: the database connection.
primary_keys: list of key properties.
partition_keys: list of partition keys.
as_temp_table: True to create a temp table.
Returns:
The new table object.
Raises:
NotImplementedError: if temp tables are unsupported and as_temp_table=True.
RuntimeError: if a variant schema is passed with no properties defined.
"""
columns: list[sa.Column[Any]] = []
primary_keys = primary_keys or []
try:
properties: dict[str, Any] = schema["properties"]
except KeyError:
raise RuntimeError(
f"Schema for table_name: '{table_name}'"
f"does not define properties: {schema}"
) from None
for property_name, property_jsonschema in properties.items():
columns.append(
sa.Column(
property_name,
self.to_sql_type(property_jsonschema),
autoincrement=False, # See: https://github.com/MeltanoLabs/target-postgres/issues/193 # noqa: E501
)
)
table_args = []
if primary_keys:
missing_pk_columns = [col for col in primary_keys if col not in properties]
if missing_pk_columns:
raise RuntimeError(
f"Primary keys {missing_pk_columns} are not defined in"
f"schema {schema} for table_name '{table_name}'"
)
table_args.append(sa.PrimaryKeyConstraint(*primary_keys))
table_kwargs: dict[str, Any] = {}
if as_temp_table:
table_kwargs["prefixes"] = ["TEMPORARY"]
new_table = sa.Table(table_name, meta, *columns, *table_args, **table_kwargs)
new_table.create(bind=connection)
return new_table
### Link to Slack/Linen
No response
meltano/sdkGitHub
07/15/2025, 8:48 PMcreate_empty_table
Issue closed as completed by edgarrmondragon
meltano/sdk