Re: [SQL] Isnumeric function?

appropriately. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] INFORMATION_SCHEMA and foreign keys

guarantee this --- in particular, the user can still pick nonunique constraint names, and databases reloaded from existing dumps are likely to still have lots of $1 etc. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget

Re: [SQL] casting BOOL to somthng

place. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] Defining Field Types with view

; regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] Arbitrary precision arithmatic with pgsql

; ?column? -- 1267650600228229401496703205376. (1 row) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http

Re: [SQL] function fails where individual statements succeed

a plan for it. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] function fails where individual statements succeed

to rely on. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] expression_tree_walker: Unexpected node type 711

: ProgrammingError: ERROR: expression_tree_walker: Unexpected node type 711 Anyone see something here I don't? 7.4 will give a more helpful error message: ERROR: relation reference official_hour cannot be used in an expression regards, tom lane

Re: [SQL] Aggregate query for multiple records

union select 2 union select 3 union select 4 union select 5 union select 6 union select 7.7 union select 8) as x order by i desc) as x; sum_first_6 - 33.7 (1 row) regression=# regards, tom lane ---(end of broadcast

Re: [SQL] CREATE TYPE VARCHAR2

; that is varchar2(100) will not work. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] ERROR: Invalid regular expression: parentheses ( ) not balanced

have rules or triggers that are fired by this INSERT? If so, you need to look at what they are doing. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http

Re: [SQL] ERROR: Invalid regular expression: parentheses ( ) not balanced

query, I have to suppose that the problem is in something that's being invoked behind-the- scenes, like a trigger or rule. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index

Re: [SQL] olympics ranking query

count(*) as numranker, gold, silver, bronze from countrymedal group by gold, silver, bronze order by gold desc, silver desc, bronze desc) ss; regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list

Re: [SQL] olympics ranking query

subselect for each output row, which doesn't seem very attractive. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] SQL Challenge: Arbitrary Cross-tab

not impossible that the thing would pick an indexscan plan for even this trivial case, were you to set random_page_cost below 1.) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] SQL Challenge: Arbitrary Cross-tab

for the planner to choose different plans depending on the OFFSET. (Maybe not very likely, with such small offsets, but could happen.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ

Re: [SQL] multi column foreign key for implicitly unique columns

. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] multi column foreign key for implicitly unique columns

, it's the fabricated unique constraint that you can't pin down the requirements for. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail

Re: [SQL] multi column foreign key for implicitly unique columns

of trouble to do something that's not in the spec. regards, tom lane ---(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] Function Issue!

; 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER; I think you are missing an OPEN step too, and the FETCH syntax is wrong for plpgsql. Read the plpgsql doc section about using cursors --- it is not at all identical to what you do in plain SQL. regards, tom lane

Re: [SQL] multi column foreign key for implicitly unique columns

create table a (f1 int unique, f2 int unique); create table b (f1 int, f2 int, foreign key (f1,f2) references a(f1,f2)); How would you decide which constraint to make the FK depend on? It'd be purely arbitrary. regards, tom lane

Re: [SQL] Verifying data type

formats. You might get away with something as simple as p_valor ~ '^[0-9]+$' if you only care about unsigned integer inputs. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Getting points from polygon

, but certainly a function like point(polygon, n) wouldn't be too hard to add. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Wierd Error on update

. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Make a column case insensitive

=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= [EMAIL PROTECTED] writes: When I use UTF-8 encoding for my database. upper and lower() functions break (no longer process accented chars correctly). This is fixed for 8.0. regards, tom lane

Re: [SQL] reply to setting

for the rest of us. Fix your mail software. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] SQL syntax extentions - to put postgres ahead in the race

order by class; It looks like (at least in CVS tip) planner.c will take into account the relative costs of doing a GroupAgg vs doing a HashAgg and re-sorting, but I'm too tired to try it right now... regards, tom lane ---(end of broadcast

Re: [SQL] SQL syntax extentions - to put postgres ahead in the race

to make that happen. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Grouping by week

. regards, tom lane ---(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] Datetime

... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Datetime

-from-epoch form at all, but some other format (such as perhaps separate /mm/dd/hh/mm/ss fields). Sorry if I added to the confusion instead of dispelling it. regards, tom lane ---(end of broadcast)--- TIP 9: the planner

Re: [SQL] Problems with UNION ALL and ORDER BY

the rows of the second query ordered. Pray tell, what Postgres release are you using? AFAICT this will result in an overall sort in all PG releases since 7.0. I don't have anything older to test... regards, tom lane ---(end of broadcast

Re: [SQL] Problems with UNION ALL and ORDER BY

are using a non-C locale you may be seeing some pretty weird sorting rules :-( regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command

Re: [SQL] How to create an aggregate?

, '|') regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] SELECT from a list

. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] regex engine

, but claiming it doesn't handle UTF8 at all is simply wrong. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] Is a backend id or something available for use as a foreign key?

of the documentation. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Trigger functions with dynamic SQL

to look at pltcl instead, which is much friendlier to dynamically generated queries (since that's the only way it does things). Of course, if you've never used Tcl there'll be a bit of a learning curve :-( regards, tom lane ---(end of broadcast

Re: [SQL] Trigger functions with dynamic SQL

you can write the constant parts of your query the same way you normally would. What about writing trigger functions in C? Seems like the hard way to me. I doubt it would be better than plpgsql, but it's all a matter of opinion... regards, tom lane

Re: [SQL] surrogate key or not?

to fix the error later. As Achilleus' nearby story shows, you can have these problems (certainly the misentry part) even with imported data that is allegedly someone else's primary key; part numbers, USA social-security numbers, etc. regards, tom lane

Re: [SQL] LIKE on index not working

-- Index Scan using fooeyi on fooey (cost=0.00..17.08 rows=5 width=32) Index Cond: (lower((f1)::text) = 'z'::text) Filter: (lower((f1)::text) ~~ 'z'::text) (3 rows) regards, tom lane ---(end of broadcast

Re: [SQL] Converting a plperlu function to a plpgsql function

--- b (1 row) You'd have to use it twice to collect two separate substrings, which is mildly annoying, but it's hard to see how to do better without bizarre behind-the-scenes stuff (like Perl's magic $n variables). regards, tom lane ---(end

Re: [SQL] immutable function calling stable function

where creating an immutable wrapper function was the recommended solution to performance problems. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your

Re: [SQL] C++ interface problem with libpq.so.3

just waiting for a response from the backend. I'd suggest looking into what the backend is doing. I doubt you have an interface problem at all... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9

Re: [SQL] [JDBC] [ADMIN] Migrate postgres databases from SQL_ASCII to UNICODE

in a Postgres 7.3.6 with SQL_ASCII. Ugh. You'll have to work out how to convert that codepage to one of the encodings that PG supports. Or else add it as a supported encoding (I'm not sure how hard that is, but it's not out of the question). regards, tom lane

Re: [SQL] Problem in age on a dates interval

tip. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] Immutable function in index

functional columns in one index is new in 7.4. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your

Re: [SQL] Custom type where not all elements are comparable

using hash aggregation, as long as equality behaves sanely; but that's not implemented now.) Probably it'd be reasonable for the comparison operators to return NULL for a noncomparable pair of inputs. regards, tom lane ---(end of broadcast

Re: [SQL] best method to copy data across databases

in its COPY commands, so a plain pg_dump should work. The way with COPY will be a good bit faster than a pile of INSERT commands. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose

Re: [SQL] quoting

quoting. regards, tom lane ---(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

Re: [SQL] Unrecognized node type

responsive to a bug report.) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: FW: [SQL] = operator vs. IS

on such a thoroughly nonstandard behavior ... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your

Re: [SQL] Unrecognized node type

as finding the bug, you need to provide a complete, self-contained test case. As far as loading the schema, how about just removing the DEFAULT clauses? default null is the default behavior anyway. regards, tom lane ---(end of broadcast

Re: [SQL] UPDATE ... WHERE (subselect on the same table)

used here will work correctly, i.e. will the test.name always refer the column in outer table, not inner (t2)? Yes. The alias *completely* hides the real name of that table reference, so test will never refer to test t2. regards, tom lane ---(end

Re: [SQL] Non-standard function names

and LOG10 could be provided, at least there would then be only one difference from the JDBC standard. loge() strikes me as pointless; you might as well just use ln(). I don't have any objections to the other proposed additions though. regards, tom lane

Re: [SQL] ERROR: Unable to format timestamp; internal coding error

the following error : ERROR: Unable to format timestamp; internal coding error FWIW, this is fixed in 7.4 and later. Is there a way to select the values in the table? I'd try something like UPDATE table SET ... WHERE timestamp '-01-01'; regards, tom lane

Re: [SQL] subselect prob in view

. regards, tom lane ---(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

Re: [SQL] subselect prob in view

on performance --- see the user's guide concerning how explicit JOIN syntax constrains the planner. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] plpgsql - Insert from a record variable?

sure about plperl or plpython. (No reflection on the languages, but pltcl has the most complete Postgres interface.) regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate

Re: [SQL] plpgsql - Insert from a record variable?

; regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] In 7.4 ensure you have DEFAULT now () with no spaces

because a string literal) versus default now() (correct because a function call). regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister

Re: [SQL] use of a composite type in CREATE TABLE?

Hannes Korte [EMAIL PROTECTED] writes: does anyone know how it is posible to set a composite type as the data type of a column when creating a new table? Use 7.5 ;-). It's not supported in any existing release, but it does work in CVS tip ... regards, tom lane

Re: [SQL] Datetime problem

Eric Lemes [EMAIL PROTECTED] writes: - select to_timestamp('2004 10 10 00 00 00', ' MM DD HH MI SS') the output is: - 2004-10-09 23:00:00-03 What PG version is this, on what platform, and what's your current timezone setting? regards, tom lane

Re: [SQL] a query with = ALL

may get results you didn't expect. These types have different ideas about whether trailing blanks are significant or not. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL

Re: RES: [SQL] Datetime problem

standard time... regards, tom lane ---(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

Re: RES: [SQL] Datetime problem

for pointing it out. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] query with =ALL

yourself clearly, because as far as I can understand you there are guaranteed to be no such results. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your

Re: [SQL] Getting FK relationships from information_schema

Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane wrote: We have discussed changing the default names of FK constraints before. I have no problem with doing something like the above --- any objection out there? I think it's a good idea. It will also make the error messages of the kind

Re: [SQL] Schema + User-Defined Data Type Indexing problems...

* from foo where my_uuid = 'xxx'; instead select * from foo where my_uuid operator(my_schema.=) 'xxx'; Yech. I think you'll end up putting uuid's schema in your search path before long anyway. regards, tom lane ---(end of broadcast

Re: [SQL] Schema + User-Defined Data Type Indexing problems...

). regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Schema + User-Defined Data Type Indexing problems...

that we could think of a more convenient behavior for default opclasses, but I don't want to do something that would foreclose having similarly-named datatypes in different schemas. You have any suggestions? regards, tom lane ---(end of broadcast

Re: [SQL] What's wrong with my date/interval arithmetic?

arithmetic, but not when doing timezone-free arithmetic. I still think the behavior you see is related to the timezone you're using, which you still haven't told us. Also, what PG version are you running, and on what platform? regards, tom lane ---(end

Re: [SQL] Getting FK relationships from information_schema

to information_schema tables? If not, what other alternatives are there? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere

Re: [SQL] Getting FK relationships from information_schema

to Joe's-Corner-Bar's database.) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Scalar in a range (but textual not numeric)

WHERE min_max_object overlaps-operator 'ABCDE' and the overlaps operator would be a GiST-indexable one. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] SQL DDL: FOREIGN KEY construct and field mapping: unexpected behavior

as the data type of the corresponding referenced column. Nothing there about try to match by name. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Formatting problems with negative intervals, TO_CHAR

for division with a negative input varies across machines. However I do see the bug on HPUX 10.20 with CVS tip. Likewise, # select to_char('4 minutes'::interval - '4 minutes 30 seconds'::interval, 'mi:ss'); to_char - 00:-3 (1 row) regards, tom lane

Re: [SQL] date format in 7.4

the very voluminous flamewar about ambiguous date input handling in the pgsql-hackers archives from last summer. although the documentation still states the following AFAICS the documentation says so too... regards, tom lane ---(end of broadcast

Re: [SQL] Difference between two times as a numeric value in a stored procedure.

Stijn Vanroye [EMAIL PROTECTED] writes: I can't seem to find a way to substract two time values (or timestamp values) and get a numeric/float value. I always get the INTERVAL datatype. extract(epoch from interval) may help. regards, tom lane

Re: [SQL] ANSI SQL-99 SYNTAX and WITH

. Maybe next time. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Selecting sample data from large tables.

take a while). Can anyone thing of a way to do this is postgresql? There is a fairly decent random-sampling engine inside ANALYZE, but no way for the user to get at it :-(. Can you make any use of ANALYZE's results, viz the pg_stats view? regards, tom lane

Re: [SQL] function with a composite type calling another function - Mission Impossible?

is essentially what you've got here. There's some chance it will work in time for 7.5. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Schemata User-Defined-Type casting issues

. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Query becoming slower on adding a primary key

it? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] passing new/old record in pl/pgsql trigger functions to other functions

that it will. regards, tom lane ---(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] insert or update violates foreign key constraint.why?

about whether this is really the most desirable behavior, but that's how it is at the moment. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs

Re: [SQL] Memory usage on subselect

. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] OR clause causing strange index performance

. regards, tom lane ---(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] Problem with JOINS

wanted some parentheses. AND (bs.value = 'bezahlt' OR bs.value = 'erlassen') AND Or you could express the same thing using IN: AND bs.value IN ('bezahlt', 'erlassen') AND regards, tom lane ---(end of broadcast)--- TIP 2

Re: [SQL] Preventing Deletions with triggers

that assignment to OLD is a no-op: you can't change the tuple that way. You'd have to do something like UPDATE person SET status = 1 WHERE key = OLD.key; (key being whatever your primary key for the table is) regards, tom lane ---(end

Re: [SQL] Simple view confuses PostgreSQL query planning

would give wrong answers --- nonnull when they should be null --- just as Victor described for constants. The general fix mentioned in the comment is still a long way off. regards, tom lane ---(end of broadcast)--- TIP 1

Re: [SQL] SCHEMA's the easy way?

commands in the dump script, reload. regards, tom lane ---(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] Function valid only for one table

. That is fixed for 7.5 though. regards, tom lane ---(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] How do i extract a certain bit from a bigint column

added, but \do would tell you quickly enough if they're in your version. Mind you that this is not going to be an especially fast solution, since these are not indexable operators. You might be better advised to rethink your data representation. regards, tom lane

Re: [ADMIN] [SQL] \set

=# select :AAA; ?column? -- whatever (1 row) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] new and old as parameter in a function

function foo(fooey) returns int as ' regression'# begin regression'# return $1.f1; regression'# end' language plpgsql; CREATE FUNCTION regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once

Re: [SQL] Subselect returning 2 columns

than one column, but it seems to work... http://www.postgresql.org/docs/7.4/static/functions-subquery.html#AEN12497 regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Procedure failing after upgrade

should be interpreted as a bool. 7.4 will coerce to bool or throw an error if it can't. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Permissions not working

? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Performance issue

. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

<    7   8   9   10   11   12   13   14   15   16   >