Question 114695 in PostgreSQL Question posted in
The official documentation can be found here.

Porting from SQLite to PostgreSQL: What is according of datatype byte[] and DateTime in .NET 6 inside PostgreSQL 14.5 SQL DDL script?

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

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    It is bytea and timestamp without time zone

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


  2. 0

    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.

    Login or Signup to reply.
  3. 0

    for timestamp without time zone you can use timestamp in your create expression.

    for imestamp with time zone you can use timestamptz in your create expression.

    see docs write-mappings
    and
    see PostgreSQL timestamp vs timestamptz

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search