pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
INSERT w/o variable names for a SERIAL type?
10+ messages / 6 participants
[nested] [flat]

* INSERT w/o variable names for a SERIAL type?
@ 2000-02-25 13:59  Emils Klotins <emils@mail.usis.bkc.lv>
  0 siblings, 2 replies; 10+ messages in thread

From: Emils Klotins @ 2000-02-25 13:59 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-25 15:14  Alessio Bragadini <alessio@albourne.com>
  parent: Emils Klotins <emils@mail.usis.bkc.lv>
  1 sibling, 0 replies; 10+ messages in thread

From: Alessio Bragadini @ 2000-02-25 15:14 UTC (permalink / raw)
  To: Emils Klotins <emils@mail.usis.bkc.lv>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-27 15:18  Peter Eisentraut <peter_e@gmx.net>
  parent: Emils Klotins <emils@mail.usis.bkc.lv>
  1 sibling, 2 replies; 10+ messages in thread

From: Peter Eisentraut @ 2000-02-27 15:18 UTC (permalink / raw)
  To: Emils Klotins <emils@mail.usis.bkc.lv>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-27 18:29  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Peter Eisentraut <peter_e@gmx.net>
  1 sibling, 2 replies; 10+ messages in thread

From: Tom Lane @ 2000-02-27 18:29 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Emils Klotins <emils@mail.usis.bkc.lv>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-27 18:58  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Peter Eisentraut <peter_e@gmx.net>
  1 sibling, 0 replies; 10+ messages in thread

From: Bruce Momjian @ 2000-02-27 18:58 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Emils Klotins <emils@mail.usis.bkc.lv>; pgsql-sql

[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))\g
	NOTICE:  CREATE TABLE will create implicit sequence 'j_x_seq' for SERIAL
	column 'j.x'
	NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'j_x_key' for
	table 'j'
	CREATE
	test=> insert into j values (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, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-27 19:04  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 10+ messages in thread

From: Bruce Momjian @ 2000-02-27 19:04 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Emils Klotins <emils@mail.usis.bkc.lv>; pgsql-sql

> > 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, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-27 19:44  Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 10+ messages in thread

From: Ross J. Reedstrom @ 2000-02-27 19:44 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-28 00:00  Peter Eisentraut <peter_e@gmx.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 10+ messages in thread

From: Peter Eisentraut @ 2000-02-28 00:00 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Emils Klotins <emils@mail.usis.bkc.lv>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-28 00:19  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 0 replies; 10+ messages in thread

From: Bruce Momjian @ 2000-02-28 00:19 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Emils Klotins <emils@mail.usis.bkc.lv>; pgsql-sql

[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, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [SQL] INSERT w/o variable names for a SERIAL type?
@ 2000-02-28 20:24  Peter Eisentraut <peter_e@gmx.net>
  parent: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
  0 siblings, 0 replies; 10+ messages in thread

From: Peter Eisentraut @ 2000-02-28 20:24 UTC (permalink / raw)
  To: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2000-02-28 20:24 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-25 13:59 INSERT w/o variable names for a SERIAL type? Emils Klotins <emils@mail.usis.bkc.lv>
2000-02-25 15:14 ` Alessio Bragadini <alessio@albourne.com>
2000-02-27 15:18 ` Peter Eisentraut <peter_e@gmx.net>
2000-02-27 18:29   ` Tom Lane <tgl@sss.pgh.pa.us>
2000-02-27 19:04     ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-02-27 19:44       ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
2000-02-28 20:24         ` Peter Eisentraut <peter_e@gmx.net>
2000-02-28 00:00     ` Peter Eisentraut <peter_e@gmx.net>
2000-02-28 00:19       ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-02-27 18:58   ` Bruce Momjian <pgman@candle.pha.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox