Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Re^2: What defines the output format of a Postgres Timestamp

by Skeeve (Parson)
on Jan 26, 2021 at 12:25 UTC ( #11127473=note: print w/replies, xml ) Need Help??


in reply to Re: What defines the output format of a Postgres Timestamp
in thread What defines the output format of a Postgres Timestamp

This StackOverflow answer suggests defining a function to curry the arguments.

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.


s$$([},&%#}/&/]+}%&{})*;#$&&s&&$^X.($'^"%]=\&(|?*{%
+.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e

Replies are listed 'Best First'.
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.

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.

      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."

        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]
      data returned from the DB matches the order your program expects

      My program does not expect any order. That's what "hashes" are made for.

      # 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
        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 *...

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://11127473]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (4)
As of 2022-12-09 16:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?