Every supported .NET type, and what PostgreSQL stores

Runs the sample. Generated for this page; it reads the connection string from the environment.

using ArxSamples.PostgreSql;
using ArxSamples.PostgreSql.Operations.Create.RealCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_POSTGRESQL")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_POSTGRESQL environment variable to your PostgreSQL connection string.");

await new RealCase2_EveryDataType().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.Models;
using ARX.SDK.Services;
using static ArxSamples.PostgreSql.SampleOutput;

namespace ArxSamples.PostgreSql.Operations.Create.RealCases;

/// <summary>
/// Every kind of value the PostgreSQL provider writes, into data_type_showcase, and what PostgreSQL stored, in four
/// scenarios: every supported type, types converted to one that holds every value, null, and a type that's refused.
/// The comment on each line is the stored value as PostgreSQL prints it.
/// </summary>
public sealed class RealCase2_EveryDataType : ISample
{
    public string Name => "create-real-2-every-data-type";

    public string Title => "Every supported .NET type, and what PostgreSQL stores";

    public async Task RunAsync(SampleContext dbContext)
    {
        // The account the showcase row points at.
        var owner = new ArxEntity("account");
        owner["name"] = "Showcase owner";
        var ownerId = await dbContext.Service.CreateAsync(owner);

        await Scenario1_EverySupportedTypeAsync(dbContext, ownerId);

        await Scenario2_TypesThatAreConvertedAsync(dbContext);

        await Scenario3_NullIsStoredAsNullAsync(dbContext);

        await Scenario4_ATypeThatIsRefusedAsync(dbContext);
    }

    /// <summary>1. One row with a value of every supported kind.</summary>
    private static async Task Scenario1_EverySupportedTypeAsync(SampleContext dbContext, ArxRecordId ownerId)
    {
        Step("1. One row with a value of every supported kind");

        var row = new ArxEntity("data_type_showcase");
        row["text_value"] = "Hello, ARX";                                                        // Hello, ARX
        row["varchar_value"] = "Short text";                                                     // Short text
        row["char_value"] = "AB";                                                                // AB (char(3) pads to "AB ")
        row["smallint_value"] = (short)32_000;                                                   // 32000
        row["int_value"] = 2_000_000_000;                                                        // 2000000000
        row["bigint_value"] = 9_000_000_000_000L;                                                // 9000000000000
        row["numeric_value"] = 12_345.678901m;                                                   // 12345.678901
        row["real_value"] = 3.25f;                                                               // 3.25
        row["double_value"] = 2.718281828459045;                                                 // 2.718281828459045
        row["bool_value"] = true;                                                                // true
        row["uuid_value"] = Guid.Parse("6f9619ff-8b86-d011-b42d-00c04fc964ff");                  // 6f9619ff-8b86-d011-b42d-00c04fc964ff
        row["timestamptz_value"] = new DateTime(2026, 11, 7, 14, 30, 0, DateTimeKind.Utc);       // 2026-11-07 14:30:00+00
        row["timestamp_value"] = new DateTime(2026, 11, 7, 9, 0, 0, DateTimeKind.Unspecified);   // 2026-11-07 09:00:00
        row["date_value"] = new DateOnly(2026, 11, 7);                                           // 2026-11-07
        row["time_value"] = new TimeOnly(17, 45, 30);                                            // 17:45:30
        row["interval_value"] = TimeSpan.FromHours(36.5);                                        // 1 day 12:30:00
        row["bytea_value"] = new byte[] { 0xCA, 0xFE };                                          // \xcafe
        row["jsonb_value"] = new ArxJsonB { SerializedValue = "{\"plan\": \"gold\", \"seats\": 25}" };  // {"plan": "gold", "seats": 25}
        row["text_array"] = new[] { "red", "green" };                                            // {red,green}
        row["int_array"] = new[] { 1, 2, 3 };                                                    // {1,2,3}
        row["numeric_array"] = new[] { 1.5m, 2.25m };                                            // {1.5,2.25}
        row["uuid_array"] = new[] { Guid.Parse("00000000-0000-0000-0000-000000000001") };        // {00000000-0000-0000-0000-000000000001}
        row["date_array"] = new[] { new DateOnly(2026, 1, 1), new DateOnly(2026, 12, 31) };      // {2026-01-01,2026-12-31}
        row["timestamptz_array"] = new[] { new DateTime(2026, 1, 1, 0, 0, 0, DateTimeKind.Utc) }; // {"2026-01-01 00:00:00+00"}
        row["mood"] = new ArxSelectionSetValue("Mood", "happy");                                 // happy (a PostgreSQL enum)
        row["account_id"] = new ArxEntityReference("account", ownerId);                          // the account's id
        row["industry"] = new ArxSelectionSetValue("Industry", Industry.Technology);             // 5

        await dbContext.Service.CreateAsync(row);
        await ShowStoredAsync(dbContext, row);

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT text_value, char_value, smallint_value, int_value, bigint_value, numeric_value, real_value, double_value,
         *        bool_value, uuid_value, timestamptz_value, timestamp_value, date_value, time_value, interval_value,
         *        bytea_value, jsonb_value, text_array, int_array, numeric_array, uuid_array, date_array, mood, industry
         * FROM data_type_showcase
         * WHERE text_value = 'Hello, ARX';
         * -- Expect: 1 row with the values in the comments next to each attribute.
         * ===========================================================
         */
    }

    /// <summary>2. Types without a PostgreSQL type of their own are converted to one that holds every value.</summary>
    private static async Task Scenario2_TypesThatAreConvertedAsync(SampleContext dbContext)
    {
        Step("2. Types without a PostgreSQL type of their own are converted to one that holds every value");

        var converted = new ArxEntity("data_type_showcase");
        converted["smallint_value"] = (byte)200;                                                 // 200 (byte, sbyte → smallint)
        converted["int_value"] = (ushort)65_000;                                                 // 65000 (ushort → int)
        converted["bigint_value"] = 4_000_000_000u;                                              // 4000000000 (uint → bigint)
        converted["numeric_value"] = ulong.MaxValue;                                             // 18446744073709551615.000000 (ulong → numeric)
        converted["char_value"] = 'X';                                                           // X (char → text)
        converted["text_value"] = Industry.Finance.ToString();                                   // Finance (an enum: its name, or (int) for its number)
        converted["timestamptz_value"] = new DateTimeOffset(2026, 11, 7, 22, 30, 0, TimeSpan.FromHours(8)); // 2026-11-07 14:30:00+00
        converted["bytea_value"] = new ArxBytea { Data = new byte[] { 1, 2 } };                  // \x0102

        await dbContext.Service.CreateAsync(converted);
        await ShowStoredAsync(dbContext, converted);

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT smallint_value, int_value, bigint_value, numeric_value, char_value, text_value, timestamptz_value, bytea_value
         * FROM data_type_showcase
         * WHERE text_value = 'Finance';
         * -- Expect: 1 row: 200, 65000, 4000000000, 18446744073709551615.000000, X, Finance, 2026-11-07 14:30:00+00, \x0102.
         * ===========================================================
         */
    }

    /// <summary>3. null is stored as NULL, whatever the column's type.</summary>
    private static async Task Scenario3_NullIsStoredAsNullAsync(SampleContext dbContext)
    {
        Step("3. null is stored as NULL");

        var empty = new ArxEntity("data_type_showcase");
        empty["text_value"] = null;
        empty["int_value"] = null;
        empty["jsonb_value"] = null;

        await dbContext.Service.CreateAsync(empty);
        await ShowStoredAsync(dbContext, empty);

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT id, text_value, int_value, jsonb_value
         * FROM data_type_showcase
         * WHERE text_value IS NULL AND int_value IS NULL AND jsonb_value IS NULL
         *   AND time_value IS NULL AND mood IS NULL AND uuid_value IS NULL;
         * -- Expect: 1 row, all NULL.
         * ===========================================================
         */
    }

    /// <summary>4. A type the provider can't write is refused before anything is sent.</summary>
    private static async Task Scenario4_ATypeThatIsRefusedAsync(SampleContext dbContext)
    {
        Step("4. A type the provider can't write is refused before anything is sent");

        var unsupported = new ArxEntity("data_type_showcase");
        unsupported["text_value"] = new Uri("https://northwind.example");

        try
        {
            await dbContext.Service.CreateAsync(unsupported);
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.UnsupportedValueType)
        {
            ShowError(error);
            Show("write uri.ToString() instead");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM data_type_showcase
         * WHERE text_value LIKE 'https://northwind%';
         * -- Expect: 0: the Uri was refused before anything was sent.
         * ===========================================================
         */
    }

    /// <summary>Prints each attribute's .NET type and the value PostgreSQL stored for it.</summary>
    private static async Task ShowStoredAsync(SampleContext dbContext, ArxEntity entity)
    {
        var columns = entity.Attributes.Keys.ToArray();
        var stored = await dbContext.Direct.TextRowAsync("data_type_showcase", entity.RecordId!.AsInt64(), columns);

        foreach (var column in columns)
        {
            Show($"{column,-18} {entity[column]?.GetType().Name ?? "null",-22} {stored[column] ?? "NULL"}");
        }
    }
}

  > 1. One row with a value of every supported kind
      text_value         String                 Hello, ARX
      varchar_value      String                 Short text
      char_value         String                 AB
      smallint_value     Int16                  32000
      int_value          Int32                  2000000000
      bigint_value       Int64                  9000000000000
      numeric_value      Decimal                12345.678901
      real_value         Single                 3.25
      double_value       Double                 2.718281828459045
      bool_value         Boolean                true
      uuid_value         Guid                   6f9619ff-8b86-d011-b42d-00c04fc964ff
      timestamptz_value  DateTime               2026-11-07 14:30:00+00
      timestamp_value    DateTime               2026-11-07 09:00:00
      date_value         DateOnly               2026-11-07
      time_value         TimeOnly               17:45:30
      interval_value     TimeSpan               1 day 12:30:00
      bytea_value        Byte[]                 \xcafe
      jsonb_value        ArxJsonB               {"plan": "gold", "seats": 25}
      text_array         String[]               {red,green}
      int_array          Int32[]                {1,2,3}
      numeric_array      Decimal[]              {1.5,2.25}
      uuid_array         Guid[]                 {00000000-0000-0000-0000-000000000001}
      date_array         DateOnly[]             {2026-01-01,2026-12-31}
      timestamptz_array  DateTime[]             {"2026-01-01 00:00:00+00"}
      mood               ArxSelectionSetValue   happy
      account_id         ArxEntityReference     1
      industry           ArxSelectionSetValue   5
  > 2. Types without a PostgreSQL type of their own are converted to one that holds every value
      smallint_value     Byte                   200
      int_value          UInt16                 65000
      bigint_value       UInt32                 4000000000
      numeric_value      UInt64                 18446744073709551615.000000
      char_value         Char                   X
      text_value         String                 Finance
      timestamptz_value  DateTimeOffset         2026-11-07 14:30:00+00
      bytea_value        ArxBytea               \x0102
  > 3. null is stored as NULL
      text_value         null                   NULL
      int_value          null                   NULL
      jsonb_value        null                   NULL
  > 4. A type the provider can't write is refused before anything is sent
      caught ArxValidationException: ARX1007 UnsupportedValueType: Attribute 'text_value' on 'data_type_showcase' has a value of type System.Uri, which the PostgreSQL provider cannot write.
      write uri.ToString() instead