Sign inTry Free

PostgreSQL to SingleStore Data Type Mapping

On this page

The following mappings define how PostgreSQL data types are represented in SingleStore.

For more information about SingleStore data types, refer to Data Types.

String Types

PostgreSQL Type

SingleStore Type (more info)

CHAR

CHAR

VARCHAR

VARCHAR

BPCHAR

CHAR

TEXT

LONGTEXT

UUID

VARCHAR

Integer Types

PostgreSQL Type

SingleStore Type (more info)

SMALLINT

SMALLINT

INTEGER

INT

BIGINT

BIGINT

SMALLSERIAL

SMALLINT

SERIAL

INT

BIGSERIAL

BIGINT

BOOL / BOOLEAN

TINYINT

Floating Point Types

PostgreSQL Type

SingleStore Type (more info)

REAL

FLOAT

FLOAT4

FLOAT

FLOAT8

DOUBLE

DOUBLE PRECISION

DOUBLE

Decimal and Numeric Types

PostgreSQL Type

SingleStore Type (more info)

DECIMAL

DECIMAL

NUMERIC

DECIMAL (65,30)

MONEY

DECIMAL

Binary String Types

PostgreSQL Type

SingleStore Type (more info)

BYTEA

LONGBLOB

Date and Time Types

PostgreSQL Type

SingleStore Type (more info)

DATE

DATE

TIME

TIME(6)

TIMESTAMP

DATETIME(6)

TIMESTAMPTZ

DATETIME(6)

Invalid PostgreSQL DATETIME and TIMESTAMP Values

Certain invalid DATETIME values are automatically converted to valid SingleStore values:

PostgreSQL Type

SingleStore Type

0000-00-00 00:00:00

1000-01-01 00:00:00

0001-00-00 00:00:00

1001-01-01 00:00:00

0001-01-01 01:01:01

1001-01-01 01:01:01

0025-01-01 00:00:00

1025-01-01 00:00:00

For TIMESTAMP, the following conversion takes place:

PostgreSQL Type

SingleStore Type

0000-00-00 00:00:00

1000-01-01 00:00:00

Flow converts these invalid values using the following rules, in order:

  1. Zero dates

    If the value is 0000-00-00 00:00:00, convert it to 1000-01-01 00:00:00.

  2. Years below 1000

    If the year is between 0000 and 0999, add 1000 to the year.

    Examples:

    • Year 0001 becomes 1001

    • Year 0025 becomes 1025

    • Year 0999 becomes 1999

  3. Invalid month or day (00)

    If the month or day is 00, it is replaced with 01.

    • If month is 00, replace it with 01.

    • If day is 00, replace it with 01.

    For example:

    • 0001-00-00 → (Rule 2) → 1001-00-00 → (Rule 3) → 1001-01-01

These rules ensure that common invalid values can be ingested without pipeline failures while preserving their relative ordering as much as possible.

JSON Types

PostgreSQL Type

SingleStore Type (more info)

JSON

JSON

JSONB

JSON

Additional Notes

  • TIME is mapped to TIME(6).

  • TIMESTAMP and TIMESTAMPTZ types are mapped to DATETIME(6). 

Last modified:

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK

Try Out This Notebook to See What’s Possible in SingleStore

Get access to other groundbreaking datasets and engage with our community for expert advice.

Sign UpLog In