Database contains about 300 tables.
Most of them contain columns of char(n) type.
How to convert all those columns to varchar automatically ?
Is it possible to run some update commands in system tables for this ?
Or is it possible to create pgsql script which creates dynamically alter table
On 10/6/2014 5:29 AM, Andrus wrote:
Database contains about 300 tables.
Most of them contain columns of char(n) type.
How to convert all those columns to varchar automatically ?
Is it possible to run some update commands in system tables for this ?
Or is it possible to create pgsql script which
Hello List,
May I know will idle cause any potential performance
issues for psql8.3 please?
version (PostgreSQL 8.3.18 on x86_64-unknown-linux-gnu, compiled
by GCC 4.1.2)
E.g., got 10 idle connections for 10 days.
select current_query
On Sat, Oct 4, 2014 at 3:46 PM, Andrus kobrule...@hot.ee wrote:
In my db people often looks for different period sales using different
filters and will sum
There are lot of sales and every sale is individual record in sales table.
So increasing sequential scan speed is important.
I tried
On 10/6/2014 10:07 AM, Emi Lu wrote:
Hello List,
May I know will idle cause any potential performance issues for
psql8.3 please?
version (PostgreSQL 8.3.18 on x86_64-unknown-linux-gnu, compiled by GCC
4.1.2)
E.g., got 10 idle connections for 10 days.
select current_query from
This query might work for you, but double check all result statements first.
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' ||
quote_ident(c.relname)
|| ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;'
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
The administors (that are not from my company) are strongly against
changing the Postgresql version :( so if this is a bug from Postgresql
they want me to show a documentation that guarantees them that it will
be fixed on an upgrade.
On 05/10/2014 17:06, Andy Colson wrote:
On 10/05/2014 10:00
I'm wondering if anyone can help with advice on how to manage large lists/sets
of items in a postgresql database.
I have a database which uses multiple lists of items roughly like this:
CREATE TABLE List (
ID SERIAL,
Name VARCHAR
);
and a table containing individual entries in the
El 06/10/14 02:38, Shingo horiuchi escribió:
Dear All,
I have read about query tuning and attempt to check the impact of
exchanging the driving table in Join condition.
To test Simple Join condition, I prepared Two tables.
One is the table for employees and another is the table
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Richard Frith-Macdonald
Sent: Monday, October 06, 2014 4:02 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to get good performance for very large lists/sets?
Hi!
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' ||
quote_ident(c.relname)
|| ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;'
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_type t ON t.oid =
On 10/6/2014 3:02 AM, Richard Frith-Macdonald wrote:
I'm wondering if anyone can help with advice on how to manage large lists/sets
of items in a postgresql database.
I have a database which uses multiple lists of items roughly like this:
CREATE TABLE List (
ID SERIAL,
Name VARCHAR
Melvin Davidson melvin6...@gmail.com writes:
This query might work for you, but double check all result statements first.
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' ||
quote_ident(c.relname)
|| ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;'
FROM pg_class
Le 2014-10-06 à 13:22, Andy Colson a...@squeakycode.net a écrit :
On 10/6/2014 3:02 AM, Richard Frith-Macdonald wrote:
I'm wondering if anyone can help with advice on how to manage large
lists/sets of items in a postgresql database.
I have a database which uses multiple lists of items
-Original Message-
From: Richard Frith-Macdonald [mailto:richard.frith-macdon...@brainstorm.co.uk]
Sent: Monday, October 06, 2014 1:53 PM
To: Igor Neyman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to get good performance for very large lists/sets?
On 6 Oct 2014, at
On 6 Oct 2014, at 17:54, Igor Neyman iney...@perceptron.com wrote:
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Richard
Frith-Macdonald
Sent: Monday, October 06, 2014 4:02 AM
To: pgsql-general@postgresql.org
On 10/6/2014 12:52 PM, Richard Frith-Macdonald wrote:
On 6 Oct 2014, at 17:54, Igor Neyman iney...@perceptron.com wrote:
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Richard Frith-Macdonald
Sent: Monday, October
On Mon, Oct 6, 2014 at 1:02 AM, Richard Frith-Macdonald
richard.frith-macdon...@brainstorm.co.uk wrote:
I'm wondering if anyone can help with advice on how to manage large
lists/sets of items in a postgresql database.
I have a database which uses multiple lists of items roughly like this:
Ohhh..
Um, completely left field, but, if your items are sequential in some
way, maybe there is some gross misuse of ranges you could use?
http://www.postgresql.org/docs/9.2/static/rangetypes.html
-Andy
Another thought, for the case of find all the items in list A but not
list B
On 10/2/14, 9:27 AM, Adam Brusselback wrote:
i've also tried to implement a database versioning using JSON to log changes in
tables. Here it is:
https://github.com/fxku/audit[https://github.com/fxku/audit][https://github.com/fxku/audit[https://github.com/fxku/audit]]
On 10/3/14, 11:21 AM, Jonathan Vanasco wrote:
I've been able to fix most of my slow queries into something more acceptable,
but I haven't been able to shave any time off this one. I'm hoping someone has
another strategy.
I have 2 tables:
resource
resource_2_tag
I want to
On 10/3/14, 2:24 PM, Israel Brewster wrote:
I have a Postgresql 9.3.5 server running on CentOS 6.5. In looking at some
stats today, I saw that it was handling about 4-5 transactions/second
(according to the SELECT sum(xact_commit+xact_rollback) FROM pg_stat_database;
query), and an instance
On 10/6/14, 10:15 AM, Andy Colson wrote:
On 10/6/2014 10:07 AM, Emi Lu wrote:
Hello List,
May I know will idle cause any potential performance issues for
psql8.3 please?
version (PostgreSQL 8.3.18 on x86_64-unknown-linux-gnu, compiled by GCC
4.1.2)
FYI, 8.3 is almost 7 years old and is no
On 10/5/14, 10:06 AM, Andy Colson wrote:
On 10/05/2014 10:00 AM, Adrian Klaver wrote:
On 10/05/2014 07:37 AM, Tim Mickelson wrote:
Sorry about that, the precise version is: PostgreSQL 9.1.9 on
x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat
4.4.7-3), 64-bit
Well 9.1
On 10/6/14, 12:41 PM, hari.fu...@gmail.com wrote:
Melvin Davidson melvin6...@gmail.com writes:
This query might work for you, but double check all result statements first.
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' ||
quote_ident(c.relname)
|| ' ALTER COLUMN ' ||
On 10/6/14, 3:02 AM, Richard Frith-Macdonald wrote:
I'm wondering if anyone can help with advice on how to manage large lists/sets
of items in a postgresql database.
I have a database which uses multiple lists of items roughly like this:
CREATE TABLE List (
ID SERIAL,
Name VARCHAR
Also, don't forget to test for relkind = 'r'. My bad from before.
Revised query is below.
SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.'
|| quote_ident(c.relname)
|| ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE
varchar;'
FROM pg_class c
JOIN
On Oct 6, 2014, at 5:56 PM, Jim Nasby wrote:
Don't join to the resource table; there's no reason to because you're not
pulling anything from it.
Thanks the reply!
I'm not pulling anything from the resource table, but the join is necessary
because I'm filtering based on it. ( see the WHERE
On 07/10/14 10:47, Jim Nasby wrote:
On 10/2/14, 9:27 AM, Adam Brusselback wrote:
i've also tried to implement a database versioning using JSON to log
changes in tables. Here it is:
Jim Nasby jim.na...@bluetreble.com writes:
Just a heads-up: each of those ALTER's will rewrite the table, so unless your
database is tiny this will be a slow process. There's ways to work around
that, but they're significantly more complicated.
I think he's trying to get rid of all the
Melvin Davidson melvin6...@gmail.com writes:
Also, don't forget to test for relkind = 'r'. My bad from before.
In principle you need to ignore attisdropped columns as well.
Thinking about Jim's point about speed: it'd be wise to collapse any
updates for multiple columns in the same table into
31 matches
Mail list logo
