How to insert DEFAULT into Postgresql SERIAL column using Scala Java-CodePudding

MySQL will accept NULL to an auto increment column and insert the last value. So you could presumably use

(Using the code from this SO answer, where st is a preparedStatement)

st.setNull(1, java.sql.Types.NULL)

I'm trying to figure out how you do the same with Postgresql, where you cannot use NULL, but must use DEFAULT instead:

INSERT INTO serial_table (id) VALUES(NULL)

Does something like this exist?

st.setDefault(1, java.sql.Types.DEFAULT)

st.setObject(1, "DEFAULT")

I assume I can't just use the string "DEFAULT".

EDIT:

To clarify, this is a testing table defined as such:

CREATE TABLE serial_table (
    id SERIAL NOT NULL PRIMARY KEY
);

So I can't just skip the column to let Postgresql handle it

INSERT INTO serial_table VALUES()
ERROR:  syntax error at or near ")"
LINE 1: INSERT INTO serial_table VALUES();
                                        ^

CodePudding user response：

To insert a row consisting only of default values you can use:

INSERT INTO serial_table DEFAULT VALUES

CodePudding user response：

Just omit the primary key:

insert into foo (bar) values ("foobar")

Will assign the default values to any columns that are not mentioned explicitly. I am not sure how exactly it is done when you only have one column (the other answer seems to be offering a way to deal with it), but as you said yourself, it's not a sensical use case anyway (as a sidenote, you should not really be writing tests that cover "ridiculous" use cases).