Porting from SQLite to PostgreSQL: What is according of datatype byte[] and DateTime in .NET 6 insid-CodePudding

My context: I need creating an web-app use ASP.NET Core WebAPI .NET 6, Entity Framework Core 6, PostgreSQL 14.5 with JWT authentication/authorization. I mimics a sample at https://github.com/patrickgod/AuthenticationWebApi , then fork to me https://github.com/donhuvy/AuthenticationWebApi .

This source code use .NET 6 , SQLite , Entity Framework Core 6, HMACSHA512 :

namespace AuthenticationWebApi.Models

{
    public class User
    {
        public int Id { get; set; }
        public string Username { get; set; } = string.Empty;
        public byte[] PasswordHash { get; set; } = new byte[32]; // <-- In don't know how to choose according datatype in PostgreSQL' DDL script.
        public byte[] PasswordSalt { get; set; } = new byte[32]; // <-- In don't know how to choose according datatype in PostgreSQL' DDL script.
        public string RefreshToken { get; set; } = string.Empty;
        public DateTime TokenCreated { get; set; } // <-- In don't know how to choose according datatype in PostgreSQL' DDL script.
        public DateTime TokenExpires { get; set; } // <-- In don't know how to choose according datatype in PostgreSQL' DDL script.
        public string Role { get; set; } = string.Empty;
    }
}

source https://github.com/donhuvy/AuthenticationWebApi/blob/master/AuthenticationWebApi/Models/User.cs

I need migration to PostgreSQL 14.6 . My wrong SQL script is

CREATE TABLE public."my_user"
(
    id integer,
    username character varying(32),
    password_hash character varying(32), // <-- Incorrect mapping.
    password_salt character varying(32), // <-- Incorrect mapping.
    refresh_token character varying(32),
    token_created time with time zone, // <-- Incorrect mapping.
    token_expires time with time zone, // <-- Incorrect mapping.
    role text
);

ALTER TABLE IF EXISTS public."my_user"
    OWNER to postgres;

I use table name my_user for avoiding duplicate with reserved keyword user in PostgreSQL database system.

Help me mapping correct datatype for SQL script inside PostgreSQL. I hope you understand my need (you can ask me for clarification).

CodePudding user response：

It is bytea and timestamp without time zone

https://www.npgsql.org/doc/types/basic.html

CodePudding user response：

As above, a .NET byte[] is mapped to a PostgreSQL bytea.

For DateTime, that depends: UTC timestamps are represented in PG by timestamp with time zone, whereas other timestamps (in some implicit local or unknown timezone) are represented by timestamp without time zone. Npgsql enforces this distinction in .NET by accepting only DateTime with Kind=UTC for timestamp with time zone, and only Kind with non-UTC for timestamp without time zone. So it's a question of what kind of timestamp data you're going to be saving there, and it affects the code you'll use to provide DateTime instances to Npgsql. See the docs and this blog post for more information.