[SQL] Rename Schema or Script DDL only a schema
Dear Friends, Postgres 7.3.2 on Linux 8. I wanna move about 100 tables from one schema to another (within a database). Is possible to do that? Seems pg_dump doesnt have an option to script only the schema. Else, it possible to rename the schema. Please shed some light. Thanks Kumar
Re: [SQL] Rename Schema or Script DDL only a schema
On Tue, 6 Apr 2004 14:32:04 +0530, kumar <[EMAIL PROTECTED]> wrote: Dear Friends, Postgres 7.3.2 on Linux 8. I wanna move about 100 tables from one schema to another (within a database). Is possible to do that? Seems pg_dump doesnt have an option to script only the schema. Else, it possible to rename the schema. Please shed some light. Thanks Kumar Not with this version of PG. v7.4 allows you to rename schema and dump specific schemas. -- Downloading signature ... 99% *CRC FAILED* signature aborted ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] could not devise a query plan
Dear Gurus, I couldn't find the string of my email's subject on the web, except for one place: the PostgreSQL source :) So I'm desperate. -- VERSION I'm using "PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4" with the patch for "shown aggregate columns is 0" (if you know what I mean ;) ) Version "PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4" on a differend machine yields the same results, except as noted below. Difference may be the version or something else, but there is a recent mirror of the 7.3.3 db (generated from textual pg_dump) on the 7.4.1 server that also throws the error. -- ABSTRACT #1. Below is a very simplified query that throws this error. The original query used a view, CASE's, aggregates, function calls and meaningful WHERE clauses :) The idea is to join the table with itself, but the subselects sum different rows in field vi_m and sz_m. Some modifications solve the problem, I show two versions. #2. One is a single field rename (counts much in NATURAL FULL), #3. the other is a group by construction. There is another erroneous query: #4. Giving an outer WHERE clause to #3, the error is back, BUT ONLY IN 7.4.1 -- DETAILS are at the end of this email. -- CONCLUSION If this is enough to give me a clue, I'd be grateful. If there is a general discussion about this error, I'd be honoured. If you'd like to see the original query and corresponding definitions, I think I can share it with you. If this is a bug and has been fixed since 7.4.1, I'd take the task to compile a newer version and see how it fares. G. %--- cut here ---% \end \d sztgy Table "pg_temp_4.sztgy" Column| Type | Modifiers -+---+--- az | integer | allapot | integer | megrendelo | integer | szallito| integer | keretrendeles_az| integer | teljesites | date | szallitolevel_fajta | integer | szallitas | integer | tetelszam | integer | cikk| integer | minoseg | integer | mennyiseg | numeric(14,4) | fajta | integer | mennyisegi_egyseg | integer | hibastatusz | integer | %--- cut here ---% -- #1: This throws the error: SELECT * FROM (SELECT * FROM (SELECT sum(mennyiseg) as vi_m FROM sztgy ) szt_having ) AS szt NATURAL FULL OUTER JOIN (SELECT * FROM (SELECT sum(mennyiseg) as sz_m FROM sztgy ) vsz_having ) AS vsz; ERROR: could not devise a query plan for the given query %--- cut here ---% -- #2: This works, with a single rename, but useless for me: SELECT * FROM (SELECT * FROM (SELECT sum(mennyiseg) as sz_m FROM sztgy ) szt_having ) AS szt NATURAL FULL OUTER JOIN (SELECT * FROM (SELECT sum(mennyiseg) as sz_m FROM sztgy ) vsz_having ) AS vsz; sz_m 530515336.8900 (1 row) %--- cut here ---% -- #3: This works, with group-by -- the original query has group-by clause, but throws the error (see #4) -- SELECT'ed count just to show the result. SELECT'ing * also works. SELECT count(*) FROM (SELECT * FROM (SELECT cikk, minoseg, sum(mennyiseg) as vi_m FROM sztgy group by cikk, minoseg ) szt_having ) AS szt NATURAL FULL OUTER JOIN (SELECT * FROM (SELECT cikk, minoseg, sum(mennyiseg) as sz_m FROM sztgy group by cikk, minoseg ) vsz_having ) AS vsz; count --- 1590 (1 row) %--- cut here ---% -- #4: This works only on server v7.3.3: SELECT * FROM (SELECT * FROM (SELECT cikk, minoseg, sum(mennyiseg) as vi_m FROM sztgy group by cikk, minoseg ) szt_having where cikk=101917 and minoseg=1 ) AS szt NATURAL FULL OUTER JOIN (SELECT * FROM (SELECT cikk, minoseg, sum(mennyiseg) as sz_m FROM sztgy group by cikk, minoseg ) vsz_having where cikk=101917 and minoseg=1 ) AS vsz; -- 7.3.3: cikk | minoseg | vi_m | sz_m +-+-+- 101917 | 1 | 20. | 20. (1 row) -- 7.4.1: ERROR: could not devise a query plan for the given query ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Entered data appears TWICE in table!!?
--- Ron Morgan <[EMAIL PROTECTED]> wrote:
> Both Internet Explorer and Safari, on a Mac running
> OS X.
>
> I'm just using the tutorial web site at
> sqlcourse.com. They have a window
> there where you type code and run it, etc.
>
> Ron
>
I recall doing that tutorial once upon a time. A very
good thing.
But I don't see the connection to PostgreSQL. Perhaps
you can explain it?
> - Original Message -
> From: "Jeff Eckermann" <[EMAIL PROTECTED]>
> To: "Ron M." <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Monday, April 05, 2004 5:33 PM
> Subject: Re: [SQL] Entered data appears TWICE in
> table!!?
>
>
> > What interface are you using?
> >
> > --- "Ron M." <[EMAIL PROTECTED]> wrote:
> > > I'm JUST getting started with the online SQL
> > > tutorial at
> > > http://sqlcourse.com. When I create a table and
> > > insert data, the data
> > > appears TWICE. A simple example:
> > >
> > > ***Create the table:
> > >
> > > create table rnmrgntable
> > > (first varchar(20),
> > > last varchar(30));
> > >
> > > ***Insert data:
> > >
> > > insert into rnmrgntable
> > > (first, last)
> > > values ('Bill' , 'Smith');
> > >
> > > ***Then look at the table:
> > >
> > > select * from rnmrgntable;
> > >
> > > And I get:
> > >
> > > firstlast
> > > BillSmith
> > > BillSmith
> > >
> > > EVERYTHING I enter appears twice, duplicated on
> two
> > > rows as in this
> > > example. What the heck's going on?
> > >
> > > Ron M.
> > >
> > > ---(end of
> > > broadcast)---
> > > TIP 7: don't forget to increase your free space
> map
> > settings
> >
> >
> > __
> > Do you Yahoo!?
> > Yahoo! Small Business $15K Web Design Giveaway
> > http://promotions.yahoo.com/design_giveaway/
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system
> (http://www.grisoft.com).
> Version: 6.0.644 / Virus Database: 412 - Release
> Date: 3/26/2004
>
__
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway
http://promotions.yahoo.com/design_giveaway/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] partial unique constraint
Trying to come up with the proper syntax to meet the following criteria:
create table foo (bar integer, baz boolean UNIQUE (bar, baz = true));
note the above syntax is not correct, but should demonstrate what i'm
trying to do; I want to add a unique constraint such that we only allow
one case of bar and baz = true... i can have unlimited bar and baz =
false, and there can be multiple bar and baz = true if the bars are
different... did some doc reading and mail list searching but a valid
syntax for this seems to be escaping me...
btw I'm pretty sure I could do this with an external trigger, but am
wondering about a constraint oriented approach
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] could not devise a query plan
"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <[EMAIL PROTECTED]> writes: > If this is a bug and has been fixed since 7.4.1, I'd take the task to > compile a newer version and see how it fares. It's still there in CVS tip :-(. Will look into it today. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] partial unique constraint
Robert Treat <[EMAIL PROTECTED]> writes: > Trying to come up with the proper syntax to meet the following criteria: > create table foo (bar integer, baz boolean UNIQUE (bar, baz = true)); The correct way to do it is with a separate CREATE INDEX command: create table foo (bar integer, baz boolean); create unique index fooi on foo (bar) where baz = true; Personally I'd spell that last as just "where baz" ... regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] partial unique constraint
O kyrios Robert Treat egrapse stis Apr 6, 2004 : > Trying to come up with the proper syntax to meet the following criteria: > > create table foo (bar integer, baz boolean UNIQUE (bar, baz = true)); You could simply leave your table as is. A null vale on baz (equivalent of 'false') will have no impact on the constraint. A 't' value (equivalent to 'true') will enforce the constraint. In other words you can convert your problem as baz == null <==> baz='false' baz == 't' <==> baz='true' (you must never use baz='f') > > note the above syntax is not correct, but should demonstrate what i'm > trying to do; I want to add a unique constraint such that we only allow > one case of bar and baz = true... i can have unlimited bar and baz = > false, and there can be multiple bar and baz = true if the bars are > different... did some doc reading and mail list searching but a valid > syntax for this seems to be escaping me... > > btw I'm pretty sure I could do this with an external trigger, but am > wondering about a constraint oriented approach > > Robert Treat > -- -Achilleus ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] partial unique constraint
You should create a functional index here.
Ries
> -Oorspronkelijk bericht-
> Van: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Robert Treat
> Verzonden: dinsdag 6 april 2004 16:30
> Aan: [EMAIL PROTECTED]
> Onderwerp: [SQL] partial unique constraint
>
>
> Trying to come up with the proper syntax to meet the
> following criteria:
>
> create table foo (bar integer, baz boolean UNIQUE (bar, baz = true));
>
> note the above syntax is not correct, but should demonstrate what i'm
> trying to do; I want to add a unique constraint such that we
> only allow
> one case of bar and baz = true... i can have unlimited bar and baz =
> false, and there can be multiple bar and baz = true if the bars are
> different... did some doc reading and mail list searching but a valid
> syntax for this seems to be escaping me...
>
> btw I'm pretty sure I could do this with an external trigger, but am
> wondering about a constraint oriented approach
>
> Robert Treat
> --
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>
>
> ---(end of
> broadcast)---
> TIP 7: don't forget to increase your free space map settings
>
---(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] partial unique constraint
On Tue, 6 Apr 2004, Robert Treat wrote: > Trying to come up with the proper syntax to meet the following criteria: > > create table foo (bar integer, baz boolean UNIQUE (bar, baz = true)); > > note the above syntax is not correct, but should demonstrate what i'm > trying to do; I want to add a unique constraint such that we only allow > one case of bar and baz = true... i can have unlimited bar and baz = > false, and there can be multiple bar and baz = true if the bars are > different... did some doc reading and mail list searching but a valid > syntax for this seems to be escaping me... > > btw I'm pretty sure I could do this with an external trigger, but am > wondering about a constraint oriented approach You can't precisely with a constraint (AFAIR sql is marginally limited in that way at least in 92 and 99), but you should be able to do it with a partial unique index something like create unique index foo_i on foo(bar) where baz is true; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] partial unique constraint
On 06/04/2004 15:29 Robert Treat wrote: Trying to come up with the proper syntax to meet the following criteria: create table foo (bar integer, baz boolean UNIQUE (bar, baz = true)); note the above syntax is not correct, but should demonstrate what i'm trying to do; I want to add a unique constraint such that we only allow one case of bar and baz = true... i can have unlimited bar and baz = false, and there can be multiple bar and baz = true if the bars are different... did some doc reading and mail list searching but a valid syntax for this seems to be escaping me... btw I'm pretty sure I could do this with an external trigger, but am wondering about a constraint oriented approach What about create table foo (bar integer, baz boolean); create unique index foo_bar_baz on foo(bar, baz) where baz = true; -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] partial unique constraint
On Tue, 2004-04-06 at 10:29, Robert Treat wrote:
> Trying to come up with the proper syntax to meet the following criteria:
>
> create table foo (bar integer, baz boolean UNIQUE (bar, baz = true));
Tt takes 2 steps.
CREATE TABLE ...
CREATE UNIQUE INDEX ... (bar) WHERE baz = true;
--
Rod Taylor
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/signature.asc
signature.asc
Description: This is a digitally signed message part
[SQL] SQL Standatd
In PostgreSQL documentation, there is a section "Compatibility" (Reference -> SQL Commands -> Any command). There, alway has something like: The ADD COLUMN form conforms with the SQL standard... Where can I find this "SQL standard"? What is its official site? Thank you, -- Ricardo Vaz Mannrich <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] group by not returning sorted rows
On Mon, 2004-04-05 at 17:34, Tom Lane wrote: > Bret Hughes <[EMAIL PROTECTED]> writes: > > and the rows resulting from the query are no longer sorted by log date. > > Is this a change since 7.2x? > > Yes. 7.4 can use hashing instead of sorting to bring grouped rows > together. > > > I can achieve the results I need by adding an order by clause identical > > to the group by but this seems counter intuitive since the rows have to > > be ordered anyway. > > No they don't; you're making an assumption about the implementation that > is no longer warranted. The SQL spec doesn't require it either ... > output ordering is only guaranteed if you specify ORDER BY, per spec. > > regards, tom lane Thanks, I replied to Tom directly by accident and did not want the list to think I was ungrateful for the quick reply :) Bret ---(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
[SQL] distinct values without seq scan
I have a table indexed on a char(35) field and want a query to return a
distinct list of the values in this column. Is there some syntactical
magic I can do to get these values without a sequential scan? I assume,
( here we go again ) that these values are in the index somewhere and I
seem to recall a select DISTINCT using an index in DB2 way back when but
I may be mistaken.
Searching the archives I found an email that indicated this was possible
in 7.4 and infact is the reason I upgraded from 7.2x.
The table in question has about 700K rows and grows daily so seq scans
hurt more and more all the time.
Tips appreciated.
elevating=# \d logrecords
Table "public.logrecords"
Column | Type |
Modifiers
--+---+---
serial | integer | not null default
nextval('"logrecords_serial_seq"'::text)
city | smallint | not null
building | smallint | not null
display | integer | not null
advertiser | character(35) | not null
pagename | character(65) | not null
log_date | date | not null
exhibition_count | integer | not null
Indexes:
"logrecords_pkey" primary key, btree (serial)
"logrecords_advertiser" btree (advertiser)
"logrecords_building" btree (building)
"logrecords_city" btree (city)
"logrecords_date" btree (log_date)
Triggers:
"RI_ConstraintTrigger_1709151" AFTER INSERT OR UPDATE ON logrecords
FROM cities NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins"('logrec_ref_cities', 'logrecords',
'cities', 'UNSPECIFIED', 'city', 'num')
"RI_ConstraintTrigger_1709152" AFTER INSERT OR UPDATE ON logrecords
FROM buildings NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins"('logrecords_ref_buildings', 'logrecords',
'buildings', 'UNSPECIFIED', 'building', 'num')
"RI_ConstraintTrigger_1709153" AFTER INSERT OR UPDATE ON logrecords
FROM displays NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins"('logrecords_ref_display', 'logrecords',
'displays', 'UNSPECIFIED', 'display', 'num')
elevating=# explain select distinct advertiser from logrecords;
QUERY
PLAN
Unique (cost=136440.68..139858.43 rows=61 width=39)
-> Sort (cost=136440.68..138149.55 rows=683550 width=39)
Sort Key: advertiser
-> Seq Scan on logrecords (cost=0.00..20785.50 rows=683550
width=39)
(4 rows)
Bret
---(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] SQL Standatd
Ricardo Vaz Mannrich wrote: > In PostgreSQL documentation, there is a section "Compatibility" > (Reference -> SQL Commands -> Any command). > > There, alway has something like: > > The ADD COLUMN form conforms with the SQL standard... > > Where can I find this "SQL standard"? What is its official site? See the developer's FAQ for URL's. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] could not devise a query plan
"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <[EMAIL PROTECTED]> writes:
> I couldn't find the string of my email's subject on the web, except for one
> place: the PostgreSQL source :)
Seems that you've managed to tickle a strange corner case, which can be
reduced to simplest form like this:
regression=# select * from a full join b on true;
ERROR: Unable to devise a query plan for the given query
In your first example, the empty join clause falls out because you don't
actually have any columns of the same names on both sides, and so the
NATURAL join doesn't find any columns to join. AFAICS this is allowed
by the SQL spec, but still I can't help suspecting that it is
programming error on your part. You're going to get a cross-product
join ... is that really what you intended?
The second example is slightly more interesting: it boils down to a case
like this:
select * from
(select unique1 from tenk1 where unique1 = 42) a
full join
(select unique1 from tenk1 where unique1 = 42) b
on a.unique1 = b.unique1;
7.4 is perhaps too smart for its own good here: it is able to figure out
that the join clause is redundant because every row coming up from the
subselects must have the same value in the join columns (here, 42). So
it discards the join clause ... leaving it in the same situation where
it can't generate a plan :-(
Although I think the first case is really user error, the second case
looks like it could arise unexpectedly in program-generated queries
given the right combination of inputs, so we probably ought to do
something about it. I have applied the attached patch to 7.4.
(It would probably work in 7.3 too, but no guarantees.)
regards, tom lane
Index: costsize.c
===
RCS file: /cvsroot/pgsql-server/src/backend/optimizer/path/costsize.c,v
retrieving revision 1.115.2.1
diff -c -r1.115.2.1 costsize.c
*** costsize.c 3 Dec 2003 17:45:36 - 1.115.2.1
--- costsize.c 6 Apr 2004 18:41:17 -
***
*** 928,950
* all mergejoin paths associated with the merge clause, we cache the
* results in the RestrictInfo node.
*/
! firstclause = (RestrictInfo *) lfirst(mergeclauses);
! if (firstclause->left_mergescansel < 0) /* not computed yet? */
! mergejoinscansel(root, (Node *) firstclause->clause,
!&firstclause->left_mergescansel,
!&firstclause->right_mergescansel);
!
! if (bms_is_subset(firstclause->left_relids, outer_path->parent->relids))
{
! /* left side of clause is outer */
! outerscansel = firstclause->left_mergescansel;
! innerscansel = firstclause->right_mergescansel;
}
else
{
! /* left side of clause is inner */
! outerscansel = firstclause->right_mergescansel;
! innerscansel = firstclause->left_mergescansel;
}
/* convert selectivity to row count; must scan at least one row */
--- 928,958
* all mergejoin paths associated with the merge clause, we cache the
* results in the RestrictInfo node.
*/
! if (mergeclauses)
{
! firstclause = (RestrictInfo *) lfirst(mergeclauses);
! if (firstclause->left_mergescansel < 0) /* not computed yet? */
! mergejoinscansel(root, (Node *) firstclause->clause,
!
&firstclause->left_mergescansel,
!
&firstclause->right_mergescansel);
!
! if (bms_is_subset(firstclause->left_relids,
outer_path->parent->relids))
! {
! /* left side of clause is outer */
! outerscansel = firstclause->left_mergescansel;
! innerscansel = firstclause->right_mergescansel;
! }
! else
! {
! /* left side of clause is inner */
! outerscansel = firstclause->right_mergescansel;
! innerscansel = firstclause->left_mergescansel;
! }
}
else
{
! /* cope with clauseless mergejoin */
! outerscansel = innerscansel = 1.0;
}
/* convert selectivity to row count; must scan at least one row */
Index: joinpath.c
===
RCS file: /cvsroot/pgsql-server/src/backend/optimizer/path/joinpath.c,v
retrieving revision 1.82
diff -c -r1.82 joinpath.c
*** joinpath.c 25 Sep 2003 06:58:00 - 1.82
--- joinpath.c 6 Apr 2004 18:41:18 -
***
*** 489,497
Re: [SQL] partial unique constraint
On Tue, 2004-04-06 at 11:17, Tom Lane wrote:
> Robert Treat <[EMAIL PROTECTED]> writes:
> > Trying to come up with the proper syntax to meet the following criteria:
> > create table foo (bar integer, baz boolean UNIQUE (bar, baz = true));
>
> The correct way to do it is with a separate CREATE INDEX command:
I think I had initially abandoned looking at that type of solution after
having run across this paragraph in the docs while looking for the
proper constraint syntax:
"Note: The preferred way to add a unique constraint to a table is ALTER
TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique
constraints could be considered an implementation detail that should not
be accessed directly."
The subliminal mind is a powerful force eh? Perhaps that paragraph
should be modified... but I'm not sure if it should expanded to include
thoughts along the lines of Stephan's response or maybe just drop the
"should not be accessed directly" bit...
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Function To Log Changes
A plpython solution is available in Issue #66 of PostgreSQL GeneralBits. http://www.varlena.com/GeneralBits/66 Let me know if this helps. elein On Mon, Apr 05, 2004 at 01:01:39PM -0400, Gavin wrote: > Hi All, I have been tinkering with a function to log the changes made on > any column through a function and trigger. However, I cant think of a way > to make this work through pl/pgsql. Any one have any ideas, or is it just > not possible? > > SNIP > create or replace function logchange2() returns OPAQUE as ' > DECLARE > columnname record; > c2 VARCHAR(64); > > BEGIN > > /* Cycle through the column names so we can find the changes being made */ > FOR columnname IN SELECT attname FROM pg_attribute, pg_type > WHERE attnum > 0 AND typrelid=attrelid AND typname=''SOMETABLE'' LOOP > > c2 := CAST(columnname.attname AS VARCHAR(64)); > /* here lies the problem. How would I make plpgsql see OLD.columnname in > a dynamic fashion. I know this wont work whats below, but I am just > trying to express what I am trying to do */ > IF ''OLD.'' || c2 != ''NEW.'' || c2 THEN > /* IF CHANGED DO SOMETHING */ > RAISE NOTICE ''Update on column %'', c2; > END IF; > > END LOOP; > > return NULL; > END; > ' > LANGUAGE plpgsql; > > create trigger logchange2 AFTER UPDATE on TABLENAME FOR EACH ROW EXECUTE > PROCEDURE logchange2(); > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] partial unique constraint
On Tuesday 06 April 2004 17:38, Robert Treat wrote: > On Tue, 2004-04-06 at 11:17, Tom Lane wrote: > > Robert Treat <[EMAIL PROTECTED]> writes: > > > Trying to come up with the proper syntax to meet the following > > > criteria: create table foo (bar integer, baz boolean UNIQUE (bar, baz = > > > true)); > > > > The correct way to do it is with a separate CREATE INDEX command: > > I think I had initially abandoned looking at that type of solution after > having run across this paragraph in the docs while looking for the > proper constraint syntax: > > "Note: The preferred way to add a unique constraint to a table is ALTER > TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique > constraints could be considered an implementation detail that should not > be accessed directly." > > The subliminal mind is a powerful force eh? Perhaps that paragraph > should be modified... but I'm not sure if it should expanded to include > thoughts along the lines of Stephan's response or maybe just drop the > "should not be accessed directly" bit... Anyone know if there is any way to do this in one of the later SQL standards? The CREATE INDEX thing is a bit of a hack, and I certainly wouldn't have thought of it either. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Getting the ranks of results from a query
On Thu, Apr 01, 2004 at 11:05:55 -0500, abhi <[EMAIL PROTECTED]> wrote: > I have a query of the form > > select id from member order by age; > > id > - > 431 > 93 > 202 > 467 > 300 > > In addition to the id, I would like the get the rank of the row-- > in other words: > > id | rank > -+--- > 431 | 1 > 93 | 2 > 202 | 3 > 467 | 4 > 300 | 5 > > > How do I do this with postgres? In the past, I have used something like > > > select id, identity(int, 1,1) from member order by age; > > > is there a postgres equivalent? Note this is going to be slow. And that it relies on ID being unique. SELECT id, (SELECT count(*) FROM member a WHERE a.age < b.age OR (a.age = b.age AND a.id <= b.id)) AS rank FROM member b ORDER BY age, id; For example: bruno=> select * from member; id | age +- 1 | 10 5 | 20 10 | 5 20 | 8 9 | 8 (5 rows) bruno=> SELECT id, (SELECT count(*) FROM member a WHERE a.age < b.age OR bruno(> (a.age = b.age AND a.id <= b.id)) AS rank bruno-> FROM member b bruno-> ORDER BY age, id; id | rank +-- 10 |1 9 |2 20 |3 1 |4 5 |5 (5 rows) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] order of results
On Sat, Apr 03, 2004 at 17:08:34 +0200, Gregor Rot <[EMAIL PROTECTED]> wrote: > Bruno Wolff III wrote: > >On Thu, Mar 25, 2004 at 14:23:00 +0100, > > Gregor Rot <[EMAIL PROTECTED]> wrote: > > > >>Hi, > >> > >>i have a table called "people" (name:varchar, lastname:varchar). > >> > >>i do a select on it: > >> > >>select * from people where name like '%n1%' or lastname like '%l1%'. > >> > >>i would like the results in this order: > >> > >>first the results that satisfy only the (name like '%n1%') condition, > >>then the ones that satisfy only the (lastname like '%l1%') condition and > >>last the results that satisfy both conditions. > >> > >>Is this possible in only one SQL? > >>(note that the search conditions n1 and l1 differ from search to search. > > > > > >Yes. You can order by true/false results from conditions to get the > >results in the desired order. > > Thank you - sorry, but how do you do that? Here is a simple example that illustrates what you can do: bruno=> select col from test order by col like 'bb%', col; col - aaa aac ccc ccd bba bbb bbc (7 rows) ---(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
