Re: [SQL] Max column number.
On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: > Unfortunately It's an inherited data model and I can't make any change for > now... > Thanks for your answer! > > On Tue, Jul 12, 2011 at 2:52 PM, Reinoud van Leeuwen < > reinou...@n.leeuwen.net> wrote: > > > On Tue, Jul 12, 2011 at 02:48:26PM -0300, Miguel Angel Conte wrote: > > > > > Something like: > > > "If I'm not going to exceed the size limit, then I can add a new column" > > > > You want to add columns in your application? Are you sure you have the > > right datamodel? > > > > Reinoud > > -- Hi Reinoud, I think you will need to write a function to grovel around in the database to determine what the size of the row would be given a definition. Tedious but straight-forward. Regards, Ken -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select random lines of a table using a probability distribution
On Wed, Jul 13, 2011 at 03:27:10PM +0200, Jira, Marcel wrote: > Hi! > > Let's consider I have a table like this > > idqualificationgenderageincome > > I'd like to select (for example 100) lines of this table by random, but the > random mechanism has to follow a certain probability distribution. > > I want to use this procedure to construct a test group for another selection. > > Example: > > I filter all lines having the qualification "plumber". > I get 50 different ids consisting of 40 males, 10 females and a certain age > distribution. > > I also get some information concerning the income of the plumbers. > > Now I want to know if the income is more influenced by the gender and age > distribution or by the qualification "plumber". > > Therefore I would like to select a test group (of 50 or more) without any > plumbers. This test group has to follow the same age and gender distribution. > > Then I would be able to compare this groups income statistics with the > plumbers income statistics. > > Is this possible (and doable with reasonable effort) in PostgreSQL? > > Thank you in advance. > > Best regards, > > Marcel Jira > You may want to take a look at pl/R which make the R system available to PostgreSQL as a function language. Regards, Ken -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Tripping over (my own) stupidity - VARCHAR declaration minus specificity
On Fri, Oct 14, 2011 at 01:51:04PM -0400, Adam Cornett wrote: > On Fri, Oct 14, 2011 at 1:27 PM, Good, Thomas wrote: > > > I created a table in a hurry and forgot to specify the max capacity for a > > varchar() data type. > > Can it be said what the max would be in a case like this (just curious)? > > > > thanks much > > > > According to > http://www.postgresql.org/docs/current/static/datatype-character.html, > "If character varying is used without length specifier, the type accepts > strings of any size. The latter is a PostgreSQL extension." > > -Adam Cornett And according to the documentation, the maximum size of a varchar is 1GB. Regards, Ken -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with sequence increment
On Wed, Jan 25, 2012 at 09:42:05AM -0430, Francisco Calderón wrote:
> Hello,
>
> I am having a situation with postgresql 8.3, i have two tables, ta and tb,
> with a relation "one tb has many ta" and... well, i will let the SQL talk
> for me ;)
>
> ---SQL---
> CREATE TABLE tb
> (
> id serial NOT NULL,
> descripcion character varying(200) NOT NULL,
> CONSTRAINT tb_pkey PRIMARY KEY (id)
> )
> WITH (OIDS=FALSE);
> INSERT INTO tb (descripcion) values ('desc 1');
> INSERT INTO tb (descripcion) values ('desc 2');
> CREATE TABLE ta
> (
> id serial NOT NULL,
> descripcion character varying(200),
> tb_id integer default null,
> CONSTRAINT ta_pkey PRIMARY KEY (id),
> CONSTRAINT ta_tb_id FOREIGN KEY (tb_id)
> REFERENCES tb (id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> WITH (OIDS=FALSE);
>
> When i make an insert like this:
>
> INSERT INTO ta (descripcion, tb_id) values ('prueba', 0);
>
> we can expect this error:
>
> ERROR: insert or update on table "ta" violates foreign key constraint
> "ta_tb_id"
> DETAIL: Key (tb_id)=(0) is not present in table "tb".
>
> and that is what i am getting but the unusual situation is the sequence
> "ta_id_seq" is incrementing every time i get the "violates foreign key
> constraint" error and i think this is not a good behavior, what do you
> think?
>
That is how sequences work. If you want different behavior, use another
process or method to produce a sequential count. Hint, it will involve
a lot of locking and be much slower than a sequence. It would be better
to have your application handle gaps in the sequence.
Regards,
Ken
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Simple method to format a string
On Wed, Jun 20, 2012 at 12:08:24PM -0600, Wes James wrote:
> On Wed, Jun 20, 2012 at 8:42 AM, Emi Lu wrote:
>
> > Good morning,
> >
> > Is there a simply method in psql to format a string?
> >
> > For example, adding a space to every three consecutive letters:
> >
> > abcdefgh -> *** *** ***
> >
> > Thanks a lot!
> > Emi
> >
> >
> I looked at "format" here:
>
> http://www.postgresql.org/docs/9.1/static/functions-string.html
>
> but didn't see a way.
>
> This function might do what you need:
>
>
> CREATE FUNCTION spaced3 (text) RETURNS text AS $$
> DECLARE
>-- Declare aliases for function arguments.
> arg_string ALIAS FOR $1;
>
> -- Declare variables
> row record;
> res text;
>
> BEGIN
> res := '';
> FOR row IN SELECT regexp_matches(arg_string, '.{1,3}', 'g') as chunk LOOP
> res := res || ' ' || btrim(row.chunk::text, '{}');
> END LOOP;
> RETURN res;
> END;
> $$ LANGUAGE 'plpgsql';
>
>
> # SELECT spaced3('abcdefgh');
>
>spaced3
> -
> abc def gh
> (1 row)
>
> # SELECT spaced3('0123456789');
> spaced3
>
> 012 345 678 9
> (1 row)
>
> to remove the function run this:
>
> # drop function spaced3(text);
>
> -wes
Just a small optimization would be to use a backreference with regexp_replace
instead of regexp_matches:
select regexp_replace('foobarbaz', '(...)', E'\\1 ', 'g');
regexp_replace
foo bar baz
(1 row)
regards,
Ken
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Split a string to rows?
On Tue, Jan 08, 2013 at 09:34:24AM -0500, Emi Lu wrote: > Hello Thomas, > > >>Thanks a lot! I just noticed that my postgresql is 8.3(unnest > >>function is not there by default). Is there a way that I could > >>download and load only this function from somewhere? > > > > > >Are you aware that 8.3 will be de-suppported as of next month? > > > >You should really think about an upgrade *now* > > > > http://www.postgresql.org/support/versioning/ > > Although 8.3 reaches EOL date, it says that "Supported=Yes" for 8.3. > > I believe that "Supported" would be fine, wouldn't it? > > Emi > Hi Emi, It will be supported until February 2013. You have about 1 month left. Regards, Ken -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how can I replace all instances of a pattern
On Tue, Mar 26, 2013 at 09:13:39AM -0400, James Sharrett wrote:
> Sorry, caught a typo. Mytext1 is correctly replaced because only one
> instance of the character (space) is in the string.
>
> This deals with the correct characters but only does the first instance of
> the character so the output is:
>
> 'Mytext1'
> 'Mytext 2' (wrong)
> 'Mytext-3' (wrong)
> 'My_text4'
> 'My!text5'
>
Hi James,
Try adding the g flag to the regex (for global). From the documentation:
regexp_replace('foobarbaz', 'b..', 'X')
fooXbaz
regexp_replace('foobarbaz', 'b..', 'X', 'g')
fooXX
regexp_replace('foobarbaz', 'b(..)', E'X\\1Y', 'g')
fooXarYXazY
Regards,
Ken
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Encrypting PGBouncer to Postgres DB connections
On Wed, Apr 10, 2013 at 11:06:32AM -0700, Bhanu Murthy wrote: > Hi all, > > Can someone please point me to detailed documentation on how to > secure/encrypt connections between PGBouncer and Postgresql database (version > 8.4.3)? > > Thanks in advance! > > Bhanu M. Gandikota > Cell: (415) 420-7740 Hi Bhanu, You will need to use your link encryption process of choice to tunnel the connections from pgbouncer to the backend. SSH and STunnel are two that we have used successfully in the past. Regards, Ken -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
