Re: [GENERAL] String trim function - possible bug?

On Wed, 6 Jun 2007, Woody Woodring wrote: > I am seeing weirdness using the trim function on a string: > > This works as expected: > > SELECT 'dhct:bn', trim(leading 'dhct:' from 'dhct:bn'); > ?column? | ltrim > --+--- > dhct:bn | bn > (1 row) > > However it fails for these cases:

Re: [GENERAL] Delete/update with limit

On Tue, 24 Jul 2007, Gregory Stark wrote: > "Csaba Nagy" <[EMAIL PROTECTED]> writes: > > >> Unfortunately the stuff that makes a ctid= nice doesn't seem to be > >> used when you're doing an in. It's possible that a function that does > >> something like > >> for rec in select ctid from my_table

Re: [GENERAL] Delete/update with limit

On Tue, 24 Jul 2007, Csaba Nagy wrote: > > How about using the following? > > > > delete from > > where ctid in (select ctid from limit ); > > > > I actually checked this out before starting this thread, and the plan > looked like: > > > explain delete from my_table where ctid in (select cti

Re: [GENERAL] query to match '\N'

On Fri, 27 Jul 2007, pc wrote: > Hi, > > I have a table test with columns col1 col2.col2 contains an entry > '\N' .I want to select all entries which have '\N' in col2.How do i > do that? > > select * from test where col2 like '\N' ; > select * from test where col2 like '\\N' ; select * from

Re: [GENERAL] why is the LIMIT clause slowing down this SELECT?

On Wed, 1 Aug 2007, Scott Marlowe wrote: > On 8/1/07, Mason Hale <[EMAIL PROTECTED]> wrote: > > On a 8.1.9 version database that has been recently vacuumed and > > analyzed, I'm seeing some dramatic performance degradation if a limit > > clause is included in the query. This seems counter-intuiti

Re: [GENERAL] why it doesn't work? referential integrity

On Sat, 11 Aug 2007, Pavel Stehule wrote: > Hello > > I found strange postgresql's behave. Can somebody explain it? There's a bug since it should work for any number, but we've likely missed something. I'm not sure why 2 references work, as I'd expect it to stop working after 1 with the likely ca

Re: [GENERAL] turning off notices

On Sat, 19 Jan 2008, Sue Fitt wrote: > Hi All, > > I'm having trouble with turning off notices. Within psql I use \set > VERBOSITY terse, which is fine. However, using psql -c I am having > trouble. It seems I should be able to use psql -qc 'mycommand' but I am > still getting notices output, e.g.

Re: [GENERAL] 8.2/8.3 incompatibility

On Thu, 7 Feb 2008, Harald Fuchs wrote: > This works fine in 8.2.4, but 8.3.0 rejects the ALTER TABLE with the > following (somewhat misleading) error message: > > ERROR: insert or update on table "t2" violates foreign key constraint > "t2_t1id_fk" > DETAIL: Key (t1id)=(t1id1) is not presen

Re: [GENERAL] SELECT CAST(123 AS char) -> 1

[Way behind on reading stuff - so I hope this wasn't covered later] On Tue, 12 Feb 2008, Tom Lane wrote: > Ken Johanson <[EMAIL PROTECTED]> writes: > > For sake of interoperability (and using an API that requires String-type > > hashtable keys), I'm trying to find a single CAST (int -> var/char)

Re: [GENERAL] SELECT CAST(123 AS char) -> 1

On Sat, 16 Feb 2008, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Tue, 12 Feb 2008, Tom Lane wrote: > >> Also, section 6.10 defines an explicit cast to > >> a fixed-length string type as truncating or padding to the target > >> l

Re: [GENERAL] SELECT CAST(123 AS char) -> 1

On Sat, 16 Feb 2008, Ken Johanson wrote: > Tom Lane wrote: > > > Hm, good point, so really we ought to have a separate casting path for > > numeric types to char(n). However, this section still doesn't offer > > any support for the OP's desire to auto-size the result; it says > > that you get an

Re: [HACKERS] [GENERAL] deadlock with truncate and foreing keys

On Mon, 18 Feb 2008, Tom Lane wrote: > Alexey Nalbat <[EMAIL PROTECTED]> writes: > > create table t1 ( id integer primary key, name text ); > > create table t2 ( id integer references t1 ); > > insert into t1 values ( 1 ); > > insert into t2 values ( 1 ); > > > Then two concurrent transactions sta

Re: [GENERAL] bug in 8.3? foreign key refers to different type

On Tue, 26 Feb 2008, craigp wrote: > These create table commands succeed, even tho the foreign key refers to > a 'different' type (int2 product_id column refers to an int8 column): The requirements in recent SQL specs appears to be that the column types are comparable, not the same. SQL2003 11.8

Re: [GENERAL] Confused about CASE

On Sat, 1 Mar 2008, Thomas Kellerer wrote: > I was writing a statement retrieve dependency information out of the > system catalog, when I noticed something that I didn't expect. > > I wanted to use the following statement to "translate" the relkind > column to a more descriptive value: > > selec

Re: [GENERAL] unexpected results with NOT IN query

On Thu, 20 Mar 2008, Mason Hale wrote: > Hello -- > > I'm getting some unexpected results with a NOT IN query -- this is on 8.2.5. > > This is the query in question: > > prod_2=> select id from feed_download_task where id in (02466,141701504) > and id not in (select last_feed_download_task_id

Re: [GENERAL] how to insert values into complex type field

On Fri, 4 Apr 2008 [EMAIL PROTECTED] wrote: > hi all, i want to know how to insert values into the field which is a complex > type. In fact it is a complex type which also include a complex type. The > following is its definition: > create TYPE lifetime as( strattime date, endtime date); > crea

Re: [GENERAL] mac ports question

On Sat, 5 Apr 2008, Tom Allison wrote: > I ran into a problem today where somewhere my port of postgresql82 just > stopped working. I'm largely an idiot on Mac because I use is as a > workstation/development box and do most of the real system related work > on my debian boxes. > > But I don't kno

Re: [GENERAL] mac ports question

On Sat, 5 Apr 2008, Tom Allison wrote: > If it doesn't remove the 8.2 then I guess I can migrate it. > But that requires that I still need to get 8.2 running. > > > Right now it complains that it can't find a listening socket at /tmp/... > (localhost mode). And I can't find the configuration file

Re: [GENERAL] SQL injection, php and queueing multiple statement

On Sun, 13 Apr 2008, Ivan Sergio Borgonovo wrote: > On Sun, 13 Apr 2008 16:02:35 +0800 > Craig Ringer <[EMAIL PROTECTED]> wrote: > > > > I think this logic is already somewhere in the driver or the pg > > > engine. Whatever you write at the application level a) risk to be > > > a duplication of pa

Re: [GENERAL] Updating with a subselect

On Tue, 22 Apr 2008, Leandro Casadei wrote: > Hi, I need to update a field from a table based in a count. > > This is the query: > > > updateshops > setitemsqty = > ( > select count(*) > from items i1 > join shops s1 on i1.shopid = s1.shopid > where s1.sh

Re: [GENERAL] Updating with a subselect

On Wed, 23 Apr 2008, Leandro Casadei wrote: > On Wed, Apr 23, 2008 at 10:59 AM, Stephan Szabo < > [EMAIL PROTECTED]> wrote: > > > On Tue, 22 Apr 2008, Leandro Casadei wrote: > > > > > Hi, I need to update a field from a table based in a

Re: [GENERAL] String Comparison and NULL

On Mon, 28 Apr 2008 [EMAIL PROTECTED] wrote: > I'm fairly new to PG and databases in general so this may very well be > a problem in my thought process. > > If I have a simple table with an ID (integer) and Animal (text) like > this... > > 1 Dog > 2 Cat > 3 NULL > 4 Horse > 5 Pig > 6 Cat > 7 Cat >

Re: [GENERAL] ranked subqueries vs distinct question

On Wed, 14 May 2008, Karsten Hilbert wrote: > Modifying to: > > select * from ( > > select distinct on (name) * from ( > > select *, 1 as rank from dem.urb where > name ilike 'Lei%' and > zip = '0

Re: [GENERAL] HELP with a query with blank fields

On Tue, 27 May 2008, J. Manuel Velasco wrote: > Hello, > > This is the current query I have: > > SELECT dominis.nom, dominis.extensio, dominis.creat, dominis.expira, > titulars.first_name, titulars.last_name, contactes_admin_tec.first_name, > contactes_admin_tec.last_name, dns1.nom, dns2.nom, dom

Re: [GENERAL] join ... using ... and - is this expected behaviour?

On Tue, 3 Jun 2008, Rob Johnston wrote: > Just wondering if this is expected behaviour. When executing a query in > the form of: > > select column from table join table using (column) and column = clause > > pgsql (8.2) returns the following: syntax error at or near "and" > > Obviously, you can ge

Re: [GENERAL] Exception handling

On Wed, 4 Jun 2008, sam wrote: > Can someone explain me about the exception handling in postgresql. Iam > not understanding the scope of a exception block. The exact confusion > that iam facing is as follows: > I have a procedure as follows > BEGIN > EXECUTE an update statement > > EXECUTE an inse

Re: [GENERAL] Script errors on run

On Wed, 4 Jun 2008, Ralph Smith wrote: >-- == >good_date := to_date(year||'-'||month||'-'||day , '-MM-DD') ; >RAISE NOTICE 'good_date = %',good_date ; >Usecs := EXTRACT(EPOCH FROM TIMESTAMP good_date) ; > END ; > > QUERY: SELECT EXTR

Re: [GENERAL] Script errors on run

On Wed, 4 Jun 2008, Ralph Smith wrote: >date_string := to_date(year||'-'||month||'-'||day , '-MM-DD') ; >RAISE INFO 'date_string = %', date_string ; >good_date := to_timestamp(date_string, '-MM-DD') ; >RAISE INFO 'good_date = %', good_date ; This seems like alot of extra

Re: [GENERAL] Script errors on run

On Wed, 4 Jun 2008, Ralph Smith wrote: >-- == >good_date := to_date(year||'-'||month||'-'||day , '-MM-DD') ; > >RAISE INFO 'good_date = %', good_date ; > >UsecsD := EXTRACT(EPOCH FROM DATE 'good_date') ; You want something like: UsecsD

Re: [GENERAL] column name gets substitudes inside an execute statement of a trigger function. how to avoid it?!

On Tue, 9 Jun 2009, G. Allegri wrote: > Hello list. > I'm a newbie with plpgsql, so I'm sorry for doing stupid questions... > I have a situation whit one table where items are related to two other > tables through a common id (unique in the first table) and the table > name. Whenever the user exec

Re: [GENERAL] Foreign Key Deferrable Misunderstanding or Bug?

On Thu, 6 Aug 2009, Paul Rogers wrote: > Why does the attached script fail with a foreign key constraint violation? Referential actions are not deferred when a constraint is marked deferrable (as that appears to be what the spec wants), so ON DELETE RESTRICT will still fail on the statement, whil

Re: [GENERAL] bytea question

On Mon, 28 Sep 2009, Maximilian Tyrtania wrote: > testdb=# create table byteatest(blob bytea); > CREATE TABLE > testdb=# insert into byteatest (blob) values (E'\\007'); > INSERT 0 1 > testdb=# insert into byteatest (blob) values (E'\\008'); > ERROR: invalid input syntax for type bytea > LINE 1: i

Re: [GENERAL] interface for "non-SQL people"

On Fri, 9 Oct 2009, Joshua D. Drake wrote: > On Thu, 2009-10-08 at 19:16 -0600, Scott Marlowe wrote: > > On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure wrote: > > > > > > The #1 tool you have at your disposal is the human brain. I > > > personally think GUI database tools are counter productive

Re: [GENERAL] What order of steps of the postgres when you change information in the table?

On Sat, 31 Oct 2009, Denis Feklushkin wrote: > > Problem: > It is necessary to synchronize the "users" table with an > external storage of passwords (krb5) > > I made a trigger: > > CREATE TRIGGER "10_krb5" > AFTER INSERT OR UPDATE OR DELETE > ON users > FOR EACH ROW > EXECUTE PROCEDURE u

Re: [GENERAL] deleting the master but not the detail

On Thu, 17 Jul 2008, Ismael wrote: > So is there no other way to do it but to verify the integrity using triggers > and drop the referential constraints? Well, you could do something using a before delete trigger on the referencing table that returns NULL to avoid the delete as well, but mak

Re: [GENERAL] Problem with trigger function

On Wed, 3 Sep 2008, Mira Dimitrijevic wrote: > Hi, > I wrote the trigger function below and when trying to execute it, I > get the following error: > > 15:00:42 [CREATE - 0 row(s), 0.000 secs] [Error Code: 0, SQL > State: 42601] ERROR: syntax error at or near "INSERT" > > I am using DBVisuali

Re: [GENERAL] Help with a foreign key with non-unique reference?

On Tue, 16 Sep 2008, Brent Wood wrote: > I need a foreign key (or equivalent) where the referenced table cannot > have a unique constraint. Well, do you need a full foreign key or just the insert-time check on the referencing table? Does the referenced table get updates or deletes that you want t

Re: [GENERAL] PL/PGSQL - character varying as function argument

On Fri, 26 Sep 2008, Chris Baechle wrote: > When I try to run it with: > select user_checkCredentials("asdf"); Actually, I think the function probably isn't at fault here, string literals should be surrounded with ' not ". -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] subquery in FROM must have an alias

On Sun, 28 Sep 2008, Ashutosh Chauhan wrote: > Hi all, > > This has been asked before and answered as well. > http://archives.postgresql.org/pgsql-sql/2007-12/msg2.php but I > still cant figure out why postgres throws this error message even when > I have provided the aliases. My query: > > se

Re: [GENERAL] Fwd: Set-valued function in wrong context

On Thu, 9 Oct 2008, Raymond O'Donnell wrote: > gfc_bookings=# select * from make_time_series('11:00', '14:00', 30); > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "make_time_series" line 10 at for over > select rows > > Now, I know what the e

Re: [GENERAL] Question about NOT NULL and default values.

On Fri, 17 Oct 2008, Tim Uckun wrote: > Is there a way to change this behavior so that an attempt to set the > column to NULL will result in the default value being put in the > field? I don't think so specifically with default, but you could use a before trigger instead that would put in a value

Re: [GENERAL] Question about NOT NULL and default values.

On Thu, 16 Oct 2008, Scott Marlowe wrote: > On Thu, Oct 16, 2008 at 10:01 PM, Stephan Szabo > <[EMAIL PROTECTED]> wrote: > > On Fri, 17 Oct 2008, Tim Uckun wrote: > > > >> Is there a way to change this behavior so that an attempt to set the > >> colum

Re: [GENERAL] empty string causes planner to avoid index. Makes me sad.

On Fri, 27 Nov 2009, Jeff Amiel wrote: > --- On Fri, 11/27/09, Tom Lane wrote: > > > You didn't show us any evidence of that, either.? Both > > of your test > > cases are using the index. > > Ok...third try. The cost when passing in an empty string is > SIGNIFICANTLY higher than when not. Would

Re: [GENERAL] Problem defining deferred check constraints

On Sun, 25 Jan 2009, Thomas Kellerer wrote: > Hi, > > I'm playing around with deferred constraints and according to the manual, it > should be possible to declare a check constraint as deferred. > > At least that's how I read the definition of /column_constraint/ at > http://www.postgresql.org/do

Re: [GENERAL] Returning 0 rows from a PL/PGSQL

On Sun, 20 Feb 2005, Vitaly Belman wrote: > I have the following plpgsql function: > > CREATE OR REPLACE FUNCTION public."temp"(int4) > RETURNS public.books AS > $BODY$DECLARE > old_book books%rowtype; > BEGIN > select * into old_book from books > where book_id = var_book_id; >

Re: [GENERAL] PQsetdb

On Wed, 23 Feb 2005, Mohsen Pahlevanzadeh wrote: > My program is using both MySQL & PostgreSQL.I don't have problem with > Mysql.But i have problem with pgsql. That could be because you don't appear to be linking to the PostgreSQL libraries (as Richard was hinting at). > > Mohsen Pahlevanzadeh

Re: [GENERAL] PQsetdb

[EMAIL PROTECTED] On Wed, 23 Feb 2005, Mohsen Pahlevanzadeh wrote: > If you see my orginal email,I mentioned to link to inc & lib for > pgsql.Even when i use PQsetdb,I didn't recieve error message for using > PQsetdb. The fragment of execution of make you gave had no mention of them in the comm

Re: [GENERAL] PQsetdb

On Wed, 23 Feb 2005, Mohsen Pahlevanzadeh wrote: > pgsql_LIBS=-L/usr/lib You need to specify to link to an actual library here (probably -lpq at least). Just giving a library directory isn't going to be good enough. ---(end of broadcast)--- TIP 4

Re: [GENERAL] Report of some problem under PL/PGSQL 7.4.7 & 8.0.1

On Thu, 24 Feb 2005, Froggy / Froggy Corp. wrote: > tetris=# select test_array(); > NOTICE: 17 > NOTICE: 33 > ERROR: invalid array subscripts > CONTEXT: PL/pgSQL function "test_array" line 16 at assignment > > > To correct this error message, i need to make 2 init,

Re: [GENERAL] Referencing created tables fails with message that

On Sun, 27 Feb 2005, Tommy Svensson wrote: > I have just installed Postgresql and tried it for the first time. > > One very serious problem I ran into was when actually trying to use > created tables. > Creating a simple table without any foreign keys works OK, but after > creating the > table it

Re: [GENERAL] Question insert data

On Sat, 19 Mar 2005 [EMAIL PROTECTED] wrote: > Hi There, > > I've a problem with inserting data and I can't figure out what the problem > is: > > > what did I do: > > > 1. I've a master table containing about 4 records. A count(*) provides > me the exact number. > > 2. I've create a table base

Re: [GENERAL] TIME TO VOTE - comp.databases.pgsql ballot

On Sat, 19 Mar 2005, Vern wrote: > The 1st CFV will also appear on the following mailing list: > > pgsql.general >Submission address: pgsql-general@postgresql.org > > NOTE- It never appeared in pgsql.general as it should have. My first guess would be that the official one is

Re: Betr: Re: [GENERAL] Question insert data

On Sun, 20 Mar 2005 [EMAIL PROTECTED] wrote: > That's what I tought the problem was, but I created a table afterwards > without > inheritence. Could it have something to do with the max size of the schema > or oid's? I can't think of a reason it would, so can you send a self-contained full exam

Re: [GENERAL] inherited table and rules

On Tue, 22 Mar 2005, Scott Frankel wrote: > > This is weird. I have two tables: one inherits from the other. And I > have a > rule that populates the inherited table with changes from the first. > When I > update a row in the first table, I get an ever-larger number of rows > added to > both i

Re: [GENERAL] inherited table and rules

On Tue, 22 Mar 2005, Scott Frankel wrote: > Syntax troubles. > > What is the proper syntax for using FROM ONLY table_name in an UPDATE > statement? According to the docs, In a FROM clause, I should be able to > use the ONLY keyword preceding the table name. This throws an error: > > UPDATE

Re: [GENERAL] Debugging deadlocks

On Sun, 27 Mar 2005, Qingqing Zhou wrote: > > "Michael Fuhr" <[EMAIL PROTECTED]> writes > > To make sure the referenced key can't change until the transaction > > completes and the referencing row becomes visible to other transactions > > (or is rolled back) -- otherwise other transactions could c

Re: [GENERAL] Baffling sequential scan plan when index scan would

On Wed, 20 Apr 2005, Jeffrey W. Baker wrote: > I always thought I would not be the kind of person who writes to this > list asking why the planner is using a sequential scan. I always looked > upon such people as newcomers who would eventually learn the mysterious > wonders of the Pg query execu

Re: [GENERAL] PRIMARY KEY on a *group* of columns imply that each

On Wed, 27 Apr 2005, Stephane Bortzmeyer wrote: > On Tue, Apr 26, 2005 at 03:48:44PM -0500, > Scott Marlowe <[EMAIL PROTECTED]> wrote > a message of 26 lines which said: > > > Here's a quote from the SQL1992 spec that's VERY clear: > > Yes, PostgreSQL is right and implement the standard. Now, w

Re: [GENERAL] inherit with foreign key reference

On Fri, 6 May 2005, Aaron Steele wrote: > dear readers, > > i've created a simple Fooey table that inherits from Foo: > !--! > CREATE TABLE Foo( > fooid serial UNIQUE, > footype text); > CREATE TABLE Fooey( >

Re: [GENERAL] Huge problem with upper/lower and foreign chars

On Fri, 13 May 2005, [ISO-8859-1] Victor Spång Arthursson wrote: > Ciao again! > > I just ran into a huge problem with the functions upper and lower… > > It turns out, that when converting the case of foreign characters, > like åäö, they stay as they were from the beginning… To help people try to

Re: [GENERAL] CREATE TABLE problem in plpgsql trigger

On Thu, 19 May 2005, James Croft wrote: > Hi all, > > I'm trying to create a trigger function for a few tables that will store > old versions of rows prior to any update on them. Part of the function > needs to creates other tables (the table to store these snapshots in). > > When this trigger run

Re: [GENERAL] numeric precision when raising one numeric to another.

On Fri, 20 May 2005, John D. Burger wrote: > I find all these statements about the near-uselessness of > NUMERIC^NUMERIC to be pretty amazing. It's fine to say, "no one seems > to be asking for this, so we haven't implemented it yet", but, c'mon, > folks, Postgres gets used for more than "busines

Re: [GENERAL] numeric precision when raising one numeric to another.

On Fri, 20 May 2005, Scott Marlowe wrote: > On Fri, 2005-05-20 at 09:06, Stephan Szabo wrote: > > On Fri, 20 May 2005, John D. Burger wrote: > > > > > I find all these statements about the near-uselessness of > > > NUMERIC^NUMERIC to be pretty amazing. It's

Re: [GENERAL] numeric precision when raising one numeric to

On Fri, 20 May 2005, Tom Lane wrote: > Has anyone bothered to actually look into the code? > > regression=# select power(2::numeric,1000); > > power > ---

Re: [GENERAL] Trigger and arguments question

On Thu, 26 May 2005, [iso-8859-1] Hervé Inisan wrote: > It sends an argument to myfunction(), and I can retrieve this value in > TG_ARGV[0]. Fine. > What I'm trying to do is using TG_ARGV[0] to point to a field in NEW or OLD. > Is it possible? > > Something like NEW.TG_ARGV[0]... > > I'm trying to

Re: [GENERAL] Foreign keys and slow insert

On Wed, 8 Jun 2005, Dan Black wrote: > I read in documentation that primary key doesn't require additional indexes > but I could find nothing about foreign keys. > Do I need to create additional indexes when I create foreign keys? > Example: > create table master > { > master_id INT4, > master_nam

Re: [GENERAL] plpgsql - TIMESTAMP variables in EXTRACT

On Wed, 15 Jun 2005, Matthew Phillips wrote: > Hi all, > > I have the following in a plpgsql proc on 7.3.4: > > > DECLARE > ... > curTime TIMESTAMP; > ppsCnt INT; > > BEGIN > ... > > -- this works > SELECT INTO curTime localtimestamp; > > -- get unix seconds from current time (doesn't work) > SE

Re: [GENERAL] Problem on function returning setof custom type

On Wed, 22 Jun 2005, Pablo Baena wrote: > FOR rec IN SELECT test.id , test.blow, test1.bla2 > FROM test > LEFT JOIN test1 ON test.id = test1.id WHERE > test1.bla2=\'$1\' LOOP This is going to compare to the exact string '$1' not to the value of th

Re: [GENERAL] Advice on merging two primary keys...

On Wed, 29 Jun 2005, Richard Huxton wrote: > Eric D. Nielsen wrote: > > I've come into a situation where I will often need to merge two primary > > keys, with numerous foreign keys hanging off of them. For instance: > > > While any update of the either primary key will cascade to all relevant

Re: [GENERAL] Advice on merging two primary keys...

On Wed, 29 Jun 2005, Eric D Nielsen wrote: > > Stephan Szabo wrote: > > On Wed, 29 Jun 2005, Richard Huxton wrote: > > > Eric D. Nielsen wrote: > > > > I've come into a situation where I will often need to merge two primary > > > > keys, wi

Re: [GENERAL] null constraints and defaults

On Wed, 29 Jun 2005, elein wrote: > It seems that the not null constraint is applied > before the default constraint if a column is > defined with both default and not null. > > I understand that default should make the > the NOT NULL constraint unnecessary. But still > > Is this the intended

Re: [GENERAL] current_user inside SECURITY DEFINER function?

On Tue, 5 Jul 2005, Richard Hayward wrote: > Is there any way to get the name of the current user inside a PL/pgSQL > function that is defined with security definer? IIRC, SESSION_USER should give the original user. ---(end of broadcast)--- TIP 9:

Re: [GENERAL] Why UPDATE gl SET gl.glnum = gl.glnum; cause error

On Fri, 8 Jul 2005, David Gagnon wrote: > Hi all, > > I was juste wondering why the following code don't work: > UPDATE gl SET gl.glnum = gl.glnum > ERROR: column "gl" of relation "gl" does not exist > > While the following works: > UPDATE gl SET glnum = glnum; > > Query returned successfully: 1

Re: [GENERAL] PostgreSQL select

On Sat, 16 Jul 2005, [ISO-8859-2] Ji?? N?mec wrote: > There's a problem with PostgreSQL 8.0.3 SELECT. All tables exist, all > columns exist, I have no idea where's the problem :/ PostgreSQL > reports this error: ERROR: relation "fieldx" does not exist... > > SELECT fieldx.field_id, COUNT(optionx.

Re: [GENERAL] Changes to not deferred FK in 8.0.3 to 7.4?

On Mon, 18 Jul 2005, Tom Lane wrote: > Janning Vygen <[EMAIL PROTECTED]> writes: > > I have lots of tables with mutli-column PK and multi-column FK. All FK are > > cascading, so updating a PK should trigger through the whole database. > > > This worked earlier in 7.4: > > > UPDATE tipprunden SET t

Re: [GENERAL] Changes to not deferred FK in 8.0.3 to 7.4?

On Mon, 18 Jul 2005, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Mon, 18 Jul 2005, Tom Lane wrote: > >> AFAICS, if it worked for you in 7.4 it was only by pure chance. There > >> was not then, and is not now, any logic that would prev

Re: [GENERAL] Changes to not deferred FK in 8.0.3 to 7.4?

On Mon, 18 Jul 2005, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Mon, 18 Jul 2005, Tom Lane wrote: > >> I don't see why. > > > Except that before I think the order would have looked like (for 1 row) > > Originating Action > &g

Re: [GENERAL] Wishlist?

On Thu, 21 Jul 2005, Martijn van Oosterhout wrote: > On Thu, Jul 21, 2005 at 07:10:03PM +1000, Ezequiel Tolnay wrote: > > * Create a ROWSET variable type in plpgsql that would function like a > > temporary but in-memory table. Support for it would have to be added for > > plpgsql's SELECT, UPDATE

Re: [GENERAL] constraint problem

On Mon, 25 Jul 2005, [iso-8859-1] Martín Marqués wrote: > I have a table with a login, password and confirmed columns (besides others), > and I'm having so trouble getting this contraint to work. > > The account is created with login and password NULL and confirmed set to > false. Once the user g

Re: [GENERAL] Problem with text_pattern_ops

On Tue, 26 Jul 2005, Joseph Shraibman wrote: > I have this index: > > "directory_lower_username_seg_key" unique, btree (lower(username) > text_pattern_ops, seg) > > ... but my query refuses to use that index. text_pattern_ops is an opclass for doing LIKE queries using the index, I don't believe i

Re: [GENERAL] Problem with text_pattern_ops

On Tue, 26 Jul 2005, Joseph Shraibman wrote: > > > Stephan Szabo wrote: > > On Tue, 26 Jul 2005, Joseph Shraibman wrote: > > > > > >>I have this index: > >> > >>"directory_lower_username_seg_key" unique, btree (lower(username) >

Re: [GENERAL] Problem with text_pattern_ops

On Tue, 26 Jul 2005, Joseph Shraibman wrote: > Stephan Szabo wrote: > > > It is for the operators ~<~, ~<=~, ~=~, ~>=~, ~>~ (for like optimization). > > The docs seem to say that it does a character by character comparison > > rather than one using the col

Re: [GENERAL] DELETE with JOIN syntax

On Wed, 27 Jul 2005, Brian Wong wrote: > I am currently migrating from MySQL to PostgreSQL and I have found > that some queries do not work. For instance, > > DELETE t1 FROM t1 LEFT JOIN t2 USING (column_id) WHERE t2.column_id IS NULL; > > works in MySQL. This works as expected even though the MyS

Re: [GENERAL] About using plpgsql funciton variable as the table

On Thu, 11 Aug 2005, Ying Lu wrote: > Greetings, > > I met a question about how to use *function variable *as the *table > name* to select count(*) into an integer variable for the table. > > > CREATE OR REPLACE FUNCTION update_code_map(VARCHAR, VARCHAR) RETURNS > VARCHAR AS $$ > DECLARE > *ta

Re: [GENERAL] regarding isolation between threads

On Thu, 11 Aug 2005, Surabhi Ahuja wrote: > the stored procedure (just the pseudo code) > table x has a primary key k > insert(integer) > { > select from table if k = $1 > if not found > insert into x ($1); > else >insert into some_other_table($1); > end if >

Re: [GENERAL] Cascades Failing

On Tue, 16 Aug 2005, Jake Stride wrote: > I seem to be having some problems with cascading updates, I seem to > remember that this worked in the database in 7.4 but seems to fail in 8, > can anyone give me any pointer please as I seem to be able to find > anything about this online. I think this

Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy from a

On Fri, 19 Aug 2005, Bernard wrote: > My suggestions for improving the COPY command so it can be used by > non-superuser users would be as follows: If you want to do this without switching to a different UNIX user, can't you already write a small SECURITY DEFINER function as a superuser that doe

Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy

On Fri, 19 Aug 2005, Bernard wrote: > But we can take this one step further so that we don't even need to > trust ourselves: > > The logical next step is that for a non-postgresql-superuser user, > COPY FROM files have to be world-readable and COPY TO files and > directories have to be world-writa

Re: [GENERAL] Suggestion for Date/Time Functions Section

On Fri, 19 Aug 2005, CSN wrote: > Hi, > > I suggestion for the date/time functions in the docs: > http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html > > I was trying to figure out how to do: > update table set next=now() + interval 'table.period > seconds'; > > I tried subquer

Re: [GENERAL] extract (dow/week from date)

On Sat, 20 Aug 2005, Bruce Momjian wrote: > Uh, you are ordering by 'date', not column 3, try ORDER BY 3. That's not really the issue. The issue is that our definition of date of week and week of year are somewhat inconsistent with each other. We appear to be doing week of year per ISO-8601, bu

Re: [GENERAL] extract (dow/week from date)

On Sat, 20 Aug 2005, Tom Lane wrote: > Clodoaldo Pinto <[EMAIL PROTECTED]> writes: > > I'm ordering by date just to show that sunday, the 0th day of the > > week, is the last day of a given week, which is not what I need. > > extract(week) follows the ISO definition of week, which is pretty > stra

Re: [GENERAL] extract (dow/week from date)

On Sun, 21 Aug 2005, Clodoaldo Pinto wrote: > 2005/8/21, Stephan Szabo <[EMAIL PROTECTED]>: > > On Sat, 20 Aug 2005, Tom Lane wrote: > > > > > Clodoaldo Pinto <[EMAIL PROTECTED]> writes: > > > > I'm ordering by date just to show that sunday,

Re: [GENERAL] extract (dow/week from date)

On Sun, 21 Aug 2005, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > I think something like: > > (CASE WHEN extract(dow from date) = 0 THEN 7 else extract(dow from date)) > > It's really not that hard: > > (extract(dow from date) + 6) %

Re: [GENERAL]

On Mon, 22 Aug 2005, Nigel Horne wrote: > On Fri, 2005-08-19 at 17:29, Tom Lane wrote: > > Adam Witney <[EMAIL PROTECTED]> writes: > > > Ah you want to return a record I suppose? > > > > > CREATE TABLE test (id int, name text); > > > INSERT INTO test VALUES(1, 'me'); > > > INSERT INTO test VALUES

Re: [GENERAL] SQL error - please help.

On Tue, 23 Aug 2005, Bernard wrote: > Dear Postgresql specialists > > I would like to seek help with a SQL query that was developed and > tested with other SQL92 compliant databases. IIRC, allowing select items that are not in the group by but are functionally dependant on the group by columns w

Re: [GENERAL] A strange problem

On Sat, 27 Aug 2005, Tang Tim Hei wrote: > Hi, > I'm new to postgresql. Anytime I type the following command to the > database to run, it give me no result record if table 'country' is > empty but can get result if 'country' is not empty. Is this so > strange? Not really. You're doing a

Re: 回覆： Re: [GENERAL] A strange problem

On Sun, 28 Aug 2005, Tang Tim Hei wrote: > > > > > ?H: Stephan Szabo <[EMAIL PROTECTED]> > > : 2005/08/27 ?P ?U?? 11:25:49 HKT > > ??: Tang Tim Hei <[EMAIL PROTECTED]> > > : pgsql-general@postgresql.org > > ?D??: Re: [GEN

Re: ： Re: [GENERAL] A strange problem

On Sun, 28 Aug 2005, Tang Tim Hei wrote: > > Well, you have to write your queries to do what you want depending on such > > things. For example, the above doesn't constrain the join from currency > > and country and so you get multiple copies of the USD currency info for > > each country. If you

Re: [GENERAL] Select gives the wrong results

On Mon, 29 Aug 2005, Crystle Numan wrote: > I am fairly knowledgeable about PostgreSQL but this behaviour is > stumping me. Any help would be wonderful. If you think it is a bug, let > me now and I'll file one. > > (select values in DB (date stamps) between Jan 1, 2000 and Jan 1, 2005, > no resul

Re: [GENERAL] Question about a query plan

On Tue, 20 Sep 2005, Bill Moseley wrote: > ws2=> select count(*) from person_role; > count > --- >123 > (1 row) > > ws2=> select count(*) from person; > count > --- > 11033 > (1 row) > > ws2=> EXPLAIN ANALYZE select id, first_name, last_name from person, > person_role where id = 94

  1   2   3   4   5   6   >