On 04/15/2014 09:53 AM, Robert DiFalco wrote:
Actually that was exactly the initial table design. There were more
fields because for my use case there were a lot more states and
certain states have additional data (for example when a call goes from
answered to connected it also gets the user_id
Not to say that perl and complex are redundant, but does the id go away
after the NOT FOUND exception?
On 04/16/2014 06:08 PM, Susan Cassidy wrote:
The function does a select to see if the id number exists, and it
fails. NOT FOUND causes a RAISE EXCEPTION.
Susan
On Wed, Apr 16, 2014 at 5:
Why do you think you need an array of theType v. a dependent table of theType.
This tack is of course immune to to most future type changess.
Sent from my iPhone
> On Apr 20, 2014, at 11:57 AM, Dorian Hoxha wrote:
>
> Was just curious about the overhead.
>
> I know the columns, but i may nee
On 05/13/2014 08:48 AM, Vegard Bønes wrote:
Hi,
I have a database on a test server with queries that perform terribly. Trying
to fix this problem, I copied the database (using pg_dump) to my laptop, and
reran tests there. The same queries perform perfectly on my laptop.
I have tried to use th
On 04/15/2011 05:36 PM, Tom Lane wrote:
Basil Bourque writes:
When a trigger calls a function, that function can determine if it is being called from
within a trigger by testing for a value in the special variable "TG_OP".
Uh, no, not really. TG_OP is a local variable in the trigger funct
On 04/22/2011 09:16 AM, Geoffrey Myers wrote:
Vick Khera wrote:
On Fri, Apr 22, 2011 at 11:00 AM, Geoffrey Myers
mailto:li...@serioustechnology.com>> wrote:
Here's our problem. We planned on moving databases a few at a time.
Problem is, there is a process that pushes data from one database to
On 04/26/2011 04:33 PM, Tom Lane wrote:
luda posch writes:
I am not an expert on the official sql spec, but I think it would be useful
if distinct on() could be used within an aggregate when supplied with
another column name. For example:
select sum(distinct on(id) order_price) from order
On 04/27/2011 12:45 PM, SUBHAM ROY wrote:
I am using postgres 8.4.8.
|EXPLAIN (ANALYZE,BUFFERS)| is not working. So is there any patch
available for it? If so, kindly specify from where to get it and how to
install it.
--
Thank You,
Subham Roy,
CSE IIT Bombay.
Have you tried just plain
exp
On 04/28/2011 11:44 AM, Andy Colson wrote:
On 4/28/2011 12:29 PM, Jim Irrer wrote:
A colleague of mine insists that using surrogate keys is the
common practice by an overwhelming margin in relational databases and
that they are used in 99 percent of large installations. I agree that
many
situa
re: 1 and 2. They're horrible (imho) reference to the attributes of the
returned tuple. Or at best an exposure of the implementation. :)
Order by "2" if you want the most frequent (highest counts) of your
distances at the bottom of the output (or ordery by 2 desc) if you want
them at the top o
David Johnston wrote:
Given that you are actively implementing the code that uses the 1 and 2 I
don't see how it is that egregious. When generating calculated fields it is
cleaner than the alternative:
Select trunc(distance * 10.)/10., count(*)
From doc_ads
Group by (trunc(distance * 10.))
Or
Jeff Davis wrote:
On Mon, 2011-05-02 at 11:10 -0400, Greg Smith wrote:
The position Merlin
has advocated here, that there should always be a natural key available
if you know the data well enough, may be true. But few people are good
enough designers to be sure they've made the decision cor
John R Pierce wrote:
otoh, there's plenty of places where natural keys are optimal. my
company makes widgets, and we make damn sure our serial #s and part
numbers are unique, and we use them as PK's for the various tables.
further, the PN has a N digit prefix which is unique to a part fam
Craig Ringer wrote:
On 03/05/11 11:07, Greg Smith wrote:
That doesn't mean you can't use
them as a sort of foreign key indexing the data; it just means you can't
make them the sole unique identifier for a particular entity, where that
entity is a person, company, or part.
Classic ca
On 05/03/2011 12:51 PM, Jeff Davis wrote:
On Mon, 2011-05-02 at 20:06 -0600, Rob Sargent wrote:
Jeff Davis wrote:
In particular, I think you are falsely assuming that a natural key must
be generated from an outside source (or some source outside of your
control), and is therefore not
On 05/03/2011 03:08 PM, Jeff Davis wrote:
On Tue, 2011-05-03 at 13:35 -0600, Rob Sargent wrote:
Sorry, but I'm confused, but that's common. Isn't a "natural key" to be
compose solely from the attributes of the entity? As in a subset of the
columns of the table i
Tarlika Elisabeth Schmitz wrote:
I have a database that will be populated solely by CSV import.
There are several CSV file formats, all denormalized.
I have created interim tables which match the CSV file formats. An
insert trigger distributes the data to their appropriate destination
tables.
Craig Ringer wrote:
On 1/06/2011 4:52 AM, Pete Chown wrote:
Is there a solution to this, or is the point that I'm simply asking too
much? Perhaps the Java EE container is not promising consistency in the
sense I'm talking about.
Distributed transactions will give you atomicity if done right
Nick Raj wrote:
I am implementing some pl/pgsql functions.
Is there any way to change the input
for example- I got some value by $1. I want to modify this value
(means split that value), Can we do this and how?
Second thing,
Suppose i defined a function test as
select test('geom',the_geom,
jonathansfl wrote:
Using PG 8.4.2 with Ubuntu 10.04.2 LTS.
Inside a cursor of FUNCTION-A, I call another function (FUNCTION-B).
Function-B has a "Drop ,Table" command followed by a CREATE TEMPORARY TABLE
command.
The cursor loops but when it runs out, it breaks, giving error: "Cannot DROP
T
Seems to me a lot of the needed standard libraries are in '/lib64' and
the linker isn't looking for them there? So far I've hit libreadline
and libz. Do I just keep making the sim-links into /usr/lib64?
postgres 9.0.3 on 11.2 worked like a champ. Unforturnately I've
upgraded my os, thought I wo
On 06/21/2011 06:49 PM, Tom Lane wrote:
> Rob Sargent writes:
>> Seems to me a lot of the needed standard libraries are in '/lib64' and
>> the linker isn't looking for them there? So far I've hit libreadline
>> and libz. Do I just keep making the si
Tom Lane wrote:
Rob Sargent writes:
The tail of config.log follows. As I confessed, libreadline had to be
simlinked into /usr/lib64, then I hit libz (as this log shows) and
started to doubt myself (even more).
configure:8316: checking for library containing readline
configure
On 06/22/2011 08:02 AM, Merlin Moncure wrote:
> On Wed, Jun 22, 2011 at 5:40 AM, Wim Bertels wrote:
>> Hallo,
>>
>> does anyone have know of a free CRUD generator
>> for generating plpgsql functions for doing CRUD operations on all the
>> tables of a database or schema or just one table or..
>>
On 06/21/2011 06:58 PM, Rob Sargent wrote:
>
>
> On 06/21/2011 06:49 PM, Tom Lane wrote:
>> Rob Sargent writes:
>>> Seems to me a lot of the needed standard libraries are in '/lib64' and
>>> the linker isn't looking for them there? So far I&
On 06/22/2011 09:10 AM, Merlin Moncure wrote:
> On Wed, Jun 22, 2011 at 9:59 AM, Rob Sargent wrote:
>> On 06/22/2011 08:02 AM, Merlin Moncure wrote:
>>> On Wed, Jun 22, 2011 at 5:40 AM, Wim Bertels
>>> wrote:
>>>> Hallo,
>>>>
>>&
On 06/22/2011 10:27 AM, Rob Sargent wrote:
>
>
> On 06/21/2011 06:58 PM, Rob Sargent wrote:
>>
>>
>> On 06/21/2011 06:49 PM, Tom Lane wrote:
>>> Rob Sargent writes:
>>>> Seems to me a lot of the needed standard libraries are in '/lib64&#
On 06/22/2011 10:27 AM, Rob Sargent wrote:
>
>
> On 06/21/2011 06:58 PM, Rob Sargent wrote:
>>
>>
>> On 06/21/2011 06:49 PM, Tom Lane wrote:
>>> Rob Sargent writes:
>>>> Seems to me a lot of the needed standard libraries are in '/lib64&#
Eduard-Cristian Stefan wrote:
I have PostgreSQL 9.0.4-1 running as a service on Windows XP Home
Edition,
with the command line of the service being:
D:\me\usr\PostgreSQL\bin/pg_ctl.exe runservice -N "pgsql" -D
"d:/me/etc/PostgreSQL"
In the postgresql.conf file I have the following sett
OpenSuse 11.4 x86-64
gmake install builds and places the requisite pieces as expected.
Running
psql --username postgres -d postgres -f xml2--1.0,sql
results in
psql:xml2--1.0.sql:8: ERROR: function "xml_valid" already exists
with same argument types
psql:xml2--1.0.sql:12: ERROR
Guillaume Lelarge wrote:
On Fri, 2011-06-24 at 11:22 -0600, Rob Sargent wrote:
OpenSuse 11.4 x86-64
gmake install builds and places the requisite pieces as expected.
Running
psql --username postgres -d postgres -f xml2--1.0,sql
results in
psql:xml2--1.0.sql:8: ERROR
On 06/25/2011 01:10 AM, Guillaume Lelarge wrote:
> On Fri, 2011-06-24 at 11:22 -0600, Rob Sargent wrote:
>> OpenSuse 11.4 x86-64
>>
>> gmake install builds and places the requisite pieces as expected.
>>
>> Running
>> psql --username postgres -d
I think Greg might be forgetting that some of us don't always get to
choose what we work on. I was in a shop that decided to go with
multi-tenancy for reason both technical and um, er envious. One schema
to update versus n, for an example of the former. Amazon does it, for
the other example. But
On 06/28/2011 04:52 PM, Greg Smith wrote:
> On 06/28/2011 05:45 PM, Rob Sargent wrote:
>> I think Greg might be forgetting that some of us don't always get to
>> choose what we work on. I was in a shop that decided to go with
>> multi-tenancy for reason both te
On 06/28/2011 04:13 PM, Grace Batumbya wrote:
> The installer for windows for 64bit versions of postgresql doesn't
> include ossp-uuid.sql.
> Does anyone know where or how to get this?
>
> Thanks
> --
> *Grace Batumbya*
> Research Assistant | Seneca CDOT
> Phone: 416-491-5050 x3548
> cdot.seneca
On 06/28/2011 06:29 PM, Ben Carbery wrote:
> Hi,
>
> I am trying to EXECUTE .. INTO a variable that I want to be
> dynamically named.
>
> stuff := '{a,b,c,d}';
>
> FOR i IN 1..4 LOOP
> thing := stuff[i];
>
> -- stuff_a, stuff_b etc are functions - substitution works here
>
Ben Carbery wrote:
Hm, "a" isn't a variable,it's the value of stuff[1].
It's both..
DECLARE
a integer;
b integer;
The point is I want a dynamically named variable. Here I've named them
the same as stuff[i] but they can be anything provided it is a
different variable name on
Deniz Atak wrote:
Hi all,
I am using postgresql on Glassfish server. When I restart the server
for some reason, one of my databases' tables duplicates the values.
For example aTable is from aDatabase:
select * from aTable
col1 | col2
---+-
text/html
On 07/27/2011 12:38 PM, Ioana Danes wrote:
> Thanks a lot Sebastian
>
> --- On Wed, 7/27/11, Sebastian Jaenicke
> wrote:
>
>> From: Sebastian Jaenicke
>> Subject: Re: [GENERAL] error when compiling a c function
>> To: "Ioana Danes"
>> Cc: "PostgreSQL General"
>> Received: Wednesday, July 27,
I've inherited some version 9.0 databases which make use of the uuid
data type but which do not have the uuid-generate-vx() functions.
These installations are running on openSUSE 11.3 (x86_64) and were
installed (probably) from yast. I've loaded the requisite share-libs
(also via yast) (/usr/lib64
I've inherited some version 9.0 databases which make use of the uuid
data type but which do not have the uuid-generate-vx() functions.
These installations are running on openSUSE 11.3 (x86_64) and were
installed (probably) from yast. I've loaded the requisite share-libs
(also via yast) (/usr/lib64
On 08/05/2011 05:26 PM, Tom Lane wrote:
> Rob Sargent writes:
>> I've inherited some version 9.0 databases which make use of the uuid
>> data type but which do not have the uuid-generate-vx() functions.
>> These installations are running on openSUSE 11.3 (x86_64) and w
On 08/05/2011 05:26 PM, Tom Lane wrote:
> Rob Sargent writes:
>> I've inherited some version 9.0 databases which make use of the uuid
>> data type but which do not have the uuid-generate-vx() functions.
>> These installations are running on openSUSE 11.3 (x86_64) and w
Pavel Stehule wrote:
Hello
there is not any possibility to change boolean output format.
Regards
Pavel Stehule
2011/8/6 Henry House :
Is there a way to set the display format of boolean values in psql just
as one can set the display of nulls using \pset null ? I
find presentation of tru
On 08/12/2011 10:04 AM, George MacKerron wrote:
> Hi all.
>
> I have a function returning setof record. The name of a table it acts on is
> one of its input variables, and its output is a set of rows from that table.
> E.g. for simplicity, imagine it's this pointless function:
>
> create or repla
W. Matthew Wilson wrote:
I'm sure I'm not the first person to end up with a gigantic query that
does lots of left joins and subselects.
It seems to work, but I would love to break it up into smaller chunks.
I'm thinking about rewriting the query to make several temporary
tables that are dropp
Apologies if this is the wrong forum.
If there has been a suggestion to get the "Up" hyperlink placed also
at/near the top of the page, please add my vote. Else could this be
considered as a feature request?
rjs
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make chan
Bruce Momjian wrote:
Rob Sargent wrote:
Apologies if this is the wrong forum.
If there has been a suggestion to get the "Up" hyperlink placed also
at/near the top of the page, please add my vote. Else could this be
considered as a feature request?
I have talked to Peter
On 08/26/2011 02:16 PM, Merlin Moncure wrote:
> On Fri, Aug 26, 2011 at 3:01 PM, Bobby Dewitt wrote:
>> Is there a way to get output from a SQL command or function and have it be
>> set to a variable value in psql? My main goal is to execute a certain
>> script depending on the version of the d
On 09/21/2011 11:34 PM, Mike Christensen wrote:
[ much omitted ]
> One of the major components I'm working on is this reporting engine
> that runs these major huge expensive queries (seriously, some of them
> take 2-3 minutes to run) to generate data that gets dumped to an Excel
> file for VPs t
On 09/28/2011 08:34 AM, Tom Lane wrote:
> Andrew Sullivan writes:
>> On Wed, Sep 28, 2011 at 06:20:04AM -0700, David Fetter wrote:
>>> There's an even better reason not to use rules: they're going away in
>>> a not too distant version of PostgreSQL.
>> Really? How? I thought views were done us
Rohit Coder wrote:
PgSQL has just one old NPGSQL driver for .NET, which is itself
sluggish. The ODBC driver works better as compared to NPGSQL, but I
suspect the ODBC driver is not the right choice for ORM framework of .NET.
I want to know whether there is any efficient .NET provider and is
P
On 10/10/2011 05:52 PM, Rich Shepard wrote:
> On Mon, 10 Oct 2011, John R Pierce wrote:
>
>> the complication is, there can be more than one date with the same
>> maximum
>> value, so such a query would be ambiguous, or it would return multiple
>> rows.
>
> John,
>
> The likelihood of that is d
Scott Marlowe wrote:
On Sun, Oct 16, 2011 at 2:41 PM, Szymon Guz wrote:
Hi,
just a couple of questions:
will there be an index bloat if I have:
- a serial column and only add rows to the table?
- a text column and I only add rows to the table?
For the serial column the numbers are only inc
On 07/06/2012 03:34 PM, Perry Smith wrote:
Hi Guys,
This isn't a PostgreSQL specific question but just a SQL question. If this is
not an appropriate question for this list, please let me know.
It is also, perhaps, a really silly question.
This query (without the 'explain' keyword) , when exe
On 07/09/2012 04:48 PM, Rich Shepard wrote:
Source data has duplicates. I have a file that creates the table then
INSERTS INTO the table all the rows. When I see errors flash by during the
'psql -d -f ' I try to scroll back in the terminal to
see where the duplicate rows are located. Too ofte
On 07/18/2012 12:07 PM, Bob Pawley wrote:
Hi
I would appreciate some fresh eyes on this expression -
update p_id.fluids
set fluid_short =
(select shape.text
from shape, num_search
where (select st_within(shape.wkb_geometry,
st_geometryn(num_search.the_geom4, 1)) = 'true')
On 08/10/2012 12:05 AM, John R Pierce wrote:
On 08/09/12 10:31 PM, Anibal David Acosta wrote:
I have a very big table, in fact only this table uses approx. 60%
space of disk.
The table is an standalone table (no one inherit from this and this is
not inherit from another).
I need to change a i
On 08/22/2012 04:19 PM, Gauthier, Dave wrote:
Here's the problem
I have a table with a column called "last_name". I have one customer
who likes to articulate queries and updates for this using column name
"last_name" (no problem there) but another who likes to call it "lname"
and yet anothe
On 08/22/2012 06:23 PM, Mike Christensen wrote:
I'd like to import this data into a Postgres database:
http://www.ars.usda.gov/SP2UserFiles/Place/12354500/Data/SR24/dnload/sr24.zip
However, I'm not quite sure what format this is. It's definitely not
CSV. Here's an example of a few rows:
~010
On 08/22/2012 06:23 PM, Mike Christensen wrote:
I'd like to import this data into a Postgres database:
http://www.ars.usda.gov/SP2UserFiles/Place/12354500/Data/SR24/dnload/sr24.zip
However, I'm not quite sure what format this is. It's definitely not
CSV. Here's an example of a few rows:
~010
On 08/24/2012 03:46 PM, John D. West wrote:
I have various background processes outside of postgres that need to be
killed and restarted after the server reboots.
-- John
On Fri, Aug 24, 2012 at 2:29 PM, Kevin Grittner
mailto:kevin.gritt...@wicourts.gov>> wrote:
"John D. West" mailt
On 09/18/2012 10:03 AM, Szymon Guz wrote:
On 18 September 2012 17:59, Robert Sosinski
mailto:rsosin...@ticketevolution.com>>
wrote:
We have a table, which has items that can be put on hold of 5
minutes (this is for an online store) once they are placed into a
cart. What we need is f
On 09/27/2012 12:50 PM, Ryan Kelly wrote:
What do you mean by "fewer faster cores"? Wouldn't "more faster cores"
be better?
I believe his point is core does not equal cpu.
Too often I've watched cpu performance meters with one core pegged and
the other(s) idle, since really it's one cpu. I s
On 11/20/2012 11:47 AM, Matthew Vernon wrote:
Matthew Vernon writes:
naiively, you might try:
\set pwd '\'' `pwd` '\''
COPY table FROM :pwd || '/relative/path/to/data' ;
I should also note that I want to run a series of these commands, hence
setting pwd once and then wanting to use it multip
On 11/20/2012 01:35 PM, Adrian Klaver wrote:
On 11/20/2012 11:36 AM, Rhys A.D. Stewart wrote:
Greetings,
I'm looking to obtain the name of a column that is passed to a function,
similar to xmlforest. Suggestions?
This is going to require some more information.
1) What language is the functio
On 11/20/2012 02:03 PM, Adrian Klaver wrote:
On 11/20/2012 12:51 PM, Rob Sargent wrote:
On 11/20/2012 01:35 PM, Adrian Klaver wrote:
On 11/20/2012 11:36 AM, Rhys A.D. Stewart wrote:
Greetings,
I'm looking to obtain the name of a column that is passed to a function,
similar to xmlf
On 12/20/2012 04:33 PM, Stephen Touset wrote:
On Dec 20, 2012, at 3:27 PM, Adrian Klaver wrote:
When on (the default), each SQL command is automatically committed upon
successful completion. To postpone commit in this mode, you must enter a BEGIN
or START TRANSACTION SQL command. When off o
The log from make shows
echo "#define PGBINDIR \"/opt/PostgreSQL/9.0/bin\"" >pg_config_paths.h
echo "#define PGSHAREDIR \"/opt/PostgreSQL/9.0/share/postgresql\""
>>pg_config_paths.h
echo "#define SYSCONFDIR \"/opt/PostgreSQL/9.0/etc/postgresql\""
>>pg_config_paths.h
echo "#
Then to get all statements would one simply set log_min_duration to some
arbitrarily small value?
On 10/01/2010 04:30 AM, Thom Brown wrote:
> 2010/10/1 Bjørn T Johansen :
>> We are using both DB2 and PostgreSQL at work and DB2 has a nice tool, i5
>> Navigator, where one can enable logging of SQL
On 11/04/2011 12:08 PM, Tom Lane wrote:
> "Ing.Edmundo.Robles.Lopez" writes:
>> Hi, I have a problem with psql, is very slow to connect. I Checked the
>> status of my network and the server and the client respond ok.
>
> First thing that comes to mind is DNS lookup problems. It's hard to
> spe
On 11/20/2011 09:24 AM, Tom Lane wrote:
> Rob Sargentg writes:
>> I just got round to updating my laptop to ubuntu-10.4 (32bit), in part
>> because I kept hitting snags while trying to configure postgres 9.1.1.
>> ubuntu-10.4 is not the latest of course and comes with gnu make 3.8.1,
>> but it
This may be a duplicate response.
On 11/20/2011 11:05 AM, Tom Lane wrote:
> Rob Sargent writes:
>> On 11/20/2011 09:24 AM, Tom Lane wrote:
>>> It appears that on Ubuntu, libbsd defines those symbols, which confuses
>>> configure into supposing that they're provide
On 12/06/2011 01:56 PM, Glyn Astill wrote:
> __
>
>> From: Merlin Moncure
>> To: Joe Miller
>> Cc: pgsql-general@postgresql.org
>> Sent: Tuesday, 6 December 2011, 17:30
>> Subject: Re: [GENERAL] PostgreSQL DBA in SPCE
>>
>> On Tue, Dec 6, 2011 at 10:56 AM,
e:
>> On Tue, Dec 6, 2011 at 2:20 PM, Rob Sargent wrote:
>>>
>>>
>>> On 12/06/2011 01:56 PM, Glyn Astill wrote:
>>>> __
>>>>
>>>>> From: Merlin Moncure
>>>>> To: Joe Miller
>&g
I would like the "file" command to tell me something other than "data",
yes even though I can tell by the name (and the directory of course).
Hoping someone has something I can slip into /usr/share/misc/magic.mgc
or that directory.
Along the same lines, what info is embedded in the file name? I se
On 01/24/2012 04:23 PM, Merlin Moncure wrote:
> On Tue, Jan 24, 2012 at 5:23 AM, panam wrote:
>> Wow, this is pretty useful. Just to fit it more to my original use case, I
>> used this:
>>
>> CREATE schema schema1;
>> CREATE schema schema2;
>> CREATE TABLE tbl (ID serial primary key,foo varchar,
On 02/23/2012 12:49 PM, Willem Buitendyk wrote:
Here are the log returns:
2012-02-23 11:31:44 PST WARNING invalid value for parameter "search_path":
"crabdata"
2012-02-23 11:31:44 PST DETAIL schema "crabdata" does not exist
Bizarre because I did set my search path to the schema c
On 03/12/2012 06:28 PM, Bret Stern wrote:
trying to update a varchar numeric string column
by converting it to int, adding a numeric value and insert it back
as a varchar
Having trouble with cast
Possibly having trouble with two casts: one from string to int, one
from int to string? You sql
I don't see anything to that effect in the release notes I've looked at.
I built 9.1.2 from source with these options.
CONFIGURE = '--with-libraries=/lib:/lib64:/lib/x86_64-linux-gnu'
'--with-python' '--with-openssl' '--with-ossp-uuid' '--with-libxml'
'--with-libxslt'
\df xpath*
On 03/14/2012 09:49 PM, Rob Sargent wrote:
On 03/14/2012 08:57 PM, Tom Lane wrote:
Rob Sargent writes:
I don't see anything to that effect in the release notes I've looked at.
That's cause it's still there. Did you remember to build/install
contrib/xml2? The xpath func
301 - 381 of 381 matches
Mail list logo
