Re: [SQL] Null function parameters
> "TL" == Tom Lane <[EMAIL PROTECTED]> writes: TL> [EMAIL PROTECTED] (Anatoly K. Lasareff) writes: >> I'afraid no. My question is: if I pass one null argument into function >> then all other argumens, which are not null, became null inside >> function body. TL> Yes --- and not only that, but the function's result will be taken to be TL> null whether you want it to be or not. This has been gone over *many* TL> times before on this mail list, so I didn't think I needed to repeat it. TL> This will be fixed in 7.1 (is already fixed in current sources). Thank you. I'll be waiting :) -- Anatoly K. Lasareff Email: [EMAIL PROTECTED]
Re: [SQL] Create table in functions
Andreas Tille wrote: > What does this mean? The ERROR is caused by the Create Table statement > (when I removed it from my complex function it worked well). > So why doesn't this work and what copy function fails here?? Maybe you can create your table using a select into statement : something like : SELECT int4(1) as Num INTO Temp_NumTable WHERE 1=2; Should create your table without CREATE statement.
[SQL] Is there a way to tell if db has changed?
> It would seem about that the only currently viable technique for creating > a standby database in case of a primary database failure is to ship output > from pg_dump(all) to an awaiting postgres instance and reload them. > > I'm currently working on a few scripts to accomplish this which use cron > to schedule the backup which then gets shipped and reloaded. > > This is not efficient since since the desired frequency of backups will > often be greater than the frequency of data changes. However, I can't > know when exactly a user will change data, so I was looking for a better > way. > > Is there a way to determine if a particular database has changed, and > therefore needs to be backed up? > > > Thanks, > Bryan >
[SQL] Question on string value expression wildcarding
I have LIKE expressions: (a) name LIKE 'kp.dhs.%' (b) name LIKE 'kp.dhs%' where the name column contains strings prefixed with "kp.dhs.". I'm using postgresql 7.0.2. Expression (a) fails to match any names while (b) matches all strings prefixed with "kp.dhs", including (as expected) those prefixed with "kp.dhs.". So I take it that ".%" has some special meaning in wildcarding, but my (limited) SQL references don't mention this case. Is this To Be Expected SQL behavior? If so, what expression can be used to match only strings prefixed with "kp.dhs."? Thanks! -- Steve Wampler- SOLIS Project, National Solar Observatory [EMAIL PROTECTED]
[SQL] weird structure
Hi, Consider the following tables/fields: table "person": fields "p_id", "p_name". table "person_attribute": fields "a_id", "a_name". table "person_data": fields "d_person_id", "d_attribute_id", "d_value". Also consider that a person may not have data related to all possible attributes. Using this structure, how could I retrieve in one row with a single select statement all attributes from a person (showing null to attributes that were not registered to him)? Thanks for any suggestion, -- Renato Sao Paulo - SP - Brasil [EMAIL PROTECTED]
Re: [SQL] weird structure
Does it have to be in one row? Otherwise, assuming that person_data.d_person_id references person.a_id and person_data.d_attribute_id references person_attribute.a_id: select a.a_name from person p, person_data d, person_attribute a where p.p_name = 'UserYou'reLookingFor' AND p.p_id = d.d_person_id AND d.d_attribute_id = a.a_id Would return a list of attributes the person has, one per row. - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, August 24, 2000 11:24 AM Subject: [SQL] weird structure > Hi, > > Consider the following tables/fields: > table "person": fields "p_id", "p_name". > table "person_attribute": fields "a_id", "a_name". > table "person_data": fields "d_person_id", "d_attribute_id", > "d_value". > > Also consider that a person may not have data related to all possible > attributes. > > Using this structure, how could I retrieve in one row with a single > select statement all attributes from a person (showing null to > attributes that were not registered to him)? > > Thanks for any suggestion, > -- > Renato > Sao Paulo - SP - Brasil > [EMAIL PROTECTED] >
Re: [SQL] Question on string value expression wildcarding
Do you have any odd locale settings or anything and what's
the table definition for the table in question?
It seems to do what I expect under my 7.0.2 system:
create table kp (name text);
insert into kp values ('kp.dhs.a');
insert into kp values ('kp.dhs.');
insert into kp values ('kp.dhs,d');
select * from kp where name like 'kp.dhs.%';
name
--
kp.dhs.a
kp.dhs.
(2 rows)
select * from kp where name like 'kp.dhs%';
name
--
kp.dhs.a
kp.dhs.
kp.dhs,d
(3 rows)
Stephan Szabo
[EMAIL PROTECTED]
On Thu, 24 Aug 2000, Steve Wampler wrote:
>
> I have LIKE expressions:
>
> (a) name LIKE 'kp.dhs.%'
> (b) name LIKE 'kp.dhs%'
>
> where the name column contains strings prefixed with "kp.dhs.".
>
> I'm using postgresql 7.0.2.
>
> Expression (a) fails to match any names while (b) matches
> all strings prefixed with "kp.dhs", including (as expected)
> those prefixed with "kp.dhs.".
>
> So I take it that ".%" has some special meaning in wildcarding,
> but my (limited) SQL references don't mention this case.
> Is this To Be Expected SQL behavior? If so, what
> expression can be used to match only strings prefixed with
> "kp.dhs."?
[SQL] Dynamic SQL
Hi, Is it possible to execute a dynamic sql query in pl/pgsql like the oracle feature for PL/SQL: EXECUTE IMMEDIATE 'UPDATE my_table SET col1=val1 '; Thanks. _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. Share information about yourself, create your own public profile at http://profiles.msn.com.
Re: [SQL] weird structure
Yes, Ryan, the idea is to use only one row with all attributes in it. The structure I described is easy to use when you want to know the attributes of a single person, and in this case your suggestion is the way to go - I knew that. I asked the question considering a specific person_id just to simplify. My real concern is about retrieving data from more than one person in a query. If attributes were all columns on "person" table, than a "select specific_fields from person where conditions=..." would do the job perfectly. But how could I achieve the same result using that different database schema? > Does it have to be in one row? > > Otherwise, assuming that person_data.d_person_id references person.a_id and > person_data.d_attribute_id references person_attribute.a_id: > > select a.a_name from person p, person_data d, person_attribute a where > p.p_name = 'UserYou'reLookingFor' AND p.p_id = d.d_person_id AND > d.d_attribute_id = a.a_id > > Would return a list of attributes the person has, one per row. > > - Original Message - > > Consider the following tables/fields: > > table "person": fields "p_id", "p_name". > > table "person_attribute": fields "a_id", "a_name". > > table "person_data": fields "d_person_id", "d_attribute_id", > > "d_value". > > > > Also consider that a person may not have data related to all possible > > attributes. > > > > Using this structure, how could I retrieve in one row with a single > > select statement all attributes from a person (showing null to > > attributes that were not registered to him)? > > > > Thanks for any suggestion, > > -- > > Renato > > Sao Paulo - SP - Brasil > > [EMAIL PROTECTED]
[SQL] Regular expression query
I have a large group of records which were entered with trailing garbage, in the form of superfluous \n. The main problem this has caused, other than the obvious one, is that the record in question is being used as a primary key, so some duplicates have slipped through. I assumed a simple statement like: SELECT url FROM sites WHERE url ~ url || '\\s+' ...would allow me to find all the duplicate-but-not-quite records. While this concatenation works with the LIKE directive (ie LIKE url || '%'), postgresql barfs on it in a regexp with the error: ERROR: Unable to identify an operator '||' for types 'bool' and 'unknown' You will have to retype this query using an explicit cast Encapsulating the concatenation in brackets leads the query parser to stop bleating. I presume this is simply a limitation in the parser's ability to make inferences about regexps vs. LIKEs. The other aspect of this is that it seems that postgresql's regexp engine doesn't understand some expected regexps; I've tried both escaped and unescaped versions of, eg \w, \s, \n and so on a pg seems to ignore them. Am I exceeding the capabilities of the regexp parser? -- Rodger Donaldson[EMAIL PROTECTED] I just had this vision of a young boy cowering in terror, whispering: "I see dumb people" -- Steve VanDevender
Re: [SQL] weird structure
[EMAIL PROTECTED] wrote: > > Hi, > > Consider the following tables/fields: > table "person": fields "p_id", "p_name". > table "person_attribute": fields "a_id", "a_name". > table "person_data": fields "d_person_id", "d_attribute_id", > "d_value". > > Also consider that a person may not have data related to all possible > attributes. > > Using this structure, how could I retrieve in one row with a single > select statement all attributes from a person (showing null to > attributes that were not registered to him)? > > Thanks for any suggestion, > -- > Renato > Sao Paulo - SP - Brasil > [EMAIL PROTECTED] I did similar things using the following technique: in frontend I gather all attributes from person_attribute and then generate a query like this: select *, (select distinct d_value from person_data where d_person_id = person.p_id and d_attribute_id = 'here first attribute id'), (select distinct d_value from person_data where d_person_id = person.p_id and d_attribute_id = 'here second attribute id'), ... from person -- Sincerely yours, Yury
[SQL] Re: [GENERAL] Getting the result of a query using COUNT(*)
Jackson Ching wrote:
>
> Hi,
>
> I'm using JSDK 2.0 and IBM JDK 1.3 on RedHat Linux 6.2
>
> As i test my servlet, I got errors saying count(*) column not found in a
>query like this
>
> SELECT COUNT(*) FROM MyTable
>
> in my rs.getint("COUNT(*)"); in oracle it works fine, in postgresql it
>doesn't. how should i handle aggregate functions in SQL then?
>
> Thanks
>
> Jackson
What version of rostgres do you use?
SELECT COUNT(*) FROM MyTable works pretty well in postgres 6.5.1 and
later.
Perhaps problem is in java?
--
Sincerely yours,
Yury
Re: [SQL] Regular expression query
Rodger Donaldson <[EMAIL PROTECTED]> writes:
> SELECT url
> FROM sites
> WHERE url ~ url || '\\s+'
> While this concatenation works with the LIKE directive (ie LIKE url || '%'),
> postgresql barfs on it in a regexp with the error:
> ERROR: Unable to identify an operator '||' for types 'bool' and 'unknown'
> You will have to retype this query using an explicit cast
LIKE and ~ do not have the same precedence. See
http://www.postgresql.org/docs/postgres/operators.htm.
~ and || actually fall in the same category ("all other") and therefore
are grouped left-to-right; so you're getting (url ~ url) || '...'.
> The other aspect of this is that it seems that postgresql's regexp engine
> doesn't understand some expected regexps; I've tried both escaped and
> unescaped versions of, eg \w, \s, \n and so on a pg seems to ignore them.
The regexp package we currently use implements POSIX 1003.2 regexps
(see src/backend/regex/re_format.7). I believe there is an item on the
TODO list about upgrading the regexp parser to something more modern
... feel free to hop on that project if it's bugging you ...
regards, tom lane
[SQL] sorting in UNICODE table
Hi, I'm use postgresql-7.0.2. It's compiled with unicode support (./configure --enable-multibyte=UNICODE ...) I have a table which contains both latin and non-latin letters. All they are in UTF-8 encoding. When I try to sort the rows ( SELECT * FROM my_table ORDER BY sort_field ) I receive strange error: the rows that begin with ascii symbols are ordered while all other - are not! Why is this happen? Regards, Alex
