Re: [SQL] truncating table permissions

documented until very recently. 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

Re: [SQL] definative way to place secs from epoc into timestamp column

? - 2001-02-17 04:38:40 (1 row) regression=# SELECT (TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second')::timestamp without time zone; timestamp - 2001-02-16 23:38:40 (1 row) regards, tom lane ---(end

Re: [SQL] definative way to place secs from epoc into timestamp

you are saying I need to do? You can make a function that embodies whichever semantics you want. also, what is happening with abstime(982384720)? this works as expected (by me ). Is this a bad idea? It won't be there forever. regards, tom lane

Re: [SQL] definative way to place secs from epoc into timestamp

of integer? There doesn't seem any reason to disallow sub-second precision. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] SQL error: function round(double precision, integer) does

TJ O'Donnell [EMAIL PROTECTED] writes: Is there some fundamental reason for round(dp) but round(numeric,int)? I think the main argument against supporting round(dp,int) is that the result would be inherently inexact (at least for int0). regards, tom lane

Re: [SQL] Serial and Index

. Perhaps you should be reading a more up-to-date copy of the FAQ. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] [GENERAL] Are SQL queries locale dependent?

can't parse this query. But I'm not sure how to fix this query so that it works under all circumstances. Use dots. The SQL syntax for a number is not locale-dependent. regards, tom lane ---(end of broadcast)--- TIP 2: you

Re: [SQL] Advanced SELECT

by the outer, so it would repeat the sort step using only key1 and very possibly destroy the key2 ordering. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Junk queries with variables?

these don't suit your needs? I guess the real question is: in what context/language are you expecting to be able to manipulate these variables? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list

Re: [SQL] pg primary key bug?

, because even if avoiding it is all that concerns you, no one can answer with any confidence until we understand what the failure mechanism is. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] pg primary key bug?

. Can you describe more detailed the idea of problem with vacuum full + update and can some one make patch if this problem exists in theory (if I understand you right)? I have no idea what the actual failure mechanism might be. regards, tom lane

Re: [SQL] pg primary key bug?

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

Re: [SQL] pg primary key bug?

,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] pg primary key bug?

pginfo [EMAIL PROTECTED] writes: Tom Lane wrote: Could we see the system columns on these rows? 01=# select oid,xmin,cmin,xmax,cmax,ctid,* from a_constants_str where constname='DOCPLAID'; oid | xmin | cmin | xmax | cmax | ctid | constname | fid | constvalue

Re: [SQL] pg primary key bug?

pginfo [EMAIL PROTECTED] writes: Will upgrade to 8.0 solve this type of problems ? The problem is probably not Postgres' fault at all. I'm wondering about disks with write cacheing enabled. And you didn't turn off fsync, I trust? regards, tom lane

Re: [SQL] Order of columns in a table important in a stored procedure?

think PostgreSQL could get the column names paired. It doesn't do it on the basis of column names. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate

Re: [SQL] parsing a string with a hexadecimal notation

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

Re: [SQL] pg_restore problem

order. Pre-8.0 versions of pg_dump are easily fooled if you use ALTER to make earlier-created objects reference later-created objects. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] case sensitive/insensitive confusion

setting. 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] MSSQL versus Postgres timing

referenced in the plans that do not appear in the schema. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] plpgsql functions and NULLs

constraints are selective enough to yield adequate performance. 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

Re: [SQL] Need SELECT rights to UPDATE/DELETE WHERE?

to be able to infer any such thing. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] error: cast the timestam expression

will need to rewrite or cast the expression. Both dates are of type timesamp! I don't think so. Let's see the definitions of the comments and xyz tables (\d in psql will do). regards, tom lane ---(end of broadcast)--- TIP 1

Re: [SQL] Complete instruction in a trigger

. 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] How to find out programmatically whether a query on a view will use an index?

work. Is there no way to get a machine readable query plan? No, and no such API is likely to be defined in the future either, because we reserve the right to change plan structures at any time. regards, tom lane ---(end of broadcast

Re: [SQL] editors with colum positioning for debugging?

=# select regression-#1/0 as a, regression-#1/ as b, regression-#1/2 as c; ERROR: syntax error at or near as at character 27 LINE 3:1/ as b, ^ regression=# regards, tom lane ---(end of broadcast

Re: [SQL] still having pg_user error

) service postgresql stop rm -rf /var/lib/pgsql/data setenforce 0 service postgresql start The start script will observe that there's no database directory and proceed to run initdb for you. regards, tom lane ---(end

Re: [SQL] ERROR: row is too big: size 9856, maximum size 8136

,atttypid::regtype,attstorage from pg_attribute where attrelid = 'pg_rewrite'::regclass and attnum 0; 7.4 should certainly be configured to have a toast table for pg_rewrite, but maybe something went wrong during initdb on your installation. regards, tom lane

Re: [SQL] still having pg_user error

/sbin/setenforce 0) for long enough to do the initdb. If you're running SELinux in strict rather than targeted policy, good luck ... I honestly haven't tried that one at all ... regards, tom lane ---(end of broadcast)--- TIP 7

Re: [SQL] query configuration for validate empty quote to zero

Yudie [EMAIL PROTECTED] writes: Now, if I use the same query it return an error: invalid input syntax for integer: Anyone know what's the problem?? None. That's an intentional change. The fact that it ever accepted '' as meaning 0 was a bug. regards, tom lane

Re: [SQL] failed to find conversion function from unknown to text

. This is an unintended side effect of the change to evaluate CASE constructs more efficiently. I'll fix it. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Simple Question

in mind. 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] View with Union and update rule - fails

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

Re: [SQL] Unresolved external: tgetent

seen it before on some platforms (obsolete HPUX versions, in my experience). You can probably work around it by reducing the maximum concurrency setting in the regression script. See the regression test discussion in the administrator's guide. regards, tom lane

Re: [SQL] replacing mysql enum

. Their thought was that if you intend to disallow NULLs, you should write an explicit NOT NULL constraint, separately from any CHECK you might write. Therefore, defining CHECK such that it tend to fall through silently on NULL inputs is a good thing. regards, tom lane

Re: [SQL] Set Returning Function problem

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

Re: [SQL] SET AUTOCOMMIT TO OFF

Christoph Haller [EMAIL PROTECTED] writes: Please, could someone point me to the right list or tell me how to do a SET AUTOCOMMIT TO OFF within libpq. libpq does not have any support for that. regards, tom lane ---(end of broadcast

Re: [SQL] inserting values into types

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

Re: [SQL] find the missing rows

Kevin B. [EMAIL PROTECTED] writes: Select a.i, b.i from t as a left join t as b on a.i = b.i where a.n = 'a' and b.n = 'b' and b.i is null This can't succeed since the b.n = 'b' condition is guaranteed to fail when b.* is nulled out ... regards, tom lane

Re: [SQL] Found Large Files.. what objects are they?

Yudie [EMAIL PROTECTED] writes: Anyone know what it could be how to delete the object related? How to = find a table by oid? Look at pg_class.relfilenode, not OID. Or try contrib/oid2name. regards, tom lane ---(end of broadcast

Re: [SQL] invalid 'having' clause

it makes no sense for it to refer to the output list entries. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] TEXT::CIDR/INET::CIDR output confusion

least, the casts between them cannot both be code-less binary-compatibility casts. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command

Re: [SQL] Type Inheritance

a look at CREATE DOMAIN, as well as the INHERITS and LIKE options in CREATE TABLE. Some part of that might be close to what you are looking for. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your

Re: [SQL] Way to stop recursion?

updating this 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] User defined types

ugly enough to make one wonder why bother with a composite type. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] PG7.4.5: query not using index on date column

. 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] missing... at end of sql expression

; this is causing it to assume that the FOR is the integer variant (FOR i IN low .. high). The error message is pretty confusing, I agree. I think 8.0 will do better. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all

Re: [SQL] Move table between schemas

if that's all the places or not... 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

Re: [SQL] JOIN not being calculated correctly

picked exactly the perfect plan. If you think not, try forcing other plan choices and see what happens to the runtime. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] CREATE TEMPORARY TABLE .. ON COMMIT DROP question

the table. Look into commands/tablecmds.c. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] transactions in functions, possible bug or what I'm doing

as of 8.0.0beta4, unless I'm doing something wrong: You can't use explicit savepoint commands, but the exception syntax does essentially the same thing implicitly. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9

Re: [SQL] NULLS and string concatenation

, then the result of the concatenation is the null value. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] select using regexp does not use indexscan

enthusiastic about setting up non-C default locales than some other distros. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] tricky GROUP BY / JOIN question

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

Re: [SQL] Delayed result from another connection

=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?= [EMAIL PROTECTED] writes: From: Tom Lane [EMAIL PROTECTED] Yes, because your transaction is working with a database snapshot that predates the other transaction run by the separate PHP connection. Yes, but the snapshot is not constant during a transaction

Re: [SQL] Delayed result from another connection

, but we didn't get consensus about changing it till recently ... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED

Re: [SQL] Join Table

on ITEM_FK. But unless your use of this table is almost all searches and hardly any modifications, adding a dedicated index is probably a net loss due to the added update costs. regards, tom lane ---(end of broadcast)--- TIP

Re: [SQL] Select Instead on a table

Markus Schaber [EMAIL PROTECTED] writes: Is it possible to create a ON SELECT DO INSTEAD rule on a table? Not unless you want it to become a view. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists

Re: [SQL] curious delay on view/where

is this? 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] How to re-sort a sorted query?

-select: select * from (select distinct on (storenumber), itemsku, storenumber,price from storeproduct where itemsku='10001' order by storenumber, price) ss order by price; regards, tom lane ---(end of broadcast)--- TIP

Re: [SQL] plpgsql: PERFORM vs. SELECT INTO (PERFORM not setting FOUND variable?)

was certainly not very well documented before.) Note that I'm concerned that the performance of this will suck ... in particular you really ought to test the $1=$2 case separately. regards, tom lane ---(end of broadcast)--- TIP 6

Re: [SQL] Create interval using column value?

'build_interval seconds'; You are confused about the difference between a literal constant and an expression. Try something like ... WHERE built_on now() - build_interval * interval '1 second'; which relies on the number-times-interval operator. regards, tom lane

Re: [SQL] Trouble with explicit joins

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

Re: [SQL] the problem of createlang!

-7.4 backend. You sure you updated your server? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] libpq-fe: PQgetvalue() ?

to, and one must explicitly copy the data into other storage if it is to be used past the lifetime of the PGresult structure itself. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ

Re: [SQL] Export tab delimited from mysql to postgres.

) at the real ends of lines, else you need to think harder about how to tell the difference between data and formatting. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http

Re: [SQL] How do FKs work?

... Do you have indexes on the referencing columns? Are they exactly the same datatype as the referenced column? You can get really awful plans for the FK-checking queries if not. regards, tom lane ---(end of broadcast)--- TIP 2

Re: [SQL] Impact of foreign keys on a simple count(*) ?

the new table back down to a reasonable size... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Using timestamp in function

the funtion from another function (which I need) the variable curtime does not change anymore. now refers to the transaction start time. You can get at current time of day with the timeofday() function. regards, tom lane ---(end of broadcast

Re: [SQL] Regex

that works reliably for the output of that query generator. But I wonder if you wouldn't be better off bypassing the parse-and-deparse hacking and tapping directly into the query generator. regards, tom lane ---(end of broadcast

Re: [SQL] Difference between IN and JOIN

it there's no semantic difference between the two. There's plenty of difference, if t2 contains repeated occurrences of f2 values. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL

Re: [SQL] Concurrency problem

and be labeled SECURITY DEFINER. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] [NOVICE] date_trunc'd timestamp index possible?

: the timestamptz variant is strongly dependent on the timezone setting and so should be STABLE not IMMUTABLE. Ooops. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] [NOVICE] date_trunc'd timestamp index possible?

not make it so. The above *will* break the first time someone uses the table with a different timezone setting. What you can do safely is date(footime AT TIME ZONE 'something'), since this nails down the zone in which the date is interpreted. regards, tom lane

Re: [SQL] FOREIGN KEY and AccessExclusiveLock

Achilleus Mantzios [EMAIL PROTECTED] writes: Whats the purpose of the AccessExclusiveLock on parent table? We're adding a trigger to it. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Best way to know if there is a row

the optimizer in favor of fast-start plans. 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] CHECK col A not NULL if col B='x'

constraints in PG. Just write it as a table constraint in ALTER TABLE. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] [GENERAL] need ``row number``

. 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] plpgsql syntax

. 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] raise is not working

quotes then I'd expect you to be getting errors along the lines of ERROR: column select * from clmhdr does not exist long before you get to the RAISEs. You need doubled single quotes, eg SQL_Str := ''select * from clmhdr''; See the docs. regards, tom lane

Re: [SQL] raise is not working

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

Re: [SQL] LIKE operator and string comparison

Wei Weng [EMAIL PROTECTED] writes: But if I choose to use string comparison, instead of 4 escape characters, I only need 2. Why is that? Backslash is an escape character for LIKE. regards, tom lane ---(end of broadcast

Re: [SQL] Difference in DATEs

Forget the interval and compare to an integer. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] raise is not working

function than you think you are --- we've seen examples of that sort of mistake recently. Check for multiple functions with same name and different argument types. regards, tom lane ---(end of broadcast)--- TIP 9: the planner

Re: [SQL] JOIN performance

that adds some other conditions you didn't mention. (Or are those conditions coming from the sub-views? Hard to tell.) If you want useful help you need to be more complete. regards, tom lane ---(end of broadcast)--- TIP 1

Re: [SQL] JOIN performance

that CASE construct with a min() function that's declared strict. I think date_smaller would do nicely, assuming the columns are actually of type date. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] JOIN performance

you will have to declare the function as STRICT, which will force it to behave in the first fashion. I suppose that COALESCE has the same problem as CASE ... Yup, of course. regards, tom lane ---(end of broadcast)--- TIP 3

Re: [SQL] JOIN performance

running 7.3.4, by the way. 7.3 is even stupider than 7.4, is why ... In 7.3 only a view whose targetlist consists of simple column references can be pulled up into the nullable side of an outer join. regards, tom lane ---(end of broadcast

Re: [SQL] How to check postgres running or not ?

if using a SysV-ish ps). Obviously none of this matters if you are just going to eyeball the output, but if you want something suitable for a test in a script, you'd better use something like the last one. regards, tom lane ---(end of broadcast

Re: [SQL] ORDER BY and NULLs

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

Re: [SQL] How to check postgres running or not ?

Andrew Sullivan [EMAIL PROTECTED] writes: On Sun, Sep 19, 2004 at 12:25:00PM -0400, Tom Lane wrote: ps aux | grep postmaster | grep -v grep (or use ps -ef if using a SysV-ish ps). Except that on Solaris, ps -ef _always_ shows postmaster, even for the individual back ends. Right, but if you

Re: [SQL] ORDER BY and NULLs

T E Schmitz [EMAIL PROTECTED] writes: Tom Lane wrote: Whatever you were reading had it pretty badly garbled :-( I just dug out the PostgreSQL book again because I thought I might've garbled it: Quote: PostgreSQL will not index NULL values. Because an index will never include NULL values

Re: [SQL] ORDER BY and NULLs

, it seems to be a veritable fount of misinformation :-( I wonder how much of this is stuff that is true for Oracle and they just assumed it carried over? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our

Re: [SQL] How to check postgres running or not ?

Worik [EMAIL PROTECTED] writes: Perhaps I have a bug in pg_ctrl? More likely you have the wrong value of PGDATA in your environment (where wrong means not what that postmaster is using). regards, tom lane ---(end of broadcast

Re: [SQL] Sequence of SQL command execution involving triggers and stored procedures.

in 8.0 to fire them at the end of the specific query that triggered them, which I believe will do what you want. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate

Re: [SQL] Interpolation and extrapolation in SQL

David Garamond [EMAIL PROTECTED] writes: 2) Does the optimizer cache the result of identical subqueries (e.g. :x1 or :x2, which is mentioned several times in the query)? No. regards, tom lane ---(end of broadcast)--- TIP

Re: [SQL] [GENERAL] PL/pgSQL Function Problem

and password_ in the CREATE USER utility statement. You could make it work by constructing the CREATE USER command as a string and then EXECUTE'ing it. (I agree this ain't ideal, but it's where we're at...) regards, tom lane ---(end of broadcast

Re: [SQL] CREATE RULE ignored, what did I do wrong

tend to class it as a you should know what you're doing feature. Preventing this kind of error would inevitably result in a serious reduction of the power of the rule feature. regards, tom lane ---(end of broadcast)--- TIP 9

Re: [SQL] create unique index schema.index_name on table (column)?

[EMAIL PROTECTED] writes: Is the syntax schema_name.index_name for create unique index wrong, unsupported or what? Yes. Put the schema name on the table, instead. regards, tom lane ---(end of broadcast)--- TIP 1

Re: [SQL] Isnumeric function?

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

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