wrong nullable type in views

[BarneyKY]

Bug Description

I use sqlx prepare to check the database and the result is like this:

{
  "db_name": "PostgreSQL",
  "query": "SELECT code,is_onsale,name,sku,price,key,pic,A as a,B as b,C as c,D as d,COALESCE(num,0) AS num,total,available,available_days, COALESCE(p_m,0) AS p_m FROM goods_storage WHER code=$1",
  "describe": {
    "columns": [
      {
        "ordinal": 0,
        "name": "code",
        "type_info": "Varchar"
      },
      {
        "ordinal": 1,
        "name": "is_onsale",
        "type_info": "Int2"
      },
      {
        "ordinal": 2,
        "name": "name",
        "type_info": "Varchar"
      },
      {
        "ordinal": 3,
        "name": "sku",
        "type_info": "Varchar"
      },
      {
        "ordinal": 4,
        "name": "price",
        "type_info": "Numeric"
      },
      {
        "ordinal": 5,
        "name": "key",
        "type_info": "Varchar"
      },
      {
        "ordinal": 6,
        "name": "pic",
        "type_info": "Varchar"
      },
      {
        "ordinal": 7,
        "name": "a",
        "type_info": "Numeric"
      },
      {
        "ordinal": 8,
        "name": "b",
        "type_info": "Int8"
      },
      {
        "ordinal": 9,
        "name": "c",
        "type_info": "Int8"
      },
      {
        "ordinal": 10,
        "name": "d",
        "type_info": "Int8"
      },
      {
        "ordinal": 11,
        "name": "num",
        "type_info": "Int8"
      },
      {
        "ordinal": 12,
        "name": "total",
        "type_info": "Numeric"
      },
      {
        "ordinal": 13,
        "name": "available",
        "type_info": "Numeric"
      },
      {
        "ordinal": 14,
        "name": "available_days",
        "type_info": "Numeric"
      },
      {
        "ordinal": 15,
        "name": "p_m",
        "type_info": "Numeric"
      }
    ],
    "parameters": {
      "Left": [
        "Text"
      ]
    },
    "nullable": [
      true,
      true,
      true,
      true,
      true,
      true,
      true,
      true,
      true,
      true,
      true,
      null,
      true,
      true,
      true,
      null
    ]
  },
  "hash": "xxx"
}

And i wrote this in Rust

let query = sqlx::query_as!(
        ProductInfo,
        "SELECT 
            code,is_onsale,name,sku,price,key,pic,A as a,B as b,C as c,D as d,
            COALESCE(num,0) AS num,total,available,available_days, COALESCE(p_m,0) AS p_m 
        FROM 
            goods_storage
        WHERE 
            code=$1",
        code
    )
    .fetch_one(&state.poolpg)
    .await;

goods_storage is a VIEW in postgre, actually most of the column are NOT NULL, when i use the view's sql directly, they are not in Option<>, but when i use view to query, all columns are nullable like this

I just don't want check this value whether it is some or none and i ensure that they are not null ( especially there is a primary key cloumn, and not in view, SQL directly works well )

How can i do ?

Info

• SQLx version: 0.7.4
• SQLx features enabled: features = ["runtime-tokio", "postgres", "macros", "time", "rust_decimal", "json"]
• Database server and version: Postgres 16.3 in Docker
• Operating system: AlmaLinux 9.4 in Docker
• rustc --version: rustc 1.79.0 (129f3b996 2024-06-10)

[BarneyKY]

For example: Postgres:

CREATE TABLE "public"."goods" (
  "code" varchar(8) COLLATE "pg_catalog"."default" NOT NULL,
  "is_onsale" int2 NOT NULL,
  PRIMARY KEY ("code")
);

CREATE VIEW "public"."goods_code" AS
SELECT
    goods.code AS code 
FROM
    goods 
WHERE
    ( goods.is_onsale = 1 ) 
ORDER BY
    goods.code;

When i use SQL directly

When i use VIEW

so strange

[BarneyKY]

https://docs.rs/sqlx/latest/sqlx/macro.query.html#nullability-output-columns

finally, according to the docs, i find out the reason

I wanna know why do like that, it makes me don't want use the VIEW but only the directly SQL

[JezerM]

You can also enforce whether to use Option values or not by selecting each column individually:

let products = sqlx::query_as!(
    ProductFull,
    r#"
    SELECT
        p.id as "id!",
        p.name as "name!",
        p.description as "description",
        p.price as "price!",
        p.image as "image",
        p.created_at as "created_at!",
        p.sell_count as "sell_count!"
    FROM public.product_with_sells p
    "#,
)

Add an exclamation mark to enforce NOT NULL.

[BarneyKY]

You can also enforce whether to use Option values or not by selecting each column individually:

let products = sqlx::query_as!(
    ProductFull,
    r#"
    SELECT
        p.id as "id!",
        p.name as "name!",
        p.description as "description",
        p.price as "price!",
        p.image as "image",
        p.created_at as "created_at!",
        p.sell_count as "sell_count!"
    FROM public.product_with_sells p
    "#,
)

Add an exclamation mark to enforce NOT NULL.

Thanks for your suggestion, it is the right way to solve this problem!