{"id":4136,"date":"2020-11-26T15:26:04","date_gmt":"2020-11-26T21:26:04","guid":{"rendered":"https:\/\/www.logikalsolutions.com\/wordpress\/?p=4136"},"modified":"2023-11-27T18:24:31","modified_gmt":"2023-11-28T00:24:31","slug":"postgresql-oddity","status":"publish","type":"post","link":"https:\/\/www.logikalsolutions.com\/wordpress\/information-technology\/postgresql-oddity\/","title":{"rendered":"Interesting PostgreSQL Oddity"},"content":{"rendered":"\n

I was working on a tiny database to send out some emails with varying subjects. Decided to use PostgreSQL so I could have a real database as the back end. SQLite doesn’t share (on most platforms) so I couldn’t add data while the program was running. Being able to add stuff and check on progress while running is one of those design things you can’t kick when you’ve been in IT as long as me.<\/p>\n\n\n\n

Nothing odd about the definition of the table. It only has two columns.<\/p>\n\n\n\n

<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
CREATE<\/span> <\/span>TABLE<\/span> <\/span>agile_book_subjects<\/span> (<\/span><\/span>\n\t<\/span>subject<\/span> <\/span>VARCHAR<\/span>(<\/span>80<\/span>),<\/span><\/span>\n\tlast_used <\/span>VARCHAR<\/span>(<\/span>60<\/span>)<\/span><\/span>\n\t); <\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
Opted to be a bit lazy when I created the test data. I created a text file with the following:<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
subject,last_used<\/span><\/span>\nSubject<\/span> <\/span>Line<\/span> <\/span>1<\/span>,<\/span><\/span>\nSubject<\/span> <\/span>Line<\/span> <\/span>2<\/span>,<\/span><\/span>\nSubject<\/span> <\/span>Line<\/span> <\/span>3<\/span>,<\/span><\/span>\nSubject<\/span> <\/span>Line<\/span> <\/span>4<\/span>,<\/span><\/span>\nSubject<\/span> <\/span>Line<\/span> <\/span>5<\/span>,<\/span><\/span>\nSubject<\/span> <\/span>Line<\/span> <\/span>6<\/span>,<\/span><\/span>\nSubject<\/span> <\/span>Line<\/span> <\/span>7<\/span>,<\/span><\/span>\nSubject<\/span> <\/span>Line<\/span> <\/span>8<\/span>,<\/span><\/span>\nSubject<\/span> <\/span>Line<\/span> <\/span>9<\/span>,<\/span><\/span>\nSubject<\/span> <\/span>Line<\/span> <\/span>10<\/span>,<\/span><\/span>\nSubject<\/span> <\/span>Line<\/span> <\/span>11<\/span>,<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Next I created a simple import script.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
# import_email_subjects.sh<\/span><\/span>\n#<\/span><\/span>\nif<\/span> <\/span>[<\/span> <\/span>-z<\/span> <\/span>"<\/span>$1<\/span>"<\/span> <\/span>];<\/span> <\/span>then<\/span><\/span>\n    <\/span>echo<\/span> <\/span>"<\/span>must pass csv file name to import<\/span>"<\/span><\/span>\n    <\/span>echo<\/span> <\/span>"<\/span>first line must have column names<\/span>"<\/span><\/span>\n    <\/span>exit<\/span><\/span>\nfi<\/span><\/span>\n<\/span>\nexport<\/span> PGUSER<\/span>=<\/span>roland<\/span><\/span>\nexport<\/span> PGPASSWORD<\/span>=<\/span>spooge_1<\/span><\/span>\n<\/span>\npsql<\/span> <\/span>-q<\/span> <\/span>-d<\/span> <\/span>email_marketing<\/span> <\/span>-c<\/span> <\/span>"<\/span>copy agile_book_subjects from '<\/span>$1<\/span>' csv header;<\/span>"<\/span><\/span>\n<\/span>\npsql<\/span> <\/span>-q<\/span> <\/span>-d<\/span> <\/span>email_marketing<\/span> <\/span>-c<\/span> <\/span>"<\/span>select count('*') from agile_book_subjects;<\/span>"<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
Here is what happened<\/h3>\n\n\n\n
When I run it all looks well. I ass-u-me it would have gagged if it didn’t like the missing value.<\/p>\n\n\n\n
\"\"
11 records as expected<\/figcaption><\/figure>\n\n\n\n
While the application is running I see only one subject being used. This is odd. I perform a few queries in another terminal.<\/p>\n\n\n\n
\"\"
Ascending sort seems borked<\/figcaption><\/figure>\n\n\n\n
Well, if that doesn’t look like a Microsoft product! My ascending sort puts the only record with a value in the sort column on top. The select statement in the Java program is as follows:<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> <\/span>subject<\/span> <\/span>FROM<\/span> agile_book_subjects <\/span>ORDER BY<\/span> last_used <\/span>ASC<\/span> <\/span>LIMIT<\/span> <\/span>1<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
\"\"
Result of actual select used<\/figcaption><\/figure>\n\n\n\n
If it is unable to sort on a null value, how did it get the first row? Better yet, explain this:<\/p>\n\n\n\n
\"\"
Nulls have a higher value than text?<\/figcaption><\/figure>\n\n\n\n
How is it the nulls sort above the record with actual text? I’m on Ubuntu 20.04 LTS using stuff out of the repos if anyone is interested in the platform.<\/p>\n\n\n\n
Of course the fix was to stick a 0 in for last_used then recreate the database and repopulate the table. Still, if it can’t handle nulls in a column as one would expect nulls, it shouldn’t have given me the first row. Is it really putting something like COBOL HIGH-VALUES in the column? <\/p>\n\n\n\n
A value should always sort higher than a null value.<\/p>\n\n\n\n
Kind of reminds me of this song<\/p>\n\n\n\n
\n