PNG

​

Description

• Implicit (the default — when neither x nor y is present). Each record corresponds to a single pixel; pixels are filled in scanline order: left to right, top to bottom.
• Explicit (when x and y columns are present, both of integer types). The x and y columns give the pixel coordinates. Records with coordinates outside the image are silently ignored. In case of multiple records with the same coordinates, the last one wins (painter’s algorithm).

​

Example usage

​

Implicit coordinates (row-per-pixel), RGB

SELECT
    toUInt8(x * 25) AS r,
    toUInt8(y * 25) AS g,
    toUInt8((x + y) * 12) AS b
FROM
(
    SELECT number % 10 AS x, intDiv(number, 10) AS y FROM numbers(100)
)
INTO OUTFILE 'gradient.png'
FORMAT PNG
SETTINGS output_format_image_width = 10, output_format_image_height = 10;

​

Explicit coordinates, grayscale

SELECT
    toInt32(x) AS x,
    toInt32(y) AS y,
    toUInt8(intensity) AS v
FROM points
INTO OUTFILE 'points.png'
FORMAT PNG
SETTINGS output_format_image_width = 512, output_format_image_height = 512;

​

Animation

SELECT
    number % 60 AS t,
    toInt32(intDiv(number, 60) % 64) AS x,
    toInt32((number * 7) % 64) AS y,
    toUInt8(255) AS v
FROM numbers(60 * 64)
INTO OUTFILE 'animation.png'
FORMAT PNG
SETTINGS output_format_image_width = 64, output_format_image_height = 64;

​

Streaming the frames

• t must be non-decreasing; the query throws an exception otherwise. Add ORDER BY t if needed.
• The number of frames is not known when the header has to be written, so the acTL chunk declares an upper bound instead of the exact count. Browsers play such a file, but decoders that trust the declared count (for example, Pillow and some command-line APNG tools) report an error after the last real frame. An animation of a single frame is the exception: the whole result has been read by the time that frame is written, so the count is declared exactly and the output conforms to the specification.

​

Displaying images in the terminal

SELECT toUInt8(x * 25) AS r, toUInt8(y * 25) AS g, toUInt8((x + y) * 12) AS b
FROM (SELECT number % 10 AS x, intDiv(number, 10) AS y FROM numbers(100))
FORMAT PNG
SETTINGS output_format_image_width = 10, output_format_image_height = 10, output_format_image_terminal_mode = 'auto';

​

Format settings