Re: [SQL] DB design and foreign keys

to reference a row by just one of them, you'll need to apply a separate unique constraint to just the order_code column. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send

Re: [SQL] [GENERAL] Looking for information on PostgreSQL Stored Procedures

keyword twice in the same block (the second instance is certainly unnecessary even if legal). regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] [GENERAL] Looking for information on PostgreSQL Stored Procedures

forget the cursor entirely and just write a FOR-loop. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] How to increase row deletion efficiency?

got one. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] Error from trigger

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

Re: [SQL] Error from trigger

Leif B. Kristensen [EMAIL PROTECTED] writes: On Thursday 08 December 2005 00:23, Tom Lane wrote: Offhand this looks like you might have dropped and recreated the event_citations table? If so it's just the known problem that plpgsql caches plans and doesn't throw them away when the referenced

Re: [SQL] JOIN query not working as expected

is that? Probably, you've got some NULLs in the messages.service_id column ... try excluding those from the sub-select. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http

Re: [SQL] JOIN query not working as expected

to the column if you do not wish to allow NULLs. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your

Re: [SQL] lost in system tables

standard --- though perhaps MATCH FULL is approximately what you are looking for? Your description as stated makes no sense at all; either the columns are allowed to be null, or they're not. regards, tom lane ---(end of broadcast

Re: [SQL] update question

only if one is needed. If you're intent on using a broken loop, you could do foo = foo where foo is any table column you didn't yet assign to. Consider though the corner case where you've already assigned all the columns. Best bet is to fix your loop... regards, tom lane

Re: [SQL] Just 1 in a series...

reference page. It's not standard SQL though. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] rename idx's with table; avoid confusing idx names?

such thing.) regards, tom lane ---(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] APPEND INTO?

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

Re: [SQL] quote_literal() fails with unmatched quotes

James M Doherty [EMAIL PROTECTED] writes: I have had the following problem and been unable to determine the cause ! Can you show us a complete example? I can't see what's going on from the info you've provided ... regards, tom lane ---(end

Re: [SQL] Quote_literal()

, I suggest giving a *complete* example, because you are obviously not looking in the right place for your problem. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http

Re: [SQL] Strange bug

relation_citations ( relation_fk INTEGER REFERENCES relations (relation_id) ) INHERITS (citations); relation_citations doesn't have a primary key. See http://www.postgresql.org/docs/8.1/static/ddl-inherit.html particularly the caveats section. regards, tom lane

Re: [SQL] Order of precedence between AND and OR.

This is required by the SQL spec. But you probably also want to read http://www.postgresql.org/docs/8.1/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through

Re: [SQL] Changing auth type in pg_hba.conf ineffective. Why?

. regards, tom lane ---(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] unplanned sub-select error?

this problem did not exist on 8.0.3. Fixed --- attached is the patch if you need it right away. Thanks for the report! regards, tom lane Index: src/backend/rewrite/rewriteHandler.c === RCS file: /cvsroot

Re: [SQL] can UNIQUEness of TEXT datatype really be guaranteed?

matches causing rejection of inputs that actually are distinct. A possibly simpler-to-understand way is to demand uniqueness in the first couple KB: create unique index myindex on mytable (substr(fieldname,1,2000)); regards, tom lane ---(end

Re: [SQL] unplanned sub-select error?

this problem did not exist on 8.0.3. Thanks for the test case. I've confirmed it fails here in CVS tip but not in 8.0 branch, so indeed it must be a new bug. Will look into it. regards, tom lane ---(end of broadcast)--- TIP

Re: [SQL] argument type problem with plpgsql function

Luca Pireddu [EMAIL PROTECTED] writes: I just tried it at home on a postgresql 8.0.3 server (debian package) and it worked the way it's supposed to. Puzzling... Maybe you have more than one blast_evalue() function with different argument types? regards, tom lane

Re: [SQL] max() unexpected type conversion

yielding varchar in recent PG releases. regards, tom lane ---(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

Re: [SQL] tid_le comparison for tuple id (ctid) values?

anything reliable to do with the age of the tuple. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] unplanned sub-select error?

gives the error: ERROR: cannot handle unplanned sub-select You need to offer a little more context, like what PG version you are using now and what is the underlying DDL --- I suspect some rules or views are involved here, but you didn't show them to us. regards, tom

Re: [SQL] Arrya variable as argument to IN expression

Emil Kaffeshop [EMAIL PROTECTED] writes: SELECT b IN (a) INTO flag; !!! does not work Is it legal to expect the array to be interpret as list of integers which IN expression takes ? No. But try b = any (a). regards, tom lane ---(end

Re: [SQL] cli in sql?

. Or upgrade ;-) See http://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-CONSTANTS for details, esp. section 4.1.2.2. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives

Re: [SQL] time

- 11:19:19.892125 (1 row) I think the OP was trying to use the functional cast syntax time(now()) which worked long ago, but has not since we added the SQL-spec time precision syntax. regards, tom lane ---(end of broadcast

Re: [SQL] RETURNS SETOF table; language 'sql'

about what you did. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] RETURNS SETOF table; language 'sql'

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

Re: [SQL] Changing location of ORDER BY has large effect on performance, but not results...

the same plan from both cases seeing that the planner knows the first one is cheaper. Can you provide a complete self-contained test case? I'm not interested in trying to reverse-engineer your table definitions ... regards, tom lane ---(end

Re: [SQL] Slow query - SELECTing one row from 'big' table.

change during the query. (In this case we can assume it's safe because nothing in that query would call nextval(), but the planner isn't omniscient enough to make that conclusion.) Fetch the currval into a local variable and use the variable in the query. regards, tom lane

Re: [SQL] PGSQL encryption functions

and can't trivially guess. Adding a randomly chosen salt string is one common way to do that. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index

Re: [SQL] function, that uses different table(names)

your table layout, instead. The SQL-ish way to do this is to combine the tables into one big table with an extra key column that stores whatever condition distinguished the smaller tables in your mind. regards, tom lane ---(end of broadcast

Re: [SQL] Poor performance in inet cidr join (Resolved)

Axel Rau [EMAIL PROTECTED] writes: Question: Can rtree_inet be included in the core? No, because rtree is going away in 8.2. Feel like converting that code to be a GIST opclass, instead? regards, tom lane ---(end of broadcast

Re: [SQL] PGSQL encryption functions

Mark R. Dingee [EMAIL PROTECTED] writes: md5 works, but I've been able to brute-force crack it very quickly, Really? Where's your publication of this remarkable breakthrough? regards, tom lane ---(end of broadcast

Re: [SQL] Two tables or three?

that. Postgres automatically stores wide fields out-of-line, so if large means more than a couple KB after compression then the system will do this behind the scenes and there's no need for you to do it. See http://developer.postgresql.org/docs/postgres/storage-toast.html regards, tom

Re: [SQL] why vacuum

Bath, David [EMAIL PROTECTED] writes: ... Note that Sybase/MS-SQL's check constraint model asserts the constraint BEFORE the trigger, which discourages you from attempting to check and handle meaning of data! Er, doesn't PG do it that way too? regards, tom lane

Re: [SQL] RETURNS SETOF primitive returns results in parentheses

produces the parenthesized data format specified at http://www.postgresql.org/docs/8.0/static/rowtypes.html#AEN5604 regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate

Re: [SQL] why vacuum

enough to overrun the fixed-size rollback areas (or whatever they call them)? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] broken join optimization? (8.0)

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

Re: [SQL] RETURNS SETOF primitive returns results in parentheses

have to do it this way because the FOR loop wants a record variable. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] SUM not matching up on a JOIN

output, so the database isn't enforcing that condition for you ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: Yes, pg does triggers first before asserting check constraints! Was Re: [SQL] why vacuum

to replace it with something valid. (Yes, people have asked for that :-() A less silly example is that domain constraints on a field of a domain type get checked before the trigger can run. regards, tom lane ---(end of broadcast

Re: [SQL] convert timezone to string ...

Marc G. Fournier [EMAIL PROTECTED] writes: I know that the server knows that ADT == -0400, and AST == -0300 ... Other way around isn't it? Unless Canada observes a pretty strange variety of daylight saving time ;-) regards, tom lane ---(end

Re: [SQL] Delete rule chain stops unexpectedly

Wiebe Cazemier [EMAIL PROTECTED] writes: Tom Lane wrote: Because the rule converts those inserts into, effectively, INSERT INTO debuglog SELECT ... WHERE EXISTS(some matching OLD row); and there are no longer any matching OLD rows in the view. Is this behaviour also present in Postgres 7

Re: [SQL] why vacuum

. Unless MySQL invents some concept equivalent to VACUUM, they won't have any prayer at all of being able to shift maintenance overhead to low-load times. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our

Re: [SQL] Blank-padding

Shaun Watts [EMAIL PROTECTED] writes: Is there any way to eliminate the blank padding at the end of character fields in a table. Use varchar, or text. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget

Re: [SQL] Postgres 7.4.9 slow!

very different compilers were used. One thing I'd wonder about is whether both databases were initialized in the same locale. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Delete rule chain stops unexpectedly

delete them. In practice, you'd be way better off using an ON DELETE trigger for these tasks. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Delete rule chain stops unexpectedly

Wiebe Cazemier [EMAIL PROTECTED] writes: Tom Lane wrote: The rule that actually deletes the rows from the underlying has to fire last, since the rows are gone from the view (and hence from OLD) the moment you delete them. A quote from the postgresql manual: But for ON UPDATE and ON DELETE

Re: [SQL] Delete rule chain stops unexpectedly

Wiebe Cazemier [EMAIL PROTECTED] writes: Tom Lane wrote: Hmm ... this appears to be a bug in EXPLAIN ANALYZE: it really should bump the CommandCounter between plan trees, but fails to ... Is this something I have to report? Nah, I fixed it already (only in CVS HEAD though

Re: [SQL] Blank-padding (was: Oracle buys Innobase)

if a numeric datatype doesn't remember how many trailing zeroes you typed after the decimal point. Those zeroes aren't semantically significant, so you have no case. regards, tom lane ---(end of broadcast)--- TIP 4: Have you

Re: [SQL] NULL in IN clause

is effectively NOT (0 = NULL OR 0 = 1) NOT (NULL OR FALSE) NOT NULL NULL ie, the result is UNKNOWN, which WHERE treats the same as FALSE. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked

Re: [SQL] casting character varying to integer - order by numeric sort

are working with ;-) You may find that username::text::integer will work, depending on which 7.x this actually is. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] owner of data type areas appears to be invalid ?

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

Re: [SQL] FULL OUTER JOIN Question

with trans_item.parent=20116?) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] pg, mysql comparison with group by clause

unnecessary GROUP BY columns. The gripe against mysql, I think, is that they don't enforce the conditions that guarantee the query will give a unique result. The gripe against postgres is that we haven't implemented the SQL99 semantics yet. regards, tom lane

Re: [SQL] pg, mysql comparison with group by clause

to implement. Those two pretty much control the committee after all ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Text-Date conversion in a WHERE clause

are made only for a very few specific constructs such as CASE. See http://www.postgresql.org/docs/8.0/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your

Re: [SQL] Text-Date conversion in a WHERE clause

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

Re: [SQL] Update timestamp on update

the documentation about triggers. The first example on this page does it along with a few other things: http://developer.postgresql.org/docs/postgres/plpgsql-trigger.html regards, tom lane ---(end of broadcast)--- TIP 6

Re: [SQL] pg, mysql comparison with group by clause

, but the primary-key case probably wouldn't be very hard to implement. We really ought to have this in TODO ... I'm sure it's been discussed before. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner

Re: [SQL] Update timestamp on update

the job done. Anybody have a better idea? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] UPDATE Trigger on multiple tables

function for multiple triggers, but you have to CREATE TRIGGER for each table separately. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs

Re: [SQL] question re. count, group by, and having

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

Re: [SQL] ichar

Judith Altamirano Figueroa [EMAIL PROTECTED] writes: ERROR: not exist the function ichar(integer) [ digs in archives... ] Looks like we renamed ichar() to chr() quite some time ago. regards, tom lane ---(end of broadcast

Re: [SQL] How to delete Large Object from Database?

) ) WITH OIDS; ALTER TABLE t_data_pic OWNER TO admin; Why am I not seeing any trigger attached to this table? That lo_manage trigger is the useful part of contrib/lo --- the separate data type is mere window dressing. regards, tom lane ---(end

Re: [SQL] Scripting GRANT on functions

-- boolin(cstring) boolout(boolean) byteain(cstring) byteaout(bytea) charin(cstring) (5 rows) regression=# regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives

Re: [SQL] UNION index use help

a text with a varchar. Make the column types the same and it'll work better. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] MOVE in SQL vs PLPGSQL

admit, on the odd occasion I want to skip a row, I just FETCH it and move on. Anyone else? There is something on the TODO list about improving plpgsql's cursor functionality --- there's no reason it shouldn't have MOVE, except that no one got around to it yet. regards, tom

Re: [SQL] Use of partial index

they obviously must all be 2. It's often better to define the index column(s) of a partial index as some other column than the one involved in the index predicate...) regards, tom lane ---(end of broadcast)--- TIP 9: In versions

Re: [SQL] combination of function to simple query makes query slow

for this is counterproductive in itself; the table access that's going on inside the function needs to be exposed for optimization in order to get reasonable overall performance. regards, tom lane ---(end of broadcast)--- TIP 2

Re: [SQL] using pg_tables and tablename in queries

. Those tables should all get merged into one big table, adding one extra column that reflects what you had been using to segregate the data into different tables. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched

Re: [SQL] Problem with function and trigger...

with this? We don't guarantee short-circuit evaluation of boolean expressions. You'll have to break that into two IFs, ie, IF TG_OP = 'DELETE' THEN IF ... test on OLD.something ... regards, tom lane ---(end of broadcast

Re: [SQL] Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT

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

Re: [SQL] redundancy in CHECK CONSTRAINTs

constraints alike, but other SQL databases are likely to be pickier. BTW, is there any actual need for the id column here, seeing that you have a natural primary key? regards, tom lane ---(end of broadcast)--- TIP 9

Re: [SQL] Where are user defined functions stored?

Hilary Forbes [EMAIL PROTECTED] writes: How can I easily get to see the definition of a user defined function please? Look in pg_proc. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ

Re: [SQL] Where are user defined functions stored?

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

Re: [SQL] Issue with UPDATE statement on v8

provided enough info to let anyone reproduce the problem for investigation. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] R-tree and start/end queries

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

Re: [SQL] delete item[5] from varchar[] array???

Matthew Peter [EMAIL PROTECTED] writes: How is it possible to delete an item from a single dimension varchar[] array? AFAIR there is no built-in function for this, but it seems like you could write a generic polymorphic function for it easily enough. regards, tom lane

Re: [SQL] Triggers Conditional Assignment

to be catered for. The equivalent construct in SQL is CASE. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] CREATE TEMPORARY TABLE ON COMMIT DROP

Luis Sousa [EMAIL PROTECTED] writes: But how can I create a table using a query and putting ON COMMIT DROP. You can't. Use INSERT ... SELECT to fill the table, instead. regards, tom lane ---(end of broadcast)--- TIP 1

Re: [SQL] Please help, can't figure out what's wrong with this function...

was pretty weak on handling rowtype variables that far back. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Panic: Page Add Item: Corrupted page pointers

exhausted the possibilities for pg_dump without it. Consider updating to a more recent PG release while you are recovering... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ

Re: [SQL] SELECT: retrieve only 2 rows next to known row

that defines this row to really say much about the best solution. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Indexing an array?

arrays to replace tables. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] queries problems

just works. regards, tom lane ---(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] Numeric Columns

values. However, the column needs to be able to hold values like 0.05. contrib/seg might do more or less what you're looking for, but none of the standard datatypes will. regards, tom lane ---(end of broadcast)--- TIP 4: Have

Re: [SQL] AGE function

1332. and here is a link to the discussion that prompted the change: http://archives.postgresql.org/pgsql-bugs/2004-12/msg00013.php regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner

Re: [SQL] POSIX Regular Expression question

code is definitely pretty weak at the moment. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] Recommendation on bytea or blob for binary data like images

bytea values in a streaming fashion. If your objects are small enough that you can load and store them as units, bytea is fine. BLOBs, on the other hand, have a number of drawbacks --- hard to dump, impossible to secure, etc. regards, tom lane

Re: [SQL] [HACKERS] ALTER TABLE ( smallinto - boolean ) ...

. I'd try ASSIGNMENT first, though, since it's less likely to bite you when you weren't expecting it. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire

Re: [SQL] [GENERAL] How do I copy part of table from db1 to db2 (and

then you are in for some pain. At the minimum you'll have to separate out the rows that are in each encoding so you can pass them through different conversion processes. regards, tom lane ---(end of broadcast)--- TIP 5: don't

Re: [SQL] Unwanted nested dollar-quoted constants

possible to develop appropriate code for dollar-quoting random text, but it's a lot harder than it is to escape the data in the old style. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ

Re: [SQL] Can EXCEPT Be Used for To Solve This Problem?

Lane Van Ingen [EMAIL PROTECTED] writes: I want to select 2nd oldest transaction from foo (transaction 3). Can't you just do select * from foo order by update_time desc offset 1 limit 1 regards, tom lane ---(end of broadcast

Re: [SQL] Problem calling stored procedure

; As far as the reason for the difference between function execution and manual execution: check for unintended variable substitutions. Which words in the query match variable names in the plpgsql function? Are those only the ones you intended? regards, tom lane

Re: [SQL] Problem with self-made plpgsql-function / casting

the argument as integer. regards, tom lane ---(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] A Table's Primary Key Listing

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

<    4   5   6   7   8   9   10   11   12   13   >