- Original Message -
> From: "Frank Millman"
> To: pgsql-general@postgresql.org
> Sent: Wednesday, October 26, 2016 4:42:29 AM
> Subject: [GENERAL] Locking question
> Hi all
> I am designing an inventory application, and I want to ensure that the stock
> level of any item cannot go negat
Erik Jones wrote:
On Aug 15, 2007, at 1:09 PM, D. Dante Lorenso wrote:
...to ensure that only one server is processing the queue item, so
inside PGSQL, use SELECT ... FOR UPDATE to lock the row...
When my server is under severe load, however, this function begins to
take a long time to execu
Ow Mun Heng wrote:
Hi,
I have 3 tables
foo
foo_loading_source1
foo_loading_source2
which is something like
create table foo (a int, b int, c int)
create table foo_loading_source1 (a int, b int, c int)
create table foo_loading_source2 (a int, b int, c int)
Is there a way which can be made eas
Ow Mun Heng wrote:
> Data which runs in the vicinity of a few million a week.
>
> What are the methods which will effectively provide the
> min/max/average/count/stdev of the weekly sample size based on different
> products/software mix etc.
>
> and still be able to answer correctly, what's the av
Richard Broersma wrote:
On Tue, Mar 4, 2008 at 8:44 AM, Justin <[EMAIL PROTECTED]> wrote:
I searched the archive of the mail list and did not find anything
Search the documentation. There are a couple great examples posted at
http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate
Kynn Jones wrote:
On Tue, Mar 11, 2008 at 5:28 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
"Kynn Jones" <[EMAIL PROTECTED]> writes:
If one can set up this insert operation so that it happens automatically
whenever a new connection is made, I'd like to learn how it's done.
For manual psql sessions
Michael Enke wrote:
I need to know if multiple tables have (may have most probably)
identical content.
Since I want a fast solution (which means not comparing tables row by row),
I thought it would be a good idea to have an sql function operating on a
table or view
similar to md5sum on a file
A B wrote:
I have a table with rows like this
A 1
A 1
B 3
B 3
C 44
C 44
and so on.
and I want it to be
A 1
B 3
C 44
so how can I remove the all the duplicate lines but one?
CREATE TEMP TABLE tmp AS SELECT DISTINCT * FROM t1;
DROP TABLE t1;
CREATE TABLE t1 AS SELECT * FROM tmp;
--
Sent via
I've run into a problem while migrating an existing 8.2.7 data base
to a new server running 8.3.3 (although I think the version numbers
may not matter -- I think I've seen this problem in the past and just
lived with it since so much of Postgresql is so great!).
The problem stems from the f
Tom Lane wrote:
> [EMAIL PROTECTED] writes:
>> Thus, when piping the output (from legacy host 192.168.2.2) to
>> populate the newly initialized cluster, by way of running (on
the new
>> host 192.168.2.3)
>> pg_dumpall -h 192.168.2.2|psql
>> an error occurs in that first sectio
Tom Lane wrote:
> [EMAIL PROTECTED] writes:
>> Thus, when piping the output (from legacy host 192.168.2.2) to
>> populate the newly initialized cluster, by way of running (on the new
>> host 192.168.2.3)
>> pg_dumpall -h 192.168.2.2|psql
>> an error occurs in that first section when the s
I've run into a problem while migrating an existing 8.2.7 data base
to a new server running 8.3.3 (although I think the version numbers
may not matter -- I think I've seen this problem in the past and just
lived with it since so much of Postgresql is so great!).
The problem stems from the fac
I've run into a problem while migrating an existing 8.2.7 data base to a
new server running 8.3.3 (although I think the version numbers may not
matter -- I think I've seen this problem in the past and just lived with
it since so much of Postgresql is so great!).
The problem stems from the fact th
Bruno Wolff III wrote:
On Tue, Jan 02, 2007 at 12:33:14 -0700,
Anthony Masinton <[EMAIL PROTECTED]> wrote:
I would like to combine data from different rows in one column into
one row.
...
Is this possible and if so, how?
Collapsing rows is done with aggregate functions. You c
Joshua D. Drake wrote:
roopa perumalraja wrote:
Thanks a lot for your immediate reply. can you please tell me how to use the command pg_dump to backup specific tables. Thanks a lot in advance.
pg_dump --help
Better yet, try this first:
"http://www.catb.org/~esr/faqs/smart-questions.html";
Ron Johnson wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 01/25/07 09:30, Inoqulath wrote:
Hello Folks
Have a look at this Table:
CREATE TABLE foo(
id serial,
a_name text,
CONSTRAINT un_name UNIQUE (a_name));
Obviously, inserting a string twice results in an error ...is there an
Andrew Edson wrote:
I've been given a file to maintain, the purpose of which is to purge the
database of records more than two years old. (Database setup is pg 8.1.3)
The program (written in perl) enters postgres as the user 'postgres', and is supposed to select foreign-key records from a
Vivek Khera wrote:
On Mar 27, 2007, at 4:09 PM, Tony Caduto wrote:
Another thing is this, how hard could it possibly be for a MS SQL DBA
or Oracle DBA to pick up using PostgreSQL?
I don't think it would take a decent admin of any database to come up
to speed in a very short time as long as th
- Original Message Follows -
From: "Stuart Cooper" <[EMAIL PROTECTED]>
>> "*Really* big sites don't ever have referential
>> integrity. Or if the few spots they do (like with
>> financial transactions) it's implemented on the
>application level (via, say, optimistic locking), never the
>da
>
> >
> > I don't believe this is good design. You'll have to
have a trigger or
> > something to verify that the country_id+state_id on the
city table are
> > exactly equal to the country_id+state_id on the state
table. If you
> > don't, you might have something like (using US city
names...) "cou
>>Is there a shorthand notation when performing a multi-table join and
What's the difference between a "multi-table join" and a "join"?
>> one column is to be equaled in all tables?
>
> Is this you are looking for?
>
> SELECT t1.c7,t2.c6
> FROM t1,t2
> USING (c1,c2,c3)
> WHERE t1.c4='2004-2-28'
> On Fri, Apr 02, 2004 at 11:58:42 -0500,
> John DeSoi <[EMAIL PROTECTED]> wrote:
>> Is there a mechanism to execute a function after the user connects to
>> the database?
Something like an "AFTER CONNECT" trigger (might as well as for a "BEFORE
DISCONNECT" trigger, too!). I've inquired about
> Suppose I have "pg_dump -s" of two pg installs, one is "dev", another
> is "production". Their schemas don't differ too much, and I want to
> get a "diff -u"-like schema diff so I can quickly add missing/remove
> old
> tables/sequences/etc to one or another (manually). Is there some quick
> too
> Dias Bantekas wrote:
>
>> does any one know how to get an md5()-like hash function using
>> pgcrypto for postgresql 7.3 ? without upgrading to 7.4
>>
>> Thanks for any input.
>
> SELECT encode(digest(v_password, 'md5'), 'hex');
BTW,
/usr/share/pgsql/contrib/pgcrypto.sql
is the script that def
> On 5/27/2004 7:15 AM, Rory Campbell-Lange wrote:
>
>> seems to suggest that the functions are schema specific.
>
> It is even better. The property that set's your "schema context" is
> called search_path. This contains a list of schema names. For an
> unqualified (schema name not explicitly give
> On Wednesday 02 June 2004 02:04, BARTKO Zoltan wrote:
>> I would appreciate anyone wiser than me to comment on the following:
>>
>> I am making an app for PostgreSQL (the server). The clients are
>> connecting through the same single user. ...
>>
>> If I want to access a function (like do this o
I can't seem to get right the regular expression for parsing data like
these four sample rows (names and addresses changed to ficticious values)
from a text-type column:
Yolanda Harris, 38, of 40 South Main St., Newtown City, was charged
Sunday with breach of peace and interfering with a police of
>
> SELECT
> SUBSTRING(description FROM '(.*), \\d{1,3},') AS vname,
> SUBSTRING(description FROM '.*, (\\d{1,3}),') AS age,
> SUBSTRING(description FROM '\\d{1,3}, of (.*?),? was charged') AS
> address, SUBSTRING(description FROM ' was charged ([^ ]+)') AS dow,
> SUBSTRING(description FRO
> I am just beginning to investigate schemas, and have run into a
> problem. Searching the mailing lists and documentation doesn't help.
>
> This is 7.3.3 on Linux, kernel 2.4.21, good ole Slackware, compiled
> from source.
>
> Two users, JoeBob and MaryJo. JoeBob has schema xyzzy with table
> pl
> On Thu, Jun 26, 2003 at 03:17:12AM -0400, [EMAIL PROTECTED]
> wrote:
>> > I have a table with 13 fields. Is that
>> > too many fields for one table.
>> > Mark
>> >
>>
>> Thirteen? No way. I've got you beat with 21:
>
> Pfft! Is *that* all? I've got a table with 116 fields.
I *knew* a number of
> This will work in most cases:
>
> SELECT c.relname,
> setval(c.relname, CASE WHEN nextval(c.relname) > 1 THEN
> currval(c.relname)-1 ELSE 1 END,'true')
> FROM pg_class c WHERE c.relkind='S';
>
The main problem with this approach is that, while you get the "current
value", the sequence is incre
> wouldn't a better situation be ADDING a record that is one higher, and
> then doing a select MAX()?
>
> The different triggers could do delete on the old records.
>
In my case that would not apply, because what I had was a need to keep a
"sequence" counter for each employee, so I added a column
> We accidentally created a table called user in our public schema. We
> are now trying to remove the table using DROP TABLE user and even DROP
> TABLE public.user. However every time we try we get an error message:
> ERROR: parser: parse error at or near "user" at character 12
>
> Any suggesti
> <[EMAIL PROTECTED]> writes:
>> The problem I have is that the SQL DDL and DML produced by pg_dump
>> fails to include the ALTER DATABASE ... SET search_path ... statement
>> that sets the search path for when I re-load the database from a dump
>> file.
>
> This functionality is in pg_dumpall, no
> <[EMAIL PROTECTED]> writes:
>> I HAVE used pg_dumpall -g to make a backup of of users and groups,
>> but this output does not include the ALTER DATABASE commands.
>
> No, it looks like pg_dumpall dumps ALTER DATABASE operations for a
> particular database when it dumps that database.
>
>> Does t
>> If You are using Delphi, there is great project called "Zeos
>> objects", and if I remember correctly it has support for large
>> objects.
>>
>Zeos are useing a lot of memory...
Another issue I had with Zeos was that when I looked into possibly using
those components (this was probably over
I'm finding that column defaults are not being assigned to nulls when I
do an insert by way of a an ON INSERT rule on a view. For example, the
following script
\set ON_ERROR_STOP ON
\c template1
--DROP DATABASE testdb;
CREATE DATABASE testdb;
\c testdb
create table test_table (
field1 char(1) no
> On Friday 19 September 2003 09:00, [EMAIL PROTECTED] wrote:
>> I'm finding that column defaults are not being assigned to nulls when
>> I do an insert by way of a an ON INSERT rule on a view. For example,
>> the following script
> [snip]
>
> Hmm - well, you're explicitly telling it to insert VAL
> I think most careers these days are shifting towards "package jobs"
> where one guy does everything.
Either that, or you work for a consulting/contractor outfit, and the
customer periodically cancels the contract in order to play hardball and
"negotiate" for lower rates. Then you find yourself
After I execute a command like
CREATE TYPE employee_wage_journal_sum AS (supplier_pk integer,
employee_pk integer,
hourly_dollars double precision,
annual_dollars double precision);
where does this definition get stored, and what query can I run to get
teh definition back
> On Monday 29 September 2003 13:35, [EMAIL PROTECTED] wrote:
>> After I execute a command like
>>
>> CREATE TYPE employee_wage_journal_sum AS (supplier_pk integer,
>> employee_pk integer,
>> hourly_dollars double precision,
>> annual_dollars double precision);
>>
>> where
I run the following script to export some data from my development
database and then update or insert the records into to the quality
assurance testing database, but I get a warning notice that I don't
understand. Aside from that notice, the script appears to work as
intended, i.e., updating existi
> In article <[EMAIL PROTECTED]>,
> Mike Mascari <[EMAIL PROTECTED]> writes:
>
>> [EMAIL PROTECTED] wrote:
>>> Has some one come up with a similar type script that could be used
>>> in a Postgresql database?
>
>>> The script below was created for a SQLServer database.
>>> Thx,
>>> -Martin
>
>> I h
Whenever I create a temporary table, with something like
CREATE TEMPORARY TABLE temptable1 AS SELECT * FROM paid.ad_hoc_query;
New schemas appear, with names like "pg_temp_1". I guess the appearance
of these schemas with "temp" in the name indicates that they are
"temporary" schemas and related t
Is there any kind of mechanism in pg 7.3 for doing something like what I
would describe as a "login trigger" procedure or maybe "on connect"
trigger, i.e., a way to specify a stored procedure to run when a user
connects to the database?
What I'm thinking is this. Right now, my end-user GUI applica
Is there any kind of mechanism in pg 7.3 for doing something like what I
would describe as a "login trigger" procedure or maybe "on connect"
trigger, i.e., a way to specify a stored procedure to run when a user
connects to the database?
What I'm thinking is this. Right now, my end-user GUI applica
> On Thu, Nov 06, 2003 at 05:01:54 -0300,
> MaRcElO PeReIrA <[EMAIL PROTECTED]> wrote:
>>
>> $ select * from products;
>> prod_id | description
>> +-
>> 1 | S470DXBLM
>> 12 | S470DXABM
>> 33 | RG250DX
>> +-
>> (3 rows)
>
> Quoting [EMAIL PROTECTED]:
>
>> Is there any kind of mechanism in pg 7.3 for doing something like
>> what I would describe as a "login trigger" procedure to run
>> when a user connects to the database?
>>
>
> Berend,
>
> I've got something like that setup on an e-communities site I built.
> Ther
>
> If you're really concerned, you can initdb separate clusters for QAT
> and DEV and run three postmasters using three different ports.
>
Follow-up question: Are different ports really necessary? I currently
have the three different databases defined all in the same cluster, and
differentiated b
> On Fri, 14 Nov 2003 10:01:51 -0500 (EST), <[EMAIL PROTECTED]>
> wrote:
>>The Production database is the "real" data, and we periodically take a
>> back up from Prod and re-instantiate QAT and DEV by dropping them and
>> then restoring from the Prod backup.
>
>> Not that OID's are in short supply
> Whoa! You mean these aren't already separate database clusters or even
> separate systems? I am very shocked, you can't do a proper Dev --> QAT
> --> Prod environment if all three systems are run by the same
> postmaster, or on the same host imo.
I can see having separate clusters would save me
> One thing that I always have to remind myself of is this:
>
> string || NULL is NULL.
> It is usually a good idea to use coalesce() unless you want this to
> happen.
>
See my recent post at
http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=24725
(and in general, every
52 matches
Mail list logo
