[GENERAL] How to change pg_trigger query so that it works in 9.0 without pg_trigger.tgisconstraint does not exist error
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_triggerdef(pg_trigger.oid) as trdef FROM pg_catalog.pg_trigger join pg_catalog.pg_class on pg_trigger.tgrelid = pg_class.oid JOIN pg_catalog.pg_namespace ON pg_namespace.oid=pg_class.relnamespace WHERE not pg_trigger.tgisconstraint and pg_namespace.nspname='firma1' -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Request for comment: pgjson project
Terry Laurenzo writes: > I'm a long-time postgres user but have never developed any extensions for > it. I had some time over the past week and decided to tackle a project I've > had on my list for a while: adding robust JSON capabilities to the database > server. Hm, are you aware that there's already work going on in that area? There are two relevant patches in the queue at https://commitfest.postgresql.org/action/commitfest_view?id=7 Perhaps you should join forces with those folk instead of starting an independent effort. I don't believe that anyone thinks those patches are set in stone yet, so design ideas are still welcome. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fastest way to check database's existence
I want to code a Perl function (part of a Perl library) for determining the existence of a particular database (in a given host/port). One way would be to just attempt making a connection to it, trapping any errors upon failure (with eval), or discarding the connection upon success. This approach has the added benefit of also checking the accessibility of the database to the user running the code, but for this application this added benefit is not necessary. Checking existence is all that matters. Is there an even faster way to check for a database's existence that does not require establishing a connection? (Maybe this question would be more suitable for the pgsql-performance list?) TIA! ~kynn
Re: [GENERAL] Fastest way to check database's existence
> Is there an even faster way to check for a database's existence that does > not require establishing a connection? > (Maybe this question would be more suitable for the pgsql-performance list?) No. You have to connect to some database in particular to do anything. That's why the postgres database exists. -- Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fastest way to check database's existence
On 16/10/2010 16:13, Kynn Jones wrote: I want to code a Perl function (part of a Perl library) for determining the existence of a particular database (in a given host/port). One way would be to just attempt making a connection to it, trapping any errors upon failure (with eval), or discarding the connection upon success. This approach has the added benefit of also checking the accessibility of the database to the user running the code, but for this application this added benefit is not necessary. Checking existence is all that matters. Is there an even faster way to check for a database's existence that does not require establishing a connection? You're going to have to connect no matter what you do, assuming that you're accessing it from another machine. An alternative, if it suited your application, would be to maintain a connection to a database which you know exists, such as template1 or (better) postgres, and just query pg_database for the existence of the database you want. If you can keep a connection open for long periods, I'm sure this would be pretty fast. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Automated Database Backups
Does anyone have a way to run pg_dump from cron in a way that doesn't require someone to enter the password on every run? I get the following error even when the backup user has read permissions on all application databases. The database that fails is part of the PostgreSQL kernel, not my application data so I'm leery of tampering with permissions here. The command line I am using is: /usr/postgres-8.4.2/bin/pg_dump -f /notraid/dbbkup.`date +%Y%m%d` -F custom -v -U db Thanks in advance. John -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] installing from source in Windows
I have MinGW installed and I've unpacked the pg 9.0 source files which now reside in the following path: * C:\Program Files\postgresql-9.0.0 In Chapter 15.5 of the 9.0 manual, the installation procedure basically begins by instructing me to run ./configure This command does not work. Much appreciated if someone could point me in the right direction or clue me in to what I need in order to run the configuration script. Thanks, John
Re: [GENERAL] Request for comment: pgjson project
Thanks Tom. I wasn't aware - I did do a quick search at the beginning but didn't turn this up. I'll post over on the hackers list. Terry On Sat, Oct 16, 2010 at 7:44 AM, Tom Lane wrote: > Terry Laurenzo writes: > > I'm a long-time postgres user but have never developed any extensions for > > it. I had some time over the past week and decided to tackle a project > I've > > had on my list for a while: adding robust JSON capabilities to the > database > > server. > > Hm, are you aware that there's already work going on in that area? > There are two relevant patches in the queue at > https://commitfest.postgresql.org/action/commitfest_view?id=7 > > Perhaps you should join forces with those folk instead of starting an > independent effort. I don't believe that anyone thinks those patches > are set in stone yet, so design ideas are still welcome. > >regards, tom lane >
[GENERAL] Constraint: string length must be 32 chars
Hello,
I'm trying to create a table, where md5 strings will serve as primary keys.
So I'd like to add a constraing that the key length should be 32 chars long
(and contain [a-fA-F0-9] only):
create table gps (
id varchar(32) primary key CONSTRAINT char_length(id)==32,
stamp timestamp DEFAULT current_timestamp,
pos point);
But it fails:
ERROR: syntax error at or near "("
LINE 2: id varchar(32) primary key CONSTRAINT char_length(id)==32,
^
Does anybody please know what's wrong here?
Thank you
Alex
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Automated Database Backups
On 15/10/2010 22:12, John Iliffe wrote: Does anyone have a way to run pg_dump from cron in a way that doesn't require someone to enter the password on every run? I get the following error even when the backup user has read permissions on all application databases. You need to use a .pgpasss file: http://www.postgresql.org/docs/9.0/static/libpq-pgpass.html You can instead specify the password in an environment variable, but that potentially makes it visible to all users on the system, so the .pgpass is better. HTH, Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Automated Database Backups
John, > Does anyone have a way to run pg_dump from cron in a way that doesn't > require someone to enter the password on every run? I get the following > error even when the backup user has read permissions on all application > databases. You can use the .pgpass file in the backup owner's home directory: *:5432:*:postgres:MyPassword *:5432:*::XsPassword -- Gary Chambers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] installing from source in Windows
No at all familiar with MinGW, but I've done some "./configure"s in my time. ./configure scripts 1) check to see what the local build environment contains and 2) which special feature you wish to toggle. But the pretty much assume a unix-like env. Could you put cygwin on rather than just mingw? Turner, John J wrote: I have MinGW installed and I’ve unpacked the pg 9.0 source files which now reside in the following path: · C:\Program Files\postgresql-9.0.0 In Chapter 15.5 of the 9.0 manual, the installation procedure basically begins by instructing me to run_ ./configure_ This command does not work. Much appreciated if someone could point me in the right direction or clue me in to what I need in order to run the configuration script. Thanks, John -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
On 16/10/2010 17:15, Alexander Farber wrote:
Hello,
I'm trying to create a table, where md5 strings will serve as primary keys.
So I'd like to add a constraing that the key length should be 32 chars long
(and contain [a-fA-F0-9] only):
create table gps (
id varchar(32) primary key CONSTRAINT char_length(id)==32,
stamp timestamp DEFAULT current_timestamp,
pos point);
But it fails:
ERROR: syntax error at or near "("
LINE 2: id varchar(32) primary key CONSTRAINT char_length(id)==32,
^
Does anybody please know what's wrong here?
From (somewhat hazy) memory, I think the syntax is something like this:
... CONSTRAINT length_check CHECK char_length(id) = 32, ...
Note also that the equality operator is a single "=", not "==" as you
have above.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
snake=> create table gps ( id varchar(32) primary key CONSTRAINT id_length char_length(id)=32, stamp timestamp DEFAULT current_timestamp, pos point); ERROR: syntax error at or near "char_length" LINE 2: id varchar(32) primary key CONSTRAINT id_length char_length(... ^ snake=> create table gps ( id varchar(32) primary key CHECK id_length char_length(id)=32, stamp timestamp DEFAULT current_timestamp, pos point); ERROR: syntax error at or near "id_length" LINE 2: id varchar(32) primary key CHECK id_length char_length(id)=3... ^ snake=> create table gps ( id varchar(32) primary key CHECK char_length(id)=32, stamp timestamp DEFAULT current_timestamp, pos point); ERROR: syntax error at or near "char_length" LINE 2: id varchar(32) primary key CHECK char_length(id)=32, ^ Sorry, any ideas? (I know it's a stupid question) Alex -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
On 16/10/2010 17:48, Alexander Farber wrote: snake=> create table gps ( id varchar(32) primary key CONSTRAINT id_length char_length(id)=32, stamp timestamp DEFAULT current_timestamp, pos point); ERROR: syntax error at or near "char_length" LINE 2: id varchar(32) primary key CONSTRAINT id_length char_length(... ^ snake=> create table gps ( id varchar(32) primary key CHECK id_length char_length(id)=32, stamp timestamp DEFAULT current_timestamp, pos point); ERROR: syntax error at or near "id_length" LINE 2: id varchar(32) primary key CHECK id_length char_length(id)=3... ^ snake=> create table gps ( id varchar(32) primary key CHECK char_length(id)=32, stamp timestamp DEFAULT current_timestamp, pos point); ERROR: syntax error at or near "char_length" LINE 2: id varchar(32) primary key CHECK char_length(id)=32, ^ Sorry, any ideas? (I know it's a stupid question) Alex None of those correspond to what I suggested! :-) Here is is again: ... CONSTRAINT length_check CHECK char_length(id) = 32, ... Note the keywords "constraint" and "check" both present. Not that I'm saying I'm correct - I'm going on memory If in doubt consult the docs under "create table". Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fastest way to check database's existence
On Saturday 16 October 2010 8:13:12 am Kynn Jones wrote: > I want to code a Perl function (part of a Perl library) for determining the > existence of a particular database (in a given host/port). > > One way would be to just attempt making a connection to it, trapping any > errors upon failure (with eval), or discarding the connection upon success. > > This approach has the added benefit of also checking the accessibility of > the database to the user running the code, but for this application this > added benefit is not necessary. Checking existence is all that matters. > > Is there an even faster way to check for a database's existence that does > not require establishing a connection? Given the restriction of host/port you probably need to connect as stated in other posts. For completeness though, it is possible to parse $DATA/global/pg_database for the existence of database in a particular cluster. Assuming you can map host/port to cluster then it is a possibility. > > (Maybe this question would be more suitable for the pgsql-performance > list?) > > TIA! > > ~kynn -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
On 10/16/2010 12:48 PM, Alexander Farber wrote: snake=> create table gps ( id varchar(32) primary key CONSTRAINT id_length char_length(id)=32, stamp timestamp DEFAULT current_timestamp, pos point); ERROR: syntax error at or near "char_length" LINE 2: id varchar(32) primary key CONSTRAINT id_length char_length(... Sorry, any ideas? (I know it's a stupid question) See the documentation section 5.3.1 Check Constraints. The condition needs to be in parentheses. And in order to name your constraint, you must use the CONSTRAINT variant. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
On Sat, Oct 16, 2010 at 12:15 PM, Alexander Farber
wrote:
> Hello,
>
> I'm trying to create a table, where md5 strings will serve as primary keys.
> So I'd like to add a constraing that the key length should be 32 chars long
> (and contain [a-fA-F0-9] only):
why don't you use the bytea type, and cut the key size down 50%? You
can always format it going out the door if you want it displayed hex.
Besides being faster, you get to skip the 'is hex' regex.
create table foo(id bytea check(length(id) = 16));
insert into foo values (decode(md5('a'), 'hex')); -- if not using pgcrypto
insert into foo values (digest('b', 'md5')); -- if using pgcrypto (preferred)
select encode(id, 'hex') from foo;
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
Merlin Moncure wrote:
On Sat, Oct 16, 2010 at 12:15 PM, Alexander Farber
wrote:
Hello,
I'm trying to create a table, where md5 strings will serve as primary keys.
So I'd like to add a constraing that the key length should be 32 chars long
(and contain [a-fA-F0-9] only):
why don't you use the bytea type, and cut the key size down 50%? You
can always format it going out the door if you want it displayed hex.
Besides being faster, you get to skip the 'is hex' regex.
create table foo(id bytea check(length(id) = 16));
insert into foo values (decode(md5('a'), 'hex')); -- if not using pgcrypto
insert into foo values (digest('b', 'md5')); -- if using pgcrypto (preferred)
select encode(id, 'hex') from foo;
merlin
Why not the support uuid type instead. Aren't md5s only as unique as
the source? i.e. The same value hashed results in the same md5, no?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
Thank you for your advices.
I actually would like to store GPS coordinates, but anonymously,
so I was going to save md5(my_secret+IMEI) coming from a mobile...
I have to lookup if uuid is supported there
Regards
Alex
On Sat, Oct 16, 2010 at 11:08 PM, Rob Sargent wrote:
> Merlin Moncure wrote:
>> why don't you use the bytea type, and cut the key size down 50%? You
>> can always format it going out the door if you want it displayed hex.
>> Besides being faster, you get to skip the 'is hex' regex.
>>
>> create table foo(id bytea check(length(id) = 16));
>> insert into foo values (decode(md5('a'), 'hex')); -- if not using pgcrypto
>> insert into foo values (digest('b', 'md5')); -- if using pgcrypto
>> (preferred)
>>
>> select encode(id, 'hex') from foo;
>
> Why not the support uuid type instead. Aren't md5s only as unique as the
> source? i.e. The same value hashed results in the same md5, no?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
Alexander Farber wrote:
I'm trying to create a table, where md5 strings will serve as primary keys.
So I'd like to add a constraing that the key length should be 32 chars long
(and contain [a-fA-F0-9] only):
create table gps (
id varchar(32) primary key CONSTRAINT char_length(id)==32,
stamp timestamp DEFAULT current_timestamp,
pos point);
If you want to use a text type for this and you are restricting the character
repertoire anyway, which presumably you'd need a regex for, then use the same
regex to restrict the length too.
Adjusting your example:
create table gps (
id text primary key CONSTRAINT id ~ '^[a-fA-F0-9]{32}$',
stamp timestamp DEFAULT current_timestamp,
pos point
);
But I would further restrict this to just upper or just lowercase, so that the
values compare correctly as text; you then have to upper/lower your inputs:
create table gps (
id text primary key CONSTRAINT id ~ '^[A-F0-9]{32}$',
stamp timestamp DEFAULT current_timestamp,
pos point
);
I would further recommend turning the above into a separate data type,
especially if you'd otherwise be using that constraint in several places, like this:
CREATE DOMAIN md5text
AS text
CHECK (
VALUE IS NOT NULL
AND
VALUE ~ '^[A-F0-9]{32}$'
)
DEFAULT '';
create table gps (
id md5text primary key,
stamp timestamp DEFAULT current_timestamp,
pos point
);
This all being said, I would go with the other advice you mentioned and use a
bitstring or numeric type to represent the md5 rather than using text.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
I just read the "anonymously" part, so I take it you have ruled out
recording the given coordinate components directly, in multiple columns
presumably? Otherwise it seems you could then do a) a composite key and
b) queries directly against coordinate values.
Alexander Farber wrote:
Thank you for your advices.
I actually would like to store GPS coordinates, but anonymously,
so I was going to save md5(my_secret+IMEI) coming from a mobile...
I have to lookup if uuid is supported there
Regards
Alex
On Sat, Oct 16, 2010 at 11:08 PM, Rob Sargent wrote:
Merlin Moncure wrote:
why don't you use the bytea type, and cut the key size down 50%? You
can always format it going out the door if you want it displayed hex.
Besides being faster, you get to skip the 'is hex' regex.
create table foo(id bytea check(length(id) = 16));
insert into foo values (decode(md5('a'), 'hex')); -- if not using pgcrypto
insert into foo values (digest('b', 'md5')); -- if using pgcrypto
(preferred)
select encode(id, 'hex') from foo;
Why not the support uuid type instead. Aren't md5s only as unique as the
source? i.e. The same value hashed results in the same md5, no?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] installing from source in Windows
On 10/17/2010 12:25 AM, Rob Sargent wrote: No at all familiar with MinGW, but I've done some "./configure"s in my time. ./configure scripts 1) check to see what the local build environment contains and 2) which special feature you wish to toggle. But the pretty much assume a unix-like env. Could you put cygwin on rather than just mingw? While I can't speak for PostgreSQL specifically, msys rather than cygwin is typically used for most autotools-based builds of software on Windows. Personally I'd recommend that the OP just grab Visual Studio Express Edition and/or the Windows SDK and compile PostgreSQL that way. See the instructions here: http://developer.postgresql.org/pgdocs/postgres/install-windows-full.html It's astonishingly easy for a Windows build of any kind, let alone of a portable autotools-based open source database. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
Hello, really good advices here! But - On Sun, Oct 17, 2010 at 2:37 AM, Rob Sargent wrote: > I just read the "anonymously" part, so I take it you have ruled out > recording the given coordinate components directly, in multiple columns > presumably? Otherwise it seems you could then do a) a composite key and b) > queries directly against coordinate values. what do you mean here? Do you suggest using line segments instead of points in my records or something else? Regards Alex -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
