0

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)

Or

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();
                                        ^
Improve this question
4
  • Have you tried null? The null in Java could be interpreted by the Postgres driver as "DEFAULT". Commented Nov 14, 2022 at 6:26
  • Yeah, that was the first thing I did. I'm so used to using MySQL that I ran it that way to start, got the error, then realized "Oh yeah, this is Postgres". Error: The future returned an exception of type: java.lang.RuntimeException, with message: ERROR: null value in column "id" of relation "serial_table" violates not-null constraint Commented Nov 14, 2022 at 6:30
  • "MySQL will accept NULL to an auto increment column and insert the last value" - which is a bug in my opinion. You are requesting to store a NULL value, but MySQL silently turns it into something else. What's next? Turning all integer values to 42 because that's "obviously" the correct value? Commented Nov 14, 2022 at 7:10
  • @a_horse_with_no_name Standard behavior in MySQL, actually. Not a bug. AUTO INCREMENT cannot be null and must be a key. So, I suppose their thinking was, "since NULL is meaningless here, we can use that as a keyword to trigger the default value insertion." Commented Nov 15, 2022 at 11:08

2 Answers 2

Reset to default
2

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

INSERT INTO serial_table DEFAULT VALUES
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thanks for your suggestion but I'm not asking how to insert into Postgres with default values. I'm asking about a way to do that through java/scala. Java has utilities to construct prepared statements and specify values but doesn't have a DEFAULT. It does have a way to pass in a value for TIMESTAMP and other such special signifiers (although I have not tried these with prepared statements) so I would think there must be a way to pass DEFAULT for SERIAL columns in Postgresql. I don't see it in java.sql.Types but perhaps there's a library I'm unaware of.
Obviously it's ridiculous to have a table that has a primary key only. That's the problem. These tables are only for the test suite, to test CRUD on different kinds of primary keys. For a table with multiple columns in Postgres you can omit the primary key and Postgres will insert it. But for these dummy tables with only a primary key there doesn't seem to be a way to do it. As in my question INSERT INTO serial_table VALUES() doesn't work. And unlike MySQL you can't use NULL for primary keys.
@ButtleButkus: well you can run that statement through Java/Scala. JDBC does not have a way to provide a "DEFAULT value" because DEFAULT is a keyword like SELECT or VALUES which needs to be part of the SQL string. Yes, INSERT INTO serial_table VALUES() doesn't work because it's invalid SQL. But the statement in my answer does exactly what you want to achieve with that.
TIMESTAMP is also a keyword, isn't it? I can see why you wouldn't be able to pass a keyword into a ? for a parameterized query, but since it seems (perhaps I'm wrong) you can pass a TIMESTAMP I thought perhaps you could also somehow pass DEFAULT (I was a bit surprised to see TIMESTAMP in the Types). I've got another solution, but if I could pass DEFAULT in, it would simplify the code.
Drawing from MySQL, I guess CURRENT_TIMESTAMP is really a function. A synonym for CURRENT_TIMESTAMP(), which also has the NOW() synonym. Perhaps Postgres is similar and it would make sense to not be able to pass a SQL function into a parameterized query, but I could also see how it could be implemented. Impossible?
|
0

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

Improve this answer

5 Comments

Perhaps you're right about the tests being ridiculous. Perhaps the real answer is that the tests should have at least one other column. I'm following suggestions for the tests from the owners of the repository. You can create a single-column table, auto increment primary key in MySQL and if you insert NULL it will work. And I assume, but haven't tested, that in java you could pass NULL as a parameter and it would work. But with Postgres, there's no way, apparently.
Yeah, as mentioned elsewhere, the behavior you a describing in MySQL looks more like a bug than a feature.
This will result in an error "foobar" refers to a column name. String values need to be enclosed in single quotes in standard SQL (and Postgres), e.g. 'foobar'
@a_horse_with_no_name OMG! You found a typo!!!!
@Dima it's well documented in MySQL to use NULL as a shorthand for triggering default value for auto increment columns, but MySQL has been moving away from non ANSI tricks like that,. Not sure I'd call it a bug, but perhaps it smells like one.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.