d
WHERE r1.c1>=r2.c1 and r1.c2>=r2.c2 and r1.c3>=r2.c3
and
WHERE (r1.c1, r1.c2,r1.c3) >= (r2.c1, r2.c2,r2.c3)
Andrus.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
HAVING clause.
In VFP this select works OK.
Andrus.
---(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 t
n a
multiple times: one time in column expression, and n times in having clause.
Are there plans to fix this?
Andrus.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Here is my problematic query which runs OK in other DBMS.
Only way to run this in Postgres is to duplicate reatasum expression two
times in HAVING clause, right ?
Andrus.
SELECT
'z' as doktyyp,
r1.dokumnr,
r1.kuluobjekt as objekt,
r1.rid2obj,
r1.rid3obj,
r1.rid4obj,
idea why Postgres installer requires physical access to server to
install Postgres ?
Andrus.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that
--
Internal error
---
The PostgreSQL installer must be run on the system console, not in a
terminal services session.
---
OK
---
What I'm doint wrong ?
Andrus.
s that I must
run in console session.
Any idea why ?
Andrus.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Cache: 157xxx
If available physical memory is too low, how to increate it (decrease system
cache) ?
Andrus.
C:\pgsql>bin\initdb -E=UTF8 -d -D data
Running in debug mode.
VERSION=8.1.3
PGDATA=data
share_path=C:/pgsql/share
PGPATH=C:/pgsql/bin
POSTGRES_SUPERUSERNAME=postgres
POSTGRES_BKI=C:/pgs
e no free opened ports in W2K server. Also In my XP client, all
incoming ports are closed.
Andrus.
---(end of broadcast)---
TIP 6: explain analyze is your friend
I have multi company database where each company is stored in different
schema.
When I create incrementally new companis and add data to it ANALYZE command
takes a lot of time: every time it analyzes the previous company data also.
How to run ANALYZE command for a single schema ?
Andrus
.
This minor difference is the only difference between Postgres/mySQL licenses
in client application, right ?
Andrus.
---(end of broadcast)---
TIP 6: explain analyze is your friend
fix this error ?
Environment:
Postgres 8.1.3 running in Windows 2000 server
Client application uses Postgres ODBC driver from XP, no SSL
Andrus.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
select 1!=-1
causes
ERROR: operator does not exist: integer !=- integer
HINT: No operator matches the given name and argument type(s). You may need
to add explicit type casts.
Isn't this a bit stange ?
If I add space before -1 error does not appear.
---(end of broa
text corresponding to sql error code ? In this case I can translate SQL
error codes returned by ODBC driver also.
Andrus.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
check and deletion should occur only when transaction commits.
Any idea ?
Is there any generic way to turn off foreign key constraints before delete
command in transaction ?
Andrus.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ
invalid foreign key data to be loaded into database.
I want that in end of transaction Postgres will check for correctness of
foreign keys like DEFERRED clause suggests.
How this check can be forced ?
Andrus.
---(end of broadcast)---
be to remove the special cases in trigger.c
> (afterTriggerSetState) and tablecmds.c (createForeignKeyTriggers), but I
> haven't tested that.
Thank you.
So I must create and maintain special version of PostgreSQL ?
Andrus.
---(end of broadcast)--
safe to remove those schemas ?
How to force Postgres to remove empty schemas automatically ?
Andrus.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
force postgres to close dead connections immediately, not
after 6 hours ?
Andrus.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
e default timeouts are usually
> upwards of an hour. (You should however ask yourself if you really know
> more about TCP than the authors of the TCP specifications do.)
My queries do not take more time than 5 minutes.
In case of connection loss application re-connects automatically.
Wh
Observed:
order of some rows in newr is different than in oldr
Expected:
newr must have exactly the same order since
CREATE temp TABLE tempreport AS ORDER BY id
creates clustered table.
Is this best method to preform this?
Why postgres 8.1.3 changes order ?
How to preserve order in newr
r';
CREATE TEMP TABLE t2 AS SELECT * FROM tempreport ORDER BY id;
ALTER TABLE t2 DROP COLUMN id;
insert into report SELECT * FROM t2;
Will DROP COLUMN preserve table clustering ?
Is it reasonable to expect that clustered table is inserted in pyhical
order ?
Is
r things Joel wrote that every table must have only natural
primary keys. No surrogates, no ids.
There was no discussion about this in Joel's book
Andrus.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
me
...
in Postgres
Andrus.
---(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
f code written for this. Refusing is difficult.
Andrus.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
cal data engine and
create reports from it.
Andrus.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Turn PITR on
Restore database to the state immediately after transaction is commited and
look for rows presence.
Andrus.
"HH" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hello,
>
> I am running 8.1.3, built from source, on RHEL4/x64.
>
> I h
ndex Cond: ((nimi ~>=~ 'Mokter'::bpchar) AND (nimi ~<~
'Moktes'::bpchar))"
" Filter: (nimi ~~ 'Mokter%'::text)"
"Total runtime: 9.615 ms"
Andrus.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
>do you have an index on klient (lower(nimi))?
Yes. As I wrote in first message, I created index explicity for this test
sample:
create index nimib2 on firma1.klient(lower(nimi) bpchar_pattern_ops);
Andrus.
---(end of broadcast)---
TI
> how about:
> create index nimib2 on firma1.klient(lower(nimi) varchar_pattern_ops);
> ^^^
Hakan, thank you.
Excellent.
It works.
Andrus.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an ap
from network drive.
If I copy application to C: drive in Windows 98 computer, application runs
OK.
Any idea how to fix this ?
Server log file included.
Andrus.
begin 666 baddata.zip
M4$L#!!0(`.!\+35:$XJ)>"<``)[EMAIL PROTECTED]@`'T`<&]S=&=R97-Q;"TR,#
In 9.0 query below returns error
7/42703:ERROR: column pg_trigger.tgisconstraint does not exist
How to change it so that it works in all servers starting at 8.0 (or at
least
from 8.1) to 9.0 ?
Or if this is not possible how to fix it so that it works in 9 ?
Andrus
SELECT
execute query: ERROR: syntax error at
or near "PROCEDURAL"
LINE 1: CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;
How to restore from this backup to 8.4.3 server using 9.0 pg_restore ?
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To
not used.
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
application,
including all VC++ runtime versions and all dlls specific to this version
and invoke
specific pg_dump/pg_restore depending on server version ?
Do you really think that this is reasonable ?
I'nt there a simpler way ?
I havent seen that pg_admin includes every pg_dump / pg_restor
ch is used to edit data in existing
servers running in different sites over internet.
Application must have function to backup and restore whole database in same
>=8 server where it connects. Only 5432 port is open to internet.
Andrus.
--
Sent via pgsql-general mailing list (pgsql
I got to thinking more about this. How are the databases administered? In
other
words how are they started/stopped, upgraded, logs read, etc?
Databases are working many years in 24x7 mode without administration.
For every new new site newest PostgreSql was installed.
Andrus.
--
Sent via
ternet for this also.
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
essed by other users ? Probably it
fails, so it cannot used.
Will this command create exact copy ?
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
tions.
How to make CREATE DATABASE TEMPLATE to work if there are connected users
to template ?
Since pg_dump works if active users are connected, CREATE DATABASE TEMPLATE
should also work.
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your sub
umber if new invoice is saved.
If multiple invoices are saved concurrently from different processes, they
will probably get same number.
How to get unique invoice number for some day in 8.1+ when multiple users
create new invoices ?
Andrus.
--
Sent via pgsql-general mailing list (pgsql-ge
There are 365 days in year.
Do you really think pre-creating sequence for every day for every year is best
solution ?
Andrus.
- Original Message -
From: Jorge Godoy
To: Andrus Moor
Cc: pgsql-general@postgresql.org
Sent: Saturday, January 15, 2011 8:41 PM
Subject: ***SPAM
Invoices can entered also some days forward or back. Users enters invoice date
and expected program to generate next sequential number for this day.
Different users can enter invoices for different days.
Andrus.
- Original Message -
From: Jorge Godoy
To: Andrus Moor
Cc: pgsql
able
4. unlock the table.
Which command should be used to obtain exclusise write access to table (lock
some reosurce or semaphore) ?
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
expects Postgres to be more powerful than FoxPro . He don't
understand why this stops working after upgrade.
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ails due to
disk failure etc then gaps are allowed).
Can this knowledge used to create simpler solution ?
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
query reason .
How to implement this ?
No idea can log_statement=all help.
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
;"4/241";4732;"ExclusiveLock";t
I will try to run it on next crash if users will not restart server before.
A little practice up front might help. When the system is broke, you can
interrogate it to see what its doing. Hopefully we'll see something
locked, and a bunch of things waiting.
Users can restart server without contacting me. For this reason I
asked for automated dump.
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
re are few tables which are used frequently. Hangup occurs probably if
queries are invoked againt those tables.
Is it better to implement this as trigger for those tables?
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscrip
uot; are of incompatible types:
numeric and integer.
How to create script which performs this change and works in 8.0+ ?
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
database in this cluster causes
error
Error connecting to the server: FATAL: index "pg_authid_rolname_index" is
not a btree
How to recover data from this cluster ?
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscrip
om tables in backup after attachme table ?
Andrus.
Environment:
Backups are created in PostgreSql 8.1 running in Windows
pg_dump is used to create backup copies to Buffalo Linkstation Pro ls-320 GB
external disk device ( \\ls ) connected to LAN
backup command: pg_dump.exe -ibv -Z3 -f \\ls\bac
35'::
pg_catalog.oid ORDER BY conname
pg_dump: *** aborted because of error
How to recover data from this database ?
Should I re-create ordering operator in some way ?
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
mands for those tables. This reduces whole data
directory size to 1.2 GB in uncompressed form.
I know which tables contain data to be recovered.
How to dump those tables out ?
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscriptio
ich is controlled by "client_encoding".
In 8.2 this script runs OK.
How to insert data to bytea field ?
Andrus
Both server and client are running in windows.
"PostgreSQL 8.4.0, compiled by Visual C++ build 1400, 32-bit"
show client_encoding
"UNICODE"
--
?
Using npgsql and C# in ASP .NET / Mono.
Andrus.
CREATE TABLE products (
SupplierCode char(20) primary key,
SegmentId char(8),
GroupId char(8),
ClassId char(8),
SeriesId char(8),
VendorId char(2),
PartNumbrt char(27),
Name Text,
Warranty Numeric(6,2),
Price Numeric(10,4),
Quantity Numeric(8,2
I tried to get list of undelivered items using script below.
Second row value (22) is incorrect (it seems to be is cumulative sum but
must be undelivered quantity for this row).
How to fix this so that every row contains correct undelivered quantity ?
Andrus.
-- Order details
CREATE TEMP TAB
backup copy to Postgres 9.0 Linux server from
windows
server using SSL ?
Andrus.
LOG: duration: 2643663.100 ms statement: COPY artpilt (id,
toode, pilt,
pildityyp, esipil
t) FROM stdin;
LOG: SSL renegotiation failure
LOG: SSL failed to send renegotiation request
LOG: SSL renegotiation failure
> Either (1) get a non-lobotomized SSL library
I'm using latest official Postgres 9.0 server and pgAdmin client.
Does one of them contain bug in SSL ?
Andrus.
I tried it but got error in 8.1:
ERROR: function regexp_split_to_table(text, "unknown") does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.
how to get data in 8.1 ?
Andrus.
--
Sent via pgsql-general mailing list (pgsql-g
use column references in default expression
Any idea how to implement this ?
Andrus.
---(end of broadcast)---
TIP 8: explain analyze is your friend
d and then updated in the same query. This eliminates the need
for a transaction.
How to convert this code to Postgres?
Or is there a better method?
Andrus
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
wanting to insert other type
of document.
Is this reasonable? Is this delay noticeable in this case?
Is it possible to get a number concecutive IDs from sequence ?
Andrus.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
r info table ?
It seems that this is not possible in Postgres.
Andrus.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
gt; single row.
>
> Regardless, your syntax doesn't seem to reflect reality. Read the CREATE
> TABLE reference thoroughly.
>
> http://www.postgresql.org/docs/8.0/static/sql-createtable.html
>
> -tfo
>
> --
> Thomas F. O'Connell
> Co-Founder, Information
ALUES ('2');
INSERT INTO invoices VALUES ('2');
BEGIN;
-- Direct Postgres to update all child tables. This causes error.
UPDATE parent SET code='1' WHERE code='2';
-- Remove duplicate row
CREATE TABLE parent AS
SELECT * FROM parent
G
postgres.exe process
stops computer activity.
CREATE TABLE ou1.dok
(
doktyyp char(1),
dokumnr numeric(12) NOT NULL DEFAULT
nextval('"ou1".dok_dokumnr_seq'::text),
krdokumnr numeric(12),
... a lot of other fields
CONSTRAINT dok_pkey PRIMARY KEY (dokumnr)
)
WITHOUT OIDS;
>
> Automatically answered?! :-)
>
> explain analyze DELETE FROM customer WHERE id=123
Martin,
I tried the command explain analyze command but it produces exactly the same
error message
about referential integrity violation as
DELETE FROM customer WHERE id=123
How this command can
sked several times in this newsgroup. This
seems to be a common requirement.
Isn't there really some ready made generic stored procedure which I can use
?
If delete error occurs, Postgres knows the ctid of the restricting record.
Why this ctid cannot be retrieved by ODBC client ?
companies (60 tables).
So I seems that my application needs to be run with super-user privileges in
Postgres.
Andrus
---(end of broadcast)---
TIP 8: explain analyze is your friend
un my application as Postgres superuser
and implement security in application.
Andrus.
"Gregory Youngblood" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>I believe you can probably use views to accomplish this.
>
> You create a view that is populated bas
Is table caching good idea?
Is this best way to implement table caching ?
Andrus.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
I need to create temporary table with data which is dropped at end of
transaction.
CREATE TABLE t1 ( c1 INTEGER ) ;
INSERT INTO t1 VALUES (1);
CREATE TEMP TABLE t2 AS SELECT * FROM t1 ON COMMIT DROP;
Causes ERROR: syntax error at or near "ON" at character 104
How to implement this
)='DEF'
AND substring(col4 for 1) ='G';
Can Postgres 8.1 use indexes to speed the queries above ?
Which is the best way to to write the where clause in this case so that
index is used ?
Andrus.
---(end of broadcast)---
TI
: scanned 4 of 4 pages, containing 122 live rows and 0 dead
rows; 122 rows in sample, 122 estimated total rows
So it seems that vacuum did make anything.
Andrus.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
'2005-01-31'
'2005-02-28'
Andrus.
---(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
86-pc-mingw32, compiled by GCC gcc.exe
(GCC) 3.4.2 (mingw-special)"
Andrus.
---(end of broadcast)---
TIP 6: explain analyze is your friend
To reproduce:
1. Add the following line as last line to postgres.conf file
archive_command='copy "%p" "x"'
Make sure that there is no CR LF characters after this line
2. Restart postgres
3. Issue SHOW archive_command
Observed result:
unset
Note. Adding a newline after this line causes
4. Run SHOW archive_command
Observed:
archive_command value is
copy "%p" "c:/arhiiv/%f"
Expected:
archive_command value is must be
unset
Before config file reload Postgres must set archive_command to unset.
Andrus.
---(end of br
select vacation
locate for between( i, dstart, dend )
if not found()
return i
endif
endfor
return null
but this is very slow
How to implement this as sql select statement ?
Andrus.
---(end of broadcast)---
TIP 1: if posting/reading
In previous week the following (and other active) newsgroups are removed
from news.postgresql.org news server witohut any notice
pgsql.interfaces.odbc
pgsql.interfaces.pgadmin.hackers
How to read those newsgroups from from news server ?
Andrus.
---(end of
d out-of memory error
?
Is it possible to add data to a single bytea field incrementally using
separate insert of update
statements each adding for example 100 KB of data ?
Or is there other solution ?
Andrus.
---(end of broadcast)---
TIP 6: ex
7184;16777184
"file2 ";15768893;15768893
It seems that for some reason postgres returns sometimes escaped string size
(eq. single byte 0x00 uploaded as \\000 size is returned as 5 )
Why result is incorrect ?
How to get the actual size of bytea field ?
Using Postgres 8.1 in XP , clu
r
step. Please clean Postgres.
Andrus.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
which can
be written from Postgres running on Linux or into Linux partition which can
be written from Postgres running in XP.
Any idea how to implement dual OS database ? Is there a ready to install
Linux distro which supports this ?
Andrus.
---(end of broadcast
assword ' causes error from my ODBC client:
Connectivity error: FATAL: password authentication failed for user "test2"
ODBC and postgres logs do not show anything about this issue. How can I find
which is wrong ?
Is
create role test2 password ''&
501 - 586 of 586 matches
Mail list logo
