Re: [SQL] comment on COLUMN, broken or misunderstanding?

2008-02-17 Thread Michael Fuhr
=Holiday, 8=School > day, 9=Special'; > COMMENT > # \dd sched.days; > Schema | Name | Object | Description > +--++- > (0 rows) You're using the wrong psql command. Try "\d+ sched". -- Michael Fuhr ---

Re: [SQL] field separator problem

2007-10-03 Thread Michael Fuhr
se, but when I issue a select > command, it still uses '|' as separator. What am I > doing wront? fieldsep applies only to unaligned mode (\a or \pset format unaligned). -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Returnd Refcurser ( not fetchable

2007-08-09 Thread Michael Fuhr
ingle quotes. Also, when using non-holdable cursors you'll need to be in a transaction block. begin; select * from getfoo('M'); fetch all in ""; commit; -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.

Re: [SQL] CIdr query qestion

2007-07-10 Thread Michael Fuhr
the >>, but it only include the values that I want to > exclude! (And I don't found how to do the opposite!) You can use NOT to negate a boolean expression; see above. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Random Unique Integer

2007-06-14 Thread Michael Fuhr
lve? The solution might depend on what you're trying to do. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] spi and error messages

2007-05-30 Thread Michael Fuhr
uld in an ordinary Perl script: eval { do something }; if ($@) { handle the error } -- Michael Fuhr ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

Re: [SQL] How to use function PointN?

2007-05-07 Thread Michael Fuhr
urn NULL if there is no linestring in the geometry. You've used Envelope to get a polygon but PointN expects a linestring. Try using ExteriorRing on Envelope's polygon: SELECT PointN(ExteriorRing(Envelope(polyline)), 1) FROM highway; -- Michael Fuhr ---

Re: [SQL] Which function transform x,y to latitude/longitude?

2007-04-25 Thread Michael Fuhr
then you might have to insert a row to create your own spatial reference system. However, I did a few tests with your parameters and various datums for the lat/lon and couldn't get the exact transformed values in your example. You might get more help on the PROJ.4 a

Re: [SQL] Retrieve month from date in SQl query

2007-04-23 Thread Michael Fuhr
t's not working -- how does the result differ from your expectation? I'd guess you're getting a syntax error; see the documentation for the correct way to use extract(): http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT -- Michael F

Re: [SQL] MD5 sums of large objects

2007-04-08 Thread Michael Fuhr
r somewhere in the > contrib directories, as you often don't need the full power of > pgcrypto is md5 suffices for your hashing needs. You could make a proposal in pgsql-hackers but I think 8.3 is in feature freeze so don't expect to see it until 8.4, if it's accepted at all

Re: [SQL] MD5 sums of large objects

2007-04-08 Thread Michael Fuhr
er than MD5 see contrib/pgcrypto. -- Michael Fuhr ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

Re: [SQL] rowcount function in postgres???

2007-04-07 Thread Michael Fuhr
ve/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore you

Re: [SQL] plpgsql function return array

2007-03-31 Thread Michael Fuhr
//www.postgresql.org/docs/8.2/interactive/functions-array.html If you're having a specific problem then please post the code you're running and describe how its behavior differs from what you expect. -- Michael Fuhr ---(end of broadcast)-

Re: [SQL] SELECT INSTEAD

2007-03-28 Thread Michael Fuhr
s id unique (PRIMARY KEY or UNIQUE)? And unless ORDER BY is necessary to determine the result set (as with DISTINCT ON) then consider leaving it out of the view definition -- if the outermost query (the query that selects from the view) needs a certain order than that's the proper place

Re: [SQL] How to declare cursor if tablename is a variable?

2007-03-23 Thread Michael Fuhr
ING-DYN http://www.postgresql.org/docs/8.2/interactive/functions-string.html#FUNCTIONS-STRING-OTHER -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [ADMIN] [SQL] ERROR: invalid byte sequence for encoding "UTF8": 0x92

2007-03-20 Thread Michael Fuhr
in those encodings; that's a common character in data that originated in Windows. Also, client_encoding can be set by the client without having to restart the backend. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading throug

Re: [SQL] Substitute

2007-03-20 Thread Michael Fuhr
One way is with translate(). See "String Functions and Operators" in the documentation for more information. http://www.postgresql.org/docs/8.2/interactive/functions-string.html -- Michael Fuhr ---(end of broadcast)--- TIP 7: You can help suppo

Re: [SQL] How to declare cursor if tablename is a variable?

2007-03-19 Thread Michael Fuhr
[...] END LOOP; or DECLARE cur_objrefcursor; query_obj text := 'SELECT mrc_xy_position FROM ' || quote_ident(tablename); BEGIN OPEN cur_obj FOR EXECUTE query_obj; [...] -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Issue with copying data from a text file.

2007-03-19 Thread Michael Fuhr
copy to have no quote character? Are there any control characters that won't appear in the data? -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your

Re: [SQL] How to declare cursor if tablename is a variable?

2007-03-14 Thread Michael Fuhr
tid; > > Error if call the function. Neither to use “execute” . Who can solve > it? Thanks! Are you sure you need to use cursors? Can you not build the query strings and use EXECUTE or "FOR variable IN EXECUTE query LOOP"? -- Michael Fuhr --

Re: [SQL] There is acid without transactions ?

2007-03-11 Thread Michael Fuhr
cessful) COMMIT wrapped around it." -- Michael Fuhr ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

Re: [SQL] How compare current_setting(..) ?

2007-02-22 Thread Michael Fuhr
eturn? SELECT '<' || current_setting('log_line_prefix') || '>', length(current_setting('log_line_prefix')); -- Michael Fuhr ---(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

Re: [SQL] Odd PL/PgSQL Error -- relation "X" does not exist when using index expression

2007-02-08 Thread Michael Fuhr
command executed last. The count limit applies to each command separately, but it is not applied to hidden commands generated by rules. "When read_only is false, SPI_execute increments the command counter and computes a new snapshot before executing each command in the string." Should that do

Re: [SQL] Odd PL/PgSQL Error -- relation "X" does not exist when using index expression

2007-02-08 Thread Michael Fuhr
On Thu, Feb 08, 2007 at 11:14:33AM -0300, Alvaro Herrera wrote: > Michael Fuhr wrote: > > The error appears to happen for anything that uses SPI. A C function > > that executes the following fails with the same error: > > > > SPI_exec("CREATE TABLE foo (t te

Re: [SQL] Odd PL/PgSQL Error -- relation "X" does not exist when using index expression

2007-02-08 Thread Michael Fuhr
t;CREATE TABLE foo (t text); CREATE INDEX foo_idx ON foo (lower(t))", 0); In 8.2.3 the error location is: LOCATION: RangeVarGetRelid, namespace.c:200 -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [PHP] [SQL] Question regarding multibyte.

2007-02-04 Thread Michael Fuhr
de more information about the difficulties you're having. A minimal but complete example that illustrates the problem might be useful. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Differentiate Between Zero-Length String and NULL Column Values

2007-01-29 Thread Michael Fuhr
FROM tmpstk WHERE TRIM(ean) = ''; > SELECT * FROM tmpstk WHERE ean = NULL; > None of the above queries return any rows. Checking for equality against NULL won't work unless you have transform_null_equals set, which you shouldn't. Use IS NULL instead: SELE

Re: [SQL] Related tables to a view

2006-12-25 Thread Michael Fuhr
ew column short of parsing the view definition or rule action; doing so would have to allow for the possibility of a view column deriving its value from an arbitrarily complex expression involving multiple tables, subqueries, etc. -- Michael Fuhr ---(end of broadcast)

Re: [SQL] Changing character set when the damage is done

2006-12-24 Thread Michael Fuhr
eases. http://www.postgresql.org/docs/8.1/interactive/release-8-1.html http://www.postgresql.org/docs/8.2/interactive/release-8-2.html -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Changing character set when the damage is done

2006-12-24 Thread Michael Fuhr
r the Euro sign then you'll probably need to use win1252 instead of latin1. Does the following show a Euro sign or does it show blank? SELECT convert('\342\202\254', 'utf8', 'win1252'); -- Michael Fuhr ---(end of broadcast)---

Re: [SQL] consistent random order

2006-11-29 Thread Michael Fuhr
the same value before each query? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] max (timestamp,timestamp)

2006-11-13 Thread Michael Fuhr
t=> SELECT greatest(1, 2); greatest -- 2 (1 row) test=> SELECT greatest(2, 1); greatest -- 2 (1 row) test=> SELECT greatest(6, 3, 1, 10, 9, 5, 2, 7, 8, 4); greatest ------ 10 (1 row) -- Michael Fuhr ---(end of broadcas

Re: [SQL] not able to execute query on spatial database.

2006-10-29 Thread Michael Fuhr
hes. You could instead use "intersects(d.the_geom, c.the_geom)" but distance = 0 is often faster (if two geometries intersect then the distance between them is 0). -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Crosstab question

2006-10-22 Thread Michael Fuhr
t4, > supp2 float4); Try using the crosstab(text source_sql, text category_sql) variant. Unrelated suggestion: prices should probably be numeric instead of floating-point due to the inexactness of the latter. -- Michael Fuhr ---(end of broadcast)-

Re: [SQL] PostgreSQL 8.1.5 Documentation - Chapter 32. Extending SQL

2006-10-20 Thread Michael Fuhr
ot found Try including utils/geo_decls.h -- that should define both the structure and the macro you're missing. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Foreign key reference counting strategy?

2006-10-14 Thread Michael Fuhr
On Sat, Oct 14, 2006 at 08:20:10PM -0400, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > Unless the inserters got there first. I just tested both ways; if > > the insert acquires the lock first then the delete fails, but if the > > delete acquires the

Re: [SQL] Foreign key reference counting strategy?

2006-10-14 Thread Michael Fuhr
On Sat, Oct 14, 2006 at 07:58:06PM -0400, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > On Sat, Oct 14, 2006 at 03:52:04PM +0200, Markus Schaber wrote: > >> Create an "after delete" trigger on the referencing table that checks > >> whethe

Re: [SQL] Foreign key reference counting strategy?

2006-10-14 Thread Michael Fuhr
EPTION clause that catches foreign_key_violation. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] regexp_replace usage

2006-10-01 Thread Michael Fuhr
cdonald into McDonald and MacDonald, respectively. However, since both Macdonald and MacDonald are used, determining which is correct would be impossible. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Transactional behaviour with trigger

2006-09-17 Thread Michael Fuhr
clause? Where would that row have come from? Is there a reason you're maintaining customeraddress as a materialized view rather than as a "real" view? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Sorting items in aggregate function

2006-09-12 Thread Michael Fuhr
subquery's order: http://archives.postgresql.org/pgsql-general/2005-09/msg00047.php -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so

Re: [SQL] Trigger on Insert to Update only newly inserted fields?

2006-08-29 Thread Michael Fuhr
o slows down the update, although you might not be able to avoid that if requirements demand a potentially distinct end_date for each row. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Trigger on Insert to Update only newly inserted fields?

2006-08-28 Thread Michael Fuhr
NGUAGE plpgsql; If that's not what you mean then please elaborate. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that y

Re: [SQL] double precision vs. numeric

2006-08-28 Thread Michael Fuhr
g and 8.1 gives an error: > > Ok, thanks. > > But NULLs will go in the future too ? Only if Chris Date takes over the project ;-) -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] double precision vs. numeric

2006-08-28 Thread Michael Fuhr
ouble precision: "" DETAIL: This input will be rejected in a future release of PostgreSQL. float8 0 (1 row) 8.1.4 test=> SELECT ''::double precision; ERROR: invalid input syntax for type double precision: "" -- Michael Fuhr

Re: [SQL] Importing data from csv

2006-08-24 Thread Michael Fuhr
he format that seems to be followed by most implementations:" -- Michael Fuhr ---(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] Function to retrieve the Id column

2006-08-24 Thread Michael Fuhr
tp://www.postgresql.org/docs/8.1/interactive/xfunc-sql.html http://www.postgresql.org/docs/8.1/interactive/plpgsql.html -- Michael Fuhr ---(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] double precision vs. numeric

2006-08-24 Thread Michael Fuhr
ndard SQL and has been so for a long time. Perhaps Aarni is thinking about the money type, which the documentation does say is deprecated. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] error with mor than 1 sub-select

2006-08-22 Thread Michael Fuhr
0 > and not exists > (select 1 > from reservation R > ) > -- and not exits Does the real query have "exits" instead of "exists"? I created some test tables and fixed that typo and then both queries worked.

Re: [SQL] to get DD-MM-YYYY format of data

2006-08-22 Thread Michael Fuhr
this wt > I have to do... See "Date/Time Input," "Date/Time Output," and "Data Type Formatting Functions" in the documentation: http://www.postgresql.org/docs/8.1/interactive/datatype-datetime.html http://www.postgresql.org/docs/8.1/inter

Re: [SQL] trigger needs to check in multiple tables.

2006-08-18 Thread Michael Fuhr
n to assign the return value to a variable? >ENDIF; ENDIF should be END IF. If you make the indicated changes then the function should be created successfully. I didn't look closely at the logic, so whether it'll actually work is another matter ;-) -- Michael Fuhr --

Re: [SQL] About DBlink

2006-08-17 Thread Michael Fuhr
means it is part of the postgresql > source code distribution. If you install PostgreSQL via packages then the contrib modules might be in a package other than the base installation. If so then the package will typically have the word "contrib" in its name. -- Michael Fuhr ---

Re: [SQL] About DBlink

2006-08-17 Thread Michael Fuhr
s that it installed. I'd mention that doing joins between databases isn't a good idea but it looks like you've already had that discussion in the recent "Multiple DB join" thread. http://archives.postgresql.org/pgsql-sql/2006-08/msg00097.php -- Michael Fuhr -

Re: [SQL] Help with optional parameters

2006-08-16 Thread Michael Fuhr
ot as "neat" as a static query but it might be worth testing. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Multiple DB join

2006-08-15 Thread Michael Fuhr
sted with dblink), but I'm wondering > why the split? 20 million records isn't very big. And why multiple databases instead of multiple schemas within the same database? Or even all data in the same schema? Is there a reason for the segregation? -- Michael Fuhr -

Re: [SQL] Function Temp Table Woes

2006-08-08 Thread Michael Fuhr
. INSERT INTO SELECT ... However, this still has a problem: after the first time you call the function subsequent calls will fail with "relation with OID X does not exist." See the FAQ for the reason and how to avoid it: http://www.postgresql.org/docs/faqs.FAQ.html

Re: [SQL] return setof records

2006-07-28 Thread Michael Fuhr
You didn't mention what error you're getting but I'd guess it's "wrong record type supplied in RETURN NEXT". Try using "numitems bigint" instead of "numitems int". -- Michael Fuhr ---(end of broadcast)

Re: [SQL] Primary key constraint violation without error?

2006-07-24 Thread Michael Fuhr
ies the table each time the function is called. Should that truncate statement be there? -- Michael Fuhr ---(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] hi let me know the solution to this question

2006-07-17 Thread Michael Fuhr
and later). http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING -- Michael Fuhr ---(end of broadcast)--

Re: [SQL] Storing encrypted data?

2006-07-17 Thread Michael Fuhr
a column's type was added in 8.0; in previous versions you can use the method shown in the FAQ: http://www.postgresql.org/docs/faqs.FAQ.html#item4.3 -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] Storing encrypted data?

2006-07-16 Thread Michael Fuhr
://www.postgresql.org/docs/8.1/interactive/datatype-binary.html http://www.postgresql.org/docs/8.1/interactive/libpq-exec.html -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] above the date functionssssssss

2006-07-11 Thread Michael Fuhr
s" and "Date/Time Functions and Operators" in the "Functions and Operators" chapter of the documentation: http://www.postgresql.org/docs/8.1/interactive/functions-formatting.html http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html -- Michael Fuhr --

Re: [SQL] information_schema for all users

2006-07-09 Thread Michael Fuhr
ly works. You could avoid it by returning SETOF some type rather than a cursor, or you could query the PostgreSQL system catalogs directly instead of using information_schema. If you're returning the results of a simple query, and if you can make that query work without SECU

Re: [SQL] Celko's Puzzle Number 5

2006-07-08 Thread Michael Fuhr
o support regular expressions but generally via a non-standard syntax (as PostgreSQL does with its ~, ~*, !*, and !~* operators). -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to

Re: [SQL] permissions from system catalogs

2006-06-26 Thread Michael Fuhr
ormation-schema.html (Adjust the version number in the above links if you're using a version other than 8.1) -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an i

Re: [SQL] sessions and prepared statements

2006-06-16 Thread Michael Fuhr
-- name| stmt statement | PREPARE stmt (integer) AS SELECT * FROM foo WHERE x = $1; prepare_time| 2006-06-16 07:07:41.682999-06 parameter_types | {integer} from_sql| t -- Michael Fuhr ---(end of broadcast)

Re: [SQL] Advanced Query

2006-06-02 Thread Michael Fuhr
expression is another; casting one of the operands to numeric or one of the floating-point types is yet another. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Advanced Query

2006-06-01 Thread Michael Fuhr
(built-in in 8.1, easily created in earlier versions) then you could replace the CASE expression with a cast (inspect_pass::integer). Whether to use the more explicit CASE or the more concise cast is a matter of style. -- Michael Fuhr ---(end of broadcast)

Re: [SQL] Encryption functions

2006-05-18 Thread Michael Fuhr
ric and public-key encryption. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] query to return hourly snapshot

2006-04-04 Thread Michael Fuhr
BY hour; Or, using PostgreSQL's non-standard DISTINCT ON clause: SELECT DISTINCT ON (date_trunc('hour', tstamp)) process, date_trunc('hour', tstamp) AS hour FROM process WHERE date_trunc('day', tstamp) = '2005-10-26' ORDE

Re: [SQL] References NULL field

2006-04-02 Thread Michael Fuhr
registrars and notes. Since notes is empty the join result is empty. Try an outer join: SELECT * FROM registrars LEFT OUTER JOIN notes USING (note_id) WHERE regname = 'blah'; -- Michael Fuhr ---(end of broadcast)--- TIP 1: if p

Re: [SQL] connectby documentation

2006-03-13 Thread Michael Fuhr
parently the contrib module and the database have different notions of where the database's library directory is (assuming that pg_config and the postmaster agree). Did the module and the database come from the same source? If so then the originator's packaging could use improving. -- Mich

Re: [SQL] connectby documentation

2006-03-13 Thread Michael Fuhr
). That shouldn't be necessary unless the package installed the shared objects somewhere other than where the database was expecting. What's the output of "pg_config --libdir --version"? > > su postgres > > psql -f /usr/share/postgresql/8.1/contrib/tablefunc.sql O

Re: [SQL] connectby documentation

2006-03-13 Thread Michael Fuhr
ve installed a file named README.tablefunc. -- Michael Fuhr ---(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] connectby documentation

2006-03-13 Thread Michael Fuhr
bjected to it for various reasons. Search the list archives for discussion. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] pgsql aggregate: conditional max

2006-03-11 Thread Michael Fuhr
it returns the entire table, not the (aid, cat) pair with the max weight for a given aid. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining col

Re: [SQL] pgsql aggregate: conditional max

2006-03-11 Thread Michael Fuhr
nough columns. Here's something more standard; it'll return all rows that match a given aid's max weight: SELECT aid, cat FROM tablename AS t JOIN (SELECT aid, max(weight) AS weight FROM tablename GROUP BY aid) AS s USING (aid, weight); -- Michael Fuhr ---

Re: [SQL] Locking row

2006-03-10 Thread Michael Fuhr
= 1 and then commits, then T2 will get an empty result set instead of getting the next row with mark = 0. The queries could use LIMIT 2 instead of LIMIT 1 and update only the first row that came back, but then you'd have the same problem with a third concurrent transaction (and with LIMIT 3

Re: [SQL] [GENERAL] Syntax error in Execute statement

2006-03-06 Thread Michael Fuhr
--- 1 (1 row) I'd guess that you created the column with an uppercase quoted identifier. See the documentation regarding case folding and quoted identifiers: http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] functions in WHERE clause

2006-03-05 Thread Michael Fuhr
o end of trouble. Would a view work? If not then please provide a more concrete example that shows what you're trying to do. CREATE VIEW foo AS SELECT * FROM some_table WHERE test_for_equality_is_syntactically_ugly; -- Michael Fuhr ---(end of broadcast)

Re: [SQL] Sequential scan where Index scan expected (update)

2006-03-03 Thread Michael Fuhr
DATE xx_thing_event SET thing_color = 'foo' WHERE thing_event_id = '1'; -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] How to check date-interval constraints

2006-03-02 Thread Michael Fuhr
, even with concurrent transactions. However, I'm not sure this is the best way to approach the problem; if it's flawed then hopefully somebody will point out why and maybe suggest something else. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Why can't I use daylight savings time aware time zones with the AT TIME ZONE clause?

2006-03-02 Thread Michael Fuhr
IME ZONE 'CST6CDT' as interval > ... > ERROR: time zone "cst6cdt" not recognized This works only since 8.1. Here's an excerpt from the 8.1 Release Notes: * Allow the full use of time zone names in AT TIME ZONE, not just the short list previously available (Magnus) -

Re: [SQL] Question about Sql SELECT and optimizer

2006-03-02 Thread Michael Fuhr
b2 (4 rows) test=> SELECT a.* FROM a, b, c WHERE a.aid = 'a1'; aid - a1 a1 a1 a1 (4 rows) -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Without OIDs

2006-03-02 Thread Michael Fuhr
//www.postgresql.org/docs/faqs.FAQ.html#item4.19 -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Change date format through an environmental variable?

2006-03-01 Thread Michael Fuhr
On Thu, Mar 02, 2006 at 12:33:31AM -0700, Michael Fuhr wrote: > On Thu, Mar 02, 2006 at 01:16:47AM -0500, Tom Lane wrote: > > Michael Fuhr <[EMAIL PROTECTED]> writes: > > > The libpq documentation has a list of environment variables, although > > &

Re: [SQL] Change date format through an environmental variable?

2006-03-01 Thread Michael Fuhr
On Thu, Mar 02, 2006 at 01:16:47AM -0500, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > The libpq documentation has a list of environment variables, although > > it's not complete: > > http://www.postgresql.org/docs/8.1/interactive/libpq-envars.h

Re: [SQL] Change date format through an environmental variable?

2006-03-01 Thread Michael Fuhr
> books I looked in or the man pages. Anyone know where I can find such a > list? The libpq documentation has a list of environment variables, although it's not complete: http://www.postgresql.org/docs/8.1/interactive/libpq-envars.html -- Michael Fuhr --

Re: [SQL] Obtaining client IP address

2006-02-24 Thread Michael Fuhr
you can use inet_client_addr(). http://www.postgresql.org/docs/8.1/interactive/functions-info.html I forget if earlier versions have a way to get the client's IP address without resorting to hacks (e.g., writing a function in a privileged language like plperlu and calling netstat or lsof).

Re: [SQL] ORDER BY with LTREE

2006-02-20 Thread Michael Fuhr
SELECT INTO row2 * FROM foo WHERE tree = subpath($2.tree, 0, level); RETURN row1.caption < row2.caption; END IF; END; $$ LANGUAGE plpgsql STABLE STRICT; CREATE OPERATOR < ( PROCEDURE = foo_lt, LEFTARG = foo, RIGHTARG = foo ); SELECT * FROM foo ORDER BY foo USIN

Re: [SQL] ORDER BY with LTREE

2006-02-19 Thread Michael Fuhr
s (e.g., to determine an ancestor's label). That could be expensive for a large table but it might be worth considering. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.

2006-02-19 Thread Michael Fuhr
he 7.3 documentation for the correct syntax in that version. But as someone else mentioned, do consider upgrading, if not to 8.1.3 or 8.0.7 then at least to 7.3.14. Lots of bugs have been fixed in the three years since 7.3.2 was released, some involving data loss. -- Michael

Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.

2006-02-17 Thread Michael Fuhr
;2006-02-01' + x FROM generate_series(0, date'2006-02-28' - date'2006-02-01') AS g(x); -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] Slow update SQL

2006-02-13 Thread Michael Fuhr
[Please copy the mailing list on replies.] On Mon, Feb 13, 2006 at 06:48:06PM -0800, Ken Hill wrote: > On Mon, 2006-02-13 at 19:14 -0700, Michael Fuhr wrote: > > How many rows does the condition match? > > csalgorithm=# SELECT count(*) FROM ncccr10 WHERE > date_part(

Re: [SQL] Slow update SQL

2006-02-13 Thread Michael Fuhr
x27;2000'; Do you have an expression index on date_part('year',dxdate)? Does the table have any triggers or rules? Have you queried pg_locks to see if the update is blocked on an ungranted lock? Do other tables have foreign key references to ncccr10? If so then you might nee

Re: [SQL] no notnull values, invalid stats?

2006-02-06 Thread Michael Fuhr
- public.foo.geom SRID:-1 TYPE:GEOMETRY DIMS:2 (1 row) postgis=> INSERT INTO foo VALUES (NULL); INSERT 0 1 postgis=> ANALYZE foo; NOTICE: no notnull values, invalid stats ANALYZE postgis=> UPDATE foo SET geom = GeomFromText('POINT(0 0)'); UPDATE 1 postgis=> ANALYZE foo;

Re: [SQL] variable scooping

2006-01-29 Thread Michael Fuhr
n you're running): http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate su

Re: [SQL] Question about check constraints

2006-01-27 Thread Michael Fuhr
r and skip checks where NEW.column is the same as OLD.column. Why the concern? Are the checks expensive? Do they have side effects? What do they do? -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] filtering after join

2006-01-25 Thread Michael Fuhr
from A, B where foo(A)<2 and A.a=B.b; > > But I want to apply foo() to the tuples generated by the join > operation. How can I do that? Is this what you're looking for? select * from (select * from A, B where A.a = B.b) as s where foo(s) < 2; -- Michael Fuhr -

Re: [SQL] Characters that needs escape characters when inserting to database

2006-01-17 Thread Michael Fuhr
ou shouldn't need to worry about escaping strings; just use your API's quote/escape (or whatever) function or its placeholder mechanism (if it has one). If you're using an interface that doesn't have any of these capabilities, what is it? Some people might want to avoid it ;-)

  1   2   3   4   >