Search code examples
sqlpostgresqlsql-insertstring-constant

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

![enter image description here]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?

Solution

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