[GENERAL] libs and upgrades

(reposting - should have originally posted here in general - sorry) To prepare for server upgrades I'm planning to update the clients on a set of servers from 9.1 to 9.4. The servers on which the clients are installed are running CentOS 5 i386. Somewhere between PostgreSQL 9.1 and 9.4 the com

Re: [GENERAL] Timezone mismatch

On 03/20/2015 08:29 AM, "Leonardo M. Ramé" wrote: Hi, I had to change the O.S. timezone and aparently PostgreSql continues using the old timezone, how can I force update it's time zone?. Using PostgreSql 8.4 on Ubuntu Server 12.04. To update the OS timezone I used sudo dpkg-reconfigure tzdata

Re: [GENERAL] now() vs 'epoch'::timestamp

On 04/01/2015 11:50 AM, James Cloos wrote: I've for some time used: (now()::timestamp without time zone - 'epoch'::timestamp without time zone)::reltime::integer to get the current seconds since the epoch. The results are consistant with date +%s. (Incidently, is there a better way in 9.

Re: [GENERAL] now() vs 'epoch'::timestamp

On 04/02/2015 10:34 AM, David G. Johnston wrote: On Thu, Apr 2, 2015 at 10:27 AM, James Cloos <mailto:cl...@jhcloos.com>>wrote: >>>>> "SC" == Steve Crawford mailto:scrawf...@pinpointresearch.com>> writes: ... What I haven't dete

[GENERAL] ERROR: could not open relation with OID

This morning we got the following error from a daily script that produces a simple largest-table report: ERROR: could not open relation with OID 597597503 I reran the script and it completed without error. Our server is running 9.1.15 from PgDg Ubuntu repos and the query run by the script is:

Re: [GENERAL] ERROR: could not open relation with OID

On 04/22/2015 01:25 PM, Adrian Klaver wrote: If it is of importance, it appears that a temporary table and temporary index were being created within the same second that the query was run. Any advice? WHERE relkind = 'r' AND relpersistence != 't' So to confirm. Fix the query and do

Re: [GENERAL] date with month and year

On 05/21/2015 10:01 AM, Daniel Torres wrote: I everybody, I'm new in the Postgresql world, and have an easy question: Is it possible to have date type data that only contain month and year?, how can I obtain that from a timestamp (without time zone) column?... Others have offered good tips bu

Re: [GENERAL] date with month and year

On 05/21/2015 10:45 AM, Paul Jungwirth wrote: You really shouldn't use WITHOUT TIME ZONE. I'd like to know more about this. Can you say why? Start by reading about the date and time data types with special attention to section 8.5.3: www.postgresql.org/docs/current/static/datatype-datetime.

[GENERAL] Re: [PERFORM] Query running slow for only one specific id. (Postgres 9.3) version

On 06/05/2015 10:54 AM, Sheena, Prabhjot wrote: Postgresql 9.3 Version Guys Here is the issue that I’m facing for couple of weeks now. I have table (size 7GB) *If I run this query with this specific registration id it is using the wrong execution plan and takes more than a minu

Re: [GENERAL] Re: Query running slow for only one specific id. (Postgres 9.3) version

On 06/05/2015 11:38 AM, Sheena, Prabhjot wrote: When I run vacuum analyze it fixes the problem but after 1 or 2 days the problem comes back Is autovacuum running and using what settings? (select name, setting from pg_settings where name ~ 'autovacuum' Konsole output or name ~ 'statistics'

Re: [PERFORM] Re: [GENERAL] Re: Query running slow for only one specific id. (Postgres 9.3) version

On 06/05/2015 12:28 PM, Steve Crawford wrote: On 06/05/2015 11:38 AM, Sheena, Prabhjot wrote: When I run vacuum analyze it fixes the problem but after 1 or 2 days the problem comes back Is autovacuum running and using what settings? (select name, setting from pg_settings where name

Re: [GENERAL] regexp_matches for digit

On 07/09/2015 09:24 AM, Ramesh T wrote: Hi, in oracle regexp_like(entered date,'[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}','i') for postgres i have regexp_matches ,But i need how to match [:digit:] in postgres when we pass date..? any help Konsole output The tilde operator wor

Re: [GENERAL] Problem with database connections timing out for long-running queries

You might check the stunnel settings. A quick search of "stunnel 12-hours" indicates that this is the stunnel default for idle connections. Cheers, Steve On Fri, Aug 21, 2015 at 11:16 AM, Rich Schaaf wrote: > I’m running into a problem where the connection between application its > database is

Re: [GENERAL] Public facing PostgreSQL hosting ?

On Mon, Aug 31, 2015 at 12:03 AM, essam Ganadily wrote: > hi > i do develop on PostgreSQL from home and from work. i need public facing > PostgreSQL , something i can use Pgadmin from anywhere. > performance and scalability is not important because i will be running > like few operations per day.

Re: [GENERAL] Very puzzling sort behavior

Any null values in first name?? -Steve On Thu, Sep 10, 2015 at 12:35 PM, Ken Tanzer wrote: > Hi. In a table that includes these columns: > > my_db=> \d tbl_client > ... > name_last | character varying(40) | not null > name_first | character varying

[GENERAL] Queuing query

While awaiting the awesomeness of the upcoming "skip locked" feature in 9.5 I need to handle a work queue. Does anyone see any glaring issues or subtle nuances with the basic method below which combines CTEs with queue-handling methods posted by depesz, on the PG wiki and elsewhere. Note that it

Re: [GENERAL] Queuing query

appropriately. I'm really looking for any things like planner ordering nuances that would make the query operate in unexpected ways. Cheers, Steve On Tue, Sep 22, 2015 at 9:26 AM, Jeff Janes wrote: > On Mon, Sep 21, 2015 at 3:51 PM, Steve Crawford < > scrawf...@pinpointresearch.c

Re: [GENERAL] dumb question

Something like: select max(id) from yourtable where sts=0 and ref_id is null; That assumes that ref_id is null. It would help to see your table structure and the query you tried that doesn't work. If ref_id is actually a character string then you might need ref_id='' or coalesce(ref_id,'')='' if

Re: [GENERAL] dumb question

On Thu, Jun 2, 2016 at 10:40 AM, Felipe Santos wrote: > > > 2016-06-02 14:23 GMT-03:00 Steve Crawford > : > >> Something like: >> >> select max(id) from yourtable where sts=0 and ref_id is null; >> >> That assumes that ref_id is null. It would help

Re: [GENERAL] Permissions pg_dump / import

Check out the --no-owner and/or --no-acl flags when performing the dump. These eliminate the statements that set and/or alter ownership of database objects. For use in a test server where the username of the test-server database is different than the username on the production server *and* where y

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

> > ... > > You can actually reduce the time more by pre-syncing to the new location. > something like: > > rsync -va /var/lib/pgsql/ /var/lib/pgsql2/ > service postgres stop > rsync -va /var/lib/pgsql/ /var/lib/pgsql2/ > > The second rsync will only copy the deltas from the first, it still has to

[GENERAL] Installing 9.6 RC on Ubuntu

I'm trying to install 9.6 RC1 on Ubuntu 16.04 Xenial on my laptop and it seems broken. Installation of 9.6 RC1 on Centos was straightforward by comparison - just add the 9.6 yum package and install. Unfortunately Ubuntu seems second-class by comparison. I already have /etc/apt.repos.d/pgdg.list w

Re: [GENERAL] Installing 9.6 RC on Ubuntu [Solved]

On Tue, Sep 13, 2016 at 11:03 AM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > I'm trying to install 9.6 RC1 on Ubuntu 16.04 Xenial on my laptop and it > seems broken. > > Installation of 9.6 RC1 on Centos was straightforward by comparison - just > add the 9

Re: [GENERAL] Installing 9.6 RC on Ubuntu [Solved]

> >> In case it is useful for reference, I beat my head on it a bit more and >> replaced "main" with "9.6": >> deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg 9.6 >> > > Yes, that is an FAQ item: > > https://wiki.postgresql.org/wiki/Apt/FAQ > > Yes, but to quote Douglass Adams, "It was on

Re: [GENERAL] Way to quickly detect if database tables/columns/etc. were modified?

Not sure if it would work for your use-case but what about just monitoring the PostgreSQL log for DDL statements? You may have to filter out temp tables (as you might in the system catalogs as well) but you could probably also watch for specific tablename patterns in case you only need to invalidat

Re: [GENERAL] Hardware recommendations?

After much cogitation I eventually went RAID-less. Why? The only option for hardware RAID was SAS SSDs and given that they are not built on electro-mechanical spinning-rust technology it seemed like the RAID card was just another point of solid-state failure. I combined that with the fact that the

Re: [GENERAL] timestamp without timezone to have timezone

On Sun, Nov 6, 2016 at 7:10 AM, Melvin Davidson wrote: > > > > On Sun, Nov 6, 2016 at 9:11 AM, Benjamin Adams wrote: >> >> I have a server that has a column timestamp without timezone. >> >> Is the time still saved? >> if I select column with timestamp it will show server timestamp with timezone.

Re: [GENERAL] Generating sample data

You could start here: http://www.softwaretestingmagazine.com/tools/open-source-test-data-generators/ I have rolled my own on occasion by just pulling some public lists of most common given names and family names and toing a full-join. Same for city, streets, etc. -Steve On Tue, Dec 27, 2016 at 1

Re: [GENERAL] Generating sample data

On Tue, Dec 27, 2016 at 12:01 PM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > You could start here: > http://www.softwaretestingmagazine.com/tools/open-source-test-data- > generators/ > > I have rolled my own on occasion by just pulling some public lists of most

Re: [GENERAL] COPY: row is too big

... > Numeric is expensive type - try to use float instead, maybe double. >> > > If I am following the OP correctly the table itself has all the columns > declared as varchar. The data in the CSV file is a mix of text, date and > numeric, presumably cast to text on entry into the table. > But a C

Re: [GENERAL] COPY to question

On Tue, Jan 17, 2017 at 10:23 AM, Rich Shepard wrote: > Running -9.6.1. I have a database created and owned by me, but cannot > copy > a table to my home directory. Postgres tells me it cannot write to that > directory. The only way to copy tables to files is by doing so as the > superuser (pos

Re: [GENERAL] psql only works with -h (even localhost)

Adrian asks the correct questions. Lacking the answers to those I'm going to venture a guess that a Unix-domain socket exists but access via Unix-domain sockets is somehow blocked, probably by pg_hba.conf. >From the psql man page: "...Not all of these options are required; there are useful default

Re: [GENERAL] Pgbouncer

Do you have any clients connected that are idle in transaction? Cheers, Steve On Mon, Nov 30, 2015 at 1:46 PM, Torsten Förtsch wrote: > Hi, > > I am not sure if this is the right place to ask this question. If not, > please point me to it. > > I am trying out the new pgbouncer (latest git). "SH

Re: [GENERAL] Packages for Ubuntu Wily (15.10)

You should be able to add the pgdg repository to your system and then install through apt as normal. Scroll down to the "PostgreSQL APT repository" section on this page: http://www.postgresql.org/download/linux/ubuntu/ Cheers, Steve On Mon, Dec 7, 2015 at 9:27 AM, Antony Gelberg wrote: > Hi all

Re: [GENERAL] Deletion Challenge

If I understand correctly the value of "click" always advances and within a "click" the "cash_journal_id" always advances - not necessarily by single steps so within a fairian_id, ordering by "click" plus "cash_journal_id" would return the records in order from which you want the most recent 5 for

Re: [GENERAL] Deletion Challenge

The two general solutions are the "keep the last one" proposed by Adrian "keep the last N" that I sent. But it might be worth stepping back a bit. You said you are having performance problems that you feel would be improved by removing only a million rows which doesn't sound like that much to me.

Re: [GENERAL] Postgres and timezones

Is this of any use? select * from pg_timezone_names where name = 'Europe/Lisbon'; name | abbrev | utc_offset | is_dst ---+++ Europe/Lisbon | WET| 00:00:00 | f -Steve On Wed, Jan 20, 2016 at 7:24 AM, Steve Rogerson wrote: > On 20/01/16 13:2

Re: [GENERAL] Postgres and timezones

" but none for "PDT". Come spring, that will change. Cheers, Steve On Wed, Jan 20, 2016 at 7:46 AM, Pavel Stehule wrote: > > > 2016-01-20 16:38 GMT+01:00 Steve Crawford > : > >> Is this of any use? >> >> s

[GENERAL] Transactions, stats and analyze (oh-my)

We have certain processes that import data then process and distribute the data. Since the processing looks primarily, but not exclusively, at the new records an ANALYZE prior to processing yields better plans. Although the table changes will trigger autovacuum to analyze the table this happens too

Re: [GENERAL] PostgreSQL flavors

Congratulations on the decision and welcome. As an overview, there is the PostgreSQL *project* which is run by the PostgreSQL Global Development Group (PgDG) with contributors around the world most of whom work for a variety of companies that either use or support PostgreSQL. PostgreSQL is BSD-lic

Re: [GENERAL] Export binary data - PostgreSQL 9.2

What exactly are you trying to do? Dump/backup your data (e.g. pg_dump)? Read binary data from a table? If so, what field type (bytea, blob, ...)? Export to where? Cheers, Steve On Sun, Feb 28, 2016 at 9:12 AM, drum.lu...@gmail.com wrote: > Hi all, > > > Which command would be to export the bi

Re: [GENERAL] psql color hostname prompt

> > 2) %M vs shell call > > %M on when connected to the local machine displays the string "[local]" > which I didn't like. I wanted a real hostname to show no matter which > client/server pair I was using. Zero chance for mistaken commands on the > wrong host. Many times we ssh to a remote serv

Re: [GENERAL] psql color hostname prompt

On Wed, Apr 27, 2016 at 1:29 AM, Francisco Olarte wrote: > Hi Cal: > > On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand wrote: > ... > > 2) %M vs shell call > > %M on when connected to the local machine displays the string "[local]" > > which I didn't like. I wanted a real hostname to show no

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

First, you hit them over the head with a copy of "SQL Antipatterns: Avoiding the Pitfalls of Database Programming". It is a tad out of date and tends to use PHP and MySQL for the main examples but does also address different solutions available in PostgreSQL, Oracle. MS SQL server, etc. while point

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

Perhaps a bit US centric but PHB = Pointy Haired Boss. See the boss in "Dilbert". Cheers, Steve On Wed, May 4, 2016 at 7:55 AM, Pierre Chevalier Géologue < pierrechevalierg...@free.fr> wrote: > Le 04/05/2016 15:25, John McKown a écrit : > >> On Wed, May 4, 2016 at 8:13 AM, Geoff Winkless >

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

On Wed, May 4, 2016 at 8:04 AM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > First, you hit them over the head with a copy of "SQL Antipatterns: > Avoiding the Pitfalls of Database Programming". It is a tad out of date and > tends to use PHP and MySQL for t

Re: [GENERAL] psql color hostname prompt

e. Cheers, Steve On Thu, Apr 28, 2016 at 2:41 AM, Francisco Olarte wrote: > Hi Steve: > > On Wed, Apr 27, 2016 at 7:09 PM, Steve Crawford > wrote: > > The various hacks appear to not deal with the fact that there may be > > multiple instances of postgresql running on different

Re: [GENERAL] When should I worry?

Filip Rembiałkowski wrote: >> For the monitoring, however, you can log your queries along with >> timings and timestamps, and copy them into a tool like R to >> statistically analyze your performance over time. You will be able to >> predict the point at which your system will be too slow to use,

Re: [GENERAL] When should I worry?

Alexander Staubo wrote: > > For the monitoring, however, you can log your queries along with > timings and timestamps, and copy them into a tool like R to > statistically analyze your performance over time. You will be able to > predict the point at which your system will be too slow to use, i

Re: [GENERAL] Database performance problem

Porell, Chris wrote: > ... I snagged a SELECT from one of the reports. It is a > fairly complex query with 4 joins, which unfortunately I can't share. I can > say that the plan for the query on both machines looks nearly identical - > that is there are no sequential scans happening on the old DB

Re: [GENERAL] Database performance problem

Porell, Chris wrote: > Hi All, > I've changed shared_buffers, checkpoint_segments, effective_cache_size and > random_page_cost in an attempt to improve performance. That has helped a > little... Another thought. Have you looked at "work_mem" - this is probably a far more important setting. The

Re: [GENERAL] is it possible to recover more than one recordset or cursor from a function?

guillermo arias wrote: > Hello, people, i have a question for you: > is it possible to recover more than one recordset or cursor from a function? > I use to do it in ms sql server, but it is a mistery for me in postgre. Start here: http://www.postgresql.org/docs/8.2/static/xfunc-sql.html#AEN36437

Re: [GENERAL] dropdb ; createdb equivalent without createdb permission?

Tim Olsen wrote: > Hello, > > I would like to be able to grant a user the ability to completely > reset a database for testing purposes. If possible, I would like the > user to be able to do a "dropdb" followed by a "createdb", but without > allowing this user to create any database. In other wo

Re: [GENERAL] Updates/Changes to a database

imageguy wrote: > I am building an application with Postrges as the backend foundation. > This is my first application and it has struck me that as we add > features/functionality to the application and database with each new > version, we will need some method of obtaining the current structure >

[GENERAL] Will partial index creation use existing index?

Does PostgreSQL use an existing index, if possible, when creating a partial index? By way of background, we have some nightly bulk processing that includes a couple of 15-30 million row tables. Most of the processing is only looking at prior-day data (up to ~200,000 rows) and for efficiency requir

Re: [GENERAL] huge file in pg_xlog and base

Nicola Benaglia wrote: > Hi, > I have 6 little dbs, but I see that my base directory needs 213MB and > log are 114MB. > Here my folder structure and size: > > 3571./base/1 > 3487./base/10792 > 4691./base/10793 > 3707./base/16384 > 16618 ./base/16386 > 0 ./base/64673/pgsql_t

Re: [GENERAL] Allowing LAN connections

Jonas Gauffin wrote: > I've installed postgresql 8.2 on a windows vista machine and are trying to > connect to it from another one. > the server has ip 192.168.1.100 and the client 192.168.1.102 >... > Any suggestions? Yes. Let us know what client you are using to connect and post the error mess

Re: [GENERAL] UPDATES hang every 5 minutes

mr19 wrote: > I have a process that updates ~ 1500 rows in a table once a second. Every 5 > minutes (almost exactly) the update takes ~ 15 seconds (normally < 1) autovacuum_naptime perhaps? Cheers, Steve ---(end of broadcast)--- TIP 4: Have y

Re: [GENERAL] UPDATES hang every 5 minutes

> It seems to me that the real solution is for me to stop using the database as > an IPC system to pass somewhat time-critical data between processes. Given > the time constraints I'm working under this unfortunately was the quickest > route. At least for the first 5 minutes. :) I was wonderin

Re: [GENERAL] Seeking datacenter PITR backup suggestions

> In general, your handling of WAL files seems fragile and error-prone Indeed. I would recommend simply using rsync to handle pushing the files. I see several advantages: 1. Distributed load - you aren't copying a full-day of files all at once. 2. Very easy to set-up - you can use it directl

Re: [GENERAL] Seeking datacenter PITR backup suggestions

Gregory Stark wrote: >> In your scenario, what about using "cp -l" (or "ln") instead? > > Postgres tries to reuse WAL files. Once the archive_command completes it > believes it is safe to reuse the old file without deleting it. That will do > nasty things if you've used ln as your archive com

Re: [GENERAL] Question regarding autovacuum

Karl Denninger wrote: >> Are your FSM settings enough to keep track of the dead space you have? >> > I don't know. How do I check? vacuum verbose; Toward the bottom you will see something like: ... 1200 page slots are required to track all free space. Current limits are: 453600 page slots, 100

[GENERAL] psql hanging

What would cause psql to hang indefinitely when the backend disappears? We have a script that uses psql to insert a record (TCP connection to DB on different machine). The command is basically psql -c "insert into..." A while back I had to restart the server and today discovered that some of the

Re: [GENERAL] psql hanging

Richard Huxton wrote: > Steve Crawford wrote: >> What would cause psql to hang indefinitely when the backend disappears? >> >> We have a script that uses psql to insert a record (TCP connection to DB >> on different machine). The command is basically >> psql -c &q

[GENERAL] psql connect_timeout feature

I've dug through the docs and peeked at the source and found no way to specify a connect_timeout so: 1. Did I, in fact, just overlook something? 2. We would find it extremely useful to have this option. Would anyone else? 3. Alternately, what about adding a "raw connection string" feature to psq

Re: [GENERAL] query help

[EMAIL PROTECTED] wrote: > hello > i add more column not row for new user. i want all "last like 'J%'". > http://www.nabble.com/an-other-provokative-question---tf4394285.html > sincerely > siva You add a new _column_ for each user?!? That is hideously broken in so many ways. It makes the trivially

Re: [GENERAL] psql hanging

Trevor Talbot wrote: > Unless psql is turning on keepalive or similar, or the OS is forcing > it on by default, there are no timeouts for idle TCP connections. If > the command was transported to the server successfully and psql was > just waiting for a result, the connection is idle and nothing

[GENERAL] Optimizing "exists"

Does the planner automatically add "limit 1" to "exists" sub-selects? In other words, take an update like: update foo set itexists = exists (select 1 from bar where bar.something = foo.something); If the sub-select returns a large result set, will there be any benefit to adding "limit 1" to th

Re: [GENERAL] Manually clearing "database "foo" is being accessed by other users"

Sysadmin wrote: > Hi all, > > I'm finding that routinely when I try to reload a database on a server > where I know there are no connections to a given DB I get the error: > > $ dropdb foo && createdb foo -O bar && psql foo -f /path/to/db.out > dropdb: database removal failed: ERROR: database

[GENERAL] PG levels vs. syslog levels

What is the correlation between PostgreSQL log severity levels and syslog levels? Specifically, I have PG (8.1) configured to route messages to syslog and want to configure syslog to give me a periodic report of all error/worse events. I configured syslog (syslog-ng) to create hourly files of erro

Re: [GENERAL] Find min year and min value

Stefan Schwarzer wrote: >> SELECT year, value FROM ... > > I feel ashamed such a simple solution... gush Thanks for that! > > Unfortunately it doesn't stop there... > > If I want to find the "common smallest year" for two given variables > (say, I have years 1970, 1971, 2005 for vari

[GENERAL] Abbreviation list

Is there a comprehensive (or semi-comprehensive) on-line list of commonly used PG-related abbreviations used in PostgreSQL documentation, mail-lists, etc.? If there is not, would such a list make a reasonable one-page addition to the PG manual? Things like GUC and DDL for example. Couldn't find th

Re: [GENERAL] Abbreviation list

Bruce Momjian wrote: > Tom Lane wrote: >> Steve Crawford <[EMAIL PROTECTED]> writes: >>> My vote is to add "Appendix I. Abbreviations". >> It seems more like FAQ material than something for the manual. > > I prefer the manual. I would think the lis

Re: [GENERAL] Abbreviation list

Alvaro Herrera wrote: > Steve Crawford wrote: >> Bruce Momjian wrote: >>> Tom Lane wrote: >>>> Steve Crawford <[EMAIL PROTECTED]> writes: >>>>> My vote is to add "Appendix I. Abbreviations". >>>> It seems more like FAQ materi

Re: Re : [GENERAL] Abbreviation list

Alvaro Herrera wrote: > Steve Crawford wrote: >> Raymond O'Donnell wrote: >>> On 18/10/2007 22:26, Laurent ROCHE wrote: >>> >>>> No idea what GUC is, though ! >>> Grand Unified Contraption? ;-) >>> >>> Ray (who's just bee

Re: Re : [GENERAL] Abbreviation list

Raymond O'Donnell wrote: > On 18/10/2007 22:26, Laurent ROCHE wrote: > >> No idea what GUC is, though ! > > Grand Unified Contraption? ;-) > > Ray (who's just been reading Jules Verne). It's Global User Configuration. But the confusion does point out the need for a reference. -Steve ---

Re: [GENERAL] Abbreviation list

Alvaro Herrera wrote: > Steve Crawford wrote: >> Is there a comprehensive (or semi-comprehensive) on-line list of >> commonly used PG-related abbreviations used in PostgreSQL documentation, >> mail-lists, etc.? If there is not, would such a list make a reasonable >>

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

Alvaro Herrera wrote: ... > > You can use CREATE TABLE LIKE, which copies the definition but does not > set the inheritance. > Well, sort of. Unless I'm using it incorrectly it only copies the basic column definitions and, as optionally specified, defaults and some of the constraints. Primary

[GENERAL] Path to top of tree

Given a table which includes tree-type information consisting of an id and a parent_id, is there an already existing function that will return the path to the top of the tree for a given record? The connectby function from the contrib tablefuncs does what I want for a whole table, but I haven'

Re: [GENERAL] Primary Key

Martijn van Oosterhout wrote: On Fri, Nov 23, 2007 at 09:33:13AM +, Peter Childs wrote: I tend to agree that primary keys should be single fields if they need to be referenced but should also be natural if at all possible. ie use car number plates rather than some serial int. Car n

Re: [GENERAL] Primary Key

Joshua D. Drake wrote: In "theory" the item that would be a natural key in this instance is the VIN. You would of course have to make some kind of allowance for cars that don't have a VIN (nothing in the last what... 50 years?). And some kind of allowance for Title 49, Sec. 565.4, subsection (d)

[GENERAL] Altering field passed as parameter to plpgsql trigger

I am trying to make a function that can be reused as a trigger on various tables but am somehow not finding the correct syntax for using a parameter to the function to identify the column to be altered by the trigger. Stripped to basics: create or replace function foo_trigger() returns trigger

Re: [GENERAL] Altering field passed as parameter to plpgsql trigger

Richard Huxton wrote: Steve Crawford wrote: What is the correct syntax for the line: new.field_to_alter = some_computed_value; Can't be done in plpgsql - it's too static a language. On first call, that assignments basically gets compiled into a planned query and from then on yo

Re: [GENERAL] Tips for upgrading from 7.4

Rick Schumeyer wrote: Tom Lane wrote: Rick Schumeyer <[EMAIL PROTECTED]> writes: I'm working with someone who is about to upgrade from (I believe) pg 7.4 to pg 8.1. What would be the best resource to read to look for "gotchas"? Release notes? At the moment, we don't care about per

Re: [GENERAL] Tutorials on high availability Postgresql setup?

On 10/07/2010 12:59 AM, Andy wrote: Ah thanks for the explanation. I was hoping for an automated setup without the need to get paged 24/7. So HA is still as hard as I thought it would be. I was hoping that with 9.0 things would be easier. My 0.02. Whether you need 3 servers (or 2 or 5 o

Re: [GENERAL] psql copy command - 1 char limitation on delimiter

On 10/12/2010 08:28 PM, Bruce Momjian wrote: Steve Crawford wrote: On 09/25/2010 07:03 AM, Tom Lane wrote: rey writes: Why limit this to a single character? Performance. Believe it or not, breaking fields at the delimiter is a significant factor in COPY speed

Re: [GENERAL] Return key from query

On 11/02/2010 01:43 PM, Jonathan Tripathy wrote: Hi everyone, When adding a new record, we run an insert query which auto-increments the primary key for the table. However the method (in java) which calls this query must return the newly created key. Any ideas on how to do this, preferably u

Re: [GENERAL] index row requires 10040 bytes, maximum size is 8191

On 11/12/2010 12:52 PM, Joshua D. Drake wrote: On Fri, 2010-11-12 at 15:47 -0500, akp geek wrote: Hi all - I am trying to create an index on character varying field. The column is just character varying with no limit. I am getting the following error " index row requires 10040

Re: [GENERAL] Survey on backing up unlogged tables: help us with PostgreSQL development!

On 11/16/2010 07:25 PM, Tom Lane wrote: Man, the number of misunderstandings in this thread is staggering First, I have plenty of processes that I would immediately convert to using this (and, FWIW, none of them would benefit from preserving data across restarts). But I have some que

Re: [GENERAL] Postgresql: Remove last char in text-field if the column ends with minus sign

On 12/16/2010 06:52 AM, Sarang Dave wrote: Hello sir, I want to remove the last char in a column if it ends with the minus sign. How could I do this in postgresql? regexp_replace(your_field, '-+$', ''); Cheers, Steve

Re: [GENERAL] Getting the name of the timezone, adjusted for daylight saving

On 01/26/2011 09:00 AM, Mark Morgan Lloyd wrote: Tom Lane wrote: Mark Morgan Lloyd writes: SELECT to_char(('2011-03-01 12:00' AT TIME ZONE 'GMT0BST')::TIMESTAMP WITH TIME ZONE, 'HH24:MI TZ'); to_char --- 12:00 GMT (1 row) You haven't said exactly what you were hoping to accompl

Re: [GENERAL] Getting the name of the timezone, adjusted for daylight saving

On 01/26/2011 09:00 AM, Mark Morgan Lloyd wrote: Tom Lane wrote: Mark Morgan Lloyd writes: SELECT to_char(('2011-03-01 12:00' AT TIME ZONE 'GMT0BST')::TIMESTAMP WITH TIME ZONE, 'HH24:MI TZ'); to_char --- 12:00 GMT (1 row) You haven't said exactly what you were hoping to accompl

Re: [GENERAL] Why does a normally fast query run so slow when the table is in a partition?

On 02/02/2011 12:17 PM, Bill Thoen wrote: I've got a large (and growing) database set up as a partitioned database What is the setting of contstraint_exclusion? http://www.postgresql.org/docs/9.0/static/runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION Cheers, Steve

Re: [GENERAL] Why does a normally fast query run so slow when the table is in a partition?

On 02/02/2011 01:35 PM, Bill Thoen wrote: Steve Crawford wrote: On 02/02/2011 12:17 PM, Bill Thoen wrote: I've got a large (and growing) database set up as a partitioned database What is the setting of contstraint_exclusion? http://www.postgresql.org/docs/9.0/static/runtime-c

Re: [GENERAL] Why does a normally fast query run so slow when the table is in a partition?

On 02/03/2011 07:29 AM, Bill Thoen wrote: Got it solved! Great. The problem was one of two things,or maybe both. I had somehow gotten over 15 million records into the master table and even though I "deleted" them and run VACUUM ANALYZE over the table, they were still taking up space in the t

Re: [GENERAL] regexp problem

On 02/24/2011 10:25 AM, Gauthier, Dave wrote: select 'abc.def[0]' ~ E'^[a-zA-Z0-9_*\.\[\]*]+$'; Try: E'^[a-zA-Z0-9._\\[\\]]+$' The "outer" level of parsing turns that into '^[a-zA-Z0-9._\[\]]+$' which is the regex you want. Also, I'm *pretty sure* you don't need to escape the '.' within a ch

Re: [GENERAL] How to configure for remote TCP/IP client conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?

On 03/09/2011 07:31 AM, John Edens wrote: Hey guys, I'm trying to get a VB program to make a client connection to my PostgreSQL server running on an Ubuntu 10.10 server listen_addresses = '*, 144.96.80.35, localhost' Using * should be fine unless you have multiple IP addresses and wan

Re: [GENERAL] How to configure for remote TCP/IP client conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?

On 03/09/2011 09:54 AM, John Edens wrote: Using * should be fine unless you have multiple IP addresses and want the *server* to *listen* on only some of those addresses - say localhost if you were running web and db on the same machine and didn't want to listen to connections fr

Re: [GENERAL] why does extract ( HOUR FROM TIMESTAMP '2010-01-01 00:00:00' at time zone 'utc' ) == 16?

On 03/17/2011 05:05 PM, bubba postgres wrote: Is this the correct behavior? It seems like if I specify the utc offset it should be 0, not 16.. It seems to be the opposite behavior from extract epoch. select extract ( HOUR FROM TIMESTAMP '2010-01-01 00:00:00' ) as defhour, extract ( HOUR FROM

Re: [GENERAL] TO_CHAR(timestamptz,datetimeformat) wrong after DST change

On 03/18/2011 07:59 AM, jonathansfl wrote: My TO_CHAR function is now an hour off thanks to Daylight Savings Time. The dates are correct (I'm in EST: TZ=-04) but my function now returns TIME an hour early. (prior to DST we were TZ=-05). TIMESTAMPTZ data (v_dt): 2011-03-17 18:21:50-04 FUNCTION SN

  1   2   3   4   5   6   >