Re: [SQL] string variable with spaces in psql
> > I need to define a variable in psql, but the variable needs to contain a > space: > > This is what I want to do; > > \set theName '\''John Nobody '\'' > SELECT add_agent( :theName, 'Test Company', 'Test Department' ); > > ... > etc > > Unfortunately psql removes the space, so that theName = 'JohnNobody' > > What do i need to do to preserve the space? > Did you try \set theName "John Nobody " Regards, Christoph ---(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
Re: [SQL] can i make this sql query more efficiant?
Tomasz, > What about this: > select > event, > sum(case when level=1 then 1 else 0 end) as ones, > sum(case when level=2 then 1 else 0 end) as twos, > sum(case when level=3 then 1 else 0 end) as threes > from baz > group by event; That version is only more efficient for small data sets.I've generally found that case statements are slower than subselects for large data sets. YMMV. BTW, while it won't be faster, Joe Conway's crosstab function in /tablefunc does this kind of transformation. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [SQL] can i make this sql query more efficiant?
Manfred, > I'd be honestly interested in the circumstances where you made that > observation. H ... one of my database involves a "crosstab" converstion where there were 13 possible values, and the converted table is heavily indexed. For that case, I found using CASE statements to be slower. For your example, how do the statistics change if you increase the number of levels to 15 and put an index on them? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL]unsubscribe
unsubscribe
Re: [SQL] timestamp with postgresql 7.3
Thanks, that made the trick.
On Fri, 4 Apr 2003, Stephan Szabo wrote:
>
> On Fri, 4 Apr 2003, Claude wrote:
>
> > I have a table a field with timestamps in seconds since epoch and I would
> > like to get a human readable date... but it seems that postgresql 7.3 does
> > not support the datetime(), timestamp(), timestamptz() functions...
>
> I'd say probably something like:
>
> CAST('epoch' as timestamp with time zone) + integerfield * INTERVAL '1 second'
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] timestamp with postgresql 7.3
Hi, I have a table a field with timestamps in seconds since epoch and I would like to get a human readable date... but it seems that postgresql 7.3 does not support the datetime(), timestamp(), timestamptz() functions... I tried the example in: http://archives.postgresql.org/pgsql-bugs/2002-07/msg00117.php and get: DB=# select val, datetime(val), "timestamp"(val), timestamptz(val) from test_table; ERROR: Function datetime(integer) does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts And tried various typecasts without any success. Any help? --- Claude ---(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] UNION and ORDER BY ... IS NULL ASC
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.95.3 I have come across some unexpected behavior while dealing with a UNION and ORDER BY. I'd like some advice. Here's a scenario where I want to order by null values: CREATE TABLE test(a int); SELECT a FROM test UNION SELECT a FROM test ORDER BY a IS NULL ASC; returns: ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns whereas: SELECT a FROM test UNION SELECT a FROM test ORDER BY a; works fine. The column name is the same in both queries, yet I get an error! Obviously, this is a gross oversimplification of what I want to do, but I couldn't get it working in this minimal case. I also tried using the column number, and that returns the same results as the name. What am I doing wrong? Thanks for any info. ><><><><><><><><>< AgentM [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] timestamp with postgresql 7.3
On 4 Apr 2003 at 22:18, Claude wrote: > > Hi, > > I have a table a field with timestamps in seconds since epoch and I > would like to get a human readable date... but it seems that > postgresql 7.3 does not support the datetime(), timestamp(), > timestamptz() functions... > > I tried the example in: > http://archives.postgresql.org/pgsql-bugs/2002-07/msg00117.php > > and get: > > DB=# select val, datetime(val), "timestamp"(val), timestamptz(val) > from test_table; ERROR: Function datetime(integer) does not exist > Unable to identify a function that satisfies the given > argument types You may need to add explicit typecasts > > And tried various typecasts without any success. > > Any help? I think you need to read the 7.3 documentation and release notes. -- Dan Langille : http://www.langille.org/ ---(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] Finding if a temp table exists in the current connection
> > Is there a way (i.e., access theinternal pg_ tables) to find out if a > particular temp table already exists (in the current connection)? > > I have written some stored procedures that check if a table exists and if a > column exists in a table. This is used so I can perform upgrades of our > system (if table doesnt exist, call create table... sql). > > I would like to do a similar thing for temp tables. > > I have noticed that if another connection creates a temp table, it will > show up in the pg_* tables so that all ocnnections can see the table. > > Is there some standard SQL way to test? > I'm using 7.3.2 and it seems there is a way. I've found out by starting a psql session using the -E option. This shows you the SQL behind \dt which shows the current temp tables. It looks like they are created in name spaces called "pg_temp_, where N is simply a connection counter. SELECT n.nspname as "Schema",c.relname as "Name" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace where n.nspname like 'pg_temp_%' AND pg_catalog.pg_table_is_visible(c.oid) ; If I do (within 1st connection) create temp table tgif (dummy int); and the select above returns Schema | Name ---+-- pg_temp_1 | tgif (1 row) And within a 2nd connection create temp table tgif (dummy int); and the select above returns Schema | Name ---+-- pg_temp_2 | tgif (1 row) Does this help? Regards, Christoph ---(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] timestamp with postgresql 7.3
On Fri, 4 Apr 2003, Claude wrote:
> I have a table a field with timestamps in seconds since epoch and I would
> like to get a human readable date... but it seems that postgresql 7.3 does
> not support the datetime(), timestamp(), timestamptz() functions...
I'd say probably something like:
CAST('epoch' as timestamp with time zone) + integerfield * INTERVAL '1 second'
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
