Harald,
Thank you.
The query
SELECT id, a[1] AS name, a[2] AS percent
FROM ( SELECT id, regexp_split_to_array(regexp_split_to_table(comment, ',
*'), '\W+') AS a
FROM project ) AS dummy
should work un every halfway recent PostgreSQL version - dunno about 8.1.
I tried it but got error
soap response below contains table of products, approx 5000 rows.
Table of products (below) is nearly similar structure as xml data.
Products table needs to be updated from xml data in every hour.
How to add this xml data to table of products ?
Should I use xpath() function or any other ides ?
U
Scott,
Thank you.
I know you're running windows, but if you can get bash working on it,
here's a simple bash script I wrote that when it detects too many
people connected creates a table called pg_stat_bk_20110120140634
(i.e. date and time) so I can then look over what was in
pg_stat_activity w
Andy,
Then answer these questions:
Does ram using increase over the week?
Server was re-started yesterday and there was little use after restart.
server is idle. Task Manager shows now
PF Usage 1.22 GB
Physical Memory (K)
Total 4191872
Avail 348 (last 4 vary)
System cache 35676xx (last
Andy,
How to automatically re-start postgres service in every night ? Or is it
better to switch to 32bit server?
Neither. You need to fix the problem.
How to configure postgres so that freeze issue can addressed ?
E.q. if query runs more that 60 seconds, postgres dumps its status and long
Thank you.
2. In point 2. add FOR UPDATE
3. Use READ COMMITED TRANSACTION ISOLATION LEVEL
Don't lock tables, You wrote you can generate invoices for few days
backward,
so you don't need locking whole table.
Don't use seqences, as sequence value will don't get back when transaction
is
rolled ba
Yes. This is customer requirement and I cannot change it.
OR... can you go back to your customer and tell them they wont like this.
Really really they should let you do it correctly. I find people dont
change because they dont have to, not because there is an actual reason.
Many times, given a
Andy,
SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7),
'^[0-9]*'),'')::int),0)+1
FROM invoice
where date= ?invoicedate
is used to get next free invoice number if new invoice is saved.
If multiple invoices are saved concurrently from different processes,
they will probably get
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
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
Invoice numbers have format yymmddn
where n is sequence number in day staring at 1 for every day.
command
SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7),
'^[0-9]*'),'')::int),0)+1
FROM invoice
where date= ?invoicedate
is used to get next free invoice number if new invoice i
Loading a dump file into an older server may require manual editing of
the dump file to remove syntax not understood by the older server. "
pg_restore ignores erros during restore (it only returns exit code 1).
So "manual editing of the dump file to remove syntax not understood" is
never requi
Robert,
I'm probably misunderstanding but "CREATE DATABASE foo TEMPLATE bar" will
clone bar as foo including data. Of course this only works within the same
cluster.
Than you.
You are genious
I haven't never tought about this.
Will this work if database bar is accessed by other users ? Proba
Another testimonial to the stability of Postgres :)
We may be arguing semantics
here but I would consider dump/restore an admin function. How do you
handle a
client restoring a database currently?
Database is 8.0 compliant.
In this case 8.4 pg_dump/pg_restore is used to dump and restore with a
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 pg
Why does it have that requirement? And why doesn't it use the pg_dump
that came with the server? It seems pretty lame to assume that your app
has to provide pg_dump and not any other part of the Postgres
installation.
Application is like pg_admin.
It is typical client application which is used
No, this is just pilot error. Any version of pg_dump will produce
output that is meant to be loaded into the matching server version
(or a later version). If you are intending to load back into 8.4,
use the 8.4 pg_dump.
You may have been reading the recommendation to use the later version's
pg_
Adrian,
thank you.
I am not sure I follow. Are you taking a pg_dump of a 9.0 database using a
9.0
version of pg_dump and trying to restore to a 8.4.3 database or are using
the
9.0 pg_dump against the 8.4.3 server and then restoring back to it? In
either
case the problem you see above will proba
Server is
PostgreSQL 8.4.3 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
4.3.2-1.1) 4.3.2, 32-bit
Backup is created using 9.0RC pg_dump.exe file
Trying to restore from this backup to same server using 9.0RC pg_restore.exe
causes error
"..\pg_dump\pg_restore.exe" -h mysite.com -U
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
pg_catalog.pg_get_t
> 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.
Steps to reproduce:
1. Ran latest pgAdmin in windows server 2005 Standard x64 Edition
2. Restore data to Postgres 9.0 linux server from 450 MB backup file if
only
SSL connection is enabled
After some time pg_restore reports that connection is closed.
server log is below.
How to restore 450 MB ba
Tim,
Thank you.
It can be done in SQL: "SUM(kogus) OVER (PARTITION BY toode
ORDER BY ID) - kogus" (*1) will give you the running sum of
the product up to that row. You can then subtract that value
from the delivered quantity to calculate the delivered quan-
tity for the current row.
I tried t
In 8.4, script
create temp table test ( test bytea );
insert into test values(E'\274')
Causes error
ERROR: invalid byte sequence for encoding "UTF8": 0xbc
HINT: This error can also happen if the byte sequence does not match the
encoding expected by the server, which is controlled by "client_
Tom,
Thank you.
Alvaro was right --- you've got damage in the system catalogs, not just
their indexes. This looks like missing entries in pg_amop.
postgres -D data mydb
PostgreSQL stand-alone backend 8.1.9
backend> select * from pg_amop
1: amopclaid (typeid = 26, len = 4, typmod =
Alvaro,
You can get around that particular problem by reindexing the pg_authid
table. But my guess is that you'll find that there's corruption
elsewhere that's not so easily recoverable ...
Thank you.
reindexing system tables and whole database succeeds.
After that I can connect to database c
I noticed that backups created by pg_dump are not usable if backup file size
is greater than 2 GB.
Backups are create in 8.1 I tried to restore them in 8.4. Backup contains
many tables. There is a large table, attachme containing bytea field. This
table grows rapidly. If .backup file size is b
Hard disk containing PostgreSql 8.1 database on Windows crashes and there
was no new
backup copy.
I installed 8.1.9 to new computer and copied data directory from crashed
disk to it.
data directory contains a lot of files with a lot of data.
Trying to connect to template0 or any other databas
8.x Db contains tables
create table ametikoh ( ametikoht numeric(7) NOT NULL DEFAULT
nextval('firma1.ametikoh_ametikoht_seq'::regclass) primary key );
create table iandmed ( ametikoht numeric(7) references ametikoh);
I created conversion script which normalizes column types:
alter table amet
I found that \ characters needs not to be quoted in password strings
create role test1 password '\'
is OK.
I havent found any mention of this behaviour in docs.
Where this is documented ?
I created role
create role test2 password
Trying to login using password ' causes error from my ODB
I have notebook which can be booted into Windows XP/NTFS and into Linux.
Notebook is not connected to any network. There are separate IDE partitions
for both OS.
I want same Postgres 8.1 database to be available in both modes.
I think I must put Postgres database cluster into a NTFS partition wh
It is difficult to write standard-compliant code in Postgres.
There are a lot of constructs which have SQL equivalents but are still used
widely, even in samples in docs!
For example, there are suggestions using
now()::CHAR!=foo
while the correct way is
CAST(CURRENT_DATE AS CHAR)<>foo
now() fu
I inserted two binary files to a bytea field "contents"
file1 size was 7834649 bytes
file2 size was 5888534 bytes
select filename,
octet_length(contents),
length(contents)
from localfil
returns
"file1 ";16777184;16777184
"file2 ";157688
I'm using Postgres 8.1 and latest ODBC driver in Windows XP with 256 MB RAM.
When trying to insert a 6 MB binary file to a bytea field, out-of-memory
error is writeen to ODBC log file and insert fails. Smaller files are
inserted OK
Any idea how to load 6 MB file to a bytea field or avoid out-o
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 broadcast
I have a table of vacations
create table vacation (
id integer primary key,
dstart date,
dend date );
I need to find first non-vacation day before given date.
This can be done using the following procedural vfp code
function nonvacation( dbefore )
for i=dbefore to date(1960,1,1) step -1
se
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
Platform:
"PostgreSQL 8.1.0 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)"
To reproduce:
1. Make sure that postgres.conf file contains in first line:
archive_command = 'copy "%p" "c:/arhiiv/%f"'
2. Edit postgres.conf file by adding # before this line
#archive_command
I want to change archive_command parameter in server postgres.conf file from
client application.
Doc wrote that set_config() applies to current session only and there is no
pg_write_file() function.
How to set archive_command from client permanently?
Platoform: "PostgreSQL 8.1.0 on i686-pc-min
I have a table containing month column in format mm.
create table months ( tmkuu c(7));
insert into months values ('01.2005');
insert into months values ('02.2005');
How to create select statement which converts this column to date type
containing last day of month like
'2005-01-31'
'2005-0
I'm using Postgres 8.1 in Windows XP
Sometimes when using pgAdmin the following Guru hint appears suddenly:
"Running VACUUM recommended
The estimated rowcount on the table "firma1.algsa" deviates significantly
from the actual rowcount. You should run VACUUM ANALYZE on this table.
Instead of iss
I have 10 CHARACTER columns in table mytable. Mytable is indexed by some of
those columns.
CREATE TABLE mytable ( col1 CHARACTER(10),
col2 CHARACTER(10),col3 CHARACTER(10),col4 CHARACTER(10),col5
CHARACTER(10),col6 CHARACTER(10),col7 CHARACTER(10),col8 CHARACTER(10),col9
CHARACTER(10), col10 CH
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 ?
Andrus.
To increase performance, I'm thinking about storing copies of less
frequently changed tables in a client computer.
At startup client application compares last change times and downloads newer
tables from server.
CREATE TABLE lastchange (
tablename CHAR(8) PRIMARY KEY,
lastchange timestamp with
Greg,
using views would be nice.
I have also a add privilege which allows to add only new documents. I think
that this requires writing triggers in Postgres.
This seems to be a lot of work.
I do'nt have enough knowledge to implement this in Postgres.
So it seems to more reasonable to run my ap
> Does the application really need superuser privileges or is that
> just a convenience? It's usually a good idea to follow the "Principle
> of Least Privilege" -- do some searches on that phrase to learn
> more about it and the rationale for following it.
> Whether this approach is "secure and b
> I forgot to add, this is of course a "simplistic" approach which:
> 1. may be simply wrong
> 2. assumes data is available to user in nformation_schema (I guess the
> information schema lists only data owned by user; yet I am not sure
> about that).
> 3. assumes foreign keys have really simple set
> On Friday 01 July 2005 19:49, you wrote:
>> In Postgres 8 I tried commad
>>
>> DELETE FROM customer WHERE id=123
>
> (snip)
>
>> ---(end of broadcast)---
>> TIP 8: explain analyze is your friend
>
>
> Automatically answered?! :-)
>
> explain analyze
The command
UPDATE dok SET krdokumnr=NULL WHERE krdokumnr NOT in (select dokumnr from
dok);
runs forever. Postgres.exe process takes 90% of CPU time, disk LED is
flashing.
Platform: Win XP Prof SP2, Postgres 8
dok table has only 7651 rows
Killing client application does not help. Only killing pos
I have item table and many child tables where the items are used.
I want to merge two item codes into single item in all tables.
It is not nice to write a lot of separate UPDATE statements for each table.
So I want to utilize REFERENCES clause for merging.
I tried the following code but got dupli
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
I need to create referential integrity constraints:
CREATE TABLE classifier (
category CHAR(1),
code CHAR(10),
PRIMARY KEY (category,code) );
-- code1 references to category 1,
-- code2 references to category 2 from classifier table.
CREATE TABLE info (
code1 CHAR(10),
code2 CHAR(10),
FOREIGN KE
>> I have table containing different types of documents (type A, B and C).
>>
>> Each document type must have separate sequential ID starting at 1
>>
>> ID of first inserted record of type A must be set to 1
>> ID of first inserted record of type B must be also set to 1
>> ID of second record of ty
I need to allocate some number of sequential values for primary keys.
Postgres nextval() function does not allow to allocate more than one number.
So it is not possible to get a range of sequential numbers from sequence
using nextval()
To solve this, I created table containing id current values
I have table containing different types of documents (type A, B and C).
Each document type must have separate sequential ID starting at 1
ID of first inserted record of type A must be set to 1
ID of first inserted record of type B must be also set to 1
ID of second record of type A must be set to
55 matches
Mail list logo
