[SQL] dump and schema
I have a problem with date default and schema's and don't know wether I am doing something wrong or not. All my tables (more than 100) have a field datumi of type date default today, as this example: CREATE TABLE tabel ( datumi date DEFAULT 'today', ); taking a dump or a schema of this table via pg_dump and restoring or using it went fine Now I am using postgres 7.4 and making a schema (and also the table in a dump gave this result) CREATE TABLE tabel( datumi date DEFAULT '2003-12-10'::date, ); which is not expected for a schema. Even worse by restoring a dump, all my new records have this fixed date in the field datumi. Something has changed? I do it in the wrong way? Is there a solution ? many thanks ---(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] dump and schema
van Elsacker Frans <[EMAIL PROTECTED]> writes: > All my tables (more than 100) have a field datumi of type date default > today, as this example: > CREATE TABLE tabel ( > > datumi date DEFAULT 'today', > > ); This does not work anymore in PG 7.4. You need something like datumi date DEFAULT CURRENT_DATE, regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Interest IN problem on 7.4
Hi, thanks a lot. All is ok now. regards, ivan. Stephan Szabo wrote: > On Sat, 13 Dec 2003, pginfo wrote: > > > Hi, > > > > I am using pg 7.4. > > > > Pls, see this test: > > > > tt07=# update a_cars set dog_or_free=0 where virtualen=0 and > > dog_or_free=4 and ids NOT IN ( select oc.ids_car_real from a_oferti_cars > > oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964) AND > > IDS = 'SOF_9989'; > > UPDATE 0 > > tt07=# update a_cars set dog_or_free=0 where virtualen=0 and > > dog_or_free=4 and ids IN ( select oc.ids_car_real from a_oferti > > _cars oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964) > > AND IDS = 'SOF_9989'; > > UPDATE 0 > > tt07=# update a_cars set dog_or_free=0 where virtualen=0 and > > dog_or_free=4 and ids NOT IN ( select oc.ids_car_real from a_oferti_cars > > oc,a_oferti o where oc.IDS_oferti=o.ids and o.date_valid>=9964 AND > > OC.IDS_CAR_REAL IS NOT NULL) AND IDS = 'SOF_9989'; > > UPDATE 1 > > > > I think IN is not working correct in this case. > > A NOT IN (subselect) when the subselect contains a NULL cannot ever return > true by specification. > > -- > A NOT IN (subselect) -> NOT (A IN (subselect)) > NOT (A IN (subselect)) -> NOT (A = ANY (subselect)) > > The result of A = ANY (subselect) is derived by the application of the > implied comparison predicate, R = RT for every row RT in the subselect. > > If the implied comparison predicate is true for at least one row RT then A > = ANY (subselect) is true. If the subselect is empty or the implied > predicate is false for every row RT then A = ANY (subselect) is false. > Otherwise it is unknown. > > For the one element row RT, A = RT where RT is a NULL returns unknown. > Therefore, we know that it's not an empty subselect (it returns at least > one row containing NULL -- that's our precondition), and that it does not > return false for every row, so A = ANY (subselect) is either true or > unknown depending on whether there's another row which does match, so > NOT(A = ANY(subselect)) is either false or unknown. ---(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
[SQL] NOT-IN CLAUSE
Hi,
My Following select st. is running from last one day. I think the problem is
NOT IN clause.
Please suggest the alternative select clause.
SELECT resreq.proj, resreq.ver, substr(resreq.act, -5, 5) act, resreq.rn,
null,
null, resreq.rl, resreq.dr, resreq.ra
FROM resreq
WHERE resreq.ver IN ('0', '99')
AND NOT (resreq.proj, resreq.ver, substr(resreq.act, -5, 5),
resreq.rn)
IN (SELECT actres.proj, actres.ver, substr(actres.act, -5, 5) act,
actres.rn
FROM actres, resreq
WHERE actres.proj = resreq.proj
AND actres.ver = resreq.ver
AND substr(actres.act, -5, 5) = substr(resreq.act, -5, 5)
AND actres.rn = resreq.rn
AND actres.ver IN ('0', '99'))
Best Regards,
Prashant Dalal.
LaborLogix
<>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] NOT-IN CLAUSE
On Mon, Dec 15, 2003 at 12:14:17 -0500, Prashant Dalal <[EMAIL PROTECTED]> wrote: > Hi, > > My Following select st. is running from last one day. I think the problem is > NOT IN clause. > Please suggest the alternative select clause. NOT IN was especially slow up until Postgres 7.4. If using 7.4 is a problem you can rewrite your query to use NOT EXISTS. ---(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] arrays in pl/pgsql
Is it possible in pl/pgsql to declare and then assign to arrays? For example if I declare an array as follows DECLARE dn text[]; How do I assign the first element to a string (say 'a'). I have tried the following (which doesn't work) dn[1] := \'a\'; The parser doesn't like the [ that follows dn. ---(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] arrays in pl/pgsql
Michael Shapiro <[EMAIL PROTECTED]> writes:
> I have tried the following (which doesn't work)
> dn[1] := \'a\';
> The parser doesn't like the [ that follows dn.
I believe Joe Conway fixed this in 7.4. Note that you also need to
initialize the array to something, because assigning to an element
of a NULL array still yields a NULL array result. For example:
regression=# create or replace function foo() returns text as '
regression'# declare dn text[] := \'{}\';
regression'# begin
regression'# dn[1] := \'a\';
regression'# return dn[1];
regression'# end' language plpgsql;
CREATE FUNCTION
regression=# select foo();
foo
-
a
(1 row)
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
