Re: [SQL] Select in From clause
Consider this:
CREATE TABLE "public"."test" (
"id" INTEGER NOT NULL,
"tbl" TEXT
) WITHOUT OIDS;
INSERT INTO "public"."test" ("id", "tbl") VALUES (1, 'status');
INSERT INTO "public"."test" ("id", "tbl") VALUES (2, 'yearplan');
Following two statements will return one record.
select tbl from test where id = 1
select * from (select tbl from test where id = 1) a
tbl
status
Following statement will return all records from table 'test' where the 'tbl'
field contains a 'y'.
select * from (select tbl from test) a where a.tbl like '%y%'
tbl
yearplan
So it does work. Just change you statement to something like:
SELECT * FROM (SELECT name, condition FROM bar WHERE conditions) AS b WHERE
b.condition = xxx;
or
SELECT * FROM (SELECT name FROM bar WHERE conditions) AS b WHERE b.name = xxx;
>>> "Ray Madigan" <[EMAIL PROTECTED]> 2007-11-09 18:21 >>>
I have never seen this done before, but it seems like it is supposed to work
from reading the manual.
I want to be able to get a table name from another table and use it in the
from clause of a select.
Something like
SELECT * FROM (SELECT name FROM bar WHERE conditions) AS b WHERE b.condition
= xxx;
which translates to something like
SELECT * FROM Dealer AS b WHERE b.zipcode = 12345;
The translated version works but the SELECT in FROM version reports that
b.condition does not exist.
---(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] Temporal databases
[EMAIL PROTECTED] wrote: > Hi Philippe, > Have a look at the post I made to the pgsql-sql list I made on 6th > November 2007 (day before you posted this). It contains alot of stuff > for temporal tables within Postgres. > However temporal tables (when done properly) are a very complex > subject at the best of times. It depends how much you want to > integrate them. A good place to start is Joe Celko's book "SQL For > Smarties" and Richard Snodgrass' book "Developing Time-Oriented > Database Applications in SQL" - this book is no longer in print, but > is downloadable (for FREE!) from: > http://www.cs.arizona.edu/~rts/tdbbook.pdf > Richard Snodgrass is one of the leading experts in the field. I warn > you - the book is heavy going - but so worth it!! > > Keith Hi Keith, I saw your post a few minutes after I posted mine, but I wasn't aware of the free book of Richard Snodgrass. Thanks for the link! Have you ever heard about some sort of database extension, or plugin, that allows using databases "temporally" without have to explicitely add code in into the schema? Is there an SQL standard already defined for querying databases temporally? I think I read about a "Time Connector" or something similar for Oracle, a few years ago... Regards, Philippe Lang ---(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] show value of backslashes in string array argument
Hi there,
I have a problem using backslash character as part of a string array item.
I use "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.4.2 (mingw-special)" version, with standard_conforming_strings = 'on'.
I found that, is that in spite of using standard_conforming_strings = 'on',
the string array items are shown in C escape sequences conventions.
Use the following scenario:
- create a table with
CREATE TABLE test
(
"colVarchar" character varying,
"colVarcharArray" character varying[]
)
- insert a row with a string composed by just one character backslash,
and an array with just one item, with the same value of one backslash, with:
INSERT INTO test VALUES ( '\', ARRAY[ '\' ] );
- show the values with:
SELECT * FROM test
And the result is:
colVarchar | colVarcharArray
+-
\ | {"\\"}
The question is why the two strings are shown different in spite they are
the same, and standard_conforming_strings = 'on' ?
Sabin
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] show value of backslashes in string array argument
If you do SELECT "colVarchar","colVarcharArray"[1] FROM test; you will see that you get identical values. -- Achilleas Mantzios ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] show value of backslashes in string array argument
Στις Δευτέρα 12 Νοέμβριος 2007 11:51, ο/η Sabin Coanda έγραψε:
> I fond another collateral problem, because there are the different
> convention to describe a varchar array item which contains backslashes,
> when standard_conforming_strings = 'on'
>
> For instance, to get a string composed by just one character backslash I
> can use any of the two forms:
>
> SELECT ARRAY[ '\' ]::varchar[];
>
> or
>
> SELECT '{\\}'::varchar[];
>
> On the other hand, standard_conforming_strings = 'on' let me use varchar
> items with '\' format. So the first format seems to be aware of
> standard_conforming_strings = 'on', but the second is not.
>
> My problem is that the java driver build arrays using the second format,
> but the driver seems to be aware of standard_conforming_strings = 'on'.
> This make inconsistence using the statement parameters, because to get the
> same thing in the database I have to initialize a varchar parameter with a
> string of one backslashes, but a varchar array item has to be initialized
> with a string of two backslashes.
>
> Sabin
I would recommend:
a) Move to Unix
b) Subscribe to [EMAIL PROTECTED]
>
>
>
> ---(end of broadcast)---
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
--
Achilleas Mantzios
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] plpgsql language not aware of standard_conforming_strings ?
Hi there, Having standard_conforming_strings = 'on', I build the following scenario. I request SELECT replace( 'a\b', '\', '\\' ), which get me the result: replace - a\\b I'd like to build a function that give me the same result, as: CREATE OR REPLACE FUNCTION "test"(s varchar) RETURNS varchar AS $BODY$ BEGIN RETURN replace( s, '\', '\\' ); END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; But I rises the error: ERROR: unterminated string SQL state: 42804 Context: compile of PL/pgSQL function "test" near line 3 Ok, I suppose the function is not aware of standard_conforming_strings = 'on', so I have to change \ with \\. I make the following function: CREATE OR REPLACE FUNCTION "test"(s varchar) RETURNS varchar AS $BODY$ BEGIN RETURN replace( s, '\\', '' ); END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; The function is created without errors. But this is useless because SELECT test( 'a\b' ); returns a\b, and SELECT test( 'a\\b' ); returns ab. How can I get my desired function that means when I call test( 'a\b' ) it will return 'a\\b' ? TIA, Sabin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] show value of backslashes in string array argument
I fond another collateral problem, because there are the different
convention to describe a varchar array item which contains backslashes, when
standard_conforming_strings = 'on'
For instance, to get a string composed by just one character backslash I can
use any of the two forms:
SELECT ARRAY[ '\' ]::varchar[];
or
SELECT '{\\}'::varchar[];
On the other hand, standard_conforming_strings = 'on' let me use varchar
items with '\' format. So the first format seems to be aware of
standard_conforming_strings = 'on', but the second is not.
My problem is that the java driver build arrays using the second format, but
the driver seems to be aware of standard_conforming_strings = 'on'. This
make inconsistence using the statement parameters, because to get the same
thing in the database I have to initialize a varchar parameter with a string
of one backslashes, but a varchar array item has to be initialized with a
string of two backslashes.
Sabin
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] plpgsql language not aware of standard_conforming_strings ?
"Sabin Coanda" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] ... > > How can I get my desired function that means when I call test( 'a\b' ) it > will return 'a\\b' ? > The problem seems to be the constant evaluation in plpgsql functions which is not aware of standard_conforming_strings. An answer may be to build my own replace function, that doesn't use constant evaluation inside. For instance: CREATE OR REPLACE FUNCTION myreplace(sText varchar, sSrc varchar, sDst varchar) RETURNS varchar AS $BODY$ BEGIN RETURN replace( sText, sSrc, sDst ); END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; Using this function will give the expected result, when standard_conforming_strings = 'on', so SELECT myreplace( 'a\b', '\', '\\' ); will give the result 'a\\b' as expected. In fact this is an workaround :((. It would be nice to make the language to works like that :). Regards, Sabin ---(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] show value of backslashes in string array argument
"Sabin Coanda" <[EMAIL PROTECTED]> writes: > I found that, is that in spite of using standard_conforming_strings = 'on', > the string array items are shown in C escape sequences conventions. That's how it's supposed to be. See http://www.postgresql.org/docs/8.2/static/arrays.html#AEN5876 regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] plpgsql language not aware of standard_conforming_strings ?
"Sabin Coanda" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>
> "Sabin Coanda" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> ...
>>
>> How can I get my desired function that means when I call test( 'a\b' ) it
>> will return 'a\\b' ?
>>
>
...
> CREATE OR REPLACE FUNCTION myreplace(sText varchar, sSrc varchar, sDst
...
Unfortunatelly this is not very productive when sSrc or sDst has to be
constants inside the function. There is another workaround for that, to
specify '\' as chr(92). For instance:
CREATE OR REPLACE FUNCTION myformat(sText varchar) RETURNS varchar AS
$BODY$
BEGIN
RETURN replace( sText, chr(92), '\\' );
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Consequently, the statement SELECT myformat('a\b' ) will get the desired
result a\\b
Sabin
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] show value of backslashes in string array argument
"Tom Lane" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > That's how it's supposed to be. See > http://www.postgresql.org/docs/8.2/static/arrays.html#AEN5876 Hi Tom, I read it and I understood there are 2 cascaded parsers, but I didn't find an explicit reference to the behavior related to standard_conforming_strings. But after some tests, I found the following behaviors: - the 1st parser is the SQL interpreter which is aware of standard_conforming_strings (on or off) - the 2nd parser which is an array interpreter, doesn't care of standard_conforming_strings, using every time C escape conventions Please confirm me whether I understand it correctly or not. TIA, Sabin ---(end of broadcast)--- TIP 1: 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] Problem with UNION-queries
Hi all
I have a somewhat furry solution to a problem for which I think there might be
a better way to do it.
The table looks like this (simplified for the sake of this example):
drop table if exists test1;
create table test1(
id serial primary key,
key varchar,
username varchar,
value varchar
);
-- Insert test-data
insert into test1(username, key, value) values('andreak', 'A', 'value1');
insert into test1(username, key, value) values('andreak', 'A', 'value2');
insert into test1(username, key, value) values('andreak', 'A', 'value3');
insert into test1(username, key, value) values('andreak', 'B', 'value2');
insert into test1(username, key, value) values('andreak', 'B', 'value3');
insert into test1(username, key, value) values('andreak', 'B', 'value4');
insert into test1(username, key, value) values('andreak', null, 'value3');
insert into test1(username, key, value) values('andreak', null, 'value4');
insert into test1(username, key, value) values('andreak', null, 'value5');
For the sake of this example the test-case is greatly simplified, so I have
the following query to give me all rows for value='A' and value='B'
select t.username, t.key, t.value from test1 t where t.key = 'A'
UNION
select t.username, t.key, t.value from test1 t where t.key = 'B'
username | key | value
--+-+
andreak | A | value1
andreak | A | value2
andreak | A | value3
andreak | B | value2
andreak | B | value3
andreak | B | value4
(6 rows)
Again, I know there are other, better, ways to accomplish that with this
simple schema, but again it's for the sake of the example. The important
thing here is that it's 2 UNION-queries providing the result.
Now - what I'm trying to accomplish is getting the following result-set:
username | key | value
--+-+
andreak | A | value1
andreak | A | value2
andreak | A | value3
andreak | B | value2
andreak | B | value3
andreak | B | value4
andreak | | value5
(7 rows)
That is - I want all rows with username='andreak' AND (key IS NULL) where
the "value" is not in the previous result (not part of the other
UNION-queries). The hard, and important, part is that the resulting
rows' "value" must not exist in the "value"-column for any previous rows.
Here is one way I figured out how to do it:
select t.username, t.key, t.value from test1 t where t.key = 'A'
UNION
select t.username, t.key, t.value from test1 t where t.key = 'B'
UNION
select t.username, t.key, t.value from test1 t where t.value NOT IN (
select value from (
select t.username, t.key, t.value from test1 t where t.key = 'A'
UNION
select t.username, t.key, t.value from test1 t where t.key = 'B'
) tmp1
)
;
Given that my real schema is way more complex I'm looking for a solution which
doesn't involve issuing the NOT IN (original UNION-query) as it is a rather
heavy query.
Does anybody have a better approach to this problem?
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment.|
NORWAY | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
+-+
---(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] design of tables for sparse data
> --- Andreas Wrote: --- > ... > > MY QUESTIONS: Your questions have a strong "home-work" look. > > 1) How would I SELECT a report that looks like the first version of > the pupil table out of the 3 table design? > There must be a nontrivial SELECT statement that combines all 3 tables. > E.g. I want the result: > pupil_id, pupil_name, attends_to_english, ., attends_to_football, > attends_to_swimming, attends_to_knitting > (42, Frank Miller, yes, , no, yes, yes) > (43, Suzy Smith, yes, ..., yes, yes, no) > ... You should check out the JOIN clause in select statements. Simple example: Select t1.col1, t2.col1, t2.col2 from t1 inner join t2 b on (t1.col1 = t2.col1) > > 2) Could I control the order in which those attends_to-columns appear > by a numerical field output_order? > You specify the order of output columns in the select statement. If you want to do this dynamically (say each user wants to configure its own order) you are really better of programming in your front-end application. No trivial solution in a pure SQL solution. It would probably require some dynamic sql and another table which holds de column printout order. > 3) Could I restrict the classes list so that only those appear when > there are pupils actually attending them in a given time frame? > 3) a) Like "competitve knitting" was only available from 2000-2005. > Now I'd produce a list of 2007 so there shouldn't appear an empty > knitting-column. --> classes.is_availlable > 3) b) Or it is availlable but no one has chosen it in 2007. --> > attends_to.in_year > Yes, you could. Read about different JOINS and WHERE clauses. ;) Regards, Fernando. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] design of tables for sparse data
Fernando Hevia schrieb: --- Andreas Wrote: --- ... MY QUESTIONS: Your questions have a strong "home-work" look. Yes but I didn't want to bother everyone with my project's details. It's more like a CRM. Up until now I just tried to manage somehow with the sql basics and now I like to get better. One interesting thing are crosstabs because I could use them in the reporting module. I used this schoolbook scenario because it's such an easy example. ;) 1) How would I SELECT a report that looks like the first version of the pupil table out of the 3 table design? There must be a nontrivial SELECT statement that combines all 3 tables. You should check out the JOIN clause in select statements. Simple example: Select t1.col1, t2.col1, t2.col2 >from t1 inner join t2 b on (t1.col1 = t2.col1) A simple JOIN won't do the trick. That would give me something like: (42, Frank Miller, Maths) (42, Frank Miller, English) (42, Frank Miller, Sports) (43, Suzy Smith, Maths) (43, Suzy Smith, History) But I want it turned around and a bit interpreted like: Column heads = (ID, Name, Maths, English, Sports, History) (42, Frank Miller, yes, yes, yes, no ) (43, Suzy Smith, yes, no, no, yes) Regards Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
