Here is a function for removing all FKs on a column (yes, PG for some
reason allows multiple similar FKs on a column):
create or replace function remove_fk_by_table_and_column(p_table_name
varchar, p_column_name varchar) returns INTEGER as $$
declare
v_fk_name varchar := NULL;
v_fk_num
On Wed, Aug 29, 2012 at 12:26 AM, Moshe Jacobson wrote:
> The docs said that the descendant tables' columns would be removed unless
> they had had their own definition for that column. I'm not sure what that
It means that when you DEFINE columns in the inherited table they will
be independent fro
>> Can somebody tell me the order in which the Unique Constraints of a table
>> are checked, like when an INSERT is done ?
>
> I had a similar question some time ago, with an answer by Tom lane:
> http://archives.postgresql.org/pgsql-general/2012-03/msg00023.php
You might also find interesting tha
Hi,
The documentation seems to say that when setting up a standby, it is OK to
rsync/copy the files from the master, but the pg_xlog directory should be
excluded. Is there some important difference between the files in pg_xlog
in the master and those in the slave? If so, what is it? If the pg_xlog
Hi,
thanks, this will help me :)
Maybe one small hint:
You use only table name variable (p_table_name) which I assume should
contain schema name. If so then quote_ident ('aaA.bbbB') will give You
"aaA.bbbB" but not "aaA"."bbbB". This will produce error. It is better
idea, in my oppinion, to add p_
I have a question regarding the return value of PQfformat()
I have a 'data' column in my table, type bytea (postgresql 9.1.5).
In postgresql.conf:
bytea_output = 'escape'
When I execute the query:
PGresult *res = PQexec(db, "SELECT data::bytea FROM data_table WHERE id='xxx'")
And I run through
Hey Jason,
2012/8/29 Jason Armstrong
> I have a question regarding the return value of PQfformat()
>
> I have a 'data' column in my table, type bytea (postgresql 9.1.5).
>
> In postgresql.conf:
> bytea_output = 'escape'
>
> When I execute the query:
> PGresult *res = PQexec(db, "SELECT data::byt
On Tue, Aug 28, 2012 at 3:25 PM, Dmitriy Igrishin wrote:
> 2012/8/20 Merlin Moncure
>>
>> On Sun, Aug 19, 2012 at 8:14 AM, Dmitriy Igrishin
>> wrote:
>> >> For various reasons, this often goes the wrong way. Views are often
>> >> the right way to go. +1 on your comment above -- the right way t
Hi All
I have created a function in C language . This is to be called from a pgsql
function .
Let say the C language function name is "test1" and pgsql function name is
"test" .
test1 is called from test three times with different arguments . For
example :
test ()
begin
test1(arg1);--test1
On Wed, Aug 29, 2012 at 8:05 AM, Dmitriy Igrishin wrote:
> Hey Jason,
>
> 2012/8/29 Jason Armstrong
>>
>> I have a question regarding the return value of PQfformat()
>>
>> I have a 'data' column in my table, type bytea (postgresql 9.1.5).
>>
>> In postgresql.conf:
>> bytea_output = 'escape'
>>
>>
On Wed, Aug 29, 2012 at 10:30 PM, Jason Armstrong wrote:
> I see the following:
> 30 5c 33 33 32 5c 30 30 30 5c 30 31 31 5c 30 30
>
> But when I look at the same data in the database:
>
> psql> select encode(substr(data, 0, 16), 'hex') from data_table where
> id='xxx';
> encode
> ---
On 08/29/2012 09:18 PM, tamanna madaan wrote:
1. what are the ways to get the value returned by this library function .
You need to wrap the C library function with a PostgreSQL C extension
that uses the fmgr.h APIs, so it's callable from SQL and can return a
result to SQL. See:
http:
Hello
see http://www.postgresql.org/docs/9.1/static/xfunc-c.html
plpgsql can call any sql function, so you have to register your C
function as custom postgresql sql function
Regards
Pavel Stehule
2012/8/29 tamanna madaan :
> Hi All
>
> I have created a function in C language . This is to be ca
Thanks Sergey.
I create these inherited tables from the main table, and then move them
into a different schema that is backed up separately from the main schema,
since they are for audit logging, which gets very big.
Questions:
1. If I want the inherited table's columns indexed the same way as th
On Wed, Aug 29, 2012 at 6:15 AM, Merlin Moncure wrote:
>
> Hm, couple points (and yes, this is a common problem):
> *) how come you don't have your function depend on the table instead
> of the view? this has the neat property of having the function
> automatically track added columns to the tab
Hello,
When doing the setup for a benchmark of pgsql on an High IO instance of
Amazon, I got the following problem and was wondering if it is expected:
On FreeBSD 9.0 amd64, I installed PostgreSQL 9.1.5 on the boot drive (UFS),
created a ZFS pool using the 2 SSD drives (tank/db), chown pgsql tank
Hi,
pg_dump -s should use add_geometrycolumn(...) instead of creating a
column+constraints with normal DDL
Because, when you don't dump the data, then the record in geometry_columns
is lost.
Cheers,
WBL
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
On Wed, Aug 22, 2012 at 6:52 PM, Albe Laurenz wrote:
> Chris Angelico wrote:
>> I'm looking at these two pages:
>>
>> http://www.postgresql.org/docs/9.1/static/ecpg-sql-set-autocommit.html
>> http://www.postgresql.org/docs/9.1/static/sql-start-transaction.html
>>
>> I'm sure there's something real
Hi:
v9.0.1 on linux.
Trying (failing) a test to see if I can run ddl in a procedure where elements
of the ddl are vars. Consider...
create or replace function newcol (text) returns integer as $$
declare
newcol alias for $1;
begin
alter table target add column newcol text;
return(0);
end
On Tue, Aug 28, 2012 at 9:46 AM, Nicola Cisternino wrote:
> Hi all,
> I'm valutating a complex porting of our application based on Sybase
> SqlAnywhere on PostgreSQL (I've love it ...) and I'd like to have your
> opinion about searching/ordering funcionality.
> The problem is about string comparis
Hello,
you need to use execute command
i.e.
execute 'alter table ' || newcol || ';'
Regards
From: "Gauthier, Dave"
To: "pgsql-general@postgresql.org"
Sent: Wednesday, August 29, 2012 4:56 PM
Subject: [GENERAL] using vars in ddl in procedure ca
Hi all,
I'm valutating a complex porting of our application based on Sybase
SqlAnywhere on PostgreSQL (I've love it ...) and I'd like to have your
opinion about searching/ordering funcionality.
The problem is about string comparision.
MS Sql server, MySql, SqlAnywhere and other DB engine allow
"Gauthier, Dave" writes:
> Trying (failing) a test to see if I can run ddl in a procedure where elements
> of the ddl are vars. Consider...
You'll need to construct the command as a string value and then run it
with EXECUTE. Beware of quoting issues (quote_ident and quote_literal
are your frie
On Wednesday, August 29, 2012 04:44:14 PM Willy-Bas Loos wrote:
> Hi,
>
> pg_dump -s should use add_geometrycolumn(...) instead of creating a
> column+constraints with normal DDL
> Because, when you don't dump the data, then the record in geometry_columns
> is lost.
That shouldn't be a problem wit
2012/8/29 Merlin Moncure
> On Tue, Aug 28, 2012 at 3:25 PM, Dmitriy Igrishin
> wrote:
> > 2012/8/20 Merlin Moncure
> >>
> >> On Sun, Aug 19, 2012 at 8:14 AM, Dmitriy Igrishin
> >> wrote:
> >> >> For various reasons, this often goes the wrong way. Views are often
> >> >> the right way to go.
On Wed, Aug 29, 2012 at 5:23 PM, Andres Freund wrote:
> That shouldn't be a problem with postgres 2 anymore as far as I understand
> things?
>
Why?
--
"Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
On Wednesday, August 29, 2012 05:41:07 PM Willy-Bas Loos wrote:
> On Wed, Aug 29, 2012 at 5:23 PM, Andres Freund
wrote:
> > That shouldn't be a problem with postgres 2 anymore as far as I
> > understand things?
Argh, postgis 2.
Two things:
* the geometry_columns table is not a table anymore but a
Il 29/08/2012 17.08, Merlin Moncure ha scritto:
On Tue, Aug 28, 2012 at 9:46 AM, Nicola Cisternino wrote:
Hi all,
I'm valutating a complex porting of our application based on Sybase
SqlAnywhere on PostgreSQL (I've love it ...) and I'd like to have your
opinion about searching/ordering funcional
Andres Freund writes:
> On Wednesday, August 29, 2012 04:44:14 PM Willy-Bas Loos wrote:
>> pg_dump -s should use add_geometrycolumn(...) instead of creating a
>> column+constraints with normal DDL
>> Because, when you don't dump the data, then the record in geometry_columns
>> is lost.
> That sho
Yup, works like a charm.
Thanks Salah and Tom for the advise !
-dave
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Wednesday, August 29, 2012 11:18 AM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] using vars in ddl in procedure call
"Gau
On Wednesday, August 29, 2012 06:02:24 PM Tom Lane wrote:
> Andres Freund writes:
> > On Wednesday, August 29, 2012 04:44:14 PM Willy-Bas Loos wrote:
> >> pg_dump -s should use add_geometrycolumn(...) instead of creating a
> >> column+constraints with normal DDL
> >> Because, when you don't dump t
Le mercredi 29 août 2012 à 09:48 -0400, Moshe Jacobson a écrit :
>
>
> Questions:
> 1. If I want the inherited table's columns indexed the same way as the
> parent, must I create new indexes on the inherited table?
> 2. If I move the inherited table to a new schema, will its indexes
> also be mo
On Thu, Aug 30, 2012 at 1:56 AM, Nicola Cisternino wrote:
> The same query using " LIKE " is completed in 15 ms while
> using " ILIKE " the execution time is 453 ms
Sounds to me like (pun not intended) there's an index that's being
used in one case and not in the other.
On Wed, Aug 29, 2012 at 10:56 AM, Nicola Cisternino wrote:
> Il 29/08/2012 17.08, Merlin Moncure ha scritto:
>
> On Tue, Aug 28, 2012 at 9:46 AM, Nicola Cisternino
> wrote:
>
> Hi all,
> I'm valutating a complex porting of our application based on Sybase
> SqlAnywhere on PostgreSQL (I've love it
On Wed, Aug 29, 2012 at 12:11 PM, Vincent Veyron wrote:
> Le mercredi 29 août 2012 à 09:48 -0400, Moshe Jacobson a écrit :
>
> > Questions:
> > 1. If I want the inherited table's columns indexed the same way as the
> > parent, must I create new indexes on the inherited table?
> > 2. If I move the
On Wed, Aug 29, 2012 at 9:45 AM, Merlin Moncure wrote:
> citext unfortunately doesn't allow for index optimization of LIKE
> queries, which IMNSHO defeats the whole purpose. to the best way
> remains to use lower() ...
> this will be index optimized and fast as long as you specified C
> locale fo
Is there a method for having unix env variables incorporated into a psql sql
statement?
Ie
Export var='dev'
Psql =c 'select count(*) from $var.customer;'
Doug Little
Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz
Worldwide
500 W. Madison, Suite 1000 Chicago IL 606
On Wednesday, August 29, 2012 12:35:32 PM Little, Douglas wrote:
> Is there a method for having unix env variables incorporated into a psql sql
> statement? Ie
> Export var='dev'
> Psql =c 'select count(*) from $var.customer;'
>
Use double-quotes, not single-quotes. Bash won't interpolate variabl
On Wed, Aug 29, 2012 at 10:31:21AM -0700, Aleksey Tsalolikhin wrote:
> On Wed, Aug 29, 2012 at 9:45 AM, Merlin Moncure wrote:
> > citext unfortunately doesn't allow for index optimization of LIKE
> > queries, which IMNSHO defeats the whole purpose. to the best way
> > remains to use lower() ...
>
On Wed, Aug 29, 2012 at 12:35:32PM -0500, Little, Douglas wrote:
> Is there a method for having unix env variables incorporated into a psql sql
> statement?
> Ie
> Export var='dev'
> Psql =c 'select count(*) from $var.customer;'
export FOO="bar"
psql -c "select count(*) from $FOO.customer;"
Note
On Wed, Aug 29, 2012 at 12:43 PM, Bruce Momjian wrote:
> On Wed, Aug 29, 2012 at 10:31:21AM -0700, Aleksey Tsalolikhin wrote:
>> On Wed, Aug 29, 2012 at 9:45 AM, Merlin Moncure wrote:
>> > citext unfortunately doesn't allow for index optimization of LIKE
>> > queries, which IMNSHO defeats the who
On Wed, Aug 29, 2012 at 11:43 AM, Bruce Momjian wrote:
> On Wed, Aug 29, 2012 at 10:31:21AM -0700, Aleksey Tsalolikhin wrote:
>> On Wed, Aug 29, 2012 at 9:45 AM, Merlin Moncure wrote:
>> > citext unfortunately doesn't allow for index optimization of LIKE
>> > queries, which IMNSHO defeats the who
Le mercredi 29 août 2012 à 13:05 -0400, Moshe Jacobson a écrit :
> On Wed, Aug 29, 2012 at 12:11 PM, Vincent Veyron
> wrote:
> Le mercredi 29 août 2012 à 09:48 -0400, Moshe Jacobson a
> écrit :
>
> > Questions:
> > 1. If I want the inherited table's columns
2012/8/29 Merlin Moncure
> On Wed, Aug 29, 2012 at 12:43 PM, Bruce Momjian wrote:
> > On Wed, Aug 29, 2012 at 10:31:21AM -0700, Aleksey Tsalolikhin wrote:
> >> On Wed, Aug 29, 2012 at 9:45 AM, Merlin Moncure
> wrote:
> >> > citext unfortunately doesn't allow for index optimization of LIKE
> >>
On Wed, Aug 29, 2012 at 8:52 AM, Chris Travers wrote:
> ALTER TABLE fruit ADD apple_id int;
> ALTER TABLE fruit ADD FOREIGN KEY (apple_id, type)
>REFERENCES apple (fruit_id, type)
>DEFERRABLE INITIALLY DEFERRED;
>
> And then do the same for orange etc. you can then:
>
> AL
On Wed, Aug 29, 2012 at 3:06 PM, Vincent Veyron wrote:
> #1
> Quote :
>
> Any indexes on the original table will not be created on the new table,
> unless the INCLUDING INDEXES clause is specified.
>
This is referring to the behavior on creating a table LIKE another table. I
am specifically aski
Thanks
-Original Message-
From: Ryan Kelly [mailto:rpkell...@gmail.com]
Sent: Wednesday, August 29, 2012 12:41 PM
To: Little, Douglas
Cc: PostgreSQL General (pgsql-general@postgresql.org)
Subject: Re: [GENERAL] psql & unix env variables
On Wed, Aug 29, 2012 at 12:35:32PM -0500, Little,
On Wed, Aug 29, 2012 at 01:45:20PM -0500, Merlin Moncure wrote:
> On Wed, Aug 29, 2012 at 12:43 PM, Bruce Momjian wrote:
> > On Wed, Aug 29, 2012 at 10:31:21AM -0700, Aleksey Tsalolikhin wrote:
> >> On Wed, Aug 29, 2012 at 9:45 AM, Merlin Moncure wrote:
> >> > citext unfortunately doesn't allow f
On Wed, Aug 29, 2012 at 12:52:50PM -0600, Scott Marlowe wrote:
> On Wed, Aug 29, 2012 at 11:43 AM, Bruce Momjian wrote:
> > On Wed, Aug 29, 2012 at 10:31:21AM -0700, Aleksey Tsalolikhin wrote:
> >> On Wed, Aug 29, 2012 at 9:45 AM, Merlin Moncure wrote:
> >> > citext unfortunately doesn't allow fo
On Wed, Aug 29, 2012 at 2:17 PM, Bruce Momjian wrote:
> On Wed, Aug 29, 2012 at 12:52:50PM -0600, Scott Marlowe wrote:
>> On Wed, Aug 29, 2012 at 11:43 AM, Bruce Momjian wrote:
>> > On Wed, Aug 29, 2012 at 10:31:21AM -0700, Aleksey Tsalolikhin wrote:
>> >> On Wed, Aug 29, 2012 at 9:45 AM, Merlin
Hello,
I have a problem with functional index feature in Postgres 8.3
There are two tables, lets call them: PARENTS and CHILDREN(with
timestamp column)
I created functional index on parents with function, which selects max
value of timestamp from child elements(for given parent_id).
The pr
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Grzegorz Tanczyk
Sent: Wednesday, August 29, 2012 5:02 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Refreshing functional index
Hello,
I have a problem with functional index feature in Po
On Wed, Aug 29, 2012 at 4:01 PM, Grzegorz Tańczyk wrote:
> Hello,
>
> I have a problem with functional index feature in Postgres 8.3
>
> There are two tables, lets call them: PARENTS and CHILDREN(with timestamp
> column)
>
> I created functional index on parents with function, which selects max va
On 08/28/2012 10:46 PM, Nicola Cisternino wrote:
1) Why PostgreSQL don't use COLLATE to manage case sensitive /
insensitive comparision (I think it's the best and ANSI standard way ) ?
Support for per-column collations in PG was only added relatively
recently - in 9.1, by the looks:
I know this post is a few months old now, but I have a strikingly similar
setup and am getting a similar, somewhat more reliable error:
ERROR: could not open file "base/29292/12186914_fsm": Invalid argument
It happens while doing an ST_Difference() function on two large geometries.
I'm runnin
On 08/30/2012 10:24 AM, johnkeefe wrote:
I know this post is a few months old now, but I have a strikingly similar
setup and am getting a similar, somewhat more reliable error:
ERROR: could not open file "base/29292/12186914_fsm": Invalid argument
It happens while doing an ST_Difference() f
Ack. I meant 9.1.4:
~ ∴ psql
psql (9.1.4)
Thanks.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/SQLSTATE-XX000-Internal-Error-7-tp5682117p5721842.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (p
57 matches
Mail list logo
