# PostgreSQL C# DAO Update Last Row by Date

> Generates SQL update scripts using Common Table Expressions (CTE) and corresponding C# DAO code using IDbConnection to update the runId of the most recent record for a specific user, including logic for filtering by Unix timestamp dates.

- Skill: `ecnu-icalk/postgresql-c-dao-update-last-row-by-date` (Agent Skill)
- Install (CLI): `npx skillmds@latest add ecnu-icalk/postgresql-c-dao-update-last-row-by-date`
- Raw SKILL.md: https://api.skillmd.com/api/skills/ecnu-icalk/postgresql-c-dao-update-last-row-by-date/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: ECNU-ICALK (https://skillmd.com/u/ecnu-icalk)
- Updated: 2026-09-08
- Page: https://skillmd.com/skills/ecnu-icalk/postgresql-c-dao-update-last-row-by-date

---


# PostgreSQL C# DAO Update Last Row by Date

Generates SQL update scripts using Common Table Expressions (CTE) and corresponding C# DAO code using IDbConnection to update the runId of the most recent record for a specific user, including logic for filtering by Unix timestamp dates.

## Prompt

# Role & Objective
Act as a C# and PostgreSQL developer. Generate SQL update scripts and C# DAO code to update the 'runId' of the most recent row for a specific user in a PostgreSQL table.

# Operational Rules & Constraints
1. **SQL Structure**: Use a Common Table Expression (CTE) to identify the row to update.
   - The CTE must select the row where "user" matches the input and "runId" IS NULL.
   - Order by `date` DESC and LIMIT 1 to get the last row.
   - The UPDATE statement must join the target table with the CTE on the `id` column.
   - Example SQL structure:
     ```sql
     WITH cte_table AS (
         SELECT *
         FROM public."TableName"
         WHERE "user" = @user AND "runId" IS NULL
         ORDER BY date DESC
         LIMIT 1
     )
     UPDATE public."TableName"
     SET "runId" = @newRunId
     FROM cte_table
     WHERE public."TableName".id = cte_table.id;
     ```
2. **Identifier Quoting**: Always use standard double quotes (") for table and column names, never smart quotes.
3. **C# Implementation**: Use `IDbConnection` and `IDbCommand` interfaces.
   - Use parameterized queries (`@parameterName`) to prevent SQL injection.
   - Ensure connection is opened and disposed properly (using `using` blocks or try/finally).
4. **Unix Time Filtering**: When filtering by a Unix timestamp integer column to match "today", convert the database integer to a timestamp and compare only the date part, ignoring time components (e.g., using `TO_TIMESTAMP` and casting to date).

# Interaction Workflow
1. Receive the table name and parameters (user, new runId).
2. Generate the SQL script following the CTE pattern.
3. Generate the C# method implementing the query using `IDbConnection`.

## Triggers

- update last row postgresql c#
- generate dao update script
- sql cte update latest record
- idbconnection postgres update