Tag: formatting

How to render timestamp with a timezone that is different from current?

This question appeared on IRC, and while I wasn't there while it happened, it caught my eye:

» Can I not render this with timezone offset: select ‘2026-01-09 04:35:46.9824-08'::timestamp with time zone at time zone ‘UTC';
» Returns ‘2026-01-09 12:35:46.9824' which is without the offset.

Continue reading How to render timestamp with a timezone that is different from current?

Small improvement for pretty-printing in paste.depesz.com

As you maybe know, some time ago I made paste service, mostly to use for queries, or related text to share on IRC.

One part of it is that it also has pretty printer of provided queries.

Recently I realized that in case of complex join conditions, the output is, well, sub-optimal. For example:

SELECT
    t1.a,
    t2.b,
    t3.c
FROM
    table_1 AS t1
    JOIN table_2 AS t2 ON t1.x = t2.x AND
    t1.y = t2.y AND
    t1.z = t2.z
    LEFT JOIN table_3 AS t3 ON t1.f = t3.f AND
    t2.g = t3.g
WHERE
    t1.v = 1 AND
    t2.t = 'x' AND
    t3.m < 123

Specifically the problem (for me) is bad indentation of join conditions (aside from first).

Anyway – as of now, the same query will be pretty-printed as:

SELECT
    t1.a,
    t2.b,
    t3.c
FROM
    table_1 AS t1
    JOIN table_2 AS t2 ON t1.x = t2.x
        AND t1.y = t2.y
        AND t1.z = t2.z
    LEFT JOIN table_3 AS t3 ON t1.f = t3.f
        AND t2.g = t3.g
WHERE
    t1.v = 1
    AND t2.t = 'x'
    AND t3.m < 123

Also, as a reminder – you can use this pretty printer from command line or some tools, without storing anything on paste.depesz.com site – simply use script that I described earlier.

Hope you'll find it useful.

New SQL pretty printer – based on parsing, and not regexps

For a long time I was looking for SQL pretty printer.

Some queries that I had to deal with, over the years, were just insane to read, like this:

Continue reading New SQL pretty printer – based on parsing, and not regexps

Waiting for PostgreSQL 13 – Support for FF1-FF6 and SSSS datetime format patterns

On 16th of September 2019, Alexander Korotkov committed two patches:

First patch:

Support for SSSSS datetime format pattern
 
SQL Standard 2016 defines SSSSS format pattern for seconds past midnight in
jsonpath .datetime() method and CAST (... FORMAT ...) SQL clause.  In our
datetime parsing engine we currently support it with SSSS name.
 
This commit adds SSSSS as an alias for SSSS.  Alias is added in favor of
upcoming jsonpath .datetime() method.  But it's also supported in to_date()/
to_timestamp() as positive side effect.
 
Discussion: https://postgr.es/m/CAPpHfdsZgYEra_PeCLGNoXOWYx6iU-S3wF8aX0ObQUcZU%2B4XTw%40mail.gmail.com
Author: Nikita Glukhov, Alexander Korotkov

and second one:

Support for FF1-FF6 datetime format patterns
 
SQL Standard 2016 defines FF1-FF9 format patters for fractions of seconds in
jsonpath .datetime() method and CAST (... FORMAT ...) SQL clause.  Parsing
engine of upcoming .datetime() method will be shared with to_date()/
to_timestamp().
 
This patch implements FF1-FF6 format patterns for upcoming jsonpath .datetime()
method.  to_date()/to_timestamp() functions will also get support of this
format patterns as positive side effect.  FF7-FF9 are not supported due to
lack of precision in our internal timestamp representation.
 
Extracted from original patch by Nikita Glukhov, Teodor Sigaev, Oleg Bartunov.
Heavily revised by me.
 
Discussion: https://postgr.es/m/-b497-f39a-923d-%402ndQuadrant.com
Discussion: https://postgr.es/m/CAPpHfdsZgYEra_PeCLGNoXOWYx6iU-S3wF8aX0ObQUcZU%2B4XTw%40mail.gmail.com
Author: Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Alexander Korotkov

Continue reading Waiting for PostgreSQL 13 – Support for FF1-FF6 and SSSS datetime format patterns

Waiting for 9.4 – Add support for wrapping to psql’s “extended” mode.

On 28th of April, Greg Stark committed patch:

Add support for wrapping to psql's "extended" mode. This makes it very
 
feasible to display tables that have both many columns and some large
data in some columns (such as pg_stats).
 
Emre Hasegeli with review and rewriting from Sergey Muraviov and
reviewed by Greg Stark

Continue reading Waiting for 9.4 – Add support for wrapping to psql's “extended" mode.