--- Tom Lane <[EMAIL PROTECTED]> wrote:
> Ludwig Lim <[EMAIL PROTECTED]> writes:
> > 1) Is the ON clause of an OUTER JOIN always
> > evaluated first before the WHERE clause?
>
> No; the planner will do whatever it thinks is the
> most efficient way
> (as
)
WHERE a.status='test';
Is there a way to rewrite the query as a view such
that one can do:
select *
from test_view
where employee_id=3 and status='test';
Thank you very much,
ludwig lim
__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $2
only column the view is of type "character
varying". Does a union of a varchar(n) column and a
another varchar(n) column would automatically cast it
to "character varying" (w/o upper limit) even if the
upper limit of the 2 varchar columns are the same?
thanks in advance,
ludwig lim
Hi Tomasz:
--- Tomasz Myrta <[EMAIL PROTECTED]> wrote:
>
> Probably you are right, but you can cast into
> timestamp before using these functions.
> Do you really need to care amount of storage?
I was just thinking if both TIMESTAMP and TIME have
use the same amount of space (I was think TIME
--- Christoph Haller <[EMAIL PROTECTED]> wrote:
>
> I'm working on
> PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by
> GCC 2.95.2
> and found a similar behaviour.
>
> T1 (within psql):
> BEGIN; DELETE FROM ;
> DELETE n
>
> T2 (within psql):
> BEGIN; DELETE FROM ;
>
>
> The documentation
Hi:
Are there cases when a TIME data type is a better
choice over the TIMESTAMP data type?
It seems that PostgreSQL (I'm using 7.2.3)
encourage its users to use TIMESTAMP over TIME data
type. I said this because of the following:
a) More functions for DATE and TIMESTAMP data types
su
--- Luke Pascoe <[EMAIL PROTECTED]> wrote:
> I have a table which defines various possible file
> delimiters (CHAR(1) NOT
> NULL), for the moment it'll only contain comma and
> tab. Inserting a comma is
> easy, but inserting a tab is proving somewhat more
> difficult.
>
> How do I do it in 'psql'
Hi shreedhar :
--- shreedhar <[EMAIL PROTECTED]> wrote:
> Can any body tell that how sub queries and joins
> differs funcationally.
> Because sub queries taking lot of time than joins.
>
The following could be the probable reasons:
a) Your are using correlated queries - Correlated
queries
--- Oliver Vecernik <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Searching Google I found a thread in July 2001
> concerning the facilities
> for debugging plpgsql functions. The actual answer
> was: it should be
> improved.
>
> What is the best way to debug a plpgsql function?
>
> Oliver
This m
--- David Durst <[EMAIL PROTECTED]> wrote:
> Can anyone tell me why postgres is creating a
> implicit index when
> I already have a PKEY specified
>
> Or am I just interpreting this all wrong?
PostgreSQL uses UNIQUE INDEX to enforce PRIMARY KEY
constraint. Therefore creating a PRIMARY KEY
--- [EMAIL PROTECTED] wrote:
> There have been a few posts recently where people
> have had problems with
> nulls. Anyone got comments on the below before I
> submit it to techdocs?
>
> TIA
>
> - Richard Huxton
>
> A Brief Guide to NULLs
> ==
>
> What is a null?
> =
--- pginfo <[EMAIL PROTECTED]> wrote:
> I have a table tableA ( ,order_num int).
>
> I will to delete some records from tableA but in asc
> or desc
> order_num-order.
>
> Is it possible to write delete from tableA where
> (some conditions) order
> by order_num ?
>
> Many thanks,
> ivan.
>
--- Tim Perdue <[EMAIL PROTECTED]> wrote:
> I have created a function in pl/pgsql to modify a
> row before it gets put
> into the database, but it seems my modification is
> being ignored, and
> the unmodified row is being inserted.
>
> I have confirmed with this RAISE EXCEPTION that my
> "NEW"
Hi:
I am just wondering. Which one is executed first:
CHECK Constraints or BEFORE INSERT OR UPDATE TRIGGER?
Thank you very much,
ludwig.
__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
-
e first and second
chunk PL/PgSQL INSERT/UPDATE trigger function code
aside from the fact that first scenario will result in
an "ABORT" state?
Are there any instances where a NOTICE and a RETURN
NULL statement is a much better than a RAISE EXCEP
--- Waheed Rahuman <[EMAIL PROTECTED]> wrote:
> Hi Greetings
> My question is How many primary key i can assign in
> a PostGresql Table
>
> Rowid| Parent1 | Parent2
> | Parent3 | Parent4 |
> Parent5 | Parent6 | Parent7
--- Jonathan Man <[EMAIL PROTECTED]>
wrote:
> Hi,
>
> There is a function on the Oracle. That is
> NVL(field, 0) to convert null into a value (e.g.
> ZERO).
>
> Can I use this function on the PostgreSQL??
>
-- The equivalent function is PostgreSQL is
SELECT COALESCE(field,0)
regards,
lu
--- Héctor Iturre <[EMAIL PROTECTED]> wrote:
> HI,
>HERE IS AN ALTERNATIVE TO USE THE SQL SERVER
> ISNULL() FUNCTION
>
>
> select case when FIELD_NAME isnull then 'EXPRESION'
> else FIELD_NAME end
> from calfiscal
> where impuesto = 1
try using
SELECT coalesce(field_name,'EXPRESSION')
--- [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_c
Hi:
I tried the following:
CREATE TABLE x(
a NUMERIC(5,0),
b VARCHAR(5)
);
CREATE TABLE y(
a INTEGER,
b VARCHAR(5)
);
INSERT INTO x(b) VALUES ('LUDZ');
INSERT INTO y(b) VALUES ('TEST');
SELECT x.b,y.b FROM x,y WHERE x.a=y.a
returns zero rows.
Hi:
Can I have more than 1 trigger of same kind on one
table? (i.e. 2 AFTER INSERT TRIGGER) on 1 table?
I'm planning to split up a large trigger function
(about 200 lines) into 2 seperate triggers. Since
PL/PGSQL functions cannot accepts OPAQUE as arguments,
I have to create 2 triggers inst
Hi:
Can I pass the the variables OLD and NEW (type
OPAQUE) to another function is expecting a composite
type as parameter?
Are opaque considered as composite type?
Thank you in advance,
ludwig.
__
Do you Yahoo!?
New DSL Internet Access from
--- Christoph Haller <[EMAIL PROTECTED]> wrote:
> It's obvious this approach is most inflexible.
> As soon as there is a new vendor, one has to
> re-write the query and add
> SUM(CASE vendor WHEN 'mr. new' THEN ... ,
>
> In an advanced example it is shown how to deal with
> cross tabs in
> gener
Hi:
Has anyone encountered this before?
SELECT CAST ( (NULL*NULL) AS NUMERIC(2,0));
returns the following error message:
Cannot cast type '"char"' to '"numeric"'
But the following sql statements returns NULL:
select NULL:
select NULL * NULL;
select cast ( NULL as NUMERIC(2,0)
Hi:
Is there a way to emulate a SELECT..FOR UPDATE to
series of LOCK/SELECT statement.
I tried the following statements using 2 psql
terminals.
T1 | T2
1)BEGIN; |
2)SELECT x | BEGIN;
FROM y |
WHERE y=1
Hi:
Are cursors in plpgsql dynamic or static?
For example :
... /* some code */
FOR rec in SELECT f1,f2
FROM table1 WHERE LOOP
/* some codes that manipulate table1 */
END LOOP;
Do the result set pointed to by the cursor remains
the same even if performed
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> A simple answer is to have T1 grab an ACCESS
> EXCLUSIVE lock on some
> table to block T2's progress. If that locks out
> third-party
> transactions that you'd rather would go through, you
> can probably use
> a lesser form of lock --- but then both T1 an
--- Terry Yapt <[EMAIL PROTECTED]> wrote:
> Hello all,
>
> I have a doubt. In the next example, I have a table
> with two columns:
> - DATE
> - MONEY
>
> And a VIEW which SUM's the money GROUPing by
> 'month/year' (I cut off the day)...
>
> Ok.. I would like to be able to SELECT * FROM VIEW..
--- Tom Lane <[EMAIL PROTECTED]> wrote:
> Achilleus Mantzios <[EMAIL PROTECTED]>
> writes:
> >> The problem is solved
> >>
> >> a) Using SERIALIZABLE XACTION ISOLATION LEVEL
> >> b) in T2 using "select for update" instead of
> select. That way T2's
> >> queries will wait untill T1's statements co
--- Joseph Syjuco <[EMAIL PROTECTED]> wrote:
>
> how can i get the current date (without the time
> part) in sql.
--> try SELECT current_date();
ludwig.
__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
-
Hi:
Suppose I have a transaction (T1) which executes a
complicated stored procedure. While T1 is executing,
trasaction #2 (T2) begins to execute.
T1 take more time to execute that T2 in such a way
that T2 finished earlier than T1. The result is that
t2 returns set of data before it can
Hi Keith:
--- Keith Gray <[EMAIL PROTECTED]> wrote:
> Help,
>
> I have just been comparing some large table
> performance
> under 7.1 using the
>
> select max(primary key)from table;
>
> We are using this for various functions including
> sequence.
>
Try using the following as alter
Hi :
1) How do I view the body of a stored procedure in
psql?
2) How do I know get the corresponding stored
procedure of a particular trigger in psql?
thanks in advance,
ludwig.
__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
htt
Hi :
Are the indices of a temporary table automatically
"dropped" together its corresponding temporary table
after a database session?
ludwig.
__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
---
Hi:
Attached to the e-mail is the body of the query and
the result of the EXPLAIN (Sorry for not placing the
query and EXPLAIN in the e-mail body . The query is
rather complicated and the EXPLAIN result is rather
long ).
The file demo.out.3 is the result of the EXPLAIN
The file demo
archives.postgresql.org/ about reset
> sequence but I obtain this information:
>SELECT setval('name_sequence', 1, false);
try SELECT setval('name_sequence',1);
Maybe setval(,,) doesn't exist in
7.0.3.
regards,
ludwig lim
_
--- Andreas Johansson <[EMAIL PROTECTED]> >
> ERROR: CreateTrigger: function fix_status() does
> not exist
Andreas :
>
> Why doesn't the trigger acknowledge that I want to
> call fix_status with a
> parameter for which table name I should use?
>
> I'm completely stuck and I someone out there
--- Andre Schubert <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> i need help to build a select query or
> plpgsql-fucntion
> for the following tables.
>> Is it possible to build a select query that selects
> d.name for each a.name where
> a.id = b.a_id and d.id = c.d_id and each b.c_id must
> exist i
--- Dawn Hollingsworth <[EMAIL PROTECTED]> wrote:
>
>
> Currently we have a table with a sequence number( id
> ) as a primary key,
> a date field which is indexed and several other
> columns. The user
> interface allows the user to sort the data by date
> and limits the result
> set to 100 rows
--- Jinn Koriech <[EMAIL PROTECTED]> wrote:
> hi all,
> but then to get the entirely new items out i use a
> sub query which takes
> for ever
>
> SELECT DISTINCT * FROM v_postcode_new WHERE postcode
> NOT IN ( SELECT
> postcode FROM v_postcode_old ) ORDER BY postcode
> ASC;
>
> does anyone know
--- Joseph Syjuco <[EMAIL PROTECTED]> wrote:
> how do i import results of my select query to a file
> thanks
>
in the psql command prompt type
\o
and then type your select query.
The result will be dumped into
ludwig.
__
Do You Yahoo!?
Yahoo!
41 matches
Mail list logo
