postgresql不喜欢多个逗号

Question

I'm working with a php script that compiles data into insert statements for a postgresql database. It was originally written for a mysql database, so there are, of course, some differences.

One thing I've run into that I can't seem to figure out, is postgresql not liking this type of statement:

INSERT INTO table ( col1, col2, col3, col4 ) VALUES ( 'value',,'value','value' );

Any time there's a missing value, the query errs at the point of the double commas.

Any ideas on how to bypass this? The fields all have varying datatypes so using a '' as a filler fails if the col is expecting an int.

Answer 1

PostgreSQL is following the SQL standard here. Your statement is invalid, so it produces an error.

You must either:

• Dynamically generate the column-name list to include only columns you want to set; or

• Use the keyword DEFAULT to say "do whatever you'd do if I hadn't specified this column"

  e.g

  INSERT INTO table ( col1, col2, col3, col4 )
  VALUES ( 'value',DEFAULT,'value','value' );
  

Note that the DEFAULT keyword is not quoted. It isn't an identifier or a literal. It's a keyword and must be unquoted. This means you cannot pass it as a query parameter via most parameterized statement query interfaces.

That means that either way, you're stuck with dynamic SQL.

I think this is a real flaw in most SQL client interfaces, which should provide a generic object you can pass to specify "make this column a default field", or a setDefault(paramname) method, or similar. Unfortunately, that won't help you, because most of them don't do it.

You can pass NULL as the placeholder if the column is nullable, but this is not the same thing as passing DEFAULT. If you use the DEFAULT keyword then any DEFAULT expression on the column is used, as if you hadn't specified the column at all. If you use NULL, any DEFAULT expression on the column definition is overridden and ignored.