Postgresql and SERIAL columns

[allenvpn]

I have a table such as this:

create table attribute (
attributeid SERIAL PRIMARY KEY,
name VARCHAR(40) NOT NULL UNIQUE
);

The assumption is SERIAL defined columns are similar to auto_increment columns in MySQL. If I pass a null value to attributeid, shouldn't it increment to the next integer value? For example:

insert into attribute values ('', 'Test attribute');

When attempting to do this, PostGresql is returing the following error:

"Fail to add null value to in not null attribute attributeid."

Does this mean I have to explicidly pass an integer value to resole this problem which kinda deplete the purpose of using a SERIAL definition.

P.S Running on RedHat 7.3 and Postgresql 7.2.1

[kimi]

Just do like this

insert into attribute(name ) values ('Test attribute');

[Sxooter]

If you define your tables with the serial column last, then you can insert without the (attr,attr) part of the query.

create table bubba (t text, a float, i serial);

insert into bubba values ('hello',3.14159);

If you do have the table defined with the sequence at the front, and you really don't want to list all the columns you're updating, you can, assuming you know the sequence name, do it like this:

insert into a values ('aaa',nextval('"a_aid_seq"'::text));

But that's hardly pretty.

[chriskl]

"If you define your tables with the serial column last, then you can insert without the (attr,attr) part of the query.

create table bubba (t text, a float, i serial);

insert into bubba values ('hello',3.14159); "

THAT WON'T WORK IN Postgres 7.3 - and it violates the SQL standard.

You need to insert the default:

insert into a values ('aaa',nextval('"a_aid_seq"'::text));

And please, before assuming that whatever MySQL does is in any way standard, or done in any other database please remember that MySQL is NOT representative of real databases.

Chris

[[deleted]]

"THAT WON'T WORK IN Postgres 7.3 "

It doen't work in 7.2 either.

Rule #1 in insert-statements, ALWAYS tell SQL which columns you want to fill.

You dont have to insert anything into the serial column, but you must tell SQL that you are not giving it a value for that column, you MUST mention all the columns that you do want to fill.

[Sxooter]

Here's the output from my workstation:

Welcome to psql 7.3b1, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

postgres=# create table test (nam text, id serial);
NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id'
NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id'
CREATE TABLE
postgres=# insert into test values ('abc');
INSERT 16984 1

What was that you were saying about not being able to do that in 7.2 and 7.3??? Maybe YOU should check out your facts before blasting someone else's, eh?

[Sxooter]

OK, here's the definitive answer, from the ANSI SQL 92 standard...

<insert statement> ::=
INSERT INTO <table name>
<insert columns and source>
<insert columns and source> ::=
[ <left paren> <insert column list> <right paren> ] <query expression>
| DEFAULT VALUES
<insert column list> ::= <column name list>

Then further on...

3) No <column name> of T shall be identified more than once. If the <insert column list> is omitted, then an <insert column list> that identifies all columns of T in the ascending sequence of their ordinal positions within T is implicit.

So, ANSI supports me too.

[[deleted]]

"If the <insert column list> is omitted, then an <insert column list> that identifies all columns of T in the ascending sequence of their ordinal positions within T is implicit. "

Now english may not be my native language, but doesn't this say:

"If you you don't specify which columns to insert into, it is assumed that you will insert into all columns"?

Your method only works because the database is not as strict as it should be (yeah, bladebla 7.3b1, like I care about what goes on in beta versions)

Your 'method' depends entirely on the oreder of the columns, which as you should know, is not a given. It can and will vary and your app should not assume a certain order. If one table happens to have the ID as the first column, your method will fail massively, and you'll get to spend a few days fixing all the mistakes you made because you couldn't be bothered to name the columns you wanted to insert into.

So just forget all about that 'method' and start doing things properly ok?

[Sxooter]

Subsection 5 of the syntax rules explains why you don't have to include all the columns in the insert column list, so my method works BECAUSE IT IS ANSI SQL 92 compliant.

5) Let QT be the table specifìed by the <query expression>. The degree of QT shall be equal to the number of <column name>s in the <insert column list>. The column of table T identified by the i-th <column name> in the <insert column list> corresponds with the i-th column of QT.

P.S. I'm not arguing if this is the best way, I'm arguing that it's SQL 92 compliant, and that it works in 7.3, which Chris said it didn't.

[[deleted]]

" Let QT be the table specifìed by the <query expression>. The degree of QT shall be equal to the number of <column name>s in the <insert column list>. The column of table T identified by the i-th <column name> in the <insert column list> corresponds with the i-th column of QT."

That just says that if you have a 'query expression' in the insert, for ex:

INSERT INTO firsttable (col1) SELECT col FROM othertable;

then the number of columns that the SELECT produces must be equal to the number of columns in the column-name list.

Still no word about not putting any columns in the column list and leaving columns out of the values list.

And yes, I have th standard in front of me and I really cannot find a shred of evidence for this.

In fact, it clearly says that if you do not specidy a column-list, then a column-list of all columns in that table is assumed.

And that goes right agains what you are saying.

[Sxooter]

<insert statement> ::=
INSERT INTO <table name>
<insert columns and source>

<insert columns and source> ::=
[ <left paren> <insert column list> <right paren> ] <query expression>
| DEFAULT VALUES

<insert column list> ::= <column name list>

So, assuming that <query expression> stands only for an insert, then where does the "values ('val1','val2',num1,num2)" part of the query get listed. I'll give you a hint. <query expression> can either be something like a (select...) kinda thing, OR it can be a "values ('val',num)" kinda thing. Look it up. Section 7.10, it can be all kinds of things, and one of those things is that it can be a simple parenthesized list of values to be pushed into the table.

[[deleted]]

" OR it can be a "values ('val',num)" kinda thing"

Ok, so in that case it says that the number of values in the values list must be equal to the number of columns mentioned in the column list.

The key word here is 'equal'.

So far the SQL92 specs say that:

• if you leave out the column-list then the values-list must contain one value for every column in the table.

• if you do give a column list then the number of values must be equal to the number of columns in the column-list.

So how does this prove that you can arbitrarily leave out columns when you leave the column-list out?

[Sxooter]

OK, so the SQL spec says that you can't have an implicit column list unless you have ALL the columns in the values () part. I agree. Seems poorly thought out. Note however, that this behaviour is NOT scheduled to be removed from Postgresql or any other database I know of.

Post gresql DID removed the behaviour where you could do something like:

insert into table (col1,col2,col3) values ('abc','123');

to comply with spec, but, much as I would have expected, decided that removing the ability to do the same with

insert into table values ('abc','123');

when inserting into a table with >2 columns was just a recipe for pissing off the users. So it'll be sticking around.

Note, I'm not advocating this as a normal way of doing things, in fact I ride my own team pretty hard for such things. Does anyone have a copy of the SQL99 standard handy to see how it handles this situation? It just seems silly to allow an implicit column list but then require that everyone of them be in the values () part of the query.

[Lars_Berg]

It is the same in SQL 99.

What you can do is

insert into t values('abc',123,default)

[chriskl]

Oooo - ok I'm getting confused between the two difference syntaxes. Still, by your own admission, doing it your way is "not good"...

Chris

[[deleted]]

"OK, so the SQL spec says that you can't have an implicit column list unless you have ALL the columns in the values () part. I agree. "

hehehe... I told you so 😃

" Oooo - ok I'm getting confused between the two difference syntaxes"

Ok, here's a recap:

The INSERT statement syntax is basically:

INSERT INTO table <column-list> VALUES <value-list>;

The value list contains the data you want to put into the table, and the column-list specifies which column each of the values should go in to.

The column-list is optional, but if you leave it out, then SQL assumes that you want to insert data into every column in the table, so the values-list must contain one value for every column in the table.

To keep the 'bad' programmers happy, databases appear to also allow you to specify fewer values than there are columns. The data in the values is inserted into the columns from left to right and when all values are used, the remaining columns will be filled with their defaults (if any)

So if you have a table consisting of 'name | id' and you run a query:
INSERT INTO table VALUES ('charles');
then there is no value for 'id', and SQL uses the default value for the 'id' column.

The bad thing about this is that the order of the values must match the order of the columns. So if you had to add a column to the table:
'name | id | occupation'
Then the trick stops working because you have to give the values for the columns from left to right, and you can't skip the id column anymore:

INSERT INTO table VALUES ('charles', 'gardner');

will try to insert 'gardner' into the 'id' column.

To keep things working you can tell SQL to use the 'default' value for the ID column, by putting DEFAULT in the values list:

INSERT INTO table VALUES ('charles', DEFAULT, 'gardner');

And as you can see, now we're back to the original 'you must give a value for each column in the table' rule.

[Sxooter]

I agree that using insert without the column definition list is generally a bad thing, but I can think of ONE instance when it makes sense, and that's when you generate the insert statement programmatically after doing something like "select * from table limit 1" to get a list of all the fields and types using pg_field_type and pg_field_name.

At that point, having to build the column definition list is overkill for a program that now knows the actual layout of the table, and can just leave off the extra columns if it wants them set to default or NULL.

But only in a machine generated insert does that make any sense. Humans should stick to writing SQL that is more easily read and debugged.