Re: BUG #18527: Imported data using a csv file and made a latest entry into same table using a form. Got an issue!

On 2024-07-01 17:08 +0200, PG Bug reporting form wrote:
> When I bulk uploaded some data using a csv file, it was all good. There were
> 543 rows in order and it went into the table and sat cleanly!
>
> I've connected the same table to an input form. When I hit submit, the
> latest entry which is supposed to be the last entry with a number id of 544
> didn't go to the end of the table (bottom most) but instead sat on top of
> all previous bulk uploaded entries.
>
> Each entry has this kind of standard ID: DCMARIN10042024001
> Subsequent entries will have the same ID with last 3 digits incrementing by
> 1.
> DCMARIN10042024002
> DCMARIN10042024003
> ...
> ...
> DCMARIN10042024543
>
> These ID's are generated using the below python code (flask):
> def generate_unique_id(dept, sub_dept, table):
> ProjectCode = ""
>
> # Automatic Start Date capture - Production
> #start_date = date.today()
>
> # Start Date - Testing
> start_date = datetime.strptime('01-08-2024', '%d-%m-%Y').date()
>
> # To check if there is at least 1 entry in the DB or not
> first_entry = table.query.first()
> lastRow = str(table.query.order_by(table.id.desc()).first().id) if
> first_entry else None

Do you expect that lastRow is the one with the latest date encoded in
its ID? If so, then date format %d%m%Y won't give you the expected
chronological ordering. Use %Y%m%d (big-endian) instead.

> # Format Date in lastRow from String to Date Object
> prev_date = datetime.strptime(lastRow[7:15], "%d%m%Y").date() if lastRow
> is not None else None
>
> # If today > latest date that is already existing in DB - For first
> entry of today
> if first_entry is None or start_date > prev_date:
> ProjectCode =
> f"{dept}{sub_dept}{start_date.strftime('%d%m%Y')}001"
>
> # Subsequent entries for the same date
> elif start_date == prev_date:
> ProjectCode =
> f"{dept}{sub_dept}{start_date.strftime('%d%m%Y')}{str(int(lastRow[15:18]) +
> 1).zfill(3)}"
>

This will generate duplicate IDs if you generate more than 1000 IDs per
day because this uses some weird modular arithmetic with
str(int(lastRow[15:18])+1).zfill(3), which will eventually generate 998,
999, 1000, 101, 102, ...

The row with number 1000 won't necessarily be the "last row", even with
date format %Y%m%d. For example, 101 will come before 1000:

test=> select unnest('{DCMARIN202404101000,DCMARIN20240410101}'::text[]) order by 1 desc;
unnest
---------------------
DCMARIN20240410101
DCMARIN202404101000
(2 rows)

> return ProjectCode

--
Erik