Re: [SQL] ERROR: syntax error at or near "select" at character 9
In article <[EMAIL PROTECTED]>,
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Fri, 2 Sep 2005, [ISO-8859-2] Graf László wrote:
>>
>> CREATE FUNCTION test_verif() RETURNS trigger AS $test_verif$
>> BEGIN
>> NEW.id := select nextval('test_azon_seq');
> I think you want to remove select here, you're already effectively doing a
> select of the right hand side in the assignment.
>> NEW.nev := nev;
> I think you want to remove this line entirely. What nev were you
> expecting on the right hand side? If it's the new one, well, NEW.new is
> already that.
László could also remove the entire trigger and use something like
CREATE TABLE test (
id SERIAL NOT NULL,
nev VARCHAR(25),
datum TIMESTAMP NOT NULL DEFAULT current_timestamp,
PRIMARY KEY (id)
);
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] ERROR: syntax error at or near "select" at character 9
On Tue, 6 Sep 2005, Harald Fuchs wrote:
> In article <[EMAIL PROTECTED]>,
> Stephan Szabo <[EMAIL PROTECTED]> writes:
>
> > On Fri, 2 Sep 2005, [ISO-8859-2] Graf László wrote:
>
> >>
> >> CREATE FUNCTION test_verif() RETURNS trigger AS $test_verif$
> >> BEGIN
> >> NEW.id := select nextval('test_azon_seq');
>
> > I think you want to remove select here, you're already effectively doing a
> > select of the right hand side in the assignment.
>
> >> NEW.nev := nev;
> > I think you want to remove this line entirely. What nev were you
> > expecting on the right hand side? If it's the new one, well, NEW.new is
> > already that.
>
> László could also remove the entire trigger and use something like
>
>
> CREATE TABLE test (
> id SERIAL NOT NULL,
> nev VARCHAR(25),
> datum TIMESTAMP NOT NULL DEFAULT current_timestamp,
> PRIMARY KEY (id)
> );
That's slightly different though. The trigger forces the value whether or
not a value was assigned in the insert, the defaults only apply if the
column does not have a value given to it.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Searching for results with an unknown amount of data
On Fri, Sep 02, 2005 at 20:40:24 +0100, DownLoad X <[EMAIL PROTECTED]> wrote: > Now, I want to find all objects that have at most properties 1,2,3, say (so > something with (1,2) is okay, as is (1,2,3)). I can't see a way to do this > -- can anyone help? It sounds like you are trying to find all objects that do not have any properties outside of a specific list. One way to get that list is: SELECT a_id FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.a_id AND b.property NOT IN (1, 2, 3) ) ; This doesn't take into account the semantics of nulls. If your data can have nulls in it, then you need to decide precisely what you want and adjust the query appropiately. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Help with UNION query
On Sun, Sep 04, 2005 at 20:54:00 +0200, Andreas Joseph Krogh <[EMAIL PROTECTED]> wrote: > > So, the question stands: any idea on how to rewrite the lower wuery to only > specify "ug.username='andreak'" once? Why do you want to do that? This isn't going to help with performance and may actually hurt. We may be able to suggest some other way to accomplish your real objective if you told us what it was. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] POSIX Regular Expression question
On Mon, Sep 05, 2005 at 16:19:28 +0200, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > Am Montag, 5. September 2005 15:57 schrieb Aldor: > > I want to get out a string only with characters A-Za-z. > > Any idea how to do this in Postgres with POSIX Regex? > > Presumably, > > colname ~ '^[A-Za-z]*$' > > If you want to be independent of locale issues, then you'd have to enumerate > all the letters instead of using a range specification. Or use: colname ~ '^[[:alpha:]]*$' ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Recommendation on bytea or blob for binary data like images
Thanks for the quick response. We will be using bytea from now on. :-) - Leon Tom Lane wrote: [EMAIL PROTECTED] writes: Hi, I'd like to know what the official recommendation is on which binary datatype to use for common small-binary size use. If bytea will work for you, it's definitely the thing to use. The only real drawback to bytea is that there's currently no API to read and write bytea values in a streaming fashion. If your objects are small enough that you can load and store them as units, bytea is fine. BLOBs, on the other hand, have a number of drawbacks --- hard to dump, impossible to secure, etc. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Statistics from Sequences
Is it possible to get from a sequence: The sequence owner The min value The max value The increment value The last used number ??? Regards Yahoo! Messenger com voz: PROMOÇÃO VOCÊ PODE LEVAR UMA VIAGEM NA CONVERSA. Participe!
Re: [SQL] Statistics from Sequences
On Tue, Sep 06, 2005 at 11:43:44PM -0300, Joÿffe3o Carvalho wrote: > Is it possible to get from a sequence: > >The sequence owner >The min value >The max value >The increment value >The last used number See the output from the following example: CREATE SEQUENCE fooseq; SELECT u.usename FROM pg_class AS c JOIN pg_user AS u ON u.usesysid = c.relowner WHERE c.relname = 'fooseq'; SELECT * FROM fooseq; -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Statistics from Sequences
On Tue, Sep 06, 2005 at 23:43:44 -0300, Joÿffe3o Carvalho <[EMAIL PROTECTED]> wrote: > Is it possible to get from a sequence: > >The sequence owner >The min value >The max value >The increment value >The last used number Yes. Associated with each sequence is a one row table with the name of the sequence. Note that numbers can be allocated and not used, so the last_value may not be exactly what you want. To get the owner you can combine pg_class with pg_user to get the table owner. I didn't see this information in the information schema. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] AGE function
When I execute this statement :
select AGE(TO_DATE('20041101','mmdd'),
TO_DATE('19991201','mmdd'))
at postgre 7.3.3, the result :
age
-
4 years 11 mons 1 day
at postgre 8.0.3, the result :
age
---
4 years 11 mons
My question :
1. How does postgre 7.3.3 calculate AGE function?
2. Why the result produced by postgre 7.3.3
is different from postgre 8.0.3 ?
Thanks,
Louise
__
Click here to donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] AGE function
On Tue, Sep 06, 2005 at 10:05:06PM -0700, Louise Catherine wrote:
> When I execute this statement :
> select AGE(TO_DATE('20041101','mmdd'),
> TO_DATE('19991201','mmdd'))
>
> at postgre 7.3.3, the result :
> age
> -
> 4 years 11 mons 1 day
>
> at postgre 8.0.3, the result :
> age
> ---
> 4 years 11 mons
>
> My question :
> 1. How does postgre 7.3.3 calculate AGE function?
> 2. Why the result produced by postgre 7.3.3
> is different from postgre 8.0.3 ?
I get the same answer ("4 years 11 mons") in 7.2.8, 7.3.10, 7.4.8,
8.0.3, and 8.1beta1. Have you verified that to_date() is returning
the correct dates? What are the results of the following queries
on each of your systems?
SELECT TO_DATE('19991201','mmdd'), TO_TIMESTAMP('19991201','mmdd');
SELECT TO_DATE('20041101','mmdd'), TO_TIMESTAMP('20041101','mmdd');
SHOW TimeZone;
What operating system are you using? Prior to 8.0, PostgreSQL
relied on the system's timezone files; as of 8.0 it has its own
timezone database. I don't know if that matters, but it's one
difference between 8.0 and previous versions that might be relevant
to the problem.
BTW, it's "PostgreSQL" or "Postgres," not "postgre."
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
