Trouble inserting value into a TEXT column with Postgres

I am getting an error when I try to insert an alphanumeric value into a TEXT column in a Postgres DB. This is the table definition

]1

This is the query I am using to insert data into the table

INSERT INTO sensor_data
            (
                time,
                i1, i2, i3, i4,
                i5, i6, i7, i8,
                mrt,air_temperature,
                humidity,tvoc, device_id)
        VALUES (to_timestamp(1667595922) AT TIME ZONE 'UTC',
                41.340000,  26.160000, 25.860000, 26.160000,
                25.900000, 25.960000, 26.720000, 25.580000,
                26.085000, 28.065536,
                 55.204773,  40.000000, 1a0032000947363339343638
            );

This is the error message I get

ERROR: syntax error at or near “a0032000947363339343638” LINE 12: 55.204773, 40.000000, 1a00320009473633…

When I enter this query, it works just fine.

INSERT INTO sensor_data
            (
                time,
                i1, i2, i3, i4,
                i5, i6, i7, i8,
                mrt,air_temperature,
                humidity,tvoc, device_id)
        VALUES (to_timestamp(1667595922) AT TIME ZONE 'UTC',
                41.340000,  26.160000, 25.860000, 26.160000,
                25.900000, 25.960000, 26.720000, 25.580000,
                26.085000, 28.065536,
                 55.204773,  40.000000, 10032000947363339343638
            );

The only difference between the 2 queries is the device_id value. The insert fails when the value is ‘1a0032000947363339343638’ and work fine when the value is ‘10032000947363339343638’.

Why would the insert fail when I try to insert ‘1a0032000947363339343638′ even though the data type is a TEXT? And how do I get the table to accept 1a0032000947363339343638’ for a device Id?

Answer

While numbers do not need to be quoted, strings must be quoted to distinguish them from other syntax and to allow for spaces.

In Postgres this must be a single quote. Other databases might allow different quoting.

INSERT INTO sensor_data
            (
                time,
                i1, i2, i3, i4,
                i5, i6, i7, i8,
                mrt,air_temperature,
                humidity,tvoc, device_id)
        VALUES (to_timestamp(1667595922) AT TIME ZONE 'UTC',
                41.340000,  26.160000, 25.860000, 26.160000,
                25.900000, 25.960000, 26.720000, 25.580000,
                26.085000, 28.065536,
                 55.204773,  40.000000, '1a0032000947363339343638'
            );

You can read more in the Postgres documentation on constants.