|
Re^3: What defines the output format of a Postgres Timestamp
by haukex (Archbishop) on Jan 26, 2021 at 12:33 UTC
|
This doesn't free me from adding a TO_CHAR to every timestamp column which is cumbersome if you have to replace every "select * …" with an explicit list of all columns.
True, but at the moment I don't know enough about Pg to suggest any better solutions than the ones I already have. As for the Perl solution, note it's possible to detect date/time columns by inspecting $sth->{pg_type} (in my code I use a regex like /^(?:datetime|timestamp(?:tz)?)$/i). For other database typesdrivers it's $sth->{TYPE} (I don't think this is standardized). And a comment in terms of future-proofing the code, SELECT * only makes sense to me if you're using selectrow_hashref or one of its variants, and otherwise, using query builders is useful too; Mojo::Pg provdes easy access to SQL::Abstract.
| [reply]
[d/l]
[select] |
Re^3: What defines the output format of a Postgres Timestamp
by jcb (Parson) on Jan 27, 2021 at 02:22 UTC
|
if you have to replace every "select * …" with an explicit list of all columns
Unless you are introspecting the returned data to determine its structure or otherwise processing it very flexibly, you should be explicitly listing all of the columns you want to ensure that the data returned from the DB matches the order your program expects. "SELECT *" seems to be meant for interactive use and I suspect that the order of columns returned from that type of query is unspecified.
| [reply] |
|
|
I suspect that the order of columns returned from that type of query is unspecified.
"Unspecified" sounds a bit like it might change when the database table hasn't changed, and that would surprise me if it were the case. It seems different from vendor to vendor, but at least according to this page on the Postgres Wiki, "Postgres currently defines column order based on the attnum column of the pg_attribute table."
| [reply] |
|
|
The problem is what is considered a table change. What about changing the precision of a column? I'm aware of a (closed source) DB engine which changes the order of columns for some precision changes (namely when the new type takes up more bytes than the old one) but keeps it the same for the rest.
map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
| [reply]
[d/l] |
|
|
|
|
# Pseudocode
my $result = $db->query('SELECT * FROM my_table')->hashes;
print "First value of price was: ",$result->[0]{price},"\n";
s$$([},&%#}/&/]+}%&{})*;#$&&s&&$^X.($'^"%]=\&(|?*{%
+.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e
| [reply]
[d/l]
[select] |
|
|
My program does not expect any order. That's what "hashes" are made for.
Well, to continue down this avenue of argumentation: If you're using hashes, then isn't it also likely that you know all the names of the keys in advance? Then you could do $db->select( my_table => ['price',...] )->hashes. Then you wouldn't be getting more columns than necessary when your table definition changes, you'd get hard failures instead of unexplained undefs when your column names change, and so on. I know that listing all columns feels tedious, but I tend to agree that SELECT * is brittle, and IMHO one solution is having the column names in Perl variables and then building the queries instead of hard-coding the SQL.
Plus, query building with SQL::Abstract means you can do fun things on the Perl side like using map to easily apply the TO_CHAR function to multiple columns, something like $db->select( my_table => [ map { \["TO_CHAR(?,'YYYY-MM-DDTHH24:MI:SS') AS ?",$_,$_] } @datetime_columns ] ) (untested).
Yet another idea might be to look at extending Mojo::Pg::Results's expand method, which already processes JSON columns, you could use this to convert date/time columns to DateTime objects automatically. The "advantage" of this solution would be that you could continue using SELECT *...
| [reply]
[d/l]
[select] |
|
|
|
|