On 08/03/2012 08:06 AM, Andreas Kretschmer wrote:
Hi all,
great feature, but i can't find a TIMERANGE, i want to store time-ranges, for
instance [10:00:00,16:00:00), how can i do that?
Regards, Andreas
Time ranges could be more complicated than you realize. You'd have
problems if you wante
I'm testing range types and I've come up with a couple of curiosities.
1) I'll start off easy. In the wild, discrete ranges tend to be
closed-closed [] while continuous ranges tend to be closed-open [). For
instance, on Tuesday stock traded at [28.34, 32.18] or Bob was employed
[2009-06-01, 20
On 07/17/2012 08:08 PM, Tom Lane wrote:
I wonder whether we could improve this by postponing the no-shell-types
check from creation to function runtime. It would be annoying to have
to make an additional catalog lookup at runtime just for typisdefined,
but I think that probably we could fold it
ry to alter it later.
-- Create the type any way
CREATE TYPE dt_range AS RANGE (
SUBTYPE = timestamptz,
SUBTYPE_DIFF = dt_subtype_diff
-- CANONICAL = dt_range_canonical -- can't use, fn doesn't exist
);
ALTER TYPE dt_range SET CANONICAL = dt_range_canonical;
This doesn't
ry to alter it later.
-- Create the type any way
CREATE TYPE dt_range AS RANGE (
SUBTYPE = timestamptz,
SUBTYPE_DIFF = dt_subtype_diff
-- CANONICAL = dt_range_canonical -- can't use, fn doesn't exist
);
ALTER TYPE dt_range SET CANONICAL = dt_range_canonical;
This doesn't
On 09/10/2010 08:07 PM, t...@exquisiteimages.com wrote:
I have a situation where I receive a file with transactions that have a
unique key from a vendor. These transactions should only be imported into
my system once, but the vendor system will occasionally resend a
transaction by mistake.
The w
oes anyone have
any advice as to how it may best be done?
Thanks
Scott Bailey
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
zhong ming wu wrote:
I have been using this one liner c function that I call my_now() to
get the number of seconds since some fixed point in the past. I find
it more convenient than built-in now()
and if I want abstime I do abstime(my_now()). Thing is everytime I
do a major version upgrade I h
junaid malik wrote:
Is there any alternative of mysql function COUNT(DISTINCT expr,
[expr...]) in postgres. We get error if we
write count like this count(distinct profile.id, profile.name,
profile.age) but it works well in mysql.
Reference url is given below
http://dev.mysql.com/doc/refman/5.
if a param has a default value, the function call may look like:
SELECT foo(default, 'hello', 43)
Scott Bailey
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Lee Hachadoorian wrote:
I work with state labor data which is reported to us in the form
industry, year, quarter1, quarter2, quarter3, quarter4
where each quarter represents an employment count. Obviously, this can
be normalized to
industry, year, quarter, employment
Can anyon
Tom Lane wrote:
Asher Hoskins writes:
I can't seem to get to_timestamp() or to_date() to work with quarters,
The source code says
* We ignore Q when converting to date because it is not
* normative.
*
* We still parse the so
dipti shah wrote:
Sorry Albe for confusion. Yes, I meant untrusted languages like C,
PL/PerlU, PL/PythonU etc...
Thanks a lot you and Tino for nice reply. Could you guys tell me how
could I verify whether those languages are installed on my PostGreSQL
server?
Thanks for being there,
Dipti
Eric B. Ridge wrote:
On Feb 18, 2010, at 4:31 PM, Scott Bailey wrote:
I'm not sure why you would be surprised by that behavior. You are grouping by a
timestamp, so any microsecond difference will be a new group.
I get that. ;) Hence the ::date. This is what doesn't make sense:
Eric B. Ridge wrote:
Maybe I'm getting too old to SQL anymore, but I ran across something yesterday in a machine generated query that took me over an hour to figure out.
Here's a little testcase. Maybe somebody can explain why the last "Not
Expected" case does what it does.
select version()
Does anyone know why plpython doesn't support returning record?
I couldn't find anything in the archives or source that indicated why it
wasn't supported. I'm trying to do Oracle style external tables and
xmltable and it would make it much easier if I could return setof record.
Scott
--
Sent
Marc Mamin wrote:
Hello,
Looking at the TODO List, I feel that only some aspects of the COPY FROM
command are adressed.
Could a discussion trigger some activity on this topic :o) ?
Best regards,
Marc Mamin
Here my wish list:
COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' |
Jamie Begin wrote:
I'm working on an e-commerce site that calls various plpgsql functions
from a Python app. One of the things I need to do is create a
shopping cart and add several items to it. I'd like for both of these
steps to be contained within the same transaction so if an error
occurs a
shulkae wrote:
We have few tables which we would like to convert to XML and store it.
Another requirement is to convert the stored XML file back to the
original tables. This helps us to clone a system. I was thinking to
use Perl XML Simple module to generate XML files.
How do I again re-create
Flash
Builder IDE is not.
Scott Bailey
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
My silly question is, do I needo to install a postgreSQL server or the freeradius-postgresql package already takes care of it?!
My question it's because it doesn't recognize any user ids and the createuser
command.
Yeah, that's pretty much the way things work on unix-like systems.
Instead
transaction. There may be problems with the freespace map or other
issues that I'm not aware of, but I'd probably at least try it if I was
going to lose my job or the company lose mega $ over it.
Scott Bailey
--
Sent via pgsql-general mailing list (pgsql-general@pos
John Oyler wrote:
I can create one or more domains, and use those to create the
composite type from. But each domain can only be constrained in its
own value, I can't constrain element #1's value based on what element
#2's value is.
If I create a domain from a composite, it will complain with a
I'm not sure which release you use, but it works for me (1.10 and 1.11). The
result of the first query is badly displayed (remember that it tries to
display an array of 10 integers), but the query returns something that
pgAdmin tries to display. The others work too but the display is muc
Pavel Stehule wrote:
Hello
2009/11/19 Scott Bailey :
On 8.4.0 I found that array_agg does not return a value when fed more than
12,000 values. (12,000 worked and 13,000 did not.)
can you send a query?
postgres=# create table f(a int);
CREATE TABLE
postgres=# insert into f select * from
probably document it.
Scott Bailey
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Sam Mason wrote:
On Thu, Nov 19, 2009 at 09:46:42AM -0800, Scott Bailey wrote:
We had an idx() function in the _int contrib module. I wonder if it
would be useful to write this in C now that _int is deprecated?
Is "idx" really the best name for this? there could be multiple
occurr
this was actually a pretty typical solution to dealing with arrays
until we got 'unnest()'. See information_schema._pg_expand_array for
example.
Oh I know. I was just having a laugh at the array_lover function. Now
that I think about it, we could replace array_agg() with array_orgy()
and un
Sam Mason wrote:
On Thu, Nov 19, 2009 at 05:24:33PM +0100, Pavel Stehule wrote:
it should be little bit more effective:
I'm not sure if it will be much more; when you put a set returning
function into a FROM clause PG will always run the function to
completion---as far as I know, but I've only
FROM generate_series(array_lover($1,1),array_upper($1,1)) g(i)
Pavel,
Don't get me wrong, I enjoy coding, but I think you've taken it too far
here ;)
Yes, definitely more effective for large arrays. Thanks. Would probably
be a good snippet for the wiki.
Scott
--
Sent via pgsql-gener
Pedro Doria Meunier wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
I'm trying to get the array position for a given match as thus:
This gets me the record for a given match:
SELECT * FROM garmin_units WHERE 'L' = ANY (protocol_tag);
Ok. so far so good...
But what about getting the
Tom Lane wrote:
Scott Bailey writes:
I'm trying to better understand the internals of Postgres, and I'm
looking at the enum type. The docs say that an enum value is stored on
disk as 4 bytes. But enum_send() returns a bytea representing the actual
text of the value and not the ind
the rows were much wider than I thought they would be, 28 bytes + 2 byte
spacer to store 4 bytes of data. Is there any way to see which bytes of
an item pointer actually map to columns in a table? And where can I find
more info on how Postgres stores tuples?
Scott Bailey
--
Sent via pgs
My personal feeling is that when you provide any ordering operator and
negation you can easily provide an absolute value operator. We've
already (somewhat arbitrarily) decided that one of '1month -30days' and
'-1month 30days) is "greater" than the other, so why not provide an
operator that retu
fferent. So his function and operator should be perfectly valid.
But there is some ambiguity around the length of a month. So INTERVAL '1
month - 30 days' = INTERVAL '0 days' = INTERVAL '-1 month +30 days'.
But when added to a date, it makes no change for months
MIN(stats.dist_dev) AS old_dev
FROM base, stats
WHERE base.i BETWEEN stats.dist_avg - dist_dev
AND stats.dist_avg + dist_dev
Scott Bailey
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
gative interval against INTERVAL '0
seconds' because you can have a positive interval that is a fraction of
a second.
But we've got two projects that implement a period data type, pgTemporal
and Chronos.
http://pgfoundry.org/projects/temporal/
http://pgfoundry.org/
Sim Zacks wrote:
I'm using 8.2.4
Numeric with scale precision always shows the trailing zeros.
Numeric plain only shows numbers after the decimal point that are being
used.
I would like to have the data in my table with scale and precision, but
my views to be cast to numeric without any scal
tom lane
You may be better off using hstore instead of straight arrays.
http://www.postgresql.org/docs/8.4/interactive/hstore.html
Scott Bailey
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Daniel Shane wrote:
Hi Scott!
The problem is that my test database has several tables with many links between
them, so I have no idea which 1000 rows to get from which table. The only thing
I can do is run the program that connects to that database and tell it to run
on a sample of the databa
Daniel Shane wrote:
Hi all!
I have an interesting problem here that I think could be of interest to everyone. I in the process of writing test cases for our applications and there is one problem I am facing. To be able to test correctly, I need to create a small database (a sample if you want) f
ted but still good.
http://www.xach.com/aolserver/mysql-to-postgresql.html
Scott Bailey
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
And I think that might work for you. The performance is going to be
miserable for large stat sets, because it's going to scan the whole
aaafacilities table every time and recompute every distance, but as an
example goes it's probably acceptable.
Something I did when implementing haversine in
to add the columns manually. You should be able to write a
function in a few minutes that walks a cursor and adds them to each
table in a given schema.
Scott Bailey
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql
I'm looking for the current state of XML capabilities in PostgreSQL and
I'm coming up with a lot of confusing links and a bit short on
documentation.
Postgres' XML is still lacking in a few spots. But the core
functionality is certainly there to do all of the every day stuff.
The trick with
please, try to look on function pg_typeof
Thanks Pavel. Just what I needed. But you're too late on the orafce
recommendation. I had already written it by the time you posted. I would
have written it any way though because Tom said I couldn't :)
You would need to write that in C.
Two probl
If I've got a function (sql or plpgsql) that takes anyelement as a
param, how do I determine the type name or oid that was actually passed
in? I figure there is probably a function for this but darn if I can
find it.
Specifically, I'm trying to make a function like Oracle's dump that will
tak
Hi!
How do you import data from an xml-file?
For instance, if I have a file like this:
Sonstiges
5
Buehne
2
Konzerte
1
Reggae
1
45
sonstige
5
44
... an
Dilyan Berkovski wrote:
Hi All,
I have a nasty table with many repeating columns of the kind port_ts_{i}_,
where {i} is from 0 to 31, and could be 3 different words.
I have made a pl/pgsql function that checks those columns from port_ts_1_status to
port_ts_31_status and counts something, howe
John DeSoi wrote:
Suppose I have an integer array (or cursor with one integer column)
which represents primary keys of some table. Is there a simple and
efficient way to return the rows of the table corresponding to the
primary key values and keep them in the same order as the array (or
cursor
I am trying to make sense of geometric literal syntax in and out of array
syntax. I cannot figure out a general rule: sometimes single quotes work,
sometimes double quotes work, and inside and outside of array literals the
rules are different an seemingly inconsistent.
Examples of all the we
On Wed, Aug 12, 2009 at 08:45:58PM -0700, Scott Bailey wrote:
CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement AS
$BODY$
SELECT $1[i] FROM
generate_series(array_lower($1,1),
array_upper($1,1)) i;
$BODY$
LANGUAGE 'sql' IMMUTABLE ST
V S P wrote:
if I have field declared
myvalue text[][]
insert into vladik (myval)
values
(
'{{"\",A", "\"B"}, {"Y", "Q"}}'
)
What do you guys use in your treasurechest of 'addons'
to successfully parse out the above trickery
and get
and get the 4 strings
",A
"B
Y
Q
from within Postgres store
Using arrays makes it a little less verbose and easier to manage IMO.
SELECT v FROM unnest(array['a','b','c','d']) v
Is that 8.4? or is unnest from contrib/ ?
thanks!
Dave
Unnest is included in 8.4, but it's pretty much essential for working
with arrays. Pre 8.4, you'd add the function
Sam Mason wrote:
On Sun, Jul 12, 2009 at 06:41:57PM +0100, Roy Walter wrote:
Scott Bailey wrote:
Roy Walter wrote:
How do I test for an empty array in postgres?
WHERE x != array[]::xml[]
Thanks Scott but that throws up a syntax error (at the closing bracket
of array[]):
ERROR
Scott Marlowe wrote:
On Sun, Jul 12, 2009 at 9:20 AM, dkeeney wrote:
Rdbhost.com offers SQL databases as a web service, running Postgresql
8.3.3 .
Create a database on our server, execute queries against it in SQL via
http request, and receive results as XML or JSON. There is a python
DB API m
Roy Walter wrote:
In postgres 8.4 When running xpath() queries it seems that empty results
are always returned. So if I query a table containing 1000 XML documents
a 1000 rows will always be fetched even if the xpath() element of the
query only matches 10 documents.
The documentation states:
Tim Uckun wrote:
I don't see any ubuntu packages for 8.4 in the default repositories.
Does anybody know if they will be upgrading the postgresql package to
8.4 or creating a new package for it.
I'd rather use the packages than to compile it myself. If anybody has
an argument as to why I should
Alvaro Herrera wrote:
Tim Keitt wrote:
I am combining query results that I know are disjoint. I'm wondering
how much overhead there is in calling union versus union all. (Just
curious really; I can't see a reason not to use union all.)
UNION needs to uniquify the output, for which it plasters
Some DB's say that an empty string is the same as null, it doesn't mean
they're right. In fact, it can be rather inconvenient if an empty string
in your data also has a meaning (namely 'known to be an empty string'
instead of 'unknown')!
This is the behavior in Oracle. And I found that out
Peter Eisentraut wrote:
On Friday 03 July 2009 06:09:37 Scott Bailey wrote:
I'm having trouble installing plpython in 8.4. I tried under Windows
(one click installer from EDB) and under Ubuntu (linux binary). In both
cases I was told:
could not load library 8.4/lib/postgresql/plpython.(s
I'm having trouble installing plpython in 8.4. I tried under Windows
(one click installer from EDB) and under Ubuntu (linux binary). In both
cases I was told:
could not load library 8.4/lib/postgresql/plpython.(so|dll)
Both systems have python 2.5 installed. And plpython was working in 8.3
(a
Scott Bailey writes:
I added the following lines to my postgresql.conf file:
custom_variable_classes = 'foo'
foo.name = '1s'
Now if I do "show foo.name" I get '1s'
But it does not show up in show all or in pg_settings.
No, it doesn't. It sho
I want to be able to change the behavior of some functions based on
custom runtime variables.
I added the following lines to my postgresql.conf file:
custom_variable_classes = 'foo'
foo.name = '1s'
Now if I do "show foo.name" I get '1s'
But it does not show up in show all or in pg_settings.
I
I noticed in the temporal project they used reserved words for their
functions (union, intersect, etc)
But when I try to create a function like that I get an error and I have
to quote it both when creating the function and when calling it. The
only difference I can see is they their functions
Grzegorz Jaśkiewicz wrote:
what difference does the (0) make than ? is timestamp() a function than ?/
The (0) is setting the precision. Telling it to store 0 places for the
fractional second. Much like setting scale and precision with numeric(6,2)
Scott
--
Sent via pgsql-general mailing list
I’m trying to connect ruby to postgres on ubuntu and the only link I
found that has the library is down. Does anyone have the postgres
library for ruby? Or direct me to it?
Yeah, I think this is an area that we need to address if we want to see
wider adoption of Postgres. I started out to wr
Pavel Stehule wrote:
2009/6/1 Grzegorz Jaśkiewicz :
That's one of things pg xml type lacks ... :/
yes - SQL/XML isn't completed yet
http://wiki.postgresql.org/wiki/XML_Support :(
I believe so some procedure like xml_to_table should be nice.
but plperlu code should be simple (as perl code sh
John R Pierce wrote:
Scott Bailey wrote:
Well at work we've got Oracle, MySQL, MS SQL and Postgres. So I
generally use Aqua Data Studio because it works with all of them. For
MySQL and MS SQL you register a single connection to the server and
can switch to any database. But with Postgres
John R Pierce wrote:
Scott Bailey wrote:
Is there an equivalent to psql's \connect database or MySQL/MS SQL's use
database command that will work with JDBC? It doesn't seem like I should
need to drop the connection and establish a new one just to switch
databases.
the \
Is there an equivalent to psql's \connect database or MySQL/MS SQL's use
database command that will work with JDBC? It doesn't seem like I should
need to drop the connection and establish a new one just to switch
databases.
Scott
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.
Grzegorz Jaśkiewicz wrote:
On Fri, May 29, 2009 at 3:37 AM, Scott Bailey wrote:
Did you read the article I sent you earlier?
Well, the difference here is that this way db doesn't really check
anything :) you just choose path of execution, that you created prior.
That's cheating :p
Grzegorz Jaśkiewicz wrote:
2009/5/27 Scott Bailey :
Who said anything about the application level?
can you give an example please ?
Did you read the article I sent you earlier? I'm doing almost the exact
same thing you are doing save the bytea field. I create a timespan
composite
Grzegorz Jaśkiewicz wrote:
well, I need database to guard data, not application.
Application can check things too, but database's job is to make sure
data is integral.
Who said anything about the application level?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
Scott Bailey wrote:
Grzegorz Jaśkiewicz wrote:
Why is it not possible to create domain on composite type ?
Consider the example, I got (a bytea, b timestamp, c timestamp). Where
b < c always, and both b and c have some default value, a can stay
null.
Now, I don't want to go berserk, an
Grzegorz Jaśkiewicz wrote:
Why is it not possible to create domain on composite type ?
Consider the example, I got (a bytea, b timestamp, c timestamp). Where
b < c always, and both b and c have some default value, a can stay
null.
Now, I don't want to go berserk, and create aditional table for t
I'd like to make an XMLTABLE() function, probably in plpython (not C).
And I'd like to follow the SQL/XML standard. So a function call should
look something like this:
SELECT *
FROM XMLTABLE('//node'
PASSING xmldoc
COLUMNS id INT PATH '@id',
name VARCHAR(30) PATH 'name')
Is this possib
Hi,
why column "acoltest" is not found by the subselect in this select:
SELECT
acol + 100 as acoltest,
(select max(t) from mytab where anothercol=acoltest) as col2
FROM mytab2
group by somet
???
Only columns belonging to a table can be used in a subselect??? What about
"calculated
Sebastien FLAESCH wrote:
Actually it's not limited to the usage of INTERVAL SECOND, I am writing
a PostgreSQL driver for our 4GL virtual machine...
I need to store all possible Informix INTERVAL types such as:
INTERVAL MONTH(8) TO MONTH
INTERVAL DAY(8) TO MINUTE
INTERVAL SECOND TO FRAC
79 matches
Mail list logo
