I'm looking at these two pages:
http://www.postgresql.org/docs/9.1/static/ecpg-sql-set-autocommit.html
http://www.postgresql.org/docs/9.1/static/sql-start-transaction.html
According to the first page, autocommit is off by default for
"embedded SQL programs". Does this mean everything except the '
Chris Angelico wrote:
> I'm looking at these two pages:
>
> http://www.postgresql.org/docs/9.1/static/ecpg-sql-set-autocommit.html
> http://www.postgresql.org/docs/9.1/static/sql-start-transaction.html
>
> According to the first page, autocommit is off by default for
> "embedded SQL programs". Do
On Tue, Aug 21, 2012 at 05:29:14PM -0400, Michael Clark wrote:
> For example, if I insert like so:
> INSERT INTO sometable (startdate) values ('1750-08-21 21:17:00+00:00');
>
> I get the following when I select:
> SELECT startdate FROM sometable;
> startdate
> ---
Hi all;
So I found an interesting and relatively manageable way of doing this.
Suppose we have an inventory table:
CREATE TABLE inventory_item (
id serial primary key,
cogs_account_id int references account(id),
inv_account_id int references account(id),
income_account_id int ref
Craid and Pavel: thanks to you both for the responses.
Craig, this is for my PhD work, so no commercial interest at this point.
However, I'm pushing very hard at various communities for funding/support
for a Postgres based implementation of an EHR repository, that'll hopefully
benefit from my PhD
Does Postgres-XC support query parallelism (at least splitting the
query up for portions that run on different nodes)? They just
released 1.0. I don't know if this sort of thing is supported there
and it might be overkill at any rate.
Best Wishes,
Chris Travers
--
Sent via pgsql-general maili
Good Day,
I'm trying to figure out why a postgresql query doesn't return what I'd expect
with a query like this where there are NULL values:
select * from users where is_enabled<>'Y';
I'm expecting it to return all records where is_enabled is 'N' or NULL.
Perhaps my expectations are misguided
2012/8/22 Michael Sacket :
> Good Day,
>
> I'm trying to figure out why a postgresql query doesn't return what I'd
> expect with a query like this where there are NULL values:
>
> select * from users where is_enabled<>'Y';
>
> I'm expecting it to return all records where is_enabled is 'N' or NULL.
On 08/22/2012 06:23 AM, Michael Sacket wrote:
Good Day,
I'm trying to figure out why a postgresql query doesn't return what I'd expect
with a query like this where there are NULL values:
select * from users where is_enabled<>'Y';
I'm expecting it to return all records where is_enabled is 'N'
On Aug 22, 2012, at 9:23, Michael Sacket wrote:
> Good Day,
>
> I'm trying to figure out why a postgresql query doesn't return what I'd
> expect with a query like this where there are NULL values:
>
> select * from users where is_enabled<>'Y';
>
> I'm expecting it to return all records where
Thank you all very much!
Unfortunately I can't change the query... but I can modify the data. I updated
the NULL values to 'N' and put the appropriate NOT NULL constraint and a
default value of 'N'.
On Aug 22, 2012, at 8:37 AM, David Johnston wrote:
> On Aug 22, 2012, at 9:23, Michael Sacket
Le mercredi 22 août 2012 à 13:15 +0800, Craig Ringer a écrit :
> He appears to be suggesting that buying access to real hardware in a
> datacenter (if not buying the hardware yourself) is more cost effective
> and easier to manage than using "cloud" style services with more
> transient hosts li
elliott writes:
> Hi,
>
> I am using PostgreSQL 9.1 and loading very large tables ( 13 million
> rows each ). The flat file size is only 25M. However, the equivalent
> database table is 548MB. This is without any indexes applied and auto
> vacuum turned on. I have read that the bloat can be
On Mon, Aug 20, 2012 at 10:53 AM, elliott wrote:
> Hi,
>
> I am using PostgreSQL 9.1 and loading very large tables ( 13 million rows
> each ). The flat file size is only 25M.
That is only 2 bytes per row. Is the size given for the flat file for
a compressed file?
Cheers,
Jeff
--
Sent via p
Yes, it is a tif file. Uncompressed it is around 85M.
On 8/22/2012 1:20 PM, Jeff Janes wrote:
On Mon, Aug 20, 2012 at 10:53 AM, elliott wrote:
Hi,
I am using PostgreSQL 9.1 and loading very large tables ( 13 million rows
each ). The flat file size is only 25M.
That is only 2 byte
Hi Michael.
NULL is not any specific value. Thus Pg correctly doesnot tell you that it
is <>'Y'. It is NULL means that we dont know the value. Thus it may be 'Y'
as much as it may not be 'Y'. The comparison is not applicable in the case
of NULL and that's why there are the IS NULL and IS NOT NULL
Vincent, I would appreciate that you stop assuming things based on zero
information about what I am doing. I understand that you are trying to be
helpful, but I can assure you that going bare-metal only does not make any
sense in my context.
Sébastien
On Wed, Aug 22, 2012 at 12:44 PM, Vincent Vey
On Wed, Aug 22, 2012 at 12:25 PM, elliott wrote:
> Yes, it is a tif file. Uncompressed it is around 85M.
ok, 85 -> 548mb is reasonable considering you have very narrow rows
and an index that covers 2/3 of your column data. if you want to see
dramatic reduction in table size, you probably need
Just looking into High IO instances for a DB deployment. In order to get past
1TB, we are looking at RAID-0. I have heard
(http://hackerne.ws/item?id=4266119) there might be a problem if TRIM isn't
supported. Does anyone know if it is and has anyone used RAID-0 on these
instances? (Linux of cou
Hi
I noticed that 'avg' works on 'interval', but 'stddev' and 'variance' don't:
hack=> create table test (start_time timestamptz, end_time timestamptz);
CREATE TABLE
hack=> insert into test values (now(), now() + interval '1 second');
INSERT 0 1`
hack=> insert into test values (now(), now() + int
On 08/22/12 10:25 AM, elliott wrote:
Yes, it is a tif file. Uncompressed it is around 85M.
a tif file is a pixel map image, eg, graphics, no? I thought we were
talking about CSV data here?
--
john r pierceN 37, W 122
santa cruz ca mi
On Wednesday, August 22, 2012 04:10:01 PM Andrew Hannon wrote:
> Just looking into High IO instances for a DB deployment. In order to get
> past 1TB, we are looking at RAID-0. I have heard
> (http://hackerne.ws/item?id=4266119) there might be a problem if TRIM isn't
> supported. Does anyone know if
So we have a large TB database that we need to migrate to 9.1 and I'm
wondering if there's a way to do this process in stages.
Since the date/time storage types changes between 8.3 and 8.4, I
realize we'll have to dump the database and my plan is to create a
backup using pg_start_backup(), run tha
On 08/22/12 2:17 PM, Scott Briggs wrote:
So we have a large TB database that we need to migrate to 9.1 and I'm
wondering if there's a way to do this process in stages.
Since the date/time storage types changes between 8.3 and 8.4, I
realize we'll have to dump the database and my plan is to creat
On Wednesday, August 22, 2012 05:17:10 PM Scott Briggs wrote:
> So we have a large TB database that we need to migrate to 9.1 and I'm
> wondering if there's a way to do this process in stages.
>
> Since the date/time storage types changes between 8.3 and 8.4, I
> realize we'll have to dump the dat
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 another who
likes to call it "surname".So 3 dif
On 08/22/12 15:19, Gauthier, Dave wrote:
>
> I know a view can be used to alias one of them.
It can alias all of them:
create view xyz as
select *, last_name as lname, last_name as surname
from mytable;
(not the nicest version but functional)
HTH.
Bosco.
--
Sent via pgsql-general
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
Ooops! Hit send too fast...
On 08/22/12 15:34, Bosco Rama wrote:
> On 08/22/12 15:19, Gauthier, Dave wrote:
>>
>> I know a view can be used to alias one of them.
>
> It can alias all of them:
>
> create view xyz as
>select *, last_name as lname, last_name as surname
> from mytable;
>
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of John R Pierce
> Sent: Wednesday, August 22, 2012 4:32 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Database Bloat
>
> On 08/22/12 10:25 AM, elliott
On Wednesday, August 22, 2012 02:43:05 PM Alan Hodgson wrote:
> The most likely way to get this done is with Slony. Setup a Slony slave,
> upgrade the slave to 9.1 with a dump/reload, run it and let Slony catch it
> up, and then promote it to be the Slony cluster master and switch your
> clients ov
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:
~01001~^~0100~^~Butter, salted~^~BUTTER,WITH
SALT~^~~^
On 08/23/2012 12:48 AM, Wells Oliver wrote:
Hey, thanks for your feedback. Just to clarify: pg_total_relation_size
returns bytes, correct?
Please reply to the list, not directly to me.
Yes, pg_total_relation_size returns bytes. The documentation
(http://www.postgresql.org/docs/9.1/static/func
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 Wed, Aug 22, 2012 at 5:34 PM, Bosco Rama wrote:
> On 08/22/12 17:23, 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
For me seems to be CSV, but you have "~" in place of double quotes, and
"^" as separator.
Regards,
Edson.
Em 22/08/2012 21:23, Mike Christensen escreveu:
I'd like to import this data into a Postgres database:
http://www.ars.usda.gov/SP2UserFiles/Place/12354500/Data/SR24/dnload/sr24.zip
How
On Thu, Aug 16, 2012 at 1:54 PM, Wells Oliver wrote:
> Hey folks, a question. We have a table that's getting large (6 million rows
> right now, but hey, no end in sight).
Does it grow in chunks, or one row at a time?
> It's wide-ish, too, 98 columns.
How many of the columns are NULL for any giv
On 08/22/12 17:23, 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:
On 08/22/2012 09:37 PM, David Johnston wrote:
On Aug 22, 2012, at 9:23, Michael Sacket wrote:
Good Day,
I'm trying to figure out why a postgresql query doesn't return what I'd expect
with a query like this where there are NULL values:
select * from users where is_enabled<>'Y';
I'm expectin
On 08/22/12 5:40 PM, David Johnston wrote:
The first delimiter is ~^~ (tilde-carat-tilde)
The last field is itself delimited with just ^ (carat)
simpler than that, ~ is the QUOTE character, ^ is the field delimiter :)
--
john r pierceN 37, W 122
santa cruz ca
On 08/22/2012 10:58 PM, Michael Sacket wrote:
Thank you all very much!
Unfortunately I can't change the query... but I can modify the data. I updated
the NULL values to 'N' and put the appropriate NOT NULL constraint and a
default value of 'N'.
What tool/app is generating the query? They n
On 08/22/12 17:41, Mike Christensen wrote:
> On Wed, Aug 22, 2012 at 5:38 PM, Mike Christensen wrote:
>> On Wed, Aug 22, 2012 at 5:34 PM, Bosco Rama wrote:
>>> On 08/22/12 17:23, Mike Christensen wrote:
I'd like to import this data into a Postgres database:
http://www.ars.usda.go
On Wed, Aug 22, 2012 at 5:38 PM, Mike Christensen wrote:
> On Wed, Aug 22, 2012 at 5:34 PM, Bosco Rama wrote:
>> On 08/22/12 17:23, 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.
On 08/22/12 5: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:
this
Hi;
I was noticing that when storing nested data in PostgreSQL, that both
CHECK and NOT NULL constraints are not fired.
It seems like this is a case where inheritance provides a cleaner way
to incorporate re-usable data structures (with internal integrity
enforcement and method mapping) in the d
The first delimiter is ~^~ (tilde-carat-tilde)
The last field is itself delimited with just ^ (carat)
I would use text parsing tools to do this myself though various commands in
PosegreSQL could be combined to get the desired result. The last 4 numbers
(second parse) should probably be stored
On 08/23/2012 06:41 AM, Bosco Rama wrote:
Ooops! Hit send too fast...
On 08/22/12 15:34, Bosco Rama wrote:
On 08/22/12 15:19, Gauthier, Dave wrote:
I know a view can be used to alias one of them.
It can alias all of them:
create view xyz as
select *, last_name as lname, last_name as s
On Wed, Aug 22, 2012 at 5:57 PM, John R Pierce wrote:
> On 08/22/12 5: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 thi
On Aug 22, 2012, at 8:17 PM, Craig Ringer wrote:
> On 08/22/2012 10:58 PM, Michael Sacket wrote:
>> Thank you all very much!
>>
>> Unfortunately I can't change the query... but I can modify the data. I
>> updated the NULL values to 'N' and put the appropriate NOT NULL constraint
>> and a defa
On 08/23/2012 10:32 AM, Michael Sacket wrote:
The good news is I now have the proper constraints in place and the app and
it's 130 tables are working with PostgreSQL in less than a day.
Wow, that's cool, especially without SQL changes.
What was the previous database? I'm curious now.
--
Cra
On 08/22/2012 05:41 PM, Mike Christensen wrote:
On Wed, Aug 22, 2012 at 5:38 PM, Mike Christensen wrote:
On Wed, Aug 22, 2012 at 5:34 PM, Bosco Rama wrote:
On 08/22/12 17:23, Mike Christensen wrote:
I'd like to import this data into a Postgres database:
http://www.ars.usda.gov/SP2UserFiles/
On 08/22/12 7:17 PM, Mike Christensen wrote:
This is exactly what I needed. Thanks so much.. Already got the
first table imported..
I think would use 'TEXT' for the string fields, INTEGER for the whole
numbers and NUMERIC for the fractional ones...
once you have the data imported, and define
On Thu, Aug 16, 2012 at 2:54 PM, Wells Oliver wrote:
> Hey folks, a question. We have a table that's getting large (6 million rows
> right now, but hey, no end in sight). It's wide-ish, too, 98 columns.
>
> The problem is that each of these columns needs to be searchable quickly at
> an applicatio
I have now been working with table inheritance for a while and after
starting to grapple with many of the use cases it has have become
increasingly impressed with this feature. I also think that some of
the apparent limitations fundamentally follow from the support for
multiple inheritance, and mu
On Thu, Aug 23, 2012 at 8:19 AM, Gauthier, Dave wrote:
> 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 another who li
>> This is exactly what I needed. Thanks so much.. Already got the
>> first table imported..
>
>
> I think would use 'TEXT' for the string fields, INTEGER for the whole
> numbers and NUMERIC for the fractional ones...
> once you have the data imported, and define the appropriate field of each
> t
On 08/23/2012 11:56 AM, Chris Angelico wrote:
On Thu, Aug 23, 2012 at 8:19 AM, Gauthier, Dave wrote:
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
On Thu, Aug 23, 2012 at 2:19 PM, Craig Ringer wrote:
> On 08/23/2012 11:56 AM, Chris Angelico wrote:
>>
>> Here's an out-of-the-box suggestion.
>>
>> Drop the column altogether and have a single column "name". Trying to
>> divide names up never works properly. Does "surname" mean family name?
>> N
On Aug 22, 2012, at 23:22, Chris Travers wrote:
> * unique constraints being able to be marked INHERIT or NOINHERIT.
> A unique constraint that is marked INHERIT would be automatically
> created again on the child table. This could be documented to be
> domain-specific to each child table, and
Chris Travers, 23.08.2012 05:22:
The fact that this allows you to create essentially derived values
from groups of re-used columns is itself remarkable and can be used
to implement path traversal etc. which is not directly supported in
PostgreSQL in the sense that it is in Oracle or DB2.
What e
Mike Christensen, 23.08.2012 02:41:
Oh, also if anyone knows of a way to export an Access database to
Postgres, that might be helpful. I don't have a copy of Access.
If you have a Windows box, you can try SQL Workbench/J. Even though it is a
Java application it can connect to an Access databas
I have a table with 40 million rows and haven't had any performance issues yet.
Are there any rules of thumb as to when a table starts getting too big?
For example, maybe if the index size is 6x the amount of ram, if the table is
10% of total disk space, etc?
--
Sent via pgsql-general mailing
63 matches
Mail list logo
