Thread: INSERT w/o variable names for a SERIAL type?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hello. I am using, which is written for connecting to another server and uses the AUTO_INCREMENT and BLOB fields (for storing text). Apparently it was written for mysql(?). I wanted to write a #define or two to change the relevant places, such as #define IDKEY "INTEGER AUTO_INCREMENT PRIMARY KEY" // mysql version #define IDKEY "SERIAL PRIMARY KEY" // postgres and currently stumbled upon mysql version of inserting the autoincrement value: NULL ie. CREATE TABLE mytable (id SERIAL PRIMARY KEY, textfield text); the mysql variant for inserting a row would be: INSERT INTO mytable VALUES (NULL, 'textvalue'); and NULL would get translated into the real value. Now, if I want to write a general #define, I'd need to have a way to specify 'default' for SERIAL field, for, if I omit the id field in VALUES, I need to specify all the rest of the fields explicitly. Is there any value I could put in place of id in VALUES part, to make it replaced with the next value in sequence? Emils -----BEGIN PGP SIGNATURE----- Version: PGP 6.5.2 -- QDPGP 2.61a Comment: http://community.wow.net/grt/qdpgp.html iQA/AwUBOLZuld0sxa1MAPWHEQJtVACg3Nsd7YiH3q2aXkIMSM/mjaLD3fgAn0bu S4NHXOsVsS26nntkkInqxBJw =HodA -----END PGP SIGNATURE-----
Emils Klotins wrote:
> I am using, which is written for connecting to another server
> and uses the AUTO_INCREMENT and BLOB fields (for storing
> text). Apparently it was written for mysql(?).
Most probably.
> I wanted to write a #define or two to change the relevant
> places, such as
>
> #define IDKEY "INTEGER AUTO_INCREMENT PRIMARY
> KEY" // mysql version
> #define IDKEY "SERIAL PRIMARY KEY" // postgres
Note that SERIAL is syntactic sugar for
INT4 NOT NULL DEFAULT NEXTVAL('<table>_<field>_SEQ')
> INSERT INTO mytable VALUES (NULL, 'textvalue');
> and NULL would get translated into the real value.
Please allow me not to comment on mysql behaviour...
> Now, if I want to write a general #define, I'd need to have a way
> to specify 'default' for SERIAL field, for, if I omit the id field in
> VALUES, I need to specify all the rest of the fields explicitly.
>
> Is there any value I could put in place of id in VALUES part, to
> make it replaced with the next value in sequence?
NEXTVAL('<table>_<field>_seq') should work.
--
Alessio F. Bragadini alessio@albourne.com
APL Financial Services http://www.sevenseas.org/~alessio
Nicosia, Cyprus phone: +357-2-750652
"It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925
Emils Klotins writes: > Now, if I want to write a general #define, I'd need to have a way > to specify 'default' for SERIAL field, for, if I omit the id field in > VALUES, I need to specify all the rest of the fields explicitly. > > Is there any value I could put in place of id in VALUES part, to > make it replaced with the next value in sequence? INSERT INTO my_table VALUES (a, b, c, DEFAULT, x, y, z, ...); -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Peter Eisentraut <peter_e@gmx.net> writes:
> Emils Klotins writes:
>> Now, if I want to write a general #define, I'd need to have a way
>> to specify 'default' for SERIAL field, for, if I omit the id field in
>> VALUES, I need to specify all the rest of the fields explicitly.
>>
>> Is there any value I could put in place of id in VALUES part, to
>> make it replaced with the next value in sequence?
> INSERT INTO my_table VALUES (a, b, c, DEFAULT, x, y, z, ...);
I think that is legal SQL92 syntax, but Postgres doesn't accept it
at present.
The usual recommendation is to call out the columns you are loading
explicitly:
INSERT INTO my_table(a,b,d) VALUES (val-for-a, val-for-b, val-for-d);
The ones you don't load get their default values substituted instead.
This way is a shade more verbose, but it's good solid defensive
programming practice: the insert will do what it's supposed to
even if the table schema changes to add/delete/reorder columns.
regards, tom lane
[Charset ISO-8859-1 unsupported, filtering to ASCII...] > Emils Klotins writes: > > > Now, if I want to write a general #define, I'd need to have a way > > to specify 'default' for SERIAL field, for, if I omit the id field in > > VALUES, I need to specify all the rest of the fields explicitly. > > > > Is there any value I could put in place of id in VALUES part, to > > make it replaced with the next value in sequence? > > INSERT INTO my_table VALUES (a, b, c, DEFAULT, x, y, z, ...); I didn't think that worked, and it doesn't seem to work. Should it? test=> create table j(x serial, y char(10))\gNOTICE: CREATE TABLE will create implicit sequence 'j_x_seq' for SERIALcolumn'j.x'NOTICE: CREATE TABLE/UNIQUE will create implicit index 'j_x_key' fortable 'j'CREATEtest=> insert into jvalues (default,'yes');ERROR: parser: parse error at or near "default" -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> > INSERT INTO my_table VALUES (a, b, c, DEFAULT, x, y, z, ...); > > I think that is legal SQL92 syntax, but Postgres doesn't accept it > at present. > > The usual recommendation is to call out the columns you are loading > explicitly: > > INSERT INTO my_table(a,b,d) VALUES (val-for-a, val-for-b, val-for-d); > > The ones you don't load get their default values substituted instead. > > This way is a shade more verbose, but it's good solid defensive > programming practice: the insert will do what it's supposed to > even if the table schema changes to add/delete/reorder columns. The problem is when you are inserting >50 columns, it is a pain. The use of DEFAULT would also allow SERIAL columns to get the proper nextval(), rather than having specify the nextval() call specifically. Added to TODO. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Sun, Feb 27, 2000 at 02:04:42PM -0500, Bruce Momjian wrote: > > > INSERT INTO my_table VALUES (a, b, c, DEFAULT, x, y, z, ...); > > > > I think that is legal SQL92 syntax, but Postgres doesn't accept it > > at present. > > > > The problem is when you are inserting >50 columns, it is a pain. The > use of DEFAULT would also allow SERIAL columns to get the proper > nextval(), rather than having specify the nextval() call specifically. This, I think, is the best reason for doing it: even if you call out the column names, as Tom rightly mentions is best, using DEFAULT allows writing INSERTs without depending on the details of how SERIAL is implemented in pgsql: i.e. knowing the algorithm for constructing the sequenec name. > > Added to TODO. Perhaps we should note which items on the TODO list are needed for what level of SQL92 compliance. This one, for example, is not necessary for ENTRY level, but required at INTERMEDIATE. Not that that should stop anyone from implementing it: SQL92 Entry is a surprisingly limited subset, and we have a lot of INTERMEDIATE and FULL features, already. Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Tom Lane writes: > > INSERT INTO my_table VALUES (a, b, c, DEFAULT, x, y, z, ...); > > I think that is legal SQL92 syntax, but Postgres doesn't accept it > at present. Oops! We do support INSERT .. DEFAULT VALUES, so I guessed too far. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
[Charset ISO-8859-1 unsupported, filtering to ASCII...] > Tom Lane writes: > > > > INSERT INTO my_table VALUES (a, b, c, DEFAULT, x, y, z, ...); > > > > I think that is legal SQL92 syntax, but Postgres doesn't accept it > > at present. > > Oops! > > We do support INSERT .. DEFAULT VALUES, so I guessed too far. We do? What does the DEFAULT do? -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Ross J. Reedstrom writes: > Perhaps we should note which items on the TODO list are needed for what > level of SQL92 compliance. This one, for example, is not necessary for > ENTRY level, but required at INTERMEDIATE. Not that that should stop > anyone from implementing it: SQL92 Entry is a surprisingly limited > subset, and we have a lot of INTERMEDIATE and FULL features, already. On Feb 19 I sent out a list of things that are missing for SQL Entry Level compliance. (See message on pgsql-hackers on that date.) The only major items left are schemas and the correct transaction semantics. When I get some time I'm planning on making this into a real document available somewhere and I could add Intermediate as well. -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden