[SQL] PREPARED STATEMENT
Hi there, I want to know that is a posibillity to test if a statement is prepared in PL/PgSQL. I have create a function: . PREPARE PSTAT_SAVE_record(INTEGER, INTEGER, DATE, VARCHAR) AS INSERT INTO table VALUES($1, $2, $3, $4); . When I try to execute it second time I got an error: prepared statement  'PSTAT_SAVE_record' already exists. How can I avoid this error? is there a posibillity to test if a statement was prepared before? Thanks, Nosy ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] replacing mysql enum
On Sat, 11 Dec 2004 10:30:55 +0530, Kenneth Gonsalves
<[EMAIL PROTECTED]> wrote:
> hi,
>
> from mysql:
>
> field enum('a','b','c') default null,
>
> i translated this as:
>
> field varchar(2) check (field in (null,'a','b','c')),
>
> is it necessary to put the 'null' in the check condition? if not will pg
> permit you to enter a null value in the field?
No, and yes:
create table consttest (field varchar(2) check (field in ('a','b','c')));
insert into consttest values (null);
Note this does not emulate the MySQL ENUM datatype precisely,
because it's possible to reference the ENUM fields by index value
too.
(Oddly enough, putting the NULL in the CHECK constraint seems
to make the constraint worthless:
test=> create table consttest (field varchar(2) check (field in
(null, 'a','b','c')));
CREATE TABLE
test=> insert into consttest values ('xx');
INSERT 408080 1
test=> SELECT * from consttest ;
field
---
xx
(1 row)
Not sure what logic is driving this).
Ian Barwick
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] Query is not using index when it should
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Fri, 10 Dec 2004, Tomas [iso-8859-1] Skäre wrote:
>
> > I have a table that looks like this:
> >
> > Table "public.cjm_object"
> > Column | Type| Modifiers
> > ---+---+---
> > timestamp | bigint| not null
> > jobid | bigint| not null
> > objectid | bigint| not null
> > class | integer | not null
> > field | character varying | not null
>
> In 7.4.x and earlier, you need to cast the value you're comparing to into
> a bigint in order to make sure the indexes are used (in your timestamp
> case it appears to work because the value doesn't fit in a plain integer).
> 8.0 should handle this better.
Thanks, casting worked well for that query. Now, could someone please
help me to get this query faster? With the 283465 rows, it takes far
too long time, I think. This is on a 2GHz Celeron running Linux 2.6.
shared_buffers=1000, sort_mem=1024.
select c.* from cjm_object c
inner join
(select max(timestamp) as timestamp,objectid,field from cjm_object
group by objectid,field) t
using(timestamp,objectid,field)
where 1=1 and data is not null
order by objectid,field;
QUERY PLAN
---
Merge Join (cost=145511.85..150759.75 rows=1 width=54) (actual
time=17036.147..20968.811 rows=208246 loops=1)
Merge Cond: (("outer".objectid = "inner".objectid) AND ("outer"."?column7?"
= "inner"."?column4?") AND ("outer"."timestamp" = "inner"."timestamp"))
-> Sort (cost=47007.75..47611.06 rows=241324 width=54) (actual
time=5113.099..5586.094 rows=236710 loops=1)
Sort Key: c.objectid, (c.field)::text, c."timestamp"
-> Seq Scan on cjm_object c (cost=0.00..5862.65 rows=241324
width=54) (actual time=0.129..1788.125 rows=236710 loops=1)
Filter: (data IS NOT NULL)
-> Sort (cost=98504.09..99212.75 rows=283465 width=48) (actual
time=11922.081..12427.683 rows=255001 loops=1)
Sort Key: t.objectid, (t.field)::text, t."timestamp"
-> Subquery Scan t (cost=45534.39..51912.35 rows=283465 width=48)
(actual time=5484.943..9289.061 rows=255001 loops=1)
-> GroupAggregate (cost=45534.39..49077.70 rows=283465
width=25) (actual time=5484.925..8178.531 rows=255001 loops=1)
-> Sort (cost=45534.39..46243.05 rows=283465 width=25)
(actual time=5484.285..6324.067 rows=283465 loops=1)
Sort Key: objectid, field
-> Seq Scan on cjm_object (cost=0.00..5862.65
rows=283465 width=25) (actual time=0.124..852.749 rows=283465 loops=1)
Total runtime: 21161.144 ms
Quick explanation of the query:
Each row in the table is a field, which is part of an object. Ex:
timestamp objectid field data
11 nametest
11 typesomething
12 nametest2
12 typewhatever
Timestamp is when the entry was inserted in the databas. When updating
a single field for an object, a new line with the new value is added,
data set to NULL if the field is deleted. So the above content could
now be:
timestamp objectid field data
11 nametest
11 typesomething
12 nametest2
12 typewhatever
21 namenewname
21 type
Now, the query picks out the highest timestamp for each
(objectid,field) and then selects all columns for each match,
filtering out NULL data and ordering per objectid.
Is there any way to make this query faster? I've tried rewriting it,
putting the subquery as EXISTS condition, but it doesn't make it
faster. I've tried to create different indices, but they don't seem to
be used in this query.
Greetings,
Tomas
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] replacing mysql enum
On Sat, 11 Dec 2004, Ian Barwick wrote:
> (Oddly enough, putting the NULL in the CHECK constraint seems
> to make the constraint worthless:
> test=> create table consttest (field varchar(2) check (field in
> (null, 'a','b','c')));
> CREATE TABLE
> test=> insert into consttest values ('xx');
> INSERT 408080 1
> test=> SELECT * from consttest ;
> field
> ---
> xx
> (1 row)
>
> Not sure what logic is driving this).
The way NULL is handled in IN (because it's effectively an equality
comparison). Unless I miss-remember the behavior, foo in (NULL, ...) can
never return false and constraints are satisified unless the search
condition returns false for some row. I think this means you need the
more verbose (field is null or field in ('a','b','c'))
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] replacing mysql enum
On Sat, 2004-12-11 at 07:47 -0800, Stephan Szabo wrote:
> On Sat, 11 Dec 2004, Ian Barwick wrote:
>
> > (Oddly enough, putting the NULL in the CHECK constraint seems
> > to make the constraint worthless:
> > test=> create table consttest (field varchar(2) check (field in
> > (null, 'a','b','c')));
> > CREATE TABLE
> > test=> insert into consttest values ('xx');
> > INSERT 408080 1
> > test=> SELECT * from consttest ;
> > field
> > ---
> > xx
> > (1 row)
> >
> > Not sure what logic is driving this).
>
> The way NULL is handled in IN (because it's effectively an equality
> comparison). Unless I miss-remember the behavior, foo in (NULL, ...) can
> never return false and constraints are satisified unless the search
> condition returns false for some row. I think this means you need the
> more verbose (field is null or field in ('a','b','c'))
Actually, he just needs check(field in ('a', 'b', 'c')). NULL is
accepted unless explicitly denied (NOT NULL constraint or an IS NOT NULL
check).
--
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] replacing mysql enum
On Sat, 11 Dec 2004, Rod Taylor wrote:
> On Sat, 2004-12-11 at 07:47 -0800, Stephan Szabo wrote:
> > On Sat, 11 Dec 2004, Ian Barwick wrote:
> >
> > > (Oddly enough, putting the NULL in the CHECK constraint seems
> > > to make the constraint worthless:
> > > test=> create table consttest (field varchar(2) check (field in
> > > (null, 'a','b','c')));
> > > CREATE TABLE
> > > test=> insert into consttest values ('xx');
> > > INSERT 408080 1
> > > test=> SELECT * from consttest ;
> > > field
> > > ---
> > > xx
> > > (1 row)
> > >
> > > Not sure what logic is driving this).
> >
> > The way NULL is handled in IN (because it's effectively an equality
> > comparison). Unless I miss-remember the behavior, foo in (NULL, ...) can
> > never return false and constraints are satisified unless the search
> > condition returns false for some row. I think this means you need the
> > more verbose (field is null or field in ('a','b','c'))
>
> Actually, he just needs check(field in ('a', 'b', 'c')). NULL is
> accepted unless explicitly denied (NOT NULL constraint or an IS NOT NULL
> check).
Right. For the same reason, even. Really need to stop answering
messages before I wake up. :)
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] replacing mysql enum
On Sat, 11 Dec 2004 07:47:51 -0800 (PST), Stephan Szabo
<[EMAIL PROTECTED]> wrote:
> On Sat, 11 Dec 2004, Ian Barwick wrote:
>
> > (Oddly enough, putting the NULL in the CHECK constraint seems
> > to make the constraint worthless:
> > test=> create table consttest (field varchar(2) check (field in
> > (null, 'a','b','c')));
> > CREATE TABLE
> > test=> insert into consttest values ('xx');
> > INSERT 408080 1
> > test=> SELECT * from consttest ;
> > field
> > ---
> > xx
> > (1 row)
> >
> > Not sure what logic is driving this).
>
> The way NULL is handled in IN (because it's effectively an equality
> comparison). Unless I miss-remember the behavior, foo in (NULL, ...) can
> never return false and constraints are satisified unless the search
> condition returns false for some row. I think this means you need the
> more verbose (field is null or field in ('a','b','c'))
This works as expected, although for constraints the nullness
of the column is better off handled by applying NOT NULL if
necessary.
What I still don't quite understand is why IN in a CHECK context is
handled differently to say: select 1 where 'x' in (null,'a','b','c') ?
This could be a bit of a gotcha for anyone constructing a constraint
similar to the original poster's and not realising it has no effect.
Ian Barwick
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] replacing mysql enum
Ian Barwick <[EMAIL PROTECTED]> writes: > What I still don't quite understand is why IN in a CHECK context is > handled differently to say: select 1 where 'x' in (null,'a','b','c') ? > This could be a bit of a gotcha for anyone constructing a constraint > similar to the original poster's and not realising it has no effect. well WHERE foo IN (null, ...) returns null if foo isn't explicitly in the list (ie, "it may or may not equal the unknown value in the list"). And I think constraints that return null are deemed to have succeeded. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] replacing mysql enum
Kenneth, > i translated this as: > > field varchar(2) check (field in (null,'a','b','c')), While this isn't the question you asked, might I encourage you to use DOMAINs instead? I.e.: CREATE DOMAIN abc_col AS TEXT CHECK VALUE IN ( 'a', 'b', 'c' ); Then you declare the table as: table ( field abc_col, ); I find that DOMAINs give vastly enhanced managability compared to table constraints. -- Josh Berkus Aglio Database Solutions San Francisco ---(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] filtering
On Thu, Dec 09, 2004 at 10:25:25 -0500, "Kevin B." <[EMAIL PROTECTED]> wrote: > Hi, > > I have a 14 million row table with one index on two fields one is a varchar > the other is a date. The combination of the two makes the row unique. > > Data > - > name date... other fields > a 1/1/01 > a 1/2/01 > a 1/3/01 > b 1/1/01 > b 1/2/01 > d 1/1/01 > d 1/2/01 > > I have a table with just the names. each name occurs once. > UName > - > name > a > b > c > d > > I've tried a number of queries to find which name is in UName but not in > Data. However, they are all taking too long (more than 30 minutes - but > the hard drive is a slow 4200rpm IDE). > > What is the quickest query to get the result that I want? Also, should I > put another index on the Data table for "name" only? It might help if you showed us the explain analyze results from your attempts. (All of the suggestions below assuming there aren't any NULL names.) I think the straight forward way to do this is something like: SELECT name FROM uname WHERE NOT EXITS ( SELECT name FROM data WHERE uname.name = data.name ) ; If you are using 7.4 or later, you might try using NOT IN. (This does not run efficiently in earlier versions of postgres.) SELECT name FROM uname WHERE name NOT IN (SELECT name FROM data); It is also possible to use set subtraction to get the result, but I doubt this will be faster than using NOT EXISTS. (Using GROUP BY eliminate duplicates allows the use of a hash aggregate plan if there aren't too many unique names.) SELECT name FROM uname EXCEPT SELECT name FROM data GROUP BY name ; ---(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] Create Calendar
Muhyiddin A.M Hayat wrote: How to create Calendar using Function/View. For example i would like to display date 2004-12-01 to 2004-12-20. date -- 2004-12-01 2004-12-02 2004-12-03 2004-12-04 2004-12-05 .. .. 2004-12-20 -- Use in Postgres 7.4.x and earlier. -- In Postgres 8.0.0 generate_series() is a built-in function CREATE OR REPLACE FUNCTION generate_series(int, int) RETURNS setof int AS ' BEGIN FOR i IN $1..$2 LOOP RETURN NEXT i; END LOOP; RETURN; END; ' LANGUAGE plpgsql; select '2004/12/01'::date + f1 from generate_series(0, 19) as t(f1); ?column? 2004-12-01 2004-12-02 2004-12-03 2004-12-04 2004-12-05 [...] 2004-12-20 (20 rows) HTH, Joe ---(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] Cast NULL into Timestamp?
At 12:11 AM 12/11/04, Josh Berkus wrote: Wei, > insert into table temp (tempname, tempdate) > select distinct 'tempname', null from some_other_relevant_table; I don't think you're reporting the error exactly as it happened. Try cutting and pasting your actual PSQL session into your e-mail. Perhaps you are mixing up the column order? A copy/paste certainly would have been helpful in this case. There are several problems with the above query. 1) the keyword 'table' is not part of insert command. 2) If 'tempname' is a fieldname, it should not have single quotes. 3) NULL doesn't seem to work as expression in select. If (3) is the real problem here, then either solution proposed by Steve/Stephan will work. ---(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] replacing mysql enum
Ian Barwick <[EMAIL PROTECTED]> writes:
> (Oddly enough, putting the NULL in the CHECK constraint seems
> to make the constraint worthless:
> test=> create table consttest (field varchar(2) check (field in
> (null, 'a','b','c')));
> CREATE TABLE
> test=> insert into consttest values ('xx');
> INSERT 408080 1
For a non-null field value, that IN clause reduces to
null OR false OR false OR false
which reduces to null because of the way 3-state boolean logic is
defined in SQL (which makes sense if you interpret null as "unknown").
And a null result from CHECK is defined not to be a failure case by
the SQL standard.
This is really the same logic that allows the explicit-null-free CHECK
condition to accept NULLs:
null IN ('a','b','c')
becomes
null OR null OR null
becomes
null
which doesn't fail.
I believe this was intentional on the part of the SQL committee. Their
thought was that if you intend to disallow NULLs, you should write an
explicit NOT NULL constraint, separately from any CHECK you might write.
Therefore, defining CHECK such that it tend to fall through silently on
NULL inputs is a good thing.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
