[GENERAL] Trigger function, C, lookup of attribute type

ypes. Do I have to query the system catalogs? Or maybe there's a function that works from a TupleDesc? -- Paul Nicholson -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Trigger function, C, lookup of attribute type

I found the solution: SPI_gettype() does the job fine. I was led to that by rummaging through the slony source code to see how they handle the triggers in C. -- Paul Nicholson -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

[GENERAL] NOT IN (NULL) ?

Please, help me. Why the condition SELECT 5 NOT IN (NULL) returns NULL, but not FALSE (as I thought)? -- Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] NOT IN (NULL) ?

)" I could use "IN (NULL)", but I was failed and result was NULL and not FALSE. :( -- Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Listing Triggers on a table.

How do I go about finding out the names of triggers attached to a table? Currently I'm doing : pg_dump | grep TRIGGER | grep from the shell command line. Is there a quicker way of doing it in psql? Paul

Re: [GENERAL] autovacuum worker running amok - and me too ;)

Circling back on this one, I had a look at our analyze code. I found one place where *maybe* we weren't freeing memory and freed it, but analyzing a 2M record table I barely see any bump up in memory usage (from 22M up to 24M at peak) during analyze. And the change I made didn't appear to alter tha

Re: [GENERAL] Postgres not using GiST index in a lateral join

Stop writing so many subqueries, think in joins; the poor planner! SELECT DISTINCT ON (a.id) a.id AS a_id, b.id AS b_id FROM a JOIN b ON ST_Contains(b.shape, a.shape) WHERE b.kind != 1 Also, the DISTINCT ON syntax (PgSQL custom) lets you winnow a result set down to just one of the inputs. P. O

Re: [GENERAL] Group by range in hour of day

tput rows. I could probably write this out in more detail if you like, but that's the short version. :-) Good luck! Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Group by range in hour of day

unt of 0 when appropriate (which seems common in reports with tables or plots), you can just tweak the above query to use a left join: FROM generate_series(0, 23) AS s(h) LEFT OUTER JOIN start_end ON h BETWEEN ... Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

Re: [GENERAL] Group by range in hour of day

teger [0,23] to a timestamp, but that seems pretty easy. Assuming start_time and end_time are UTC that's just adding that many hours to UTC midnight of the same day. Some weird edge cases to be careful about: activities that cross midnight. Activities that last more than one full day, e.g.

Re: [GENERAL] Group by range in hour of day

). Probably you want tsrange intersection as your join condition rather than BETWEEN. Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: [postgis-users] Memory management in postgres (with liblwgeom functions in particular)

Igor, Your supposition is is all correct (you might want to ask your questions on postgis-devel, where the developer density is higher). lwalloc by default is just a facade over malloc, for standalone use. But, when used in PostGIS, it it backed by palloc, which is in turn the memory manager provid

Re: [GENERAL] Overlap function for hstore?

#x27;t test for key and value together like in 'b=>2'! How about hstore_to_matrix? Then you have a tuple for each key/value pair. Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Success story full text search

e author! Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] date with month and year

, so you can still use date functions to manipulate them. Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] date with month and year

ve written about that approach in a Rails context here: http://illuminatedcomputing.com/posts/2014/04/timezones/ I find that this helps me to ignore time zones in most parts of my application and cut down on my timezone-related bugs. Thanks! Paul -- Sent via pgsql-general mailing list (pg

Re: [GENERAL] date with month and year

ll keep an eye out for when WITH might be handy. And maybe I'll do some research to see how well Rails would handle those columns. Thanks again for your generosity! Yours, Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] date with month and year

ank you for pointing that out. I'll have to think some more about when I'd want that behavior. Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Less is More

Hey all, I have an odd performance quirk I wonder if anyone has any theories for… (not a one-time result, table all heated up, etc) spain=# explain analyze select way from planet_osm_point;                                                         QUERY PLAN --

Re: [GENERAL] Average New Users Per DOW

data. My go-to solution is to use generate_series---in your case from 0 to 6---then do a left join from there to your actual data. Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Average New Users Per DOW

Thanks Paul, I guess I'm not sure how a generate_series between 0 to 6 would solve this problem. Wouldn't I have to generate a series based on the date range (by day) and then group by DOW _after_ that? Can you give me an example of how I'd do it with a series based on 0 to 6? L

[GENERAL] relpages for pg_toast.* tables

I've been trying to write up a "pg_total_relation_size()" replacement that just uses the system tables (sum up relpages for tables, indexes and toast tables), thereby avoiding the overhead of running stat() on thousands of filesystem tables, but I've come up against what seems to be an unsuperable

Re: [GENERAL] relpages for pg_toast.* tables

P. On Fri, Aug 28, 2015 at 6:16 AM, Paul Ramsey wrote: > I've been trying to write up a "pg_total_relation_size()" replacement > that just uses the system tables (sum up relpages for tables, indexes > and toast tables), thereby avoiding the overhead of running stat() on &

[GENERAL] PostGIS 2.2.0 Released

http://postgis.net/2015/10/07/postgis-2.2.0 PostGIS 2.2.0 is released! Over the last two years a number of interesting new features have been added, such as: * True nearest-neighbor searching for all geometry and geography types * New volumetric geometry support, including ST_3DDifference, ST_3D

Re: [GENERAL] Left Join with Limit 1

ing in that subquery so that the results are deterministic. Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_archivecleanup not deleting anything?

ssage, and the same files remain. Does anyone have any idea why pg_archivecleanup isn't deleting anything? Thanks, Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_archivecleanup not deleting anything?

Is there anything else beside *.backup files in the directory? There were a few *.history files, and a few files with no extension, like this: 000600BE0040. Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] pg_archivecleanup not deleting anything?

7;s not a misconfiguration on our side. I guess we should just make sure we remove older *.backup files by some other means, because thousands of 16MB files add up eventually. We had more than 30k of them! Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chang

Re: [GENERAL] pg_archivecleanup not deleting anything?

On 11/02/2015 09:11 AM, Adrian Klaver wrote: The *.backup files should not be 16MB and from your original post they looked to be 300 bytes. Now if you have 30K of 16MB files then something else is going on. Ah, you are right! Sorry for the misunderstanding. Paul -- Sent via pgsql-general

Re: [GENERAL] pg_archivecleanup not deleting anything?

So something is doing a base backup roughly every two hours. Is that what you would expect? No. :-) Sounds like I need to do some archeology. This is a system I inherited, so I haven't yet explored all the dark corners. Paul -- Sent via pgsql-general mailing list (pgsql-ge

Re: [GENERAL] postgres sometimes returns no data

many open connections? Possibly some hanging around longer than they should? Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgresSQL and HIPAA compliance

nds on the rest of your stack. Yours, Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] UUID and Enum columns in exclusion constraints

.html I would love to rebase that to the current code and re-submit. Maybe this weekend. :-) Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump fundenental question

> a good point, but I would prefer NOT to open a 324GB backup file in a text > editor. I can however cat/less/head/tail the file in Linux. Use vi (or flavour thereof) - it doesn't load the entire file in order to read the contents of lines 1 - 100 (say). Paul... -- Sent via pg

[GENERAL] Possible to create canonicalized range type without being superuser?

type. The problem is this (tried on 9.3 and 9.5): db=> create type inetrange; ERROR: must be superuser to create a base type So I'm wondering whether there is any way around this circle without being a superuser? Thanks! Paul -- Sent via pgsql-general mailing list (pgsql-general@pos

Re: [GENERAL] Update statement results in Out of memory

Running a multi-million row update will take a long time. It's possible you've exposed a memory leak in ST_Buffer (the older your version of GEOS, the more likely that is) but it's also possible you're just running a really long update. I find for batch processing purposes that creating fresh table

Re: [GENERAL] migrating data from an old postgres version

> On Jul 15, 2016, at 6:55 AM, Melvin Davidson wrote: > > > > On Fri, Jul 15, 2016 at 9:46 AM, Willy-Bas Loos > wrote: > Hi, > > A coworker is getting a new laptop and he wants to migrate some data from his > old one to the new one. So he installed postgres 9.5 on

Re: [GENERAL] GIN Indexes: Extensibility

; ordering of arbitrary JSON objects is. FWIW, Postgres arrays do sort in the way he's expecting: paul=# create table t (id integer, v integer[]); CREATE TABLE paul=# insert into t values (1, array[2014]), (2, array[2014, 1]), (3, array[2016]); INSERT 0 3 paul=# select * from t order b

Re: [GENERAL] journaling / time travel

poral is where you really cross over into lack of available tools, outside of a few commercial offerings. (Teradata has temporal support, using a Snodgrass-like approach that pre-dates the standard.) Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] journaling / time travel

> On 09/19/2016 10:56 AM, Willy-Bas Loos wrote: > > On Mon, Sep 19, 2016 at 6:26 PM, Paul Jungwirth > > mailto:p...@illuminatedcomputing.com>> wrote: > > I've worked on similar > > projects that maintain history for regulatory reasons. > Can yo

[GENERAL] Stats update difference between VACUUM ANALYZE and ANALYZE in 9.2?

er an automatic analyze for that table. If that table is rarely inserted into or updated, the inheritance statistics will not be up to date unless you run ANALYZE manually." Can anyone explain what's going on here? Thanks, Paul Jones -- Sent via pgsql-general m

Re: [GENERAL] Time travel?

-between-mtime-ctime-and-atime I hope that explains it! Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Multi tenancy : schema vs databases

o there's no solution there. Good luck! Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Avoiding double-counting in aggregates with more than one join?

e some different way of doing things I haven't thought of yet? Also I wonder about the performance merging all these subqueries together. Would the final merging be any faster if I had an ORDER BY in each CTE? It seems like this pattern comes up a lot; what have others done about it? Thanks,

Re: [GENERAL] Extensions and privileges in public schema

When you create the student user, remove their create privs in public. Then create a scratch schema and grant them privs there. Finally, alter the student user so that the scratch schema appears FIRST in their search path. This will cause unqualified CREATE statements to create in the scratch schem

Re: [GENERAL] Index impact on update?

You'd be better off forcing the table to write in bulk with something like CREATE TABLE mynewtable AS SELECT *, geography(ST_SetSRID(ST_MakePoint(lng, lat), 4326)) AS geog FROM myoldtable; Then index the new table, rename, etc. Bulk update will, in addition to being slow, use 2x the amount of spa

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

The index filters using bounding boxes. A long, diagonal route will have a large bounding box, relative to the area you actually care about (within a narrow strip of the route). Use ST_Segmentize() to add points to your route, ST_DumpPoints() to dump those out as point and ST_MakeLine to generate

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

Thu, Jan 5, 2017 at 11:36 AM, Israel Brewster wrote: > On Jan 5, 2017, at 8:50 AM, Paul Ramsey wrote: > > > The index filters using bounding boxes. A long, diagonal route will have > a large bounding box, relative to the area you actually care about (within > a narrow strip

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

gt; On Jan 5, 2017, at 10:38 AM, Paul Ramsey > wrote: > > Yes, you did. You want a query that spits out a tupleset of goemetries > (one each for each wee segment), and then you can join that set to your > main table using st_dwithin() as the join clause. > So start by ditching

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

lt, > you'll have to reduce the number of row in your index, > that is partition your table into several tables ! > > This is not easy to do with current postgres partitionning methods as far > as I know > (partitionning is easy, automatic efficient query is hard). > > An

[GENERAL] Can we not give tyrannical pedants control of #postgresql?

Hi all! It's really a bad sign when some user is given operator status who is intolerant to minor offtopic conversations that span no more than a couple of line buffers. Witnessing a user getting kicked for asking for my location was way beyond reasonable, considering even the channel was rath

[GENERAL] ERROR: could not read block 3 in file "base/12511/12270"

I have been having disk errors that have corrupted something in my postgres database. Other databases work ok: Running on Ubuntu 10.04. paul@kitanglad:~$ psql -U postgres psql (9.4.5) Type "help" for help. postgres=# SELECT pg_catalog.pg_is_in_recovery(); ERROR: could not read block

[GENERAL] 9.4 -> 9.5 upgrade problem when both python2 and python3 present

pg_upgrade complains about not being able to find $libdir/plpython3 when upgrading a 9.4 cluster that has both python2 and python3 used. Both the 9.4 and 9.5 PGs have been built from source with python2/3 in the recommended way and the plpython3.so is present in /usr/local/pgsql/lib. I dropped the

Re: [GENERAL] 9.4 -> 9.5 upgrade problem when both python2 and python3 present

On Mon, Jan 11, 2016 at 10:04:16AM -0500, Tom Lane wrote: /tmp/mutt-mayon-1000-19386-284b6a00794950f414 > Paul Jones writes: > > pg_upgrade complains about not being able to find $libdir/plpython3 > > when upgrading a 9.4 cluster that has both python2 and python3 used. > >

Re: [GENERAL] 9.4 -> 9.5 upgrade problem when both python2 and python3 present

On Mon, Jan 11, 2016 at 06:23:06PM -0500, Tom Lane wrote: > Date: Mon, 11 Jan 2016 18:23:06 -0500 > From: Tom Lane > To: Paul Jones > cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] 9.4 -> 9.5 upgrade problem when both python2 and > python3 present > > >

Re: [GENERAL] Ubuntu and Rails postgresql setup

not set DATABASE_URL. > Finally I am wondering whether the choice of installing PostgreSQL > through the PostgreSQL apt repository would be safe enough or it would > be preferable to install the LTS version of Ubuntu. Either is fine. Good luck! Paul -- Sent via pgsql-general mailin

Re: [GENERAL] Ubuntu and Rails postgresql setup

On 02/24/2016 09:44 AM, Paul Jungwirth wrote: Also, Rails wants to use Postgres "ident" authentication, which does not require a password because it trusts that the OS has already authenticated you. Sorry, I misspoke: this is "peer" authentication, not "ident". S

[GENERAL] Anyone compare PG 9.5 and MongoDB 3.2?

MongoDB has released 3.2 with their WiredTiger storage. Has anyone benchmarked 9.5 against it, and for JSONB elements several MB in size? PJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-gene

[GENERAL] MongoDB 3.2 beating Postgres 9.5.1?

I have been running the EDB benchmark that compares Postgres and MongoDB. I believe EDB ran it against PG 9.4 and Mongo 2.6. I am running it against PG 9.5.1 and Mongo 3.2 with WiredTiger storage using 10,000,000 JSON records generated by the benchmark. It looks like Mongo is winning, and apparen

[GENERAL] Question about shared_buffer cache behavior

In Postgres 9.5.1 with a shared_buffer cache of 7Gb, a SELECT from a single table that uses an index appears to read the table into the shared_buffer cache. Then, as many times as the exact same SELECT is repeated in the same session, it runs blazingly fast and doesn't even touch the disk. All go

Re: [GENERAL] Proper relational database?

orial D seems like a good place to start if SQL isn't relational enough for you. The book I mentioned is short and easy to read. Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Thoughts on "Love Your Database"

there ways I can use SQL (and PLSQL) without giving up Rails? How will I maintain that stuff? Does it play nice with Rails database migrations? How will I write tests for it? How do I debug it? What principles will help me draw the line between (PL)SQL and Ruby? Good luck! I think a book like that

Re: [GENERAL] Thoughts on "Love Your Database"

On 05/04/2016 08:39 AM, Paul Jungwirth wrote: On 05/03/2016 09:11 PM, Guyren Howe wrote: I think I'm going to write a book called Love Your Database, aimed at web developers I gave a talk here about doing "interesting" Postgres things in Rails: Oh also: one part of my talk

[GENERAL] problem calling psql multiple times from a script ?

by a database rename caused the rename to fail in some cases. I had to add a loop with a "sleep" followed by a check if the database was open. We are using postgres Version 7.4.8. TIA. Paul Tilles ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

[GENERAL] question regarding postgres

Hello, I have a question regarding Postgres 8.2. I am trying to set the datestyle in the postrgresql.conf permanently to European. But this does seem to work. This is what I did, I hope you can help me.   This is in the postgresql.conf.   datestyle = 'SQL,DMY'   but when I restart the server and u

Re: [GENERAL] varchar(n) VS text

) values ('abc''test'); It works fine. But that's beside the point - my question is should I convert everything to text fields and, if so, is there any easy way of writting a script to change all varchar fields to text? -- Paul Lambert Database Administrator Au

Re: [GENERAL] varchar(n) VS text

itially through pgAdminIII and it appears I selected the wrong character varying from the dropdown list. CREATE TABLE tester ( test_varchar character varying[], test_text text ) If I change it to character varying(20) it works fine. Apologies for that. Thanks for the other info though. -- Pa

Re: [GENERAL] greatest/least semantics different between oracle and postgres

I believe the spec says nulls are ignored for min/max. Postgres is as far as I know behaving according to spec. But I question the original poster's report of Oracle's behavior. I don't have 9.2.0.8 to test, but on 9.2.0.7: SQL> select f1, case when f1 is not null then 'not null' else 'null' e

Re: [GENERAL] greatest/least semantics different between oracle and postgres

> > Er ... your example doesn't actually seem to involve greatest() or > least()? > So sorry, it's been a long day, I misread. Yes, greatest/least definitely does work on Oracle as the OP said. Apologies again. ---(end of broadcast)--- TIP

Re: [GENERAL] greatest/least semantics different between oracle and postgres

At risk of putting my foot in my mouth again, greatest() returns null if one or more expressions are null for Oracle enterprise 9.2.0.7 and 10.2.0.3. The docs for greatest() don't talk of NULL: SQL> select greatest(1,2,null,3) from dual; GREATEST(1,2,NULL,3) SQL> se

Re: [GENERAL] greatest/least semantics different between oracle and postgres

> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-general- > [EMAIL PROTECTED] On Behalf Of Pavel Stehule > Sent: Saturday, June 30, 2007 10:37 AM > To: Bruno Wolff III; Pavel Stehule; pgsql-general@postgresql.org > Subject: Re: [GENERAL] greatest/least semantics different betwe

[GENERAL] IN clause performance

Basic query optimization question- does Postgres process x IN (y1, y2) as fast as (x = y1 OR x = y2) in a function?

Re: [GENERAL] Question about Postgres

to PL/SQL. http://www.postgresql.org/community/lists/ Paul... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] WAL Queries

rd disk? Enable it in the "Write Ahead Log" section of postgresql.conf file in the PGDATA directory. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send

Re: [GENERAL] WAL Queries

whilst the modification is being undertaken, just a quick restart once you have saved the file. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] WAL Queries

space you need is largely dependant on the size of your database and the size and frequency of updates coming through. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend

[GENERAL] Changing column types

quick way of doing this. Is there any way, using the system catalogues - pg_tables, pg_attributes etc that I can automatically change all text type fields to varchar or am I better of just recreating the schema? -- Paul Lambert Database Administrator AutoLedgers ---

Re: [GENERAL] Changing column types

Paul Lambert wrote: Owing to a problem with the way access and a couple of other programs I've found handle text columns in PG as something they call 'memo', I want to change all of the text columns in my database to varchar. There's about 600 text columns all up, so I&#

Re: [GENERAL] copy command - date

ce, line 1, column maintenance_id: "3665 OK SM 07/07/13 06:09" That's not complaining about the date, that is complaining that your input file does not contain the maintenance_id column. -- Paul Lambert Database Administrator AutoLedgers ---

Re: [GENERAL] copy command - date

Paul Lambert wrote: novice wrote: I'm using pg version 8.2.4. What is the best method to load this data? I have just a little over 55,000 entries. db5=> \copy maintenance FROM test.txt ERROR: invalid input syntax for integer: "3665 OK SM 07/07/13 06:09&qu

Re: [GENERAL] TimestampTZ

g the offset for the timezone zone. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [GENERAL] TimestampTZ

Paul Lambert wrote: Refer to this paragraph: (8.5.1.2) For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using

Re: [GENERAL] MVCC cons

t50 ( f1 ) values ( '012345678901234567890123456789012345678901234567890123456789012345678901234 5678901234567890123456789' ); -- Repeat above insert 1000 times commit transaction select datediff(ms, @start, getdate()) On my system, test one averages around 16ms over 100 tests. Test 2 averages around 33ms over 100 tests. I would wager my week's coffee change the same outcome on SQL 2005 sp2. Paul ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

[GENERAL] ecpg: dtime_t vs timestamp

#x27;dtime_t' How should we fix this? Paul Tilles ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

[GENERAL] date of next Version 8.2 release

We would like to use the 8.2 version of postgres with our next software build. The next 8.2 version will contain a software patch which is critical to our needs. When is the next release of 8.2 expected to occur? As always, TIA. Paul Tilles ---(end of broadcast

[GENERAL] Version 8.2.4 ecpg - function not found

31.9.1) describes the functions risnull and rsetnull. These are the names of the original Informix functions. Are they available for use through ecpg? As always, TIA. Paul Tilles ---(end of broadcast)--- TIP 3: Have you checked our extensive

[GENERAL] Regular expression on a string problem.

Here is a problem I'm having with a function I've created. It should be returning a varchar value no matter what the input is but I'm getting a null value back. Then it says in the error that I'm using "nonstandard use of escape in a string literal at character 9". What do you think this should be

[GENERAL] Function to determine next payment date

OP; RETURN start_date; END; $$ LANGUAGE PLPGSQL STABLE; Since it's my first function in PL/pgSQL I've probably missed something pretty obvious - but if someone could point it out to me that would be muchly appreciated. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Function to determine next payment date

Paul Lambert wrote: I'm trying to create a function to determine the next due payment date of a recurring expense. I have a table containing the date the payment first started, and a payment frequency key which relates to a payment ID in another table containing a string definin

Re: [GENERAL] Database reverse engineering

t for -cp needs to be the full path to the .jar file, and must obviously use the real filename of that file itself. You may then get complaints about not finding the schema: I had to specify "-s public", I think. Paul ---(end of broadcast)

Re: [GENERAL] Partitioned table limitation

se a little stricter than partitioning issues in Oracle or SQL Server. HTH, Paul ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly

Re: [GENERAL] Partitioned table limitation

:38 am, [EMAIL PROTECTED] ("paul rivers") wrote: > > > -Original Message- > > > From: [EMAIL PROTECTED] [mailto:pgsql-general- > > > [EMAIL PROTECTED] On Behalf Of Goboxe > > > Sent: Monday, October 01, 2007 2:18 AM > > > To: [EMAI

Re: [GENERAL] looking for some real world performance numbers

results, regardless of whether it should. Paul ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

[GENERAL] Extracting Index Creation Script

Hello, Does anyone have any good code to extract the metadata needed to create indexes on a specific table? The Client Tools (like pgadmin-III) presents that code so I'm sure it's extractable but could not find it in my trace that I ran while operating pgadmin... Thanks in adva

Re: [GENERAL] Extracting Index Creation Script

Hello, I answered my own question. :) I continued looking last night after I posted this and found a really easy way to get this info... select * from pg_indexes where tablename like 'YOURTABLENAME' This will give you the DDL to create your indexes. Regards, Paul Paul Silv

Re: [GENERAL] Selecting K random rows - efficiently!

N ALL (SELECT 2 AS seq, * FROM mydata ORDER BY random_number ASC LIMIT ) ORDER BY seq ASC, random_number ASC LIMIT K; This should provide each row with an equal chance of being selected while requiring the database to fetch at most 2 * K rows. Regards, Paul Tillotson -

Re: [GENERAL] select count() out of memory

y identifiable subsets of the data, as they seem to in this case and in my own case. The various tuning guides out there have been very useful, but isn't there a point at which partitioning is inevitable? Paul ---(end of broadcast)--- TIP 9: In

Re: [GENERAL] Partitioning: how to exclude unrelated partitions?

obvious at first reading, as the focus is more on querying than designing the table. Maybe that would be worth expanding on a little in the docs? Regards, Paul ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] young guy wanting (Postgres DBA) ammo

shop to decide what RDBMS is best for them, but the alleged reason of "MySQL requires less time" is definitely not one of them. HTH, Paul ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Enabling password complexity for password authentication

any suggestions. Paul ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

  1   2   3   4   5   6   7   >