[GENERAL] Any Postgres experts not afraid of the camera?

http://meta.stackoverflow.com/questions/270574/an-experiment-stack-overflow-tv?cb=1

Re: [GENERAL] Regular expression question with Postgres

Yea looks like Postgres has it right, well.. per POSIX standard anyway. JavaScript also has it right, as does Python and .NET. Ruby is just weird. On Thu, Jul 24, 2014 at 1:57 PM, Tom Lane wrote: > Mike Christensen writes: > > I'm curious why this query returns 0: > > S

Re: [GENERAL] Regular expression question with Postgres

, David G Johnston < david.g.johns...@gmail.com> wrote: > Mike Christensen-2 wrote > > I'm curious why this query returns 0: > > > > SELECT 'AAA' ~ '^A{,4}$' > > > > Yet, this query returns 1: > > > > SELECT 'AAA'

[GENERAL] Regular expression question with Postgres

I'm curious why this query returns 0: SELECT 'AAA' ~ '^A{,4}$' Yet, this query returns 1: SELECT 'AAA' ~ '^A{0,4}$' Is this a bug with the regular expression engine?

Re: [GENERAL] WARNING: database must be vacuumed within 8439472 transactions

Sounds like you just have to wait until it finishes.. On Mon, Jul 7, 2014 at 12:56 PM, Prabhjot Sheena < prabhjot.she...@rivalwatch.com> wrote: > Hello >We are using postgresql 8.3 database for last 5 yrs for this > production database and its running fine. This is our critical database

Re: [GENERAL] PostgreSQL specific datatypes very confusing for beginners who use wrappers around JDBC

Oh. The CREATE CAST command. Wow, I was totally unaware of this entire feature! On Tue, Jan 28, 2014 at 3:36 PM, Mike Christensen wrote: > How do you create casts in Postgres? > > > On Tue, Jan 28, 2014 at 3:24 PM, Andrew Sullivan wrote: > >> On Tue, Jan 28, 2014 at 0

Re: [GENERAL] PostgreSQL specific datatypes very confusing for beginners who use wrappers around JDBC

How do you create casts in Postgres? On Tue, Jan 28, 2014 at 3:24 PM, Andrew Sullivan wrote: > On Tue, Jan 28, 2014 at 02:55:03PM -0800, Mike Christensen wrote: > > > I'd be curious as to what types of bugs were caused by these implicit > > casts.. > > Typically, t

Re: [GENERAL] PostgreSQL specific datatypes very confusing for beginners who use wrappers around JDBC

On Tue, Jan 28, 2014 at 2:46 PM, John R Pierce wrote: > On 1/28/2014 2:35 PM, Mike Christensen wrote: > >> This works. However, to agree with the original poster's point, if >> Postgres could be a little more forgiving about values that could be >> interpreted as

Re: [GENERAL] PostgreSQL specific datatypes very confusing for beginners who use wrappers around JDBC

I've had the same problem as well with NHibernate (On .NET) with Postgres ENUM types. Luckily, NHibernate is incredibly powerful and you *can* get everything working flawlessly, however it takes some serious digging into the source code and reading the docs to figure it out. The main issue is tha

Re: [GENERAL] Npgsql - Where can I find Npgsql.NpgsqlServices

ityFramework.dll in the EntityFramework/bin folder. > > I hope it helps. > > Let me know if you have any question. > > > > On Thu, Dec 12, 2013 at 1:24 AM, Mike Christensen wrote: > >> It seems I need NpgsqlServices to use Npgsql with EF6, however I can't >&g

[GENERAL] Npgsql - Where can I find Npgsql.NpgsqlServices

It seems I need NpgsqlServices to use Npgsql with EF6, however I can't figure out where you get this thing! I've tried installing it through NuGet: PM> Install-Package Npgsql -pre Installing 'Npgsql 2.0.14.1'. Successfully installed 'Npgsql 2.0.14.1'. Adding 'Npgsql 2.0.14.1' to EFTest. Successfu

Re: [GENERAL] Call for design: PostgreSQL mugs

Oooh can we make the handle an elephant trunk? (Ok, now I'm sure I'm adding all sorts of expense - but hey you'll save so much money using Postgres you can afford an expensive coffee mug!) On Thu, Sep 12, 2013 at 5:30 AM, Andreas 'ads' Scherbaum < adsm...@wars-nicht.de> wrote: > On 09/10/2013 10

Re: [GENERAL] Call for design: PostgreSQL mugs

How about something incredibly cheesy like SELECT * FROM Mug; On Mon, Sep 9, 2013 at 8:22 AM, Karsten Hilbert wrote: > Inside of the mug: > > - runs of 0's and 1's = data > - neatly aligned or in compartments/boxes/shelved ? > > Outside of mug: > > 10 elephants > >

Re: [GENERAL] function with unknown params

You passed in: 22/1/2013 Which is 22 divided by 1, divided by 2013 - which is an integer.. On Tue, Jul 9, 2013 at 10:17 AM, giozh wrote: > ok, it works. But why on error message i had that two unknown data type? if > was an error on date type, why it don't signal that? > > > > -- > View this

Re: [GENERAL] PERFORM statement

Ah ok that makes sense. The FAQ wasn't exactly clear about that. On Mon, Jul 8, 2013 at 9:38 PM, Tony Theodore wrote: > > On 09/07/2013, at 2:20 PM, Mike Christensen wrote: > > > PERFORM MyInsert(1,101,'2013-04-04','2013-04-04',2,'f' ); >

[GENERAL] PERFORM statement

I was reading about Postgres stored procs in the FAQ: https://wiki.postgresql.org/wiki/FAQ#Does_PostgreSQL_have_stored_procedures.3F It claims that an alternative syntax to: SELECT theNameOfTheFunction(arg1, arg2); Is: PERFORM theNameOfTheFunction(arg1, arg2); However, when I try the followin

Re: [GENERAL] Success stories of PostgreSQL implementations in different companies

On Thu, May 23, 2013 at 2:51 PM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > On 05/23/2013 02:36 PM, Oscar Calderon wrote: > >> Hi, this question isn't technical, but is very important for me to know. >> Currently, here in El Salvador our company brings PostgreSQL support, but >> Ora

Re: [GENERAL] dblink does not resolve DNS, but works with IP

Ah, gotcha! I guess whatever sample I was originally copying from used hostaddr for some reason.. Thanks for the clarification, Tom! On Wed, May 15, 2013 at 6:08 AM, Tom Lane wrote: > Mike Christensen writes: > > Though I'm a bit curious why there's a host and host

Re: [GENERAL] dblink does not resolve DNS, but works with IP

Though I'm a bit curious why there's a host and hostaddr. Why can't it just resolve whatever you give it? On Tue, May 14, 2013 at 9:31 PM, Mike Christensen wrote: > Excellent! Thanks so much. > > > On Tue, May 14, 2013 at 9:25 PM, Adrian Klaver wrote: > &

Re: [GENERAL] dblink does not resolve DNS, but works with IP

Excellent! Thanks so much. On Tue, May 14, 2013 at 9:25 PM, Adrian Klaver wrote: > On 05/14/2013 09:17 PM, Mike Christensen wrote: > >> If I have this: >> >> CREATE OR REPLACE VIEW Link.Foo AS >>select * from dblink( >> 'hostaddr=123.

[GENERAL] dblink does not resolve DNS, but works with IP

If I have this: CREATE OR REPLACE VIEW Link.Foo AS select * from dblink( 'hostaddr=123.123.123.123 dbname=KitchenPC user=Website password=secret', 'select * from Foo') as ... Then it works. However, if I do: CREATE OR REPLACE VIEW Link.Foo AS select * from dblink( 'hostaddr=db.d

Re: [GENERAL] how to create materialized view in postgresql 8.3

This is the number one requested feature on Uservoice: http://postgresql.uservoice.com/forums/21853-general/suggestions/247548-materialized-views On Mon, Apr 8, 2013 at 9:27 AM, John R Pierce wrote: > On 4/7/2013 11:58 PM, Zahid Quadri wrote: > > > is it possible to created materialized view

[GENERAL] Using Postgres ENUM types with NHibernate

HI all - I wrote a blog post on how my site takes advantage of Postgres ENUM types with NHibernate and Castle ActiveRecord. Thought I'd share: http://blog.kitchenpc.com/2012/12/21/using-enum-types-with-postgresql-and-castle-activerecord/ Mike PS - Lemme know if any of the Postgres stuff isn't

Re: [GENERAL] youtube video on pgsql integrity

On Thu, Nov 29, 2012 at 9:41 AM, Shaun Thomas wrote: > On 11/29/2012 07:48 AM, Ray Stell wrote: > > > http://www.youtube.com/watch?**v=1PoFIohBSM4 >> > > To be fair to MySQL, you can turn all that "helpfulness" off if you set > the SQL mode: > > https:

Re: [GENERAL] When does CLUSTER occur?

On Thu, Nov 29, 2012 at 10:28 AM, Shaun Thomas wrote: > On 11/29/2012 12:20 PM, Jeff Janes wrote: > > It would maintain an imperfect clustering, but still much better than >> current behavior. >> > > I thought about that, too. The "imperfect clustering" made me erase > everything I'd written. If

Re: [GENERAL] When does CLUSTER occur?

On Thu, Nov 29, 2012 at 9:33 AM, Shaun Thomas wrote: > On 11/29/2012 11:28 AM, Mike Christensen wrote: > > It's always kinda annoyed me that the CLUSTER command in Postgres >> doesn't work like it does on Microsoft SQL. >> > > It's a natural side-effect

Re: [GENERAL] When does CLUSTER occur?

On Thu, Nov 29, 2012 at 9:19 AM, Jeff Janes wrote: > On Thu, Nov 29, 2012 at 8:11 AM, Schnabel, Robert D. > wrote: > > > My question is whether or not the “ALTER TABLE gen1011 CLUSTER ON > > xgen1011_si_sn” actually clusters the table at that point or if it just > > tells it to use that index fo

Re: [GENERAL] Boolean type storage format

It would also matter what columns were next to it, correct? For example, if you had 4 bools in a row, that could also be 1 byte.. On Wed, Oct 31, 2012 at 11:08 AM, Raghavendra < raghavendra@enterprisedb.com> wrote: > On Wed, Oct 31, 2012 at 8:52 PM, Alexander Gataric wrote: > >> What is the

Re: [GENERAL] Great site for comparing databases (or anything else)

don't, but hopefully the overall results would be accurate. Actually one of the reasons I posted it here was because I was hoping more database experts could weigh in with their opinions.. Mike On Sat, Oct 27, 2012 at 4:00 PM, Stephen Cook wrote: > On 10/27/2012 5:18 PM, Mike Chris

[GENERAL] Great site for comparing databases (or anything else)

Just came across this one: http://hammerprinciple.com/databases/items/mysql/postgresql mySQL is great for embedding in applications though? Have they not read Oracle's license? Mike

Re: [GENERAL] moving from MySQL to pgsql

On Thu, Oct 11, 2012 at 9:38 AM, Gavin Flower wrote: > On 12/10/12 04:39, Merlin Moncure wrote: > >> On Thu, Oct 11, 2012 at 4:44 AM, Vineet Deodhar >> wrote: >> >>> Thanks all for your replies. >>> This is my first experience with postgres mailing list. >>> Hats Off to the active community of p

Re: [GENERAL] Counting all results before LIMIT

You could use a windowing function. Something like: SELECT x, y, z, COUNT(*) OVER() FROM Foo LIMIT 50; On Fri, Oct 5, 2012 at 8:02 AM, P Gouv wrote: > You cant. There is an article about count performance. Generally its slow > but latest version 9.2 i think supports index for count under some

[FIXED] Re: [GENERAL] Installing uuid-ossp on 9.2

Ack! This fixed it: ln -s /usr/local/lib/libuuid.so.16 /lib/x86_64-linux-gnu/libuuid.so.16 I dream of the day where UUIDs just work out of the box in Postgres.. Mike On Sat, Sep 29, 2012 at 5:24 PM, Mike Christensen wrote: > I built 1.6 directly from the source and ran a make install.

Re: [GENERAL] Installing uuid-ossp on 9.2

12 PM, Mike Christensen wrote: > Yea, this is already installed.. I can type "uuid" from the command > line and get a UUID generated.. > > On Sat, Sep 29, 2012 at 5:07 PM, Adrian Klaver > wrote: >> On 09/29/2012 04:54 PM, Mike Christensen wrote: >>> >

Re: [GENERAL] Installing uuid-ossp on 9.2

Yea, this is already installed.. I can type "uuid" from the command line and get a UUID generated.. On Sat, Sep 29, 2012 at 5:07 PM, Adrian Klaver wrote: > On 09/29/2012 04:54 PM, Mike Christensen wrote: >> >> I'm having problems installing the uuid-ossp extensio

[GENERAL] Installing uuid-ossp on 9.2

I'm having problems installing the uuid-ossp extension on 9.2, running on Ubuntu. This is a fresh install, and I downloaded the binaries off of EnterpriseDB (I did not build from source).. According to: select * from pg_available_extensions; The extension is available. However, when I run: CRE

Re: [GENERAL] Column aliases in WHERE clauses

On Tue, Sep 18, 2012 at 11:20 AM, Eden Cardim wrote: >>>>>> "Mike" == Mike Christensen writes: > > Mike> I can also say if the table came before the columns, we'd > Mike> probably have a lot more SQL editors with auto-complete that >

Re: [GENERAL] Column aliases in WHERE clauses

On Tue, Sep 18, 2012 at 2:53 AM, Eden Cardim wrote: >> "Craig" == Craig Ringer writes: > > Craig> I just wish they hadn't written it backwards! > > Craig> It'd be much less confusing were it formulated as something > Craig> like: > > Craig> SELECT FROM thetable WHERE first_let

Re: [GENERAL] RFE: Column aliases in WHERE clauses

On Mon, Sep 17, 2012 at 4:32 PM, Tom Lane wrote: > Mike Christensen writes: >> This definitely makes sense in the context of aggregation, but I'm >> wondering if the same argument applies in the use case originally >> posted: > >> SELECT left(value, 1) as first

Re: [GENERAL] RFE: Column aliases in WHERE clauses

On Mon, Sep 17, 2012 at 3:10 PM, Daniel Serodio (lists) wrote: > Ryan Kelly wrote: > > On Mon, Sep 17, 2012 at 06:18:53PM -0300, Daniel Serodio (lists) wrote: > > It would be nice if PostgreSQL supported column aliases in WHERE > clauses, eg: > > SELECT left(value, 1) AS first_letter > FROM some_t

Re: [GENERAL] What is the state of the art for using LINQ with PostgreSQL?

BTW, they'll probably take a day to respond since they're all in the Ukraine :) They also seem to respond to questions fairly quickly on StackOverflow, if you use the DevArt tag. On Thu, Sep 13, 2012 at 10:26 AM, Rob Richardson wrote: > Replying partially to my own question: > > > > I downloaded

Re: [GENERAL] What is the state of the art for using LINQ with PostgreSQL?

> I would like to begin learning to use LINQ with PostgreSQL. Ideally, it > would be good to have free tools to do it, since I probably won’t be > finished playing with it before 30 days are up and I’d have to pay for > something. It would also be good if whatever I use has a good set of > tutori

Re: [GENERAL] Where do I get pgAdmin 1.16 for openSuSE?

On Thu, Sep 13, 2012 at 12:15 AM, Devrim GÜNDÜZ wrote: > > Hi, > > On Wed, 2012-09-12 at 20:51 -0700, Mike Christensen wrote: >> In file included from ../pgadmin/include/pgAdmin3.h:24:0, >> from ./pgAdmin3.cpp:13: >> ../pgadmin/include/ctl/ctlSQLBo

Re: [GENERAL] Where do I get pgAdmin 1.16 for openSuSE?

On Wed, Sep 12, 2012 at 8:43 PM, Mike Christensen wrote: > On Tue, Sep 11, 2012 at 6:49 AM, Adrian Klaver > wrote: >> On 09/10/2012 09:43 PM, Mike Christensen wrote: >>> Is there a place to download pgAdmin 1.16 for openSuse (or a >>> repository I can add?) >

Re: [GENERAL] Where do I get pgAdmin 1.16 for openSuSE?

On Tue, Sep 11, 2012 at 6:49 AM, Adrian Klaver wrote: > On 09/10/2012 09:43 PM, Mike Christensen wrote: >> Is there a place to download pgAdmin 1.16 for openSuse (or a >> repository I can add?) >> >> All I can find is packages for 1.14, however this version is unable to

Re: [GENERAL] Bad pg_dump error message

On Mon, Sep 10, 2012 at 10:21 PM, Tom Lane wrote: > Mike Christensen writes: >> Oh reading the online docs, it looks like what I may have wanted was: >> --format=custom > > Right. That does everything tar format does, only better --- the only > thing tar forma

Re: [GENERAL] Bad pg_dump error message

On Mon, Sep 10, 2012 at 10:06 PM, Mike Christensen wrote: > On Mon, Sep 10, 2012 at 9:57 PM, Tom Lane wrote: >> Jeff Janes writes: >>> On Mon, Sep 10, 2012 at 5:27 PM, Mike Christensen >>> wrote: >>>> Is there something that can be done smarter wit

Re: [GENERAL] Bad pg_dump error message

On Mon, Sep 10, 2012 at 9:57 PM, Tom Lane wrote: > Jeff Janes writes: >> On Mon, Sep 10, 2012 at 5:27 PM, Mike Christensen wrote: >>> Is there something that can be done smarter with this error message? >>> >>> pg_dump: dumping contents of table pages >&g

[GENERAL] Where do I get pgAdmin 1.16 for openSuSE?

Is there a place to download pgAdmin 1.16 for openSuse (or a repository I can add?) All I can find is packages for 1.14, however this version is unable to connect to Postgres 9.2 databases. Thanks! Mike -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to y

[GENERAL] Bad pg_dump error message

Is there something that can be done smarter with this error message? pg_dump: dumping contents of table pages pg_dump: [tar archiver] archive member too large for tar format pg_dump: *** aborted because of error If there's any hard limits (like memory, or RAM) that can be checked before it spen

Re: [GENERAL] When does Postgres cache query plans?

On Wed, Sep 5, 2012 at 3:51 PM, Tom Lane wrote: > Mike Christensen writes: >> I'm curious under what circumstances Postgres will cache an execution >> plan for a query. > > If you're writing raw SQL, never. The assumption is that the > application knows its u

[GENERAL] When does Postgres cache query plans?

I'm curious under what circumstances Postgres will cache an execution plan for a query. Obviously if you create it with the PREPARE statement, it will be cached.. However, if I just run an ad-hoc query such as: select * from Foo where X < 5; A few hundred times, will that be cached? What if I

[GENERAL] A next logical step after index-only scans?

Now that 9.2 supports index-only scans, I'm curious what possibilities this opens up in the future. It seems to me that the next logical step is to allow a user to put all columns into the index, at which point you would no longer even need the heap table. Oracle calls this "index-organized table

Re: [GENERAL] "Too far out of the mainstream"

On Mon, Sep 3, 2012 at 3:38 PM, Geert Mak wrote: > I have been looking into heroku lately, they run on PostgreSQL - > > https://postgres.heroku.com/postgres Went out to lunch with a guy who worked for Redfin as well. I guess they're all Postgres over there too.. -- Sent via pgsql-general mail

Re: [GENERAL] What text format is this and can I import it into Postgres?

>> 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

Re: [GENERAL] What text format is this and can I import it into Postgres?

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 >>

Re: [GENERAL] What text format is this and can I import it into Postgres?

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/SP2UserF

Re: [GENERAL] What text format is this and can I import it into Postgres?

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,

[GENERAL] What text format is this and can I import it into Postgres?

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~^~~^

Re: [GENERAL] Is this a Postgres Bug?

On Sun, Aug 5, 2012 at 1:21 PM, Tom Lane wrote: > Mike Christensen writes: >> 1) Is it possible to make int2 + int2 = int4? > > We could do that, but why stop there? int4 + int4 can overflow, maybe > its result should be int8? int8 + int8 can overflow, maybe its result &

Re: [GENERAL] Is this a Postgres Bug?

>> First off, I've posted this question on StackOverflow in case anyone >> wants to answer it: >> >> http://stackoverflow.com/questions/11814132/postgresql-smallint- >> overflowing-when-creating-index-on-multiple-columns-is-th >> >> The repro can be found here: http://sqlfiddle.com/#!1/734d7/1 >> >

[GENERAL] Is this a Postgres Bug?

First off, I've posted this question on StackOverflow in case anyone wants to answer it: http://stackoverflow.com/questions/11814132/postgresql-smallint-overflowing-when-creating-index-on-multiple-columns-is-th The repro can be found here: http://sqlfiddle.com/#!1/734d7/1 I'm happy to log this a

Re: [GENERAL] Another question about Range types

>> If I do switch to RANGE types, I think [2,2] would make sense in this > case. >> Using unbounded ranges might make sense if I wanted to express something >> like "Use up to 1 cup of flour" or "You'll need at least 3 cups of water". >> > > In these cases: > > Flour: [0, 1] - "optional, but maximu

Re: [GENERAL] Another question about Range types

>> There's another ongoing thread about range types, which was great because >> I wasn't familiar with the feature (guess it's new in 9.2?). >> >> I run a recipe website and was looking for *exactly* this sort of feature > a few >> weeks ago when I was adding in support for ranges of ingredients (s

Re: [GENERAL] Another question about Range types

> about that on our feature requests project page? http://project.npgsql.org > > Thanks in advance. > > Em 03/08/2012 17:01, "Mike Christensen" escreveu: >> >> There's another ongoing thread about range types, which was great >> because I wasn't

[GENERAL] Another question about Range types

There's another ongoing thread about range types, which was great because I wasn't familiar with the feature (guess it's new in 9.2?). I run a recipe website and was looking for *exactly* this sort of feature a few weeks ago when I was adding in support for ranges of ingredients (such as "1-2tsp s

Re: [GENERAL] Smaller data types use same disk space

On Thu, Jul 26, 2012 at 9:12 AM, Merlin Moncure wrote: > On Thu, Jul 26, 2012 at 11:02 AM, Mike Christensen wrote: >> I don't really think you'd need to decouple the internal column order >> from what the user sees. A REORDER COLUMNS command should re-build >> th

Re: [GENERAL] Smaller data types use same disk space

On Wed, Jul 25, 2012 at 4:12 PM, Tom Lane wrote: > Mike Christensen writes: >> On Wed, Jul 25, 2012 at 1:35 PM, Tom Lane wrote: >>> Yeah, this has been discussed multiple times. The sticking point is >>> the extra infrastructure needed to have a physical column numbe

Re: [GENERAL] Smaller data types use same disk space

On Wed, Jul 25, 2012 at 1:35 PM, Tom Lane wrote: > "McGehee, Robert" writes: >> One might even imagine a future version of PostgreSQL using an >> efficient disk layout that may not match the table layout in order to >> avoid wasted space from padding. > > Yeah, this has been discussed multiple ti

Re: [GENERAL] PostgreSQL limitations question

On Fri, Jul 13, 2012 at 12:05 AM, Chris Angelico wrote: > On Fri, Jul 13, 2012 at 4:14 AM, Bartosz Dmytrak wrote: >> I think there should be an explanation what *unlimited* really means. >> > > Unlimited doesn't mean you can have infinite of something, but just > that that specific attribute does

Re: [GENERAL] I'd like to learn a bit more about how indexes work

On Tue, Jun 5, 2012 at 7:20 PM, Chris Curvey wrote: > > > On Tue, Jun 5, 2012 at 6:24 PM, Mike Christensen wrote: >> >> Hi - >> >> I'm trying to increase my general knowledge about how indexes work in >> databases.  Though my questions are probably

Re: [GENERAL] I'd like to learn a bit more about how indexes work

s" which is what > is indicated in your questions below. > > If that is the case, then I suggest performing search queries with keywords > such as: > sql cost based optimizer > > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsq

[GENERAL] I'd like to learn a bit more about how indexes work

Hi - I'm trying to increase my general knowledge about how indexes work in databases. Though my questions are probably general and implemented in a similar way across major relational DBs, I'm also curious as to how they're implemented in Postgres specifically (mainly because I like PG, and am al

Re: [GENERAL] Does Postgres compress data?

On Wed, May 23, 2012 at 6:16 PM, Greg Williamson wrote: > Mike -- > > > <...> > >> >>Is PG compressing this data?  I'm curious as I was considering >>converting this column to a byte array and gzip'ing the data to save >>space, however if PG is already doing this for me, then I'm not going >>to bo

[GENERAL] Does Postgres compress data?

If I run this query: select sum(length(html)) from Indexer.Pages; I get: 15,680,005,116 However, if I type: C:\Program Files\PostgreSQL>dir /s I get: Total Files Listed: 5528 File(s) 7,414,385,333 bytes 575 Dir(s) 43,146,137,600 bytes free So all the Postgres data on disk i

Re: [GENERAL] Is there a way to avoid hard coding database connection info into views?

I've never done that in PG before, but I've used named connections with Oracle.  Is it the same sort of deal?  There's a file on the disk somewhere with the connection info?  Either way, I'm sure it's a RTFM thing so I'll look into it. >>> >>> yeah, there's a good example in the

Re: [GENERAL] Is there a way to avoid hard coding database connection info into views?

>> I've never done that in PG before, but I've used named connections >> with Oracle.  Is it the same sort of deal?  There's a file on the disk >> somewhere with the connection info?  Either way, I'm sure it's a RTFM >> thing so I'll look into it. > > yeah, there's a good example in the docs here:

Re: [GENERAL] Is there a way to avoid hard coding database connection info into views?

y 15, 2012 at 12:45 PM, Merlin Moncure wrote: > On Tue, May 15, 2012 at 2:28 PM, Mike Christensen wrote: >> I often manually pull in production data into my test database so I >> can test new code on realistic data, as well as test upgrade scenarios >> or repro data speci

[GENERAL] Is there a way to avoid hard coding database connection info into views?

I often manually pull in production data into my test database so I can test new code on realistic data, as well as test upgrade scenarios or repro data specific bugs. To do this, I've setup a `VIEW` for each production table in my test database. These views look something like this: CREATE

Re: [GENERAL] dblink.sql and Linux

I just installed Ubuntu 12.04 today. Postgres was not listed in the Ubuntu Software Center, so I downloaded the apt installer from: http://www.openscg.com/se/oscg_home_download.jsp Mike On Mon, May 14, 2012 at 6:10 PM, Devrim GÜNDÜZ wrote: > Hi, > > On Mon, 2012-05-14 at 18:05 -0

[GENERAL] dblink.sql and Linux

I've become a big fan of DBLink lately, but I'm curious where it lives on Linux installations. On my Windows 9.0.0 installation, there's a contrib\dblink.sql file that I just run and everything is setup for me. I have a few Linux installations as well (I think they're all 9.1 though) and there's

Re: [GENERAL] Question about schema-level permissions

Excellent, thanks so much! Mike On Thu, May 10, 2012 at 9:38 PM, Christophe Pettus wrote: > > On May 10, 2012, at 9:16 PM, Mike Christensen wrote: >> Am I missing something?  Doesn't GRANT ALL mean that user can do >> anything they want with objects in that schema, includ

[GENERAL] Question about schema-level permissions

I've created a schema called Indexer and a user called Indexer. I then grant Indexer ALL on said schema: GRANT ALL ON SCHEMA Indexer TO "Indexer"; Next, I attempt to INSERT into Indexer.ParseErrors, I get a permission denied error message. However, if I specifically grant Indexer INSERT permiss

Re: [GENERAL] current thinking on Amazon EC2?

> On Mon 3/19/2012 4:30 PM Mike Christensen writes: > >>I've been running my site on RackSpace CloudServers (similar to EC2) >>and have been getting pretty good performance, though I don't have >>huge amounts of database load. > >>One advantage, though,

Re: [GENERAL] current thinking on Amazon EC2?

On Mon, Mar 19, 2012 at 11:16 AM, Ben Chobot wrote: > On Mar 19, 2012, at 10:59 AM, Welty, Richard wrote: > > i just finished this thread from May of last year, and am wondering if this > still represents consensus thinking about postgresql deployments in the EC2 > cloud: > > http://postgresql.104

Re: [GENERAL] Can a function return more then one table?

> 2012/1/11 Mike Christensen : >> I would like to write a function that returns one row from one table, >> and about 10 rows or so from another table.. >> >> Is there a clean way to do this, or am I better off making two separate >> queries? >> >> I&

[GENERAL] Can a function return more then one table?

I would like to write a function that returns one row from one table, and about 10 rows or so from another table.. Is there a clean way to do this, or am I better off making two separate queries? I'm thinking maybe I can use OUT parameters for the first table, and the return value for the second

Re: [GENERAL] Is there a reason why Postgres doesn't have Byte or tinyint?

>>> According to the manuals, Postgres has smallint (2 byte), integer (4 >>> bytes) and bigint (8 bytes)..  I use a lot of structures with >>> "bytes" >>> in my code and it's kinda annoying to cast DB output from Int16 to >>> Byte every time, especially sinc

Re: [GENERAL] Is there a reason why Postgres doesn't have Byte or tinyint?

> According to the manuals, Postgres has smallint (2 byte), integer (4 > bytes) and bigint (8 bytes)..  I use a lot of structures with "bytes" > in my code and it's kinda annoying to cast DB output from Int16 to > Byte every time, especially since there's no explicit

Re: [GENERAL] Is there a reason why Postgres doesn't have Byte or tinyint?

On Sun, Jan 8, 2012 at 10:12 AM, Francisco Figueiredo Jr. wrote: > On Sun, Jan 8, 2012 at 06:54, Mike Christensen wrote: >>>>>>> According to the manuals, Postgres has smallint (2 byte), integer (4 >>>>>>> bytes) and bigint (8 bytes)..  I use a lot

Re: [GENERAL] Is there a reason why Postgres doesn't have Byte or tinyint?

> According to the manuals, Postgres has smallint (2 byte), integer (4 > bytes) and bigint (8 bytes)..  I use a lot of structures with "bytes" > in my code and it's kinda annoying to cast DB output from Int16 to > Byte every time, especially since there's no explicit cast in .NET an

Re: [GENERAL] Is there a reason why Postgres doesn't have Byte or tinyint?

>> According to the manuals, Postgres has smallint (2 byte), integer (4 >> bytes) and bigint (8 bytes)..  I use a lot of structures with "bytes" >> in my code and it's kinda annoying to cast DB output from Int16 to >> Byte every time, especially since there's no explicit cast in .NET and >> you hav

[GENERAL] Is there a reason why Postgres doesn't have Byte or tinyint?

According to the manuals, Postgres has smallint (2 byte), integer (4 bytes) and bigint (8 bytes).. I use a lot of structures with "bytes" in my code and it's kinda annoying to cast DB output from Int16 to Byte every time, especially since there's no explicit cast in .NET and you have to use System

Re: [GENERAL] Correct syntax to create partial index on a boolean column

On Thu, Dec 15, 2011 at 6:00 AM, Craig Ringer wrote: > On 12/15/2011 03:53 PM, Alban Hertroys wrote: >> >> Correct, but... >> That's not a particularly useful index to create. That index just contains >> values of true where the associated column equals true - you're storing the >> same informatio

Re: [GENERAL] Correct syntax to create partial index on a boolean column

On Wed, Dec 14, 2011 at 9:54 PM, Mike Christensen wrote: >> For the boolean column Foo in Table1, if I want to index all values of >> TRUE, is this syntax correct? >> >> CREATE INDEX IDX_MyIndex ON Table1(Foo) WHERE Foo; >> >> The query: >> >> SELE

Re: [GENERAL] Correct syntax to create partial index on a boolean column

> For the boolean column Foo in Table1, if I want to index all values of > TRUE, is this syntax correct? > > CREATE INDEX IDX_MyIndex ON Table1(Foo) WHERE Foo; > > The query: > > SELECT * FROM Table1 WHERE Foo; > > should use the index, and: > > SELECT * FROM Table1 WHERE NOT Foo; > > should not, c

[GENERAL] Correct syntax to create partial index on a boolean column

For the boolean column Foo in Table1, if I want to index all values of TRUE, is this syntax correct? CREATE INDEX IDX_MyIndex ON Table1(Foo) WHERE Foo; The query: SELECT * FROM Table1 WHERE Foo; should use the index, and: SELECT * FROM Table1 WHERE NOT Foo; should not, correct? I just want t

Re: [GENERAL] What's the best way in Postgres9 to store a bunch of arbitrary boolean values for a row?

>> I have a database full of recipes, one recipe per row.  I need to >> store a bunch of arbitrary "flags" for each recipe to mark various >> properties such as Gluton-Free, No meat, No Red Meat, No Pork, No >> Animals, Quick, Easy, Low Fat, Low Sugar, Low Calorie, Low Sodium and >> Low Carb.  User

[GENERAL] What's the best way in Postgres9 to store a bunch of arbitrary boolean values for a row?

I have a database full of recipes, one recipe per row. I need to store a bunch of arbitrary "flags" for each recipe to mark various properties such as Gluton-Free, No meat, No Red Meat, No Pork, No Animals, Quick, Easy, Low Fat, Low Sugar, Low Calorie, Low Sodium and Low Carb. Users need to be ab

Re: [GENERAL] Way to create unique constraint in Postgres even with null columns

On Sun, Nov 27, 2011 at 2:18 PM, David Johnston wrote: > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Mike Christensen > Sent: Sunday, November 27, 2011 5:02 PM > To: Thomas Kellerer >

  1   2   3   >