在PostgreSQL中添加空日期值 [英] Adding Null Date Value in PostgreSQL

问题描述

我正在将Oracle数据库查询转换为PostgreSQL查询. Oracle查询中的日期值有一个空参数.我已经搜索了，但是找不到任何用于PostgreSQL查询的内容.我可以在PostgreSQL查询中使用什么作为空日期值?任何帮助将不胜感激.谢谢.

I'm converting an Oracle database query to a PostgreSQL query. There is a null parameter for date value in the Oracle query. I've searched but I couldn't find anything for PostgreSQL query. What can I use for null date value in PostgreSQL query? Any help will be appreciated. Thanks.

我正在使用Java，而Oracle查询是

I'm using Java and the Oracle query is

INSERT INTO tableName (name, age, date) VALUES(?,?,?)

值是"James"，"10"，为空.

values are "James", "10", null.

在Postgres查询中，当我尝试将null作为参数时，它会给出异常.

In Postgres query, when I try to give null as parameter, it gives an exception.

推荐答案

tl; dr

• 通过Java JDBC 代码传递null作为PreparedStatement::set…结果的参数在数据库行中的NULL值中.
  • 例外:如果使用NOT NULL定义列，则数据库系统当然会拒绝这种提交.

  tl;dr

  • Passing a null in Java JDBC code as an argument to PreparedStatement::set… results in a NULL value in your database row.
    • Exception: If you define your column with NOT NULL, then such a submission will of course be rejected by the database system.

    您的问题尚不完整，应根据发布的评论予以重写.

    Your Question is not complete, and should be rewritten per the posted comments.

    但是我可以展示一些通用的示例代码供您细读.通过JDBC在Java中传递null会导致数据库中的值为空.

    But I can show some general example code for your perusal. Passing a null in Java through JDBC will result in a null value in your database.

    pstmt.setString( 1 , "John Doe" );  
    pstmt.setObject( 2 , null );        // <-- Passing `null` into the SQL prepared statement.
    pstmt.executeUpdate();
    

    这是使用 H2数据库引擎的完整示例应用程序. H2是关系数据库的纯Java实现，可以像其他任何依赖项一样放入示例应用程序中.

    Here is a complete example app using the H2 Database Engine. H2 is a pure Java implementation of a relational database, and can be pulled into the example app like any other dependency.

    我希望在Postgres上可以运行相同类型的代码.

    I expect the same kind of code would run on Postgres.

    package work.basil.example;
    
    import java.sql.*;
    import java.time.LocalDate;
    import java.time.Month;
    import java.util.Objects;
    import java.util.UUID;
    
    import org.h2.jdbcx.JdbcDataSource;
    
    public class ExampleH2App
    {
    
        public static void main ( String[] args )
        {
            ExampleH2App app = new ExampleH2App();
            app.doIt();
        }
    
        private void doIt ( )
        {
            JdbcDataSource dataSource = Objects.requireNonNull( new JdbcDataSource() );  // Implementation of `DataSource` bundled with H2.
            // The `mem` in URL means "in-memory-only".
            // Set `DB_CLOSE_DELAY` to `-1` to keep in-memory database in existence after connection closes.
            dataSource.setURL( "jdbc:h2:mem:h2_example_db;DB_CLOSE_DELAY=-1" );
            dataSource.setUser( "scott" );
            dataSource.setPassword( "tiger" );
    
            try (
                    Connection conn = dataSource.getConnection() ;
                    // Set `DB_CLOSE_DELAY` to `-1` to keep in-memory database in existence after connection closes.
                    Statement stmt = conn.createStatement() ;
            )
            {
                String sql =
                        """
                        CREATE TABLE person_ ( 
                            pkey_ UUID NOT NULL DEFAULT RANDOM_UUID() PRIMARY KEY ,
                            name_ VARCHAR NOT NULL ,
                            birthdate_ DATE  
                        );
                        """;
                stmt.execute( sql );
    
                // Insert row.
                sql = """
                      INSERT INTO person_( name_ , birthdate_ )
                      VALUES( ? , ? )
                      ;
                      """;
    
                try (
                        PreparedStatement pstmt = conn.prepareStatement( sql , Statement.RETURN_GENERATED_KEYS ) ;
                )
                {
                    pstmt.setString( 1 , "Margaret Hamilton" );  // https://en.wikipedia.org/wiki/Margaret_Hamilton_(software_engineer)
                    pstmt.setObject( 2 , LocalDate.of( 1936 , Month.AUGUST , 17 ) );
                    pstmt.executeUpdate();
    
                    pstmt.setString( 1 , "John Doe" );
                    pstmt.setObject( 2 , null );  // <-- Passing `null` into the SQL prepared statement.
                    pstmt.executeUpdate();
    
                    ResultSet rs = pstmt.getGeneratedKeys();
                    System.out.println( "INFO - Reporting generated keys." );
                    while ( rs.next() )
                    {
                        UUID uuid = rs.getObject( 1 , UUID.class );
                        System.out.println( "generated keys: " + uuid );
                    }
                }
    
                // Dump all rows.
                System.out.println( "INFO - Reporting all rows in table `person_`." );
                sql = "SELECT * FROM person_";
                try ( ResultSet rs = stmt.executeQuery( sql ) ; )
                {
                    while ( rs.next() )
                    {
                        UUID pkey = rs.getObject( "pkey_" , UUID.class );
                        String name = rs.getString( "name_" );
                        LocalDate birthdate = rs.getObject( "birthdate_" , LocalDate.class );
                        System.out.println( "Person: " + pkey + " | " + name + " | " + birthdate );
                    }
                }
            }
            catch ( SQLException e )
            {
                e.printStackTrace();
            }
        }
    }
    

    运行时:

    INFO - Reporting generated keys.
    generated keys: 386460f8-c2d4-4207-9291-e5aa7eb51471
    INFO - Reporting all rows in table `person_`.
    Person: ad05fb5e-e731-4a61-ae03-9ed8d9ede100 | Margaret Hamilton | 1936-08-17
    Person: 386460f8-c2d4-4207-9291-e5aa7eb51471 | John Doe | null
    

    提示:避免使用诸如date之类的关键字在数据库中命名列或其他对象.不幸的是，各种数据库系统中有一千多个关键字和保留字.因此，避免所有这些都是棘手的.我的解决方案:我用下划线命名所有数据库对象. SQL规范明确承诺不要在任何命名中使用结尾的下划线.

    Tip: Avoid naming a column or other object within your database using a keyword such as date. Unfortunately, there are over a thousand keywords and reserved words across various database systems. So avoiding all of them is tricky. My solution: I name all my database objects with a trailing underscore. The SQL specification specifically promises to never use a trailing underscore in any naming.

    顺便说一句，无需将年龄存储在表中.给定存储日期后，您可以在SQL查询或Java代码中即时计算年龄.

    By the way, no need to store age in a table. Given the stored date, you can calculate the age on-the-fly in your SQL query or in your Java code.

    这篇关于在PostgreSQL中添加空日期值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！