Re: What defines the output format of a Postgres Timestamp

[haukex]

Now I'm wondering where the output format is defined.

A quick look at the documentation: sections 8.5.2. Date/Time Output and 19.11.2. Locale and Formatting appear to discuss this.

I would have to change each and every select statement to have a TO_CHAR with the proper format.

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

In another piece of code I have lying around that uses Mojo::Pg, I use DateTime::Format::Strptime to parse the date strings coming from Postgres and DateTime's strftime to format them, though I suspect that's going to be less efficient than letting the database handle it.

[Skeeve]

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.

[haukex]

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.

[jcb]

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.

[haukex]

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

[Skeeve]

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