Re: [GENERAL] Client Authentication methods

chiru r wrote: Hi All, I am trying to understand the Authentication method in pg_hba.conf file (password & md5) in PostgreSQL database server. I am assuming that the user provides the connection string host/usser/password,then client will go and contact the DB server pg_hba.conf file in

Re: [GENERAL] Best way to allow column to initially be null?

Glen Huang wrote: > I’m trying to make a column have these properties: > > 1. When a row is inserted, this column is allowed to be null. 2. When the row is updated, no null > can be assigned to it this column. > > I initially thought I can drop the not null constraint before insertion and turn

Re: [GENERAL] Selecting a daily puzzle record - which type of column to add?

Alexander Farber wrote: Good evening, In a word game I store all player moves in the table: CREATE TYPE words_action AS ENUM ( 'play', 'skip', 'swap', 'resign', 'ban', 'expire' ); CREATE TABLE words_moves ( mid BIGSERIAL PRIMARY KEY,

Re: [GENERAL] What is exactly a schema?

marcelo wrote: The question is not trivial. Could I maintain two or three separate/distinct "versions" of same database using one schema for every of them? Could some tables (in the public schema) be shared among all the schemas? Yes and yes. In the Postgresql world, the word "schema" is

Re: [GENERAL] How to drop column from interrelated views

Guyren Howe wrote: On Jul 8, 2017, at 16:11 , Berend Tober <bto...@computer.org <mailto:bto...@computer.org>> wrote: Guyren Howe wrote: I’ve a set of interrelated views. I want to drop a column from a table and from all the views that cascade from it. I’ve gone to the leaf

Re: [GENERAL] How to drop column from interrelated views

Guyren Howe wrote: I’ve a set of interrelated views. I want to drop a column from a table and from all the views that cascade from it. I’ve gone to the leaf dependencies and removed the field from them. But I can’t remove the field from the intermediate views because Postgres doesn’t appear

Re: [GENERAL] Question regarding pgsql-general mailing list.

FYI, Postgresql caps for sale on ebay at http://www.ebay.com/itm/162564660418 -- 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] Server SSL key with passphrase

dhanuj hippie wrote: I have a postgres-9.6 server running with SSL enabled, and I have setup the certificates as per documentation. But currently the key file is not protected by passphrase. Does postgres provide a way to use passphrase protected keys ? If by "per documentation" you refer

Re: [GENERAL] Means to emulate global temporary table

Karsten Hilbert wrote: On Wed, Jan 11, 2017 at 05:54:11PM -0700, David G. Johnston wrote: I don't see where "call a setup function immediately after connecting" Sounds like a "login trigger", more generally an ON CONNECT event trigger, which we don't have at the moment as far as I know. One

Re: [GENERAL] Invoice Table Design

rob wrote: Hi Rich, thanks for the response -- going from Mongo to Postgres does require the kind of approach you suggest. I suppose my question was a little bit more along the lines if anyone has experience with designing payment / invoicing systems and any caveats they may have encountered

Re: [GENERAL] MediaWiki + PostgreSQL is not ready for production?

John R Pierce wrote: On 7/18/2016 9:14 PM, Tatsuo Ishii wrote: I found following comment for using PostgreSQL with MediaWiki: https://www.mediawiki.org/wiki/Compatibility#Database "Anything other than MySQL or MariaDB is not recommended for production use at this point." This is a sad and

Re: [GENERAL] Running query without trigger?

haman...@t-online.de wrote: Hi, a table is associated with a trigger for normal use. An admin (someone with ALTER privilege) can disable tthe trigger, run some bulk update, and then re-enable it. This means, however, that normal user activity has to be locked out. There are two possible

Re: [GENERAL] Using a VIEW as a temporary mechanism for renaming a table

Ben Buckman wrote: Hello, I would like to rename a table with ~35k rows (on pgsql 9.4), let's say from `oldthings` to `newthings`. Our application is actively reading from and writing to this table, and the code will break if the table name suddenly changes at runtime. So I can't simply run an

Re: [GENERAL] Function PostgreSQL 9.2

drum.lu...@gmail.com wrote: It's working now... Final code: ALTER TABLE public.companies ADD COLUMN client_code_increment integer; ALTER TABLE public.companies ALTER COLUMN client_code_increment SET NOT NULL; ALTER TABLE public.companies ALTER COLUMN client_code_increment SET

Re: [GENERAL] Function PostgreSQL 9.2

David G. Johnston wrote: ​Berend already identified the problem for you. Thank you. -- 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] Function PostgreSQL 9.2

drum.lu...@gmail.com wrote: I'm just having some problem when doing: INSERT INTO public.users (id,email,encrypted_password,sign_in_count,created_at,updated_at,company_id) VALUES (66,'tes...@test.com ','password','0','2016-05-03 00:01:01','2016-05-03

Re: [GENERAL] Function PostgreSQL 9.2

David G. Johnston wrote: On Wed, May 4, 2016 at 2:57 PM, drum.lu...@gmail.com ... I would expect a minimum of respect from the members of this list, but seems you got none. If someone would need my help, I'd never insult him/her like you guys are doing. If my questions are too

Re: [GENERAL] Function PostgreSQL 9.2

On Tuesday, May 3, 2016, drum.lu...@gmail.com > wrote: * This is what I did... |-- Creating the table CREATE TABLE public.company_seqs (company_id BIGINTNOT NULL, last_seq

Re: [GENERAL] "Keyed" sequence?

Adrian Klaver wrote: On 04/28/2016 11:48 AM, Israel Brewster wrote: On Apr 28, 2016, at 10:39 AM, Vik Fearing wrote: On 04/28/2016 08:30 PM, Israel Brewster wrote: This is probably crazy talk, but in Postgresql is there any way to have a "keyed" sequence? That is, I

Re: [GENERAL] Logger into table and/or to cli

Thiemo Kellner, NHC Barhufpflege wrote: I would like to log from within pgsql into table and/or to command line, something like https://sourceforge.net/projects/plnsqllogger/ . Does anybody know of an open source solution available? I like "18.8.4. Using CSV-Format Log Output", although it

Re: [GENERAL] A motion

Adrian Klaver wrote: Motion: The Coc discussion be moved to its own list where those who care can argue to their hearts content and leave the rest of us to deal with technical questions. Upon a decision on said list the result be posted to the Postgres web site for consideration. Been

Re: [GENERAL] Multi-Table Insert/Update Strategy - Use Functions/Procedures?

Don Parris wrote: I have several tables... and want db users to be able to add or update ... ... in one step, and get all the information into the correct tables. I think I am ok with setting the privileges on the tables and columns as appropriate to allow each group to select, insert and

Re: [GENERAL] WIP: CoC V5, etc., etc., etc., etc., ....

Joshua D. Drake wrote: On 01/13/2016 06:00 PM, Berend Tober wrote: Whether or not it is a foregone conclusion that this community will adopt a CoC, it seems like a mailing list is not the place to do revision control. Can you people start a github project or something to develope your ideas

Re: [GENERAL] WIP: CoC V5, etc., etc., etc., etc., ....

Bill Moran wrote: On Tue, 12 Jan 2016 22:10:43 -0500 Tom Lane wrote: Kevin Grittner writes: I'm not the greatest word-smith, but I'll attempt to rework Josh's draft to something that seems more "natural" to me. Minor (or not?) comment: Whether or

Re: [GENERAL] Code of Conduct

Geoff Winkless wrote: On 11 January 2016 at 20:13, Regina Obe wrote: While this is funny to some, I don't think it adds value to this conversation. I would consider it a derailment and not very helpful. If I had a Coc to point at, I would point at the section I feel you are

Re: [GENERAL] Deletion Challenge

Jim Nasby wrote: On 12/9/15 7:59 PM, Berend Tober wrote: This project is a game, btw, described at You might be interested in https://schemaverse.com/ Schemaverse looks somewhat interesting. Seems like it and Fairwinds share in common Postgresql as a foundation, but they are very

Re: [GENERAL] Deletion Challenge

Steve Crawford wrote: 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

Re: [GENERAL] Deletion Challenge

Steve Crawford wrote: 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

Re: [GENERAL] Deletion Challenge

Adrian Klaver wrote: On 12/05/2015 08:08 AM, Berend Tober wrote: /* Deletion Challenge I want to delete all but the most recent transaction, per person, from a table that records a transaction history because at some point the transaction history grows large enough to adversely effect

[GENERAL] Deletion Challenge

/* Deletion Challenge I want to delete all but the most recent transaction, per person, from a table that records a transaction history because at some point the transaction history grows large enough to adversely effect performance, and also becomes less relevant for retention. I have devised

Re: [GENERAL] PostgreSQL Developer Best Practices

Melvin Davidson wrote: 9. 1) What happens if someone mis-types the account-id? To correct that, you also need to correct the FK field in the other dozen tables. 2) What happens when your company starts a new project (or buys a I would not consider the general use of natural primary

Re: [GENERAL] How to intelligently work with views that depend on other views

Melvin Davidson wrote: The best solution, IMHO, is don't create views that depend on other views. ... Much better to just make each view a stand alone. Seconding Mr. Davidson's advice. But, given that you are in the situation, here is a workable alternative: Matthew Wilson On Thu, Aug 6,

Re: [GENERAL] How to hide stored procedure's bodies from specific user

Saimon Lim wrote: Thanks for your help I want to restrict some postgres users as much as possible and allow them to execute a few my own stored procedures only. Create the function that you want restrict access to in a separate 'private' schema to which usage is not granted. Create the

Re: [GENERAL] How to hide stored procedure's bodies from specific user

Guillaume Lelarge wrote: 2015-02-14 14:07 GMT+01:00 Berend Tober bto...@broadstripe.net mailto:bto...@broadstripe.net: Saimon Lim wrote: Thanks for your help I want to restrict some postgres users as much as possible and allow them to execute a few my own stored

[GENERAL] Can pg_restore produce create or replace commands

I often work with the output of pg_restore from a custom format dump file. For example a file produced by running pg_restore -s -1 -L listfile dumpfile where listfile has been edited to comment out most of the rows to leave only the data base objects I'm currently interested in. Most often,

Re: [GENERAL] Can pg_restore produce create or replace commands

Adrian Klaver wrote: On 01/17/2015 10:05 AM, Berend Tober wrote: I often work with the output of pg_restore from a custom format dump file. ... Most often, I'm refactoring functions and so don't really want to drop the function but rather want to do a create or replace function... Not sure

Re: [GENERAL] Can pg_restore produce create or replace commands

Adrian Klaver wrote: On 01/17/2015 10:05 AM, Berend Tober wrote: I often work with the output of pg_restore from a custom format dump file... Most often, I'm refactoring functions and so don't really want to drop the function but rather want to do a create or replace function... I am

Re: [GENERAL] Can pg_restore produce create or replace commands

Thomas Kellerer wrote: Berend Tober wrote on 17.01.2015 19:05: I often work with the output of pg_restore from a custom format dump file. ... Most often, I'm refactoring functions and so don't really want to drop the function but rather want to do a create or replace function ... To me

Re: [GENERAL] Simple Atomic Relationship Insert

John McKown wrote: On Tue, Jan 13, 2015 at 10:42 AM, Robert DiFalco robert.difa...@gmail.com mailto:robert.difa...@gmail.comwrote: Let's say I have two tables like this (I'm leaving stuff out for simplicity): CREATE SEQUENCE HOMETOWN_SEQ_GEN START 1 INCREMENT 1; CREATE TABLE

Re: [GENERAL] How to insert into 2 tables from a view?

Chris Hoover wrote: Correct sql: BEGIN; CREATE TABLE table1 ( table1_id SERIAL PRIMARY KEY, table1_field1 TEXT ); CREATE TABLE table2 ( table1_id INTEGER NOT NULL PRIMARY KEY REFERENCES table1(table1_id) ON DELETE CASCADE, table2_field1 TEXT ); CREATE VIEW orig_table AS

[GENERAL] How to individually list the DDL for all individual data base objects

Is there a good way to individually list the DDL for all individual data base objects? Running a data base dump like: pg_dump mydatabase mydatabase-database.sql produces one big file with all the DDL and DML to restore the data base, which is very convenient for most cases. Using that I

Re: [GENERAL] How to individually list the DDL for all individual data base objects

François Beausoleil wrote: Le 2014-11-24 à 10:14, Berend Tober bto...@broadstripe.net a écrit : Is there a good way to individually list the DDL for all individual data base objects? snip Were you aware that pg_restore can restore to STDOUT, and output DDL for only a single named

Re: [GENERAL] How to individually list the DDL for all individual data base objects

Adrian Klaver wrote: On 11/24/2014 08:12 AM, Berend Tober wrote: François Beausoleil wrote: Le 2014-11-24 à 10:14, Berend Tober bto...@broadstripe.net a écrit : Is there a good way to individually list the DDL for all individual data base objects? snip Were you aware that pg_restore

Re: [GENERAL] How to individually list the DDL for all individual data base objects

Melvin Davidson wrote: You should probably look at the pg_extractor utility. https://github.com/omniti-labs/pg_extractor With it, you can dump individual or selected objects to separate directories. That looks like what I'm looking for. (Note: I did Google searching, but apparently did not

Re: [GENERAL] Whole record returned in stead of field

Johann Spies wrote: SELECT A.article_id, A publication_year You are missing a . between in what you think is the second column (after the A). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Call for design: PostgreSQL mugs

Peter Geoghegan wrote: On Thu, Sep 5, 2013 at 8:22 AM, Merlin Moncure mmonc...@gmail.com wrote: I'm still partial to this guy: http://www.commandprompt.com/images/mammoth_versus_dolphin_500.jpg I dislike that image, and always have. ... I agree with Mr. Geoghegan. That image should be

Re: [GENERAL] New Zealand Postgis DBA job vacancy

pcr...@pcreso.com wrote: Mat be of interest to someone here... http://careers.eroad.co.nz/vacancies/showVacancy/11 Brent Wood I attended a technical conference in the US recently, and someone posted an Auckland job flyer (not this same organization, though). He included a statement

Re: [GENERAL] New Zealand Postgis DBA job vacancy

Gavin Flower wrote: On 27/12/12 07:54, Berend Tober wrote: ...regarding New Zealand expatriate work visas and ... that it was notably easier for foreigners under age 30 to get permission to work. Can anyone with direct experience comment on this government-sanctioned discrimination? I think

Re: [GENERAL] obtain the difference between successive rows

Thalis Kalfigkopoulos wrote: On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell r...@iol.ie wrote: On 20/10/2012 11:54, ochaussavoine wrote: I have a table 'tmvt' with a field 'created' in the row, and would like to compute the difference between successive rows. The solution I found is: I

Re: [GENERAL] obtain the difference between successive rows

Thalis Kalfigkopoulos wrote: SELECT current_meter_reading - lag(current_meter_reading) OVER(ORDER BY current_reading_date) AS kWh_diff, extract('days' FROM current_reading_date - lag(current_reading_date) OVER(ORDER BY current_reading_date)) as num_service_days FROM mytable; How would you get

Re: [GENERAL] obtain the difference between successive rows

Raymond O'Donnell wrote: On 20/10/2012 17:02, Berend Tober wrote: Thalis Kalfigkopoulos wrote: How would you get the previous reading (and perhaps the previous read date) to also appear ... Just include them in the SELECT: Well, that is surprisingly easy! How about this then: the table

Re: [GENERAL] obtain the difference between successive rows

Berend Tober wrote: Raymond O'Donnell wrote: On 20/10/2012 17:02, Berend Tober wrote: Thalis Kalfigkopoulos wrote: How would you get the previous reading (and perhaps the previous read date) to also appear ... Just include them in the SELECT: Well, that is surprisingly easy! How about

Re: [GENERAL] obtain the difference between successive rows

Виктор Егоров wrote: 2012/10/20 Berend Tober bto...@broadstripe.net: Your suggestion almost worked as is for this, except that you have to note that reading for meter #2 and meter #3 overlap ... You can do … OVER(PARTITION BY electric_meter_pk ORDER BY reading_date) to split you data

Re: [GENERAL] obtain the difference between successive rows

Thalis Kalfigkopoulos wrote: On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell r...@iol.ie wrote: On 20/10/2012 11:54, ochaussavoine wrote: I have a table 'tmvt' with a field 'created' in the row, and would like to compute the difference between successive rows. The solution I found is: I

[GENERAL] How to ;ist all table foreign key dependency relationships

I am interested in listing all the pairwise foreign key dependencies between tables by name, i.e., if I have table A with some primary key column A.id, and table B has a foreign key referencing A.id, then table B depends on A. I found some things by Google searching, but most of these were

Re: [GENERAL] Sequence moves forward when failover is triggerred

Craig Ringer wrote: On 07/11/2012 07:23 AM, Andy Chambers wrote: I think I made a poor decision by having our application generate checkbook numbers on demand using sequences. Sure did. Sequences are exempt from most transactional rules; that's why they're fast and lock-free. This may be

Re: [GENERAL] create a script which imports csv data

Robert Buckley wrote: I have to create a script which imports csv data into postgresql The csv data is automatically created from an external database so I have no influence over which columns etc are downloaded. How can I best create a table for the import? This is what I do: 1) I have a

Re: [GENERAL] Easy form of insert if it isn't already there?

Chris Angelico wrote: On Wed, Feb 15, 2012 at 5:26 PM, Bartosz Dmytrakbdmyt...@eranet.pl wrote: e.g. You can use BEGIN... EXCEPTION END, good example of such approach is there: http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE; I wonder

Re: [GENERAL] manage changes to views having depencies

Eric Worden wrote: Can anyone recommend a reasonably efficient system for changing a view definition (say by adding a column) when it has a bunch of dependent functions? Right now I work with the output from pg_dump to recreate things after doing DROP VIEW ... CASCADE. But the pg_dump schema

Re: [GENERAL] serial data type usage

EXT-Rothermel, Peter M wrote: I have a table where I would like the primary key to be generated during the insert. Here is a simplified example: CREATE TABLE employee_type { tname varchar(10) PRIMARY KEY, id_prefix char(1) ; ... } tname | id_prefix --+--

Re: [GENERAL] Schema Upgrade Howto

Thomas Guettler wrote: Hi, is there a schema upgrade howto? I could not find much with google. There is a running DB and a development DB. The development DB has some tables, columns and indexes added. What is the preferred way to upgrade? I see these solutions: - pg_dump production DB.

Re: [GENERAL] Advice on implementing counters in postgreSQL

Marco Bizzarri wrote: Hi all. I need to keep a numer of counters in my application; my counters are currently stored in a table: name | next_value | year The counters must be progressive numbers with no holes in between them, and they must restart from 1 every year. What I've done so

Re: [GENERAL] creating a perfect sequence column

Jack Brown wrote: Dear list, I need some tips and/or pointers to relevant documentation implementing (what I chose to call) a perfect sequence i.e. a sequence that has no missing numbers in the sequence. I'd like it to auto increment on insert, and auto decrement everything bigger than its

Re: [GENERAL] Primary Key with serial

x asasaxax wrote: I have the following tablecreate table product(cod serial, user_cod bigint, constraint product_fk Foreign Key(user_cod) references user(cod), constraint product_pk Primary Key(cod, user_cod)); What i want to happend is that: user_codcod 1

Re: [GENERAL] Trigger to run @ connection time?

Alban Hertroys wrote: On Mar 11, 2008, at 10:28 PM, Tom Lane wrote: An ON CONNECT trigger enforced by the database seems a bit scary to me. If it's broken, how you gonna get into the DB to fix it? regards, tom lane If creating the trigger wouldn't be possible from within the

Re: [GENERAL] How do I aggregate data from multiple rows into a delimited list?

D. Dante Lorenso wrote: I want to select several rows of data and have them returned in a single record with the rows joined by a delimiter. Review the User Comments at http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html; for some ideas.

Re: [GENERAL] How to enforce uniqueness when NULL values are present?

Christian Schröder wrote: Berend Tober wrote: Christian Schröder wrote: Peter Eisentraut wrote: A first step in that direction would be to rethink the apparently troublesome use of null values. Some of the limits are only valid after a given date, whereas

Re: [GENERAL] How to enforce uniqueness when NULL values are present?

Christian Schröder wrote: Peter Eisentraut wrote: A first step in that direction would be to rethink the apparently troublesome use of null values. Some of the limits are only valid after a given date, whereas other limits are valid all the time. How would you put this

Re: [GENERAL] How to capture and handle failed INSERT

Postgres User wrote: I'm using this code to increment a counter table: IF Exists (SELECT keyfld FROM counter_tbl WHERE key_fld = 'key_val') THEN UPDATE counter_tbl SET counter_fld = counter_fld + 1 WHERE key_fld = 'key_val'; ELSE INSERT INTO counter_tbl(key_fld, counter_fld)

Re: [GENERAL] resetting sequence to cur max value

l_sequence_name ALIAS FOR $1; l_last_value ALIAS FOR $2; BEGIN IF l_last_value = 0 THEN PERFORM setval(l_sequence_name,1, False); ELSE PERFORM setval(l_sequence_name,l_last_value); END IF; RETURN 1; END;' LANGUAGE 'plpgsql' VOLATILE; Regards, Berend Tober ---(end

Re: [GENERAL] Male/female

Raymond O'Donnell wrote: Just wondering.how do list member represent gender when storing details of people in a database? I've done it two ways: * A bool column, with the understanding that true/false represents one gender or the other. * Create a domain, something like: CREATE DOMAIN

Re: [GENERAL] Male/female

Joshua D. Drake wrote: On Fri, 2006-12-08 at 09:31 -0700, John Meyer wrote: COPY gender (gender_pk, gender) FROM stdin; 0(unknown) 1Male 2Female 3Trans \. Not to take this completely off track, but isn't transgendered not so much a gender as it is a process of moving

Re: [GENERAL] Male/female

Scott Marlowe wrote: On Fri, 2006-12-08 at 10:44, John Meyer wrote: David Fetter wrote: On Fri, Dec 08, 2006 at 03:23:11PM -, Raymond O'Donnell wrote: Just wondering.how do list member represent gender when storing details of people in a database? I usually

Re: [GENERAL] PG Admin

Bob Pawley wrote: Your missing the point. I am creating a design system for industrial control. The control devices need to be numbered. The numbers need to be sequential. If the user deletes a device the numbers need to regenerate to again become sequential and gapless. Could you explain

Re: [GENERAL] Separation of clients' data within a database

John McCawley wrote: Oh, I see, so there's one master schema, and one customer schema, and the customer schema views are automatically filtered based on login...Makes sense...I will definitely try to implement this, thanks! I've on-and-off toyed with the idea of accomplishing a similar

Re: [GENERAL] chop off non-meaningful digits

A. Kretschmer wrote: am Tue, dem 14.11.2006, um 0:58:56 -0500 mailte Tom Lane folgendes: SunWuKung [EMAIL PROTECTED] writes: Yep, I think this is it: select trim(trailing '0.' from 1.020) Um, I think not: regression=# select trim(trailing '0.' from 1000.000); rtrim ---

Re: [GENERAL] [SQL] Can we convert from Postgres to Oracle !!???

Devrim GUNDUZ wrote: On Tue, 2006-10-17 at 14:21 +0530, Sandeep Kumar Jakkaraju wrote: Can we convert from Postgres to Oracle !!??? Yes!!??? But of course, that begs the question: Why on earth would you want to!!??? ---(end of

Re: [GENERAL] Is Postgres good for large Applications

Sandeep Kumar Jakkaraju wrote: Is Postgres good for large Applications ?? Yes. cf., e.g., http://www.computerworld.com.au/index.php?id=760310963; ... Vice president of operations at Afilias, Ram Mohan said the .ORG database will be based on a standard implementation of PostgreSQL version

Re: [GENERAL] Can we convert from Postgres to Oracle !!???

Sandeep Kumar Jakkaraju wrote: Can we convert from Postgres to Oracle !!??? Umm, this would be the wrong forum for that. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] remote duplicate rows

. But I suppose one can make the point that your suggestion is a great solution, given the contrived example and insufficient problem understanding presented by the OP -- I really think he needs more help than he realizes. Regards, Berend Tober ---(end of broadcast

Re: [GENERAL] PostgreSQL slammed by PHP creator

AgentM wrote: On Sep 14, 2006, at 11:27 , Arturo Perez wrote: One performance enhancement that Lerdorf suggested based on code analysis was to use MySQL instead of PostgreSQL for the database. ...If you give up A, C, I, and D, of course you get better performance- just like you can

Re: [GENERAL] Template1 oops

On Wed, Sep 13, 2006 at 10:56:41AM +0200, Alban Hertroys wrote: I found an oops in one of our template1 databases; tables and stuff were apparently loaded into the wrong database (namely template1). I found this page describing a solution:

Re: [GENERAL] Cast null string '' to integer 0

Guy Rouillier wrote: Dwight Emmons wrote: I am upgrading from Postgres 7.2 to 8.1. We have multiple systems already in place that took advantage of the implicit cast of a null '' string to an integer of '0'. It is not financially feasible for us to modify all the instances. Does anyone

Re: [GENERAL] Best approach for a gap-less sequence

Jorge Godoy wrote: Berend Tober [EMAIL PROTECTED] writes: A business requirement is to generate table rows that have uniformly increasing, whole number sequences, i.e., the gap-less sequence. This is something that I'll also have to code ;-) But the sequence for employees would also

Re: [GENERAL] Best approach for a gap-less sequence

elein wrote: On Mon, Aug 14, 2006 at 02:46:17PM -0700, Adrian Klaver wrote: On Monday 14 August 2006 01:59 pm, Brad Nicholson wrote: On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote: Wouldn't SELECT ... FOR UPDATE give you the row lock you need without locking the table

Re: [GENERAL] Best approach for a gap-less sequence

locked a selected row or rows, SELECT FOR UPDATE will wait for the other transaction to complete, and will then lock and return the updated row (or no row, if the row was deleted). ... Regards, Berend Tober ---(end of broadcast)--- TIP 3: Have you

Re: [GENERAL] Best approach for a gap-less sequence

Brad Nicholson wrote: On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote: Jorge Godoy wrote: Chris [EMAIL PROTECTED] writes: I'm not sure what type of lock you'd need to make sure no other transactions updated the table (see http://www.postgresql.org/docs/8.1/interactive/sql

Re: [GENERAL] Disabling and enabling constraints and triggers to

Ken Winter wrote: It's a basic bit of database administration work. I'm trying to establish two (or if necessary more) instances of the same database - a production instance and a development instance - and a change management process for coordinating them. As you can probably guess: You

Re: [GENERAL] How to read cleartext user password from pgsql database

''||encode(digest(ls_passwd||ls_usename , ''md5''), ''hex'') = passwd); END;' LANGUAGE 'plpgsql' VOLATILE; So, you can see that pg_shadow.passwd stores the md5 hash of the concatinated plaintext password and username. Regards, Berend Tober ---(end of broadcast

Re: [GENERAL] Null and Void() - Or, Abandon All Hope Ye Who allow

be committed NULL initially. Regards, Berend Tober 860-767-0700 x118 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] What is the point of create or replace view command

Chris Velevitch wrote: On 6/5/06, Berend Tober wrote: Chris Velevitch wrote: What is the point of the create or replace view command if you can't change the column and data types ? You could have a calculated column and change the calcuation. You could change the sort order, selection

Re: [GENERAL] What is the point of create or replace view command

Chris Velevitch wrote: What is the point of the create or replace view command if you can't change the column and data types ? You could have a calculated column and change the calcuation. You could change the sort order, selection, or grouping clauses. Just to name two.

Re: [GENERAL] Age function

:00'::interval, now()) might help, professorial pontificationas would RTFM (http://www.postgresql.org/docs/8.1/static/functions-datetime.html;, Table 9-26. Date/Time Functions). /professorial pontification Regards, Berend Tober begin:vcard fn:Berend Tober n:Tober;Berend org:Seaworthy Systems

Re: [GENERAL] Feature-Request: Login-Procedure

it to update their password expiration date to NOW()+30 DAYS, so that the data base automatically expires accounts that aren't being used. Regards, Berend Tober begin:vcard fn:Berend Tober n:Tober;Berend org:Seaworthy Systems, Inc. adr:;;22 Main Street;Centerbrook;CT;06409;USA email;internet:[EMAIL

Re: [GENERAL] How to replace rows in table so that foreign key rows

pg_class.oid = tgrelid) WHERE oid = 'country'::pg_catalog.regclass; Regards, Berend Tober ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Backup method

Bob Powell wrote: I have a systems admin that is backing up our Linux computers (postgres) by backing up the directory structure. This of course includes all the files that pertain to my postgres databases. I maintain that using pgdump and creating a file of SQL commands for restore is a

Re: [GENERAL] Advantages of PostgreSQL over MySQL 5.0

Tony Caduto wrote: Jimbo1 wrote: With MySQL, customers across all industries are finding ... If any PostgreSQL devotees on this group can comment on the above and its accuracy/inaccuracy, I'd really appreciate it. That's exactly what it is propoganda, I can think of two really high

Re: [GENERAL] How to idenity duplicate rows

Peter Eisentraut wrote: David Inglis wrote: Can anybody assist with this problem I have a table that has some duplicated rows of data, I want to place a unique constraint on the columns userid and procno to eliminate this problem in the future but how do I identify and get rid of the

Re: [GENERAL] multi-column aggregates

dissatisfied with having to cast my data pairs as POINT data type, once I got confortable with that it made a lot of sense. Regards, Berend Tober ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] multi-column aggregates

; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Regards, Berend Tober Seaworthy Systems, Inc. 860-767-9061 ---(end of broadcast)--- TIP 4: Have you searched our list

  1   2   >