Tom Lane wrote:
Hm, good point, so really we ought to have a separate casting path for
numeric types to char(n). However, this section still doesn't offer
any support for the OP's desire to auto-size the result; it says
that you get an error if the result doesn't fit in the declared
length:
Michael Glaesemann wrote:
On Feb 15, 2008, at 18:11 , Ken Johanson wrote:
Tom, is it accurate to assume that newer PG versions will further
tighten type-strictness (say, '2008-01-01' presently being comparable
to a datetime)? Also, do you know of any other vendors that are
heading
Tom Lane wrote:
Ken Johanson [EMAIL PROTECTED] writes:
select 5'6' - true
select 5'6' - false
select 15'60' - true
select 15'60' - false
These examples miss the point, because they'd give the same answer
whether you think the values are text or integer. Consider instead
these cases
Jeff Davis wrote:
If postgresql were to revert to 8.2 implicit casting behavior, would
that actually improve compatibility with other DBMSs? Every DBMS
probably has it's own rules for implicit casting, different from every
other DBMS.
So are you sure it wouldn't just introduce more
Tom Lane wrote:
It's possible to special-case any particular function you really feel
you need this behavior for. We did special-case || (the string
concatenation operator), and there was some discussion of also putting
in a built-in special case for LIKE, but we desisted from sliding any
I continue to hope that 8.4 later (or 8.3 patched) will provide behavior
/ compatibility controls..
I personally have requested others in past, but one outstanding one now,
seems to be an ability to turn back-on implicit type conversion. I'm
struggling to see Mysql, Mssql, Oracle, and others
Alvaro Herrera wrote:
If you are arguing that the spec's definition of the CHARACTER type is
not really very useful, I think you are going to find a lot of
supporters. You can send your complaints to the SQL committee; but
then, it is unlikely that this is going to change anytime soon because
Andrew Sullivan wrote:
No, you're trying to convey that it is more benign/useful _to you_. Others
are arguing that they want to write conformant code, and don't much care
what MyOccasionallyReadTheSpec does. It's a pity that SQL conformance is
not better across systems, but surely the way to
Richard Huxton wrote:
What I couldn't figure out was what type MySQL was using. I mean, what
type is this?
mysql SELECT cast(a as char) as achar FROM tt;
+---+
| achar |
+---+
| 1 |
| 10|
+---+
2 rows in set (0.00 sec)
Is it char(2)?
mysql CREATE TEMPORARY TABLE ttchar0
Tom Lane wrote:
Simply that a commonly used database (my) does not support it.
They do support char(n) in this context, which would have the advantage
of being standards compliant as well as de-facto portable.
Hmm, interesting. Mysql actual returns:
select cast(123 AS char(10)) - '123'
Dean Gibson (DB Administrator) wrote:
Then I don't understand. While I've never used MySQL, the MySQL web
pages apparently indicate that VARCHAR has been supported since version
3.2: http://dev.mysql.com/doc/refman/4.1/en/char.html
Only in DDL and not the cast function, apparently.
Dean Gibson (DB Administrator) wrote:
On 2008-02-12 16:17, Ken Johanson wrote:
Dean Gibson (DB Administrator) wrote:
...
I'm guessing you declare an explicit length of 1 (for portability), or
do you CAST (x as char)? And one might ask in what context we'd need
CHAR(1) on a numeric type
Dean Gibson (DB Administrator) wrote:
On 2008-02-12 07:30, Ken Johanson wrote:
Sure, but you're a prime candidate for understanding the value of
following the spec if you're trying to write software that works with
multiple databases.
The spec has diminished in this (CAST without length
Gregory Stark wrote:
Ken Johanson [EMAIL PROTECTED] writes:
Tom Lane wrote:
SQL92 section 6.1 data type quoth
character string type ::=
CHARACTER [ left paren length right paren ]
| CHAR [ left paren length right paren ]
...
4
For sake of interoperability (and using an API that requires String-type
hashtable keys), I'm trying to find a single CAST (int - var/char)
syntax that works between the most databases. Only char seems to be a
candidate, but in 8.3 casting from an integer outputs only the first char...
Is
Tom Lane wrote:
SQL92 section 6.1 data type quoth
character string type ::=
CHARACTER [ left paren length right paren ]
| CHAR [ left paren length right paren ]
...
4) If length is omitted, then a length of 1 is implicit.
Therefore,
I acknowledge that from time to time we must accept changes in the 3rd
party software that will break our apps if we (or customers) ever
upgrade them (a compounded issue if we have heavily-used deployments in
the field and not just in-house ones to maintain).
But given the recent and dramatic
Stephen Frost wrote:
* Ken Johanson ([EMAIL PROTECTED]) wrote:
But given the recent and dramatic example of 8.3's on-by-default stricter
typing in functions (now not-autocasting), I worry that kind of change
could happen in every minor version (8.4 etc).
8.3 isn't a minor version.
PG
Magnus Hagander wrote:
PG uses a different versioning system than this one?:
http://en.wikipedia.org/wiki/Software_versioning#Numeric
Or do you mean the changes are not minor? :-)
Yes, we use the one stated on our site, not wikipedia ;)
See: http://www.postgresql.org/support/versioning
(re-post, last seemed not to get on-list)
Hi all,
In the link below we asked if it was possible to set, in this case, the
standard_conforming_strings behavior, on a per-connection basis.
Hi all, I've seen other mentions of this happening but this instance is
with a newer server version than the ones that had a patch applied.
Basically, 8 LEFT JOINs, and removing any one of them stop the symptom.
I'll provide more data if the query in itself doesn't not indicate
what's causing
Is there a SQL/ALTER (pg proprietary or otherwise) syntax that uniformly
changes the names off all object tied to a column when that column name
is changed? Or can anyone recommend a UI that does this?
For example in a table 'foo' I have a 'rowid' (pk and auto increment)
and I choose to
Tom Lane wrote:
Please see if this patch fixes it for you:
http://archives.postgresql.org/pgsql-committers/2008-01/msg00164.php
If not, we'll need a self-contained test case.
Please pardon my non-awareness, what comprises a test case for this; is
DDL sufficent? I'm electing to go that route
Tom Lane wrote:
If increasing join_collapse_limit to 9 or more makes the problem go away
for you, that would be additional evidence that what you are seeing is
the same problem already diagnosed. That might be a usable workaround
for you until 8.2.7 comes out...
Thank Tom. That worked
Tino Wildenhain wrote:
Ken Johanson wrote:
I am looking for expertise on how to program the equivalent to this
query, but using the pg_catalog tables, which I understand have fewer
security restrictions than information_schema in some cases:
SELECT column_name
FROM information_schema.columns
Merlin Moncure wrote:
On Jan 15, 2008 1:15 AM, Ken Johanson [EMAIL PROTECTED] wrote:
The output of this is very verbose and broken into multiple queries
making joins difficult for me to understand, I'm afraid; my current
experience level likely will not reliably produce a single-query
Merlin Moncure wrote:
On Jan 15, 2008 1:15 AM, Ken Johanson [EMAIL PROTECTED] wrote:
The output of this is very verbose and broken into multiple queries
making joins difficult for me to understand, I'm afraid; my current
experience level likely will not reliably produce a single-query
I am looking for expertise on how to program the equivalent to this
query, but using the pg_catalog tables, which I understand have fewer
security restrictions than information_schema in some cases:
SELECT column_name
FROM information_schema.columns
WHERE table_catalog=? AND table_schema=? AND
Hi all,
I am looking for expertise on how to program the equivalent to this
query, but using the pg_catalog tables, which I understand have fewer
security restrictions than information_schema in some cases:
SELECT column_name
FROM information_schema.columns
WHERE table_catalog=? AND
I am looking for expertise on how to program the equivalent to this
query, but using the pg_catalog tables, which I understand have fewer
security restrictions than information_schema in some cases:
SELECT column_name
FROM information_schema.columns
WHERE table_catalog=? AND table_schema=? AND
Kris and all,
Here is the query I will call to the get the name of columns by ordinal
position. Do you see any compatibility drivers will older server
versions, or other issues?
SELECT column_name
FROM information_schema.columns
WHERE table_catalog=? AND table_schema=? AND table_name=?
ORDER
Kris Jurka wrote:
Using pg_catalog tables is better than using information_schema because
of the way permissions work. For information_schema you must be the
table owner, while people who only have permissions to access a table
will most likely be able to read pg_catalog.
Do you have
Tom Lane wrote:
Kris Jurka [EMAIL PROTECTED] writes:
I think the expectation is that:
CREATE TABLE t(a int, b int);
INSERT INTO t(b,a) VALUES (1,2) RETURNING *;
will return 1,2 instead of 2,1 as it does now.
Hmm ... I see your point, but on what grounds could one argue that
a *
Kris Jurka wrote:
I think the expectation is that:
CREATE TABLE t(a int, b int);
INSERT INTO t(b,a) VALUES (1,2) RETURNING *;
will return 1,2 instead of 2,1 as it does now. In this case the op is
not expecting that the (potentially reorganized) table order is driving
the results, but the
If true, my next idea would be to derive the column name using a
subquery in the returning clause. But it sounds like this may have
potential security contraints (will any INSERT query always have read
access to the PG tables?). And no guarantee of the order matching in
the long term.
I am attempting to implement (in a driver)(PG JDBC) support for
specifying which column indexes (that generated keys) to return, so I'm
searching for a way to get the server to return the values of the
columns by their index, not name. By name, it is simply to append the
RETURNING clause and
John D. Burger wrote:
Stephane Bortzmeyer wrote:
But he can write one in PostgreSQL quite easily. Rational numbers are
always the first exercice in CS courses about Abstract Data Types :-)
It's a little tricky to get good performance for all the operations:
The addition and subtraction
In SQL servers in general, or in PG, is there a native field type to
store fractions? Or must one resort to char or separate
numerator/denominator columns?
Thanks,
Ken
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
Adam Rich wrote:
Let's say you have a table with id, value columns.
And your normal query would be this:
INSERT into mytable (id,value) values (1,foo),(2,bar);
Your new query would be like this:
INSERT into mytable (id,value) values (1,foo),(2,bar)
RETURNING id;
And you would get a result
Now playing devil's advocate, can anyone see scenarios where this will
not work as expected? Examples (descriptions not sql necessarily) of
those would be helpful too...
Just to be sure, will the RETURNING clause work with custom sequences
(say, non numeric or increment by two) or other
Greetings,
I am looking into possibly contributing some code for one of the
existing PG drivers, that will allow us to, after INSERT, get a
ResultSet containing the server generated keys (sequences or other).
I've been told that (short of implementing a new V4 server protocol) the
most
Hi,
I'm trying out the new 8.2 beta; the following query:
select 'ab\cd'; yields: abcd, not ab\cd.
Is there a setting I'd need to switch to enable the ANSI/ISO escape
behavior? I am using the JDBC driver; not sure if that could be the
culprit..
Thanks,
Ken
You guys, and PG ROCK!! The standard_conforming_strings mode works
beautifully... it is (was) the last low-level barrier to using PG in a
really professional/interop environment. And as I become familiar,
again, with how PG and it's tools have come along, I am SUPER impressed.
Many, many
Tom and folks,
Will it be possible to set this more standard backslash handling
behavior -- and possibly similar conformance modes... in a way similar
to how mysql allows? They allow one to issue commands on the connection
like:
SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES,IGNORE_SPACE,ANSI'
Stefan Kaltenbrunner wrote:
postgresql can do that in an even more powerful way - but people tend to
not notice much of it in your case that would be:
ALTER ROLE foo SET standard_conforming_strings='off'
or even:
ALTER DATABASE bar SET standard_conforming_strings='off'
you can do that for
Stefan Kaltenbrunner wrote:
foo=# create table backslash(baz text);
CREATE TABLE
foo=# set standard_conforming_strings to on;
SET
foo=# insert into backslash values ('\\');
INSERT 0 1
foo=# set standard_conforming_strings to off;
SET
foo=# insert into backslash values ('\\');
WARNING:
Does anyone know roughly when there might be an 8.2 beta? Would a rough
guess of about November be right (1 year after 8.1)?
Regards,
ken
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
Just a quick thought, and an possibly over-optimistic forecast:
I think PG will see a really significant increase in usage, especially
'corporate' use, with the release of the version that has support for
(what I consider to be) one of the biggest features in recent memory:
standard iso/ansi
Tony Caduto wrote:
Ken Johanson wrote:
Most of the corp folks I know who have tried using PG to augment or
replacement a commercial offering just tend to silently pause and
wait for this change.. that why this topic isn't really heard very
often. It's like going to a car lot to buy a SUV
Bruce Momjian wrote:
E'' is more a marker than a type. I realize making E a type might work,
but it seems unusual.
What we could do is backpatch E'' to 8.0.X as a no-op like it will be in
8.1.
Bruce,
Is it possible in the 8.1 betas to 'switch on' on the standard SQL
escape behavior? This
The latest mysql build (5.0.3) now supports standard backslash behavior,
using the below config option.
set-variable=sql-mode=PIPES_AS_CONCAT,ANSI_QUOTES,NO_BACKSLASH_ESCAPE
PG seems to be the last holdout. :-)
Ken
Ken Johanson wrote:
[snip]
I think most people agree that being SQL compliant
Uh, yea, this is going to require quite a bit of discussion in the
group, and I am concerned how it will affect other apps using
PostgreSQL. (The mode isn't going to be useful if it breaks plug-in
extensions and stuff.)
The hard part of this isn't turning off backslash quoting; the code
Martijn van Oosterhout wrote:
On Sun, Feb 27, 2005 at 06:25:18PM -0700, Ken Johanson wrote:
I feel somewhat confident (very actually) that a config option that
disabled the backslash behavior globally(*) would be acceptable, BUT
leave the current backslash behavior turned
I'm a little worried about PostgreSQL having the same problems as PHP.
In PHP, every time you want to download an application, you never see
This application works on php 4+. Instead, you see This application
works on php4+ with the following config options set long list.
Sometimes these
Bruno Wolff III wrote:
On Mon, Feb 28, 2005 at 10:13:00 -0700,
Ken Johanson [EMAIL PROTECTED] wrote:
Besides, the version-deprecation / version requirements you mention
exists in every piece of software I've even seen. Sometime they're okay
with a really old version, sometime only the newest
[snip]
I think most people agree that being SQL compliant is good. The question
is: is it worth the pain for existing users?
My guess is that it is worth it, if the users are given the discretion
of treading that water.. and to save them future pain by encouraging
them to migrate toward
Bruce Momjian wrote:
Ken Johanson wrote:
Greetings,
Does anyone know if it will be possible to run the server with ANSI/ISO
string escaping instead of C-style escapes? The C style escaping is a
shoot-down for our adoption of postgres, since its non-standard.
Not yet
57 matches
Mail list logo
