Re: [SQL] why the difference?
hi, the where clause is evaluated before the distinct clause, so your queries aren't equivalent because you switched the order by splitting the query into two queries... so to obtain same results do create table as select ... where category_id=781 and then select distinct on () ... hth, kuba > tradein_clients=# select distinct on (amount,co_name,city) >category_id,amount,co_name,city from eyp_listing > where keywordidx ## 'vegetable' and category_id=781 ; > > category_id | amount | co_name |city > -++---+ > 781 | 0 | ANURADHA EXPORTS | CHENNAI > 781 | 0 | R.K.INTERNATIONAL | CHENNAI > 781 | 0 | SAI IMPEX | MUMBAI > 781 | 0 | TRIMA ENTERPRISES | CHENNAI > 781 | 0 | UNIQUE DEHYDRATES LIMITED | AHMEDABAD > 781 | 5000 | RSV EXPORT| COIMBATORE > (6 rows) > > > lets remove the contraint "category_id=781" and store the output in a table "t_a". > > tradein_clients=# CREATE TABLE t_a AS select distinct on (amount,co_name,city) >category_id,amount,co_name,city from > eyp_listing where keywordidx ## 'vegetable' ; > > then when i select from t_a with category_id=781 i have less secords > > tradein_clients=# SELECT * from t_a where category_id=781; > category_id | amount | co_name |city > -++---+ > 781 | 0 | R.K.INTERNATIONAL | CHENNAI > 781 | 0 | SAI IMPEX | MUMBAI > 781 | 0 | UNIQUE DEHYDRATES LIMITED | AHMEDABAD > 781 | 5000 | RSV EXPORT| COIMBATORE > (4 rows) > > > Can anyone please explain the difference? > > > Regds > Mallah. > > > > > > > > > -- > Rajesh Kumar Mallah, > Project Manager (Development) > Infocom Network Limited, New Delhi > phone: +91(11)6152172 (221) (L) ,9811255597 (M) > > Visit http://www.trade-india.com , > India's Leading B2B eMarketplace. > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Date trunc in UTC
On Wednesday 20 Nov 2002 3:40 pm, Juan Fernandez wrote: > Hi Richard > > Ok, I'll do my best to explain clearer ;) I'll do my best to be of some use ;-) > I have to make some monthly reports about some service requests > activity. So, I'm keeping in a table the monthly traffic. > > TABLE traffic > +-+++ > > | service | month | visits | > > +-+++ > > | chat| 2002-11-01 00:00:00+01 | 37002 | > | video | 2002-11-01 00:00:00+01 | 186354 | > | chat| 2002-10-01 00:00:00+01 | 41246 | > | video | 2002-10-01 00:00:00+01 | 86235 | > > So, when I have a new visit on any service, I increase the counter for > that month. The problems are: > > - As you see, the month includes timezone information (+01), which > corresponds to the CET beggining of the month. > > - Whenever a new month starts, I have to create a new entry in the table. [snip] > So, as I can see in the traffic table, the DATE_TRUNC is, in fact, > equivalent to > > 2002-11-01 00:00:00+01 (CET) == 2002-09-30 23:00:00+00 (UTC) > > If we think that I will work in an international environment, I would > rather to have in the table as the result of the DATE_TRUNC the right > UTC value, so, the right begginning of the month in UTC. [snip] > In fact, DATE_TRUNC is returning the beggining of the month FOR THE > WORKING TIME ZONE, but I need to know, in my timezone, what is the > begginning of the UTC month. Ah! now I understand. Is this the sort of thing you're after? => SELECT now() AT TIME ZONE 'PST' AS allsame UNION SELECT now() AT TIME ZONE 'UTC' UNION SELECT now() AT TIME ZONE 'CCT'; allsame 2002-11-21 02:00:17.615067 2002-11-21 10:00:17.615067 2002-11-21 18:00:17.615067 (3 rows) Above was run at about 10am local time (I'm in London). Note the lack of timezone on the end. > Another more problem is that if I set the time zone in the session, I'm > not able to recover to its previous state. In plpgsql, > > client preferences -> SET TIME ZONE 'PST8PDT'; > > ... calling to my wrapper function > > CREATE FUNCTION date_trunc_utc (TEXT, TIMESTAMP) RETURN TIMESTAMP AS ' > DECLARE > st_month TIMESTAMP; > BEGIN > SET TIME ZONE ''UTC''; > st_month = DATE_TRUNC ($1, $2); > RESET TIME ZONE; > END > ' LANGUAGE 'plpgsql'; > > -> SHOW TIME ZONE > NOTICE: Time zone is 'CET' > > > so basically, I cannot change to UTC because I'm not able no more to > recover to the client timezone preferences. Hmm - good point. You can revert to the client default but not to the previous value. I don't know of any way to read these SET values either - a quick poke through pg_proc didn't show anything likely. -- Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] importing a 7.2 db with contrib/tsearch to 7.3
If this is the wrong list, please redirect me to the correct one. I have a PostgreSQL-7.2 installation with several databases in it which uses the contrib/tsearch module. I've done a (on 7.2): pg_dumpall and (on 7.3): /usr/local/pgsql/bin/psql -d template1 -f prod2-pg7.2.dmp I get a bunch of theese errors in the log-file: --- psql:prod2-pg7.2.dmp:89: NOTICE: TypeCreate: changing argument type of function qtxt_in from OPAQUE to CSTRING psql:prod2-pg7.2.dmp:89: NOTICE: TypeCreate: changing argument type of function qtxt_out from OPAQUE to query_txt psql:prod2-pg7.2.dmp:89: NOTICE: TypeCreate: changing return type of function qtxt_in from OPAQUE to query_txt psql:prod2-pg7.2.dmp:89: NOTICE: TypeCreate: changing return type of function qtxt_out from OPAQUE to CSTRING CREATE TYPE CREATE FUNCTION psql:prod2-pg7.2.dmp:105: NOTICE: TypeCreate: changing argument type of function mqtxt_in from OPAQUE to CSTRING psql:prod2-pg7.2.dmp:105: ERROR: TypeCreate: function qtxt_out(mquery_txt) does not exist CREATE FUNCTION psql:prod2-pg7.2.dmp:121: ERROR: Type "mquery_txt" does not exist CREATE FUNCTION psql:prod2-pg7.2.dmp:137: ERROR: Type "mquery_txt" does not exist CREATE FUNCTION psql:prod2-pg7.2.dmp:153: ERROR: Type "mquery_txt" does not exist CREATE OPERATOR CREATE OPERATOR psql:prod2-pg7.2.dmp:192: ERROR: Type "mquery_txt" does not exist psql:prod2-pg7.2.dmp:205: ERROR: Type "mquery_txt" does not exist .. .. psql:prod2-pg7.2.dmp:2539: ERROR: data type txtidx has no default operator class for access method "gist" You must specify an operator class for the index or define a default operator class for the data type .. .. psql:prod2-pg7.2.dmp:4419: ERROR: execqtxt: must be owner psql:prod2-pg7.2.dmp:4427: ERROR: Type "mquery_txt" does not exist psql:prod2-pg7.2.dmp:4435: ERROR: rexecqtxt: must be owner psql:prod2-pg7.2.dmp:4443: ERROR: Type "mquery_txt" does not exist --- The database seems to work just fine, and one of the tables which uses the txtidx datatype looks like this in 7.2 and 7.3 respectively: e4u=> select version(); version PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 (Mandrake Linux 9.0 3.2-1mdk) (1 row) e4u=> \d on_article_searchable Table "on_article_searchable" Column | Type | Modifiers -+-+--- id | integer | not null lang_id | integer | not null content | txtidx | Indexes: searchable_lang_idx, t_idx Primary key: on_article_searchable_pkey Triggers: RI_ConstraintTrigger_18817, RI_ConstraintTrigger_18823 e4u=> select version(); version - PostgreSQL 7.3rc1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 (Mandrake Linux 9.0 3.2-1mdk) (1 row) e4u=> \d on_article_searchable Table "public.on_article_searchable" Column | Type | Modifiers -+-+--- id | integer | not null lang_id | integer | not null content | txtidx | Indexes: on_article_searchable_pkey primary key btree (id, lang_id), searchable_lang_idx btree (lang_id) Triggers: RI_ConstraintTrigger_41091, RI_ConstraintTrigger_41094 Notice, the t_idx index is missing on the 7.3 table. Does that mean I have to manually "install" tsearch on each database? When I try to create the index t_idx on the table, the following error occures: e4u=> CREATE INDEX t_idx ON on_article_searchable USING gist (content); ERROR: data type txtidx has no default operator class for access method "gist" You must specify an operator class for the index or define a default operator class for the data type Any help appreciated. -- Andreas Joseph Krogh <[EMAIL PROTECTED]> - Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. Albert Einstein ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Drop NOT NULL constraint !!!
--- [EMAIL PROTECTED] wrote: > > do a \d tablename > > for the name of the contraint. > say its $1 > the do > > psql> alter table drop contstraint "$1" > RESTRICT; > I alter a table with by adding a foriegn key constraint. ALTER TABLE sc_city ADD CONSTRAINT cons_fkey FOREIGN KEY state_cd REFERENCE sc_state(state_cd); After altering the table I executed "\d " show the following: Triggers: RI_ConstraintTrigger_56743429 I'm surprised that name of constraint doesn't appear even though I explictly name it. Is there a way to show the name of the constraint? Should I do : ALTER TABLE sc_city DROP CONSTRAINT RI_ConstraintTrigger+5674329 RESTRICT ? Thank you very much, ludwig __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] importing a 7.2 db with contrib/tsearch to 7.3
Thats what i do for intarray: -Install all contrib packages you want to use (*before* restoring your DB) -Restore Your DB -Look in log for errors -Correct them. Normally all you shoud get is a bunch of notices that somethings are allready defined. I had the same problem as you with gist indexes in intarray. == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] psql on FreeBSD 4.7-RELEASE-p2 and greek (iso8859-7) chars
Hi, I noticed that psql on FreeBSD (i checked also fbsd 4.5 with pgsql port installed) does not accept 8bit iso8859-* chars > 128 (where the greek chars are). In linux that works ok, and i can update/insert/select values using greek strings. I know it must be a fbsd/locale issue, but it would be nice if someone knew something about it. == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] help optimise this ?
I have a table of image 'instances' where the columns include: md5 char(32),-- the 'original' image md5 key file_md5 char(32) primary key, -- the md5 of each version of an image image_width int, image_length int I want to then find either the largest (max) or smallest (min) version of an image that falls within some range of sizes: e.g. select file_md5 from image_instance where image_width = (select min(image_width) from image_instance where md5 = '546b94e94851a56ee721f3b755f58462') and image_length = (select min(image_length) from image_instance where md5 = '546b94e94851a56ee721f3b755f58462') and md5 = '546b94e94851a56ee721f3b755f58462' and image_width between 0 and 160 and image_length between 0 and 160; Now, having to do three selects on 'md5' to limit the search seems a little unoptimal to me. Note that the test tables are small and I have no other indexes apart from the 'primary key' constraint yet - this is not my primary concern at this point, I would just like cleaner SQL. All I want back is (for some definition) the 'file_md5' that best matches my min/max criteria. I have not - and will leave for now - the case where a cropped image results in a scale change between width and length such that the min/max test returns a different set of rows for each dimension. Argh. And help given is greatly appreciated. rgds, -- Peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] why the difference?
Jakub Ouhrabka <[EMAIL PROTECTED]> writes: > the where clause is evaluated before the distinct clause, so your queries > aren't equivalent because you switched the order by splitting the query > into two queries... Besides which, SELECT DISTINCT ON is order-sensitive. If you don't use an ORDER BY with it, you are going to get rather unpredictable results. See the example on the SELECT reference page. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] help optimise this ?
Title: RE: [SQL] help optimise this ? Peter Galbavy wrote: > I have a table of image 'instances' where the columns include: > > md5 char(32), -- the 'original' image md5 key > file_md5 char(32) primary key, -- the md5 of each version of an > image image_width int, > image_length int > > I want to then find either the largest (max) or smallest (min) > version of an image that falls within some range of sizes: > > e.g. > > select file_md5 from image_instance > where image_width = > (select min(image_width) from image_instance where md5 = > '546b94e94851a56ee721f3b755f58462') > and image_length = > (select min(image_length) from image_instance where md5 = > '546b94e94851a56ee721f3b755f58462') > and md5 = '546b94e94851a56ee721f3b755f58462' > and image_width between 0 and 160 > and image_length between 0 and 160; > > Now, having to do three selects on 'md5' to limit the search seems a > little unoptimal to me. Note that the test tables are small and I > have no other indexes apart from the 'primary key' constraint yet - > this is not my primary concern at this point, I would just like > cleaner SQL. > > All I want back is (for some definition) the 'file_md5' that best > matches my min/max criteria. > > I have not - and will leave for now - the case where a cropped image > results in a scale change between width and length such that the > min/max test returns a different set of rows for each dimension. Argh. > > And help given is greatly appreciated. > > rgds, > -- > Peter > If you are willing to use pgsqlism how about: select file_md5 from image_instance WHERE md5 = '546b94e94851a56ee721f3b755f58462' AND image_width between 0 and 160 AND image_length between 0 and 160 AND ORDER BY image_width::int8*image_length::int8 LIMIT 1 This should get the smallest overall image size within your bounds. It might be faster to do ORDER BY image_width,image_length LIMIT 1 but this wouldn't necessarily give the smallest if the aspect ratio changed hth, - Stuart
Re: [SQL] Date trunc in UTC
Richard Huxton <[EMAIL PROTECTED]> writes: > Hmm - good point. You can revert to the client default but not to the > previous value. I don't know of any way to read these SET values > either - a quick poke through pg_proc didn't show anything likely. In 7.3 you can use current_setting() and set_config() to access SHOW/SET functionality. However, I agree with your suggestion of AT TIME ZONE to rotate a timestamp into a target timezone, rather than mucking with the TimeZone setting. BTW, Thomas: is AT TIME ZONE supposed to accept timestamp-without-timezone input? If so, what's it supposed to do with it? The current behavior seems unintuitive to say the least: regression=# select now(); now --- 2002-11-21 10:19:14.591001-05 (1 row) regression=# select now() at time zone 'UTC'; timezone 2002-11-21 15:19:18.588279 (1 row) regression=# select localtimestamp; timestamp 2002-11-21 10:19:22.629865 (1 row) regression=# select localtimestamp at time zone 'UTC'; timezone --- 2002-11-21 05:19:26.178861-05 (1 row) It seems to me that the last case should give either an error or 2002-11-21 15:19:26.178861 (ie, assume that the timestamp without time zone is in my TimeZone zone). In any case, surely the result should be of type timestamp WITHOUT time zone? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] help optimise this ?
"Peter Galbavy" <[EMAIL PROTECTED]> writes: > I want to then find either the largest (max) or smallest (min) version of an > image that falls within some range of sizes: Depends on how you want to define "largest" and "smallest", but if "area" is a good enough definition, seems like this would work: select file_md5 from image_instance where md5 = '546b94e94851a56ee721f3b755f58462' and image_width between 0 and 160 and image_length between 0 and 160 order by image_width * image_length limit 1; Probably an index on md5 would be sufficient to make this go fast --- I assume you're not going to be storing a vast number of sizes of the same image. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] help optimise this ?
Wow. Three people have replied with an effectively identical solution. Why didn't I think of this ? Answers on a postcard to... Thanks to all that have replied. Peter - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Peter Galbavy" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, November 21, 2002 3:46 PM Subject: Re: [SQL] help optimise this ? > "Peter Galbavy" <[EMAIL PROTECTED]> writes: > > I want to then find either the largest (max) or smallest (min) version of an > > image that falls within some range of sizes: > > Depends on how you want to define "largest" and "smallest", but if > "area" is a good enough definition, seems like this would work: > > select file_md5 from image_instance > where md5 = '546b94e94851a56ee721f3b755f58462' > and image_width between 0 and 160 > and image_length between 0 and 160 > order by image_width * image_length > limit 1; > > Probably an index on md5 would be sufficient to make this go fast --- > I assume you're not going to be storing a vast number of sizes of > the same image. > > regards, tom lane > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] psql on FreeBSD 4.7-RELEASE-p2 and greek (iso8859-7) chars
Achilleus Mantzios <[EMAIL PROTECTED]> writes: > Hi, > > I noticed that psql on FreeBSD (i checked also fbsd 4.5 with pgsql port > installed) > does not accept 8bit iso8859-* chars > 128 (where the greek chars are). > > In linux that works ok, and i can update/insert/select values > using greek strings. > > I know it must be a fbsd/locale issue, but it would be nice > if someone knew something about it. If pgsql is using readline adding set meta-flag on set convert-meta off set output-meta on to /etc/inputrc or ~/.inputrc could help. See readline(3). Regards, Manuel. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Dropping Ref. Integrity
Hi, I'm trying to drop some referential integrity on a table and am getting this error: PostgreSQL said: ERROR: zero-length delimited identifier Your query: DROP TRIGGER "RI_ConstraintTrigger_6187534" ON "host_domain"; DROP TRIGGER "RI_ConstraintTrigger_6187538" ON "host_domain"; DROP TRIGGER "" ON ""; I created this using the 'references' statement. I dropped the integrity using the PhpPgAdmin tool. There's a screen where you can see the triggers on a database. The 'update' trigger dropped ok but the 'on' trigger is giving the above error. Any idea's ? TIA Regards Rudi. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] connectby questions
I just installed 7.3rc1 and added contrib/tablefunc. I am able to get the
example in the README document to work. I am having trouble understanding
how to get my real data to behave.
The table is:
freshports=# \d element
Table "public.element"
Column| Type |Modifiers
-+--+--
id | integer | not null default
nextval('element_id_seq'::text)
name| text | not null
parent_id | integer |
directory_file_flag | character(1) | not null
status | character(1) | not null
I have been able to get simple examples to work:
freshports=# select id, parent_id from connectby('element', 'id',
'parent_id', '104503', 0) as t(id int, parent_id int, level int);
id | parent_id
+---
104503 |
104504 |104503
104505 |104503
(3 rows)
Why does level not appear here? I see a similar problem with this query:
freshports=# select id, parent_id from connectby('element', 'id',
'parent_id', '104503', 0, '/') as t(id int, parent_id int, level int,
branch text);
id | parent_id
+---
104503 |
104504 |104503
104505 |104503
(3 rows)
Here is the actual data for the above nodes:
freshports=# select * from element where id in (104503, 104504, 104505);
id | name | parent_id | directory_file_flag | status
+--+---+-+
104503 | multimedia | 77344 | D | A
104504 | Makefile |104503 | F | A
104505 | chapter.sgml |104503 | F | A
(3 rows)
What I would like to include in the output is all of the above fields.
But I can't seem to get that to work:
freshports=# select id, parent_id, name from connectby('element', 'id',
'parent_id', '104503', 0, '/') as
t(id int, parent_id int, level int, branch text, name text);
ERROR: Query-specified return tuple not valid for Connectby: wrong number
of columns
I was able to do this with a view:
freshports=# create view simple_element as select id, parent_id from
element;
CREATE VIEW
freshports=# select * from connectby('simple_element', 'id', 'parent_id',
'104503', 0, '/') as t(id int,
parent_id int, level int, branch text);
id | parent_id | level |branch
+---+---+---
104503 | | 0 | 104503
104504 |104503 | 1 | 104503/104504
104505 |104503 | 1 | 104503/104505
(3 rows)
Whis is expected given what I see in the README.
But there doesn't seem to be any way to get the name field out:
freshports=# drop view simple_element;
DROP VIEW
freshports=# create view simple_element as select id, parent_id, name from
element;
CREATE VIEW
freshports=# select * from connectby('simple_element', 'id', 'parent_id',
'104503', 0, '/') as t(id int,
parent_id int, level int, branch text);
id | parent_id | level |branch
+---+---+---
104503 | | 0 | 104503
104504 |104503 | 1 | 104503/104504
104505 |104503 | 1 | 104503/104505
(3 rows)
freshports=# select * from connectby('simple_element', 'id', 'parent_id',
'104503', 0, '/') as t(id int, parent_id int, level int, branch text, name
text);
ERROR: Query-specified return tuple not valid for Connectby: wrong number
of columns
freshports=#
I hope it's just that it's late and I'm missing something. Cheers.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] [GENERAL] Bug with sequence
It seems worth pointing out, too, that some SQL purists propose not
relying on product-specific methods of auto-incrementing.
I.e., it is possible to do something like:
insert into foo( col, ... )
values( coalesce( ( select max( col ) from foo ), 0 ) + 1, ... );
and this is easily placed in a trigger.
-tfo
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Tom Lane) wrote:
> Justin Clift <[EMAIL PROTECTED]> writes:
> > Oliver Elphick wrote:
> >> I created a sequence using SERIAL when I created a table. I used the
> >> same sequence for another table by setting a column default to
> >> nextval(sequence).
> >>
> >> I deleted the first table. The sequence was deleted too, leaving the
> >> default of the second table referring to a non-existent sequence.
>
> > This sounds like a serious bug in our behaviour, and not something we'd
> > like to release.
>
> We will be releasing it whether we like it or not, because
> nextval('foo') doesn't expose any visible dependency on sequence foo.
>
> (If you think it should, how about nextval('fo' || 'o')? If you think
> that's improbable, consider nextval('table' || '_' || 'col' || '_seq').)
>
> The long-term answer is to do what Rod alluded to: support the
> Oracle-style syntax foo.nextval, so that the sequence reference is
> honestly part of the parsetree and not buried inside a string
> expression.
>
> In the meantime, I consider that Oliver was misusing the SERIAL
> feature. If you want multiple tables fed by the same sequence object,
> you should create the sequence as a separate object and then create
> the tables using explicit "DEFAULT nextval('foo')" clauses. Doing what
> he did amounts to sticking his fingers under the hood of the SERIAL
> implementation; if he gets his fingers burnt, it's his problem.
>
> > Specifically in relation to people's existing scripts, and also to
> > people who are doing dump/restore of specific tables (it'll kill the
> > sequences that other tables depend on too!)
>
> 7.3 breaks no existing schemas, because older schemas will be dumped
> as separate CREATE SEQUENCE and CREATE TABLE ... DEFAULT nextval()
> commands.
>
> regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [HACKERS] [GENERAL] Bug with sequence
Of course, those would be SQL purists who _don't_ understand
concurrency issues. ;-)
---
Thomas O'Connell wrote:
> It seems worth pointing out, too, that some SQL purists propose not
> relying on product-specific methods of auto-incrementing.
>
> I.e., it is possible to do something like:
>
> insert into foo( col, ... )
> values( coalesce( ( select max( col ) from foo ), 0 ) + 1, ... );
>
> and this is easily placed in a trigger.
>
> -tfo
>
> In article <[EMAIL PROTECTED]>,
> [EMAIL PROTECTED] (Tom Lane) wrote:
>
> > Justin Clift <[EMAIL PROTECTED]> writes:
> > > Oliver Elphick wrote:
> > >> I created a sequence using SERIAL when I created a table. I used the
> > >> same sequence for another table by setting a column default to
> > >> nextval(sequence).
> > >>
> > >> I deleted the first table. The sequence was deleted too, leaving the
> > >> default of the second table referring to a non-existent sequence.
> >
> > > This sounds like a serious bug in our behaviour, and not something we'd
> > > like to release.
> >
> > We will be releasing it whether we like it or not, because
> > nextval('foo') doesn't expose any visible dependency on sequence foo.
> >
> > (If you think it should, how about nextval('fo' || 'o')? If you think
> > that's improbable, consider nextval('table' || '_' || 'col' || '_seq').)
> >
> > The long-term answer is to do what Rod alluded to: support the
> > Oracle-style syntax foo.nextval, so that the sequence reference is
> > honestly part of the parsetree and not buried inside a string
> > expression.
> >
> > In the meantime, I consider that Oliver was misusing the SERIAL
> > feature. If you want multiple tables fed by the same sequence object,
> > you should create the sequence as a separate object and then create
> > the tables using explicit "DEFAULT nextval('foo')" clauses. Doing what
> > he did amounts to sticking his fingers under the hood of the SERIAL
> > implementation; if he gets his fingers burnt, it's his problem.
> >
> > > Specifically in relation to people's existing scripts, and also to
> > > people who are doing dump/restore of specific tables (it'll kill the
> > > sequences that other tables depend on too!)
> >
> > 7.3 breaks no existing schemas, because older schemas will be dumped
> > as separate CREATE SEQUENCE and CREATE TABLE ... DEFAULT nextval()
> > commands.
> >
> > regards, tom lane
>
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [HACKERS] [GENERAL] Bug with sequence
"Thomas O'Connell" <[EMAIL PROTECTED]> writes: > It seems worth pointing out, too, that some SQL purists propose not > relying on product-specific methods of auto-incrementing. > I.e., it is possible to do something like: > insert into foo( col, ... ) > values( coalesce( ( select max( col ) from foo ), 0 ) + 1, ... ); > and this is easily placed in a trigger. ... but that approach is entirely unworkable if you want any concurrency of insert operations. (Triggers are a tad product-specific, too :-() regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [HACKERS] [GENERAL] Bug with sequence
On Thu, 2002-11-21 at 15:09, scott.marlowe wrote: > On 21 Nov 2002, Rod Taylor wrote: > > > On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote: > > > Of course, those would be SQL purists who _don't_ understand > > > concurrency issues. ;-) > > > > Or they're the kind that locks the entire table for any given insert. > > Isn't that what Bruce just said? ;^) I suppose so. I took what Bruce said to be that multiple users could get the same ID. I keep having developers want to make their own table for a sequence, then use id = id + 1 -- so they hold a lock on it for the duration of the transaction. -- Rod Taylor <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
