Re: [SQL] backup
On 2009-06-09, Jyoti Seth wrote: > This is a multipart message in MIME format. > > --=_NextPart_000_003C_01C9E916.43A8D460 > Content-Type: text/plain; > charset="us-ascii" > Content-Transfer-Encoding: 7bit > > Hi all, > > > > Can we take backup of specific data of a table (using where clause)? in psql: /copy (select * from table WHERE condition ) to 'FILENAME' requires postgres version >= 8.2 IIRC. you can use any query. -- 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] setting the where clause
On 2009-06-10, johnf wrote: > Hi, > I'm am programming in python using the Dabo modules. www.dabodev.com if your > interested. Dabo is a framework that provides an easy way to build desktop > app's. To clear a data entry form. I have been setting the where clause > to "where 1=0". This of course retrieves 0 records and my form will display > nothing. It has been suggested that it might be better to set the where > clause to a primary key value that does not exist in the table "where PK >= -999". "where PK=NULL" is better as anything=NULL is never true. PK=-999 may be true sometimes. As Tom says PK=-999 causes postgres to look for a record that matches, PK=NULL doesn't cause needless search. that said if -999 is outside of the valid range for PK then the search will finish very quickly as at worst only a sigle btree page will need to be loaded. in order of preference. no query at all where FALSE where PK=NULL where PK=-999 (note that this one may not work) -- 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] SQL File in encrypted form
On 2009-06-19, Jyoti Seth wrote: > This is a multipart message in MIME format. > > --=_NextPart_000_0001_01C9F0F8.92EE3490 > Content-Type: text/plain; > charset="us-ascii" > Content-Transfer-Encoding: 7bit > > Hi All, > > > > I want to send the sql script file to a client but wants to hide the details > in it. Can I send the sql script file in encrypted form which they can > execute but can't view the details. only by building it into an encrypted executable. be sure to use SSL on the database connection. or possibly you could write a backend extension (stored procedure writtern in C, Java, or other compiled language) that does decrypting and execution in a single step and use that. what sort of things are you trying to hide? -- 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] Client-side compression
On 2009-06-23, Rob Sargent wrote: > > Not sure if this belongs here or on the admin or performance list. > Apologies if so. (And this may be a second posting as the first was from > an un-registered account. Further apologies) > > My assumption is that any de/compression done by postgres would be > server-side. there may already be compression of the communication stream (probably not on unix sockets) > We're considering minimizing bandwidth utilization by using client-side > compression on a column value that will typically be multi-megabyte in > size. We would use ALTER TABLE SET STORAGE EXTERNAL to prevent the > server from un-necessary compression. > > Is this generally worthwhile? I haven't found any thread on the subject > of client-side compress so any pointer more than welcome. we recently switched from uncompressed pixmaps to JPEG data for some stored images. we have not tested performance but have certainly not noticed a decrease in performance. > Is there a great penalty for a query which delves into the value, given > that the server will not be aware it's compressed? I assume we're > pretty much on our own to prevent such actions (i.e. the app can never > query against this column via sql). It just looks like data to the server. -- 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] Timestamp with timezone with Default value
On 2009-07-18, Gianvito Pio wrote: > This is a multi-part message in MIME format. > > --=_NextPart_000_0017_01CA0791.49E30EB0 > Content-Type: text/plain; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > Hi all, > how can I define a column of timestamp with timezone type...with a = > default value? Same as any other column with a default value. If you're inserting NULL into the column the NULL will override the default. Postgres does what you tell it. If you want the default value use the keyword DEFAULT (in for the value in that column) or don't mention that column. The DEFAULT keyword also works with UPDATE. -- 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] Tweak sql result set... ?
On 2009-07-28, Axe wrote: > I have a problem where I want to tweak a simple select in an > "unobtrusive way". Imagine I have the following select statement: > "SELECT name FROM customer LIMIT 1" and I get a normal result set from > this. But, could I,maybe by defining some other function or similar, > change the result set *without* changing the query? Suppose I get the > result from the query above, saying: "Peter Peterson". I would > (sometimes) like to get the result "Peter Peterson" but I > should not have to change the original query. > > I know I could write "SELECT '' || name || '' as name FROM > customer" but then I have altered the original query and I cannot do > this since it is supposed to function different in two different > situations. > > Any ideas on how to achieve this? I would like to let the original sql > code stay original. I can prepare postgres before executing the sql if > this makes it easier to acheive the goal put a wrapper round whatever it is you use to send the queries that modifies the returned values. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: Determining logically unique entities across many partially complete rows where at least one column matches
On 2009-08-11, Jamie Tufnell wrote: > Hi, > > I am faced with a modeling problem and thought I'd see if anyone has run > into something similar and can offer some advice. > > Basically my problem domain is cataloguing "snippets of information" about > "entities" which are loosely identified. > > Entities can be identified up to 3 different methods (email, phone or > openid.) > > Entities can have zero or many emails, phone numbers and openids. The > only restriction is they must have at least one value in one of those three > columns. > > > Some sample data: > > snippet #1 > email: null > phone: +1234567890 > openid: j...@myopenid.net > information: This is snippet #1 > > snippet #2 > email: f...@bar.com > phone: null > openid: johnny.name > information: This is snippet #2 > > At this point snippet #1 and #2 could refer to different entities. > > snippet #3 > email: b...@baz.com > phone: +1234567890 > openid: johnny.name > information: This is snippet #3 > > But now all three snippets definitely refer to the same entity, as far as > we're concerned: > > Entity: 1 > OpenIDs: johnny.name, j...@myopenid.net > Phones: +1234567890 > Emails: f...@bar.com, b...@baz.com > > So as far as modeling this goes, I'm stuck between: > > 1. Normalizing as usual with some serious triggers to maintain the >relationships. > 2. Just having a snippets table with these fields inline and make these >inferences at query time. > 3. Something in between. > 4. Using a document store like CouchDB. I think three tables openid,email, phone (phone's a bad one for a unique id IME, especially POTS lines) create table (entid integer, phone text unique) etc,etc... then at insert time you use a rule that runs a function with exception handling when the unique rules detect a match with some existing data and then in the exception code you do updates to replace the higher entid with the lower one > The kinds of queries I need to do right now (which will no doubt change): > > * Return all snippets. > * Return all distinct entities. > * Find all id for a distinct entity given a single piece of id. > * Find all snippets for a distinct entity. > > To do it in one table, I am thinking something like this: > > create table snippets ( > id serial not null primary key, > email text, > phone_number text, > openid text, > information text not null, > check (email is not null or > phone_number is not null or openid is not null) > ); > > with queries like: > > * Find all snippets for one distinct entity, searching by openid: > > select * from snippets > where phone_number = > (select phone_number from snippets where openid = 'j...@myopenid.net') > or email = > (select email from snippets where openid = 'j...@myopenid.net') > or openid in > (select openid from snippets >where phone_number = > (select phone_number from snippets where openid = 'j...@myopenid.net') >or email = >(select email from snippets where openid = 'j...@myopenid.net')); but that won't get them all. > Or if I was to model as usual I am thinking something like this: > > create table entities ( > id serial not null primary key > ); > > create table entity_has_email ( > entity_id integer not null > references entities (id) on delete cascade on update cascade, > email text not null unique > ); > > create table entity_has_phone_number ( > entity_id integer not null > references entities (id) on delete cascade on update cascade, > phone_number text not null unique > ); > > create table entity_has_openid ( > entity_id integer not null > references entities (id) on delete cascade on update cascade, > openid text not null unique > ); > > create table snippets ( > id serial not null primary key, > entity_id integer not null > references entities (id) on delete cascade on update cascade, > information text not null > ); > > (followed by a mass of on insert/update/delete triggers) > > select s.* from snippets s > join entity_has_email e on s.entity_id = e.id > join entity_has_phone_number p on s.entity_id = p.id > join entity_has_openid o on s.entity_id = o.id > where o.openid = 'j...@myopenid.net'; looks like the wrong query for snippets to me. select s.* from snippets s join entity_has_openid o on s.entity_id = o.id where o.openid = 'j...@myopenid.net'; > Another option, sort of half way between the two could be: > > create table snippets ( > id serial not null primary key, > entity_id integer not null > references entities (id) on delete cascade on update cascade, > information text not null > ); > > create table entities ( > id serial not null primary key, > email text, > phone_number text, > openid text, > check (email is not null or > phone_number is not null or openid is not null) > ); that's not going to work with your example data. (subject has two different email addresses) I guess you could use arrays for email, openid, and phone. -- Sent
Re: [SQL] mail alert
On 2009-08-11, Jan Verheyden wrote: > > Hi All, > > I was looking in what way it's possible to alert via mail when some conditi= > ons are true in a database. > > Thanks in advance! Assuming you mean email, and not ink on paper (hmm, OTOH you could load postcards into a printer) you could do this using NOTIFY and a listener written in some other langauge, notify is really neat. or possibly invoke mail(1) with a plpythonu or C function, or as superuser you can write a file (using copy ...) and arrange for something else to look, find it, and mail it. arbitrary file contents are possible with copy: COPY (SELECT NULL) TO myfile_name WITH NULL AS myfile_contents; -- 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] mail alert
On 2009-08-12, Jan Verheyden wrote: > --_000_E30C7040DE22624185BAD4093190B54437BE5DB4A9EX2007MBX2uzk_ > Content-Type: text/plain; charset="us-ascii" > Content-Transfer-Encoding: quoted-printable > > It's on Windows > I'd go with notify and a listener written in C using c-client to send emails, but only because I've used those before. -- 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? query
On 2009-08-13, Jan Verheyden wrote:
> --_004_E30C7040DE22624185BAD4093190B54437BE5DB4C1EX2007MBX2uzk_
> Content-Type: multipart/alternative;
> boundary="_000_E30C7040DE22624185BAD4093190B54437BE5DB4C1EX2007MBX2uzk_"
>
> --_000_E30C7040DE22624185BAD4093190B54437BE5DB4C1EX2007MBX2uzk_
> Content-Type: text/plain; charset="us-ascii"
> Content-Transfer-Encoding: quoted-printable
>
> Hi,
>
> I was trying to run following query but doesn't work:
>
> if (uid='janvleuven10') then
> insert into test (registered) values ('1');
> else
> insert into test (registered) values ('0');
> end if;
that's not SQL. (it could be plpgsql)
if you need to do it in SQL do this.
insert into test (registered)
values ( case when uid='janvleuven10' then '1' else '0' end );
--
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] operator contains in older Pgsql
On 2009-08-18, W. Kinastowski wrote: > I need a functionality of "@>" array operator in 8.1 Pg server. i.ex. > SELECT * FROM table WHERE array_col @> ARRAY ['xxx'] (works in 8.2, > error in 8.1) > How to performe such a query ? Is it possible ? Thanks for help. SELECT * FROM table WHERE 'xxx' = ANY( array_col ) -- 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] operator contains in older Pgsql
On 2009-08-18, W. Kinastowski wrote: > Jasen Betts wrote: >> On 2009-08-18, W. Kinastowski wrote: >> >>> I need a functionality of "@>" array operator in 8.1 Pg server. i.ex. >>> SELECT * FROM table WHERE array_col @> ARRAY ['xxx'] (works in 8.2, >>> error in 8.1) >>> How to performe such a query ? Is it possible ? Thanks for help. >>> >> >> SELECT * FROM table WHERE 'xxx' = ANY( array_col ) >> >> >> > thanks, it works ... and when there is more elements in array: i.ex > SELECT * FROM table WHERE array_col @> ARRAY ['xxx', 'yyy'] > i need to > SELECT * FROM table WHERE 'xxx' = ANY( array_col ) AND 'yyy' = ANY( > array_col ) > is that correct ? it looks good it to me. -- 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] Updating one table with data from another
On 2009-08-18, drew wrote: > Hey all, > There are two things I need to do: > 1. Update existing rows with new data > 2. Append new rows > > I need to update only some of the fields table1 with data from > table2. These tables have the exact same fields. > > So here's what I have currently for appending new rows (rows where CID > does not currently exist in table1, but have been added to table2): > INSERT INTO table1 (field1, field2, ...) > SELECT field1, field2, ... > FROM table2 > WHERE NOT EXISTS (SELECT CID FROM table1); > > > But something is wrong with the logic there and I'm not quite getting > it. the where clause is wrong. WHERE NOT EXISTS (SELECT 1 FROM table1 where table1.cid=table2.cid); or WHERE NOT cid IN (SELECT CID FROM table1); -- 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] multiple substitution in a single replace call?
On 2009-08-21, Gerardo Herzig wrote: > Hi all. There is a way to simulate the `pipe' in linux so y can use > replace() for replacing 2 different things? use regexp_replace instead? -- 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] Differences between bit string constant sintax
On 2009-09-09, Oliveiros C, wrote: > This is a multi-part message in MIME format. > > --=_NextPart_000_013B_01CA3146.85B62920 > Content-Type: text/plain; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > Dear All, > > I have a table which has a field that is of type bit varying. > > When I do a direct INSERT with , say, X'1F', everything > works fine. copy syntax differs from literal syntax in several ways (an obvious one is that tabs are allowed in literals) in this case you don't use quotes in the value. copy mytable from stdin; 4 xdeadbeef 5 x123 \. -- 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 to get the previous date?
On 2009-10-14, Shruthi A wrote: > --0016e64698e4af821f0475e1f43d > Content-Type: text/plain; charset=ISO-8859-1 > > Hi, > > I'm using Enterprise DB PostgresPlus version 8.3. > Is there is a simple function or command like previous_date(mydate) which > when passed a particular date, simply returns the previous date? I tried > > mydate - 1 > > but this doesnt work. it should, if mydate is a date variable. if it's some sort of timestamp more care is needed mytimestamp - interval'1 day' also don't use interval'24h' unless you want 24h, because not all days are 24h select timestamptz'2009-09-28 00:30' - interval'24 h' ; ?column? 2009-09-26 23:30:00+12 (the interval crosses the local start of DST) -- 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] PostgreSQL Security/Roles/Grants
On 2009-11-01, Andrew Hall wrote: > 1. Default Roles -> a role which is activated at login time. Oracle imposes= > a limit on the number of default roles which any given user can have. > > 2. Non-default role -> a role which has to be explicitly activated during t= > he lifecycle of an application in order to gain access to database resource= > s. There are no limits on the number of non-default roles. This type of rol= > e helps us to only provide a user with the minimal set of privileges that t= > hey require at any given time=2C and minimise their access to database reso= > urces. the only way I know of to provide anything like non-default roles is via functions declared with "security definer" > Secondly=2C is there a limit on the number of roles which can be assigned t= > o a user (or more accurately a 'login role') in postgreSQL? no (2^16 maybe??) IIRC you do hit an complexity limit, O(n^2) or worse. -- 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] Getting more than one row in UNIQUE fields
On 2009-12-04, Another Trad wrote: > --001485f94df095921c0479ea62fd > Content-Type: text/plain; charset=ISO-8859-1 > > My table 'client' has the unique 'client_id'. > My test server, when I try: > select * from client where client_id = 12 > My server returns 3 rows I had similar with a client on friday, windows event log shouwed disk errors, chkdsk showed damaged database files. I passed the problem back to their IT guy. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: constants in 2-column foreign keys or how to design a storage for text-groups ?
On 2009-12-09, Andreas wrote: > Hi, ... > stupid example: > --- > color: red, green, blue > size: tiny, little, big, giant > structure: hard, soft, floppy > > How would I solve the rather common text storage issue? have you considered using enumerated types instead? -- 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] Proper case function
On 2009-12-31, Michael Gould wrote:
> Gary,
>
> Based on what I read it wouldn't handle cases where the result should be
>
> MacDonald from macdonald. There are other cases such as the sentence below
>
> ('it''s crazy! i couldn''t believe kate mcdonald, leo dicaprio, (terrence)
> trent d''arby (circa the 80''s), and jada pinkett-smith all showed up to
> [cHris o''donnell''s] party...donning l''oreal lIpstick! They''re heading to
> o''neil''s pub later on t''nite. the_underscore_test. the-hyphen-test)
>
> This wouldn't handle the quotes and proper case all of the words.
There is no case-restoring algorithm that works in all cases.
Some people case their name differently just as some spell their name
differently. Possibly for the same reasons.
Contact the owners of the names and offer them the opportunity to have
their name represented in the correct case - send them a link to an online
form.
--
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 using regexp_replace
On 2010-01-11, gher...@fmed.uba.ar wrote: > CREATE FUNCTION valores_sustitucion(valor_ingresado varchar[]) > returns varchar > as > $$ > select case > $1[1] when 'Action_1' then > (select descripcion from load_by_cod($1[2])) > >when 'Action_2' then (select descripcion from pay_by_view($1[2]) > > else 'FALSE' > end; > $$ language sql; > Anybody has a hint? you are missing a ) -- 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 using regexp_replace
On 2010-01-11, gher...@fmed.uba.ar wrote:
> So, i come with this:
> SELECT regexp_replace(
> formato, E'{([^.]*)\.([a-zA-Z0-9]*)},
> valores_sustitucion(ARRAY[E'\\1'::varchar,E'\\2'::varchar]),
> 'g')
> from table where id =1;
select valores_sustitucion(ARRAY[E'\\1'::varchar,E'\\2'::varchar]);
valores_sustitucion
-
FALSE
that's the problem you are getting, the valores_sustitucion works on
the values given and that result is given to regexp_replace.
try this:
create OR REPLACE function magic( inp text ) returns text as $F$
DECLARE
tmp text;
res text;
BEGIN
tmp= 'SELECT ' ||
regexp_replace(quote_literal(inp),E'{([^.]*)\.([a-zA-Z0-9]*)}',
$s$'|| valores_sustitucion(ARRAY[E'\1',E'\2']) ||'$s$,'g');
-- raise notice 'tmp=%',(tmp);
EXECUTE tmp INTO res;
RETURN res;
END;
$F$ language plpgsql;
SELECT magic( formato ) FROM from table where id =1;
--
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] 'image' table with relationships to different objects
On 2010-02-09, Louis-David Mitterrand wrote: > Hello, > > In my database I have different object types (person, location, event, > etc.) all of which can have several images attached. can one image be several people? can one image be both event and location? > What is the best way to manage a single 'image' table with relationships > to (potentially) many different object types while keeping referrential > integrity (foreign keys)? probably several join tables image_location image_person image_event with uniques and cascades where needed. -- 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] very frustrating feature-bug
On 2010-02-17, silly sad wrote: > > acc=> > > CREATE OR REPLACE FUNCTION add_user (TEXT, TEXT, TEXT, TEXT) > RETURNS usr AS $$ >INSERT INTO usr (login,pass,name,email) VALUES ($1,$2,$3,$4) >RETURNING usr.*; > $$ LANGUAGE sql SECURITY DEFINER; > > acc=> > > ERROR: return type mismatch in function declared to return usr > DETAIL: Function's final statement must be a SELECT. > CONTEXT: SQL function "add_user" > > SURPRISE :-) SURPRISE :-) SQL functions are inlined when invoked, and so must be valid subselects. rewrite it in plpgsql. CREATE OR REPLACE FUNCTION add_user (TEXT, TEXT, TEXT, TEXT) RETURNS usr AS $$ DECLARE retval usr; BEGIN INSERT INTO usr (login,pass,name,email) VALUES ($1,$2,$3,$4) RETURNING usr.* INTO retval; RETURN retval; END; $$ LANGUAGE PLPGSQL SECURITY DEFINER; -- 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] Create functions using a function
On 2010-03-01, Gianvito Pio wrote: > --001485f44fc07594a40480c43c01 > Content-Type: text/plain; charset=ISO-8859-1 > > Hi all, > is there a way to define functions and/or triggers in a function? assuming plpgsql: execute > For example, can I create a function that takes an argument and defines a > function that has the name passed as argument to the first function? > > Something like this . CREATE FUNCTION test (name text) RETURNS VOID AS $$ BEGIN EXECUTE 'CREATE FUNCTION '||quote_ident(name)|| ... END $$ LANGUAGE PLPGSQL; -- 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] Does IMMUTABLE property propagate?
On 2010-03-06, Petru Ghita wrote: > > Given f1(x) as IMMUTABLE and f2(x) as IMMUTABLE, and f3(f1,f2) as > IMMUTABLE, does the query planner cache the result of f3 and reuse it > or if you want to get a little more speed you better explicitly define > yourself f3 as IMMUTABLE? > > I had an aggregate query like: > > select id, >sum(p1*f1(a)/f2(b) as r1, >sum(p2*f1(a)/f2(b) as r2, >... >sum(pn*f1(a)/f2(b) as rn > > ... > group by id; should be smart enough to know that. > Where f1(x) and f2(x) were defined as IMMUTABLE. > By the experiments I ran looks like after defining a new function > f3(a,b):= f1(a)/f2(b) and rewriting the query as: > > select id, >sum(p1*f3(a,b) as r1, >sum(p2*f3(a,b) as r2, >... >sum(pn*f3(a,b) as rn > > ... > group by id; > > *Looks like* I got a little (5%) improvement in performance of the > query. Is there a way to find out if the function is re-evaluated each > time? add a " raise notce 'here'; " to it (if plpgsql) more likely 5% is the function call overhead. -- 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] Clarification With Money data type
On 2010-03-10, Navanethan Muthusamy wrote: > --0016e68e9a5510f1f504816d1fcb > Content-Type: text/plain; charset=ISO-8859-1 > > Hi, > > I am using Postgresql 8.4, Can you tell me How Can I use Money data type? it's best not to, "money" is a fixed-point fromat based on 32 bit integers, abn it's deprecated. > I want to store the money and retrieve. Please give me idea to work on that. for that you use a bank, not a database :) > I am using Java with Postgresql, I have tried java.math.BigDecimal with > Money, but its giving error. (Its asking me to do the casing) ??? it's better to use some sort of numeric -- 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] Private functions
On 2010-03-13, Gianvito Pio wrote: > Hi all, > is there a way to write a function that can only be called by another > function but not directly using SELECT function_name ( )? not really. but there may be another way to get the effect you want. -- 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] list of all months
On 2010-03-08, query wrote: > --=_484d28810a276e7b5e461f0328ee205f > Content-Transfer-Encoding: 7bit > Content-Type: text/plain; charset="UTF-8" > > Hi, > > I want to display data for all days in a month even if no data > exists for that month. Some of the days in a month might not have any > data at all. With normal query, we can display days only if data > exists.But I want to display rows for all days in a month with blank > data for non-existing day in database. > > How can this be achieved ? > an outer join to (select FIRST_DAY_OF_MONTH + generate_series(1,DAYS_IN_MONTH) -1 ) where the date columns match FIRST_DAY_OF_MONTH and DAYS_IN_MONTH are to be replaced (by you) with the apropriate expressions by some means, possibly date arithmetic. -- 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] Plpgsql: Iterating through a string of parameters
On 2010-03-25, Leif Biberg Kristensen wrote: > I'm struggling with how to make plpgsql iterate through a list of numbers > input as a text string, eg. "1438 2656 973 4208". I figure that I can use the > regexp_split_to_array() function to make an array of the string, but can I > iterate through an array with eg. a FOR loop? using regex_split_to_table with for is easier for x in regex_split_to_table() do But if you must you can use split to array and then use the array measuring functions to determine the limit for a counted for loop. -- 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] Week to date function
On 2010-03-27, Hiltibidal, Rob wrote: > U only 52 calendar weeks in a year... I'm almost sure that is the > norm All hours have 60 minutes All weeks have 7 days All years have 12 months all else is variable. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: CHECK constraints with plpgsql functions - check 'fires' BEFORE data modification?
On 2010-04-19, Mario Splivalo wrote:
> The 'data integrity' rule for database I'm designing says that any
> subject we're tracking (persons, companies, whatever) is assigned an
> agreement that can be in several states: 'Approved', 'Unapproved' or
> 'Obsolete'. One subject can have only one (or none) 'Approved' or
> 'Unapproved' agreement, and as many (or none) 'Obsolete' agreements.
...
> The 'proper' way to do this (as suggested by earlier posts on this
> mailing list) is to use partial UNIQUE indexes, but I have problem with
> that too: indexes are not part of DDL (no matter that primary key
> constraints and/or unique constraints use indexes to employ those
> constraints), and as far as I know there is no 'partial unique
> constraint' in SQL?
huh?
create unique index agreements_approved_onlyone on agreements(subject_id)
where agreement_state='approved';
> And, wouldn't it be better to have CHECK constraints check the data
> AFTER data-modification?
no.
> CREATE FUNCTION check_agreements_onlyone_approved(a_subject_id integer)
> RETURNS boolean AS
> $$
> SELECT
> CASE COUNT(agreement_id)
> WHEN 0 THEN true
> WHEN 1 THEN true
> ELSE false
> END FROM agreements WHERE subject_id = $1 AND agreement_state =
> 'approved';
> $$ LANGUAGE 'sql';
>
> Now, the above does not work because CHECK function is fired BEFORE
> actuall data modification takes place so I can end up with two rows with
> 'approved' state for particular subject_id. If I change the CASE...WHEN
> conditions so that function returns TRUE only when there is 0 rows for
> the state = 'approved' then I have problems with UPDATEing:
>
> UPDATE agreements SET agreement_state = 'obsolete' where subject_id =
> AND agreement_state = 'approved'
>
> That update will fail because the CHECK function is fired before the
> actuall update, and there is allready a row with state = 'approved' in
> the table.
fix the check so that it knows what the new state will be. then it test
if the proposed new state is compatible with the old state.
(but seriously, first explain why the index doesn't work)
...
CONSTRAINT check_agreements_onlyone_approved CHECK
(check_agreements_onlyone_approved(subject_id,agreement_id,agreement_state))
);
CREATE OR REPLACE FUNCTION check_agreements_onlyone_approved(the_subject_id
integer, the_pkey integer, the_new_state enum_agreement_state)
RETURNS boolean AS
$$
SELECT
CASE COUNT(agreement_id)
WHEN 0 THEN true
WHEN 1 THEN $3 != 'approved'
ELSE false
END FROM agreements WHERE subject_id = $1 AND agreement_state
= 'approved' and agreement_id != $2
$$ LANGUAGE 'sql';
still not perfect: if you need to change the agreement_id this will
block you from doing that on approved agreements.
Newsgroups: gmane.comp.db.postgresql.sql
From: Jasen Betts
Subject: Re: CHECK constraints with plpgsql functions - check 'fires' BEFORE
data modification?
References: <4bcc272c.3020...@megafon.hr>
Organization: Dis (not Dat) Organisation
Followup-To:
X-Face:
?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o...@6{uf8s;a+M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W;(T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6{T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&t...@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q;8"9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^
On 2010-04-19, Mario Splivalo wrote:
> The 'data integrity' rule for database I'm designing says that any
> subject we're tracking (persons, companies, whatever) is assigned an
> agreement that can be in several states: 'Approved', 'Unapproved' or
> 'Obsolete'. One subject can have only one (or none) 'Approved' or
> 'Unapproved' agreement, and as many (or none) 'Obsolete' agreements.
>
> I was thinking on employing the CHECK constraint on agreements table
> that would check that there is only one 'Approved' state per subject.
>
> My (simplified) schema looks like this:
>
> CREATE TYPE enum_agreement_state AS ENUM
>('unapproved',
> 'approved',
> 'obsolete');
>
> CREATE TABLE subjects
> (
> subject_id serial NOT NULL,
> subject_name character varying NOT NULL,
> CONSTRAINT subjects_pkey PRIMARY KEY (subject_id)
> );
>
> CREATE TABLE agreements
> (
> agreement_id serial NOT NULL,
> subject_id integer NOT NULL,
> agreement_state enum_agreement_sta
Re: [SQL] [SPAM]-D] How to find broken UTF-8 characters ?
On 2010-04-29, Andreas wrote:
> Hi,
>
> while writing the reply below I found it sounds like beeing OT but it's
> actually not.
> I just need a way to check if a collumn contains values that CAN NOT be
> converted from Utf8 to Latin1.
> I tried:
> Select convert_to (my_column::text, 'LATIN1') from my_table;
>
> It raises an error that says translated:
> ERROR: character 0xe28093 in encoding »UTF8« has no equivalent in »LATIN1«
use a regular expression.
ISO8859-1 is easy, all the caracters a grouped together in unicode so
the regular expression consists of a single inverted range class
SELECT pkey FROM tabname
WHERE ( textfield || textfiled2 || textfield3 ) ~
('[^'||chr(1)||'-'||chr(255)||']');
--
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] [SPAM]-D] How to find broken UTF-8 characters ?
On 2010-04-29, Justin Graf wrote: > I'm pretty sure this is the regualr expression to find all non ASCII=20 > chars.. [^\x00-\xFF] Not in postgres. \x00 does not work well in strings, and \xFF is invalid utf-8. this is why I used char() (also ASCII is undefined past at \x7F ... but the original request was for LATIN-1 which does end at char(255)) -- 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] LIMIT problem
On 2010-04-30, silly sad wrote: > suppose i request > > SELECT foo(t.x) FROM t LIMIT 1; > > Whither it DEFINED how many times foo() will be executed? foo will be executed repeatedly until it returns a result or all the rows in t are exhausted. > May anyone rely on it? not sure > Or we have to avoid this non SQLish trick? This will execute it once (or not at all where t has no rows) SELECT foo(x) FROM (SELECT x FROM t LIMIT 1) as bar; But may return a number of records differing from 1 in the case where foo is a set-returning function. jasen=# select a from foo; a --- 1 4 7 6 3 6 rows) jasen=# select generate_series(1,a),a from foo limit 1; generate_series | a -+--- 1 | 1 (1 row) the first row jas 1 and the first row from generate_series(1,1) is returned jasen=# select generate_series(5,a),a from foo limit 1; generate_series | a -+--- 5 | 7 (1 row) the 1st row has 1 and generate_series(5,1) returns 0 rows the 2nd row has 4 and generate_series(5,4) returns 0 rows the 3rd row has 7 and generate_series(5,7) returns 3 rows And the first of those is returned. -- 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 to get localized to_char(DATE) output
On 2010-05-06, Thomas Kellerer wrote: > Tom Lane, 06.05.2010 00:51: >> Thomas Kellerer writes: >>> I'm trying to get the output of the to_char(date, text) method in German >>> but I can't get it to work: >> >> I think you need 'TMMon' to get a localized month name. >> >> regards, tom lane >> > Ah! Silly me. Now that you write this I can see it in the manual ;) > > The manual says the value for lc_time is OS dependent and indeed "set lc_time > = 'German'" does not work on Solaris. > > Is there a way to get a list of allowed values for lc_time for a specific > installation? "man -k locale" would be my starting point (for anything POSIXish) Looks like "locale -a" does it on linux, that may be worth a try. "de_DE" is the locale for German as used in Germany. -- 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] Greetings folks, dumb question maybe?
On 2010-05-12, Josh wrote:
> Hello, I'm a little new at this so please bear with me.
>
> I am trying to create a function that loads 100M test records into a
> database, however I am having a hard time building the function that
> does so.
>
> I'm trying to do this in PGAdmin III for Ubuntu. Is there something
> that I have wrong with this? I know that this works in MySQL (and yes I
> know that MySQL bends the SQL Standards), but I am not sure what I am
> doing wrong exactly. I am coming up with the error that says there's an
> error in my syntax near the v INTEGER := 0 line. I get the same error
> in psql as I do in the PGAdmin III.
>
> I have the following so far:
>
> DECLARE
> v INTEGER := 0;
> BEGIN
> while v < 1
> DO
> INSERT INTO unpart_tbl_test VALUES
> (v, 'test string data', adddate('1995-01-01', (rand(v)*36520) mod 3652));
> v := v + 1;
> END WHILE;
> END;
>
> Any insight would be greatly appreciated.
>
> - J
don't need a function for that one.
INSERT INTO unpart_tbl_test
SELECT GENERATE_SERIES(0,999),
'teststring data',
date '1995-01-01' +(floor(random()*36520)::int % 3652);
--
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] [Trigger] Help needed with NEW.* and TG_TABLE_NAME
On 2010-05-11, Torsten Zühlsdorff wrote:
> Hello,
>
> i have a problem with a trigger written in pl/pgsql.
>
> It looks like this:
>
> CREATE OR REPLACE FUNCTION versionize()
> RETURNS TRIGGER
> AS $$
> BEGIN
>
>NEW.revision := addContentRevision (OLD.content_id, OLD.revision);
>
>/* not working line, just a stub:
>EXECUTE 'INSERT INTO ' || TG_TABLE_NAME || ' SELECT $1 ' USING NEW;
>*/
>
>RETURN NULL;
>
> END;
> $$ LANGUAGE 'plpgsql' VOLATILE;
>
> The function should be used at different tables and is invoked before
> UPDATEs. Everything what happens is the function call of
> addContentRevision. After this call all data (with the updated revision
> column) should be stored in the table as a new row.
What many people have missed is that you want to INSERT when the DML
comnabd UPDATE is used.
for things like that usually a rule is used instead, but I can see where
that may be unsuitable for your needs. I found the following
to work on a simple test case.
The problem is that INSERT in PLPGSQL needs a fixed table-name, and
that "EXECUTE" can't use variable-names, and further that quote_literal
doesn't convert ROW variables into something that can be used in a
VALUES clause.
so, Here's what I did.
CREATE OR REPLACE FUNCTION versionize()
RETURNS TRIGGER
AS $$
BEGIN
-- Not havign a definition for addContentRevision
-- I had this line commented out during testing.
NEW.revision := addContentRevision (OLD.content_id, OLD.revision);
EXECUTE 'INSERT INTO '||TG_TABLE_NAME||' SELECT (' ||
QUOTE_LITERAL(NEW) || '::' || TG_TABLE_NAME ||').*' ;
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL VOLATILE;
I take NEW, convert it to a quoted literal so I can use it in EXECUTE, cast it
to the apreopreiate row type and split it into columns using SELECT
and .*. That gets inserted.
you should probably use QUOTE_IDENT on the TG_TABLE_NAME and possibly
also use similarly quoted TG_SCHEMA_NAME
--
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 to get CURRENT_DATE in a pl/pgSQL function
On 2010-05-18, Kenneth Marshall wrote: > I am trying to write a function that updates the > date column to the current date. According to: > > http://www.postgresql.org/docs/8.4/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT > > you can use CURRENT_DATE. When I try to use it in > the following pl/pgSQL function it gives the error: > > ERROR: date/time value "current" is no longer supported > CONTEXT: PL/pgSQL function "merge_data" line 4 at assignment > > Here is the code I am using: > > CREATE FUNCTION merge_data(key INT, i INT) RETURNS > VOID AS > $$ > DECLARE > curtime date; > BEGIN > curtime := 'CURRENT_DATE'; use one of CURRENT_DATE 'today' NOW() CURRENT_TIMESTAMP 'now' there are probably others which will work too. -- 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] cast record type to array?
On 2010-05-26, Gerardo Herzig wrote: > Hi all. Im not being able to cast a record variable into an array. > > Im writing a trigger, and i would like to store NEW (and OLD) as text[]. > There is a way to do it in plpgsql? (w/o any contrib modules) why not store them as text instead? new::text is a valid cast. -- 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] Extending Regular Expression bounds limit of 255.
On 2010-05-27, Brent DeSpain wrote: > --00504502c13812967604879b4ba3 > Content-Type: text/plain; charset=ISO-8859-1 > > It looks like most of our tools are using the Perl version of regular > expressions with an upper limit of a bound being 32766. Is there any way to > change this in PG? Or can I change from POSIX to Perl? perhaps you can do something in pl-perl? posix regular expressions are different to perl regular expressions in several ways. -- 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] Extending Regular Expression bounds limit of 255.
On 2010-05-29, Tim Landscheidt wrote:
> Jasen Betts wrote:
>
>>> It looks like most of our tools are using the Perl version of regular
>>> expressions with an upper limit of a bound being 32766. Is there any way to
>>> change this in PG? Or can I change from POSIX to Perl?
>
>> perhaps you can do something in pl-perl?
>
>> posix regular expressions are different to perl regular expressions in
>> several ways.
>
> Another last resort possibility would of course be to "pre-
> compile" the regular expressions from "A{2000}" to
> "A{255}A{255}A{255}A{255}A{255}A{255}A{255}A{215}"
(A{200}){10}
might work better.
> (with the headaches of "A{1000,2000}" left as an exercise to the read-
> er :-)).
easy enoungh to write, but probably easy to write an expression with
factorial complexity too, but this one should work, having at worst
two ways of matching any string.
(A{200}){5,9}A{0,200}
but
(A{5,10}){200}
is asking for trouble.
--
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 to construct sql
On 2010-06-02, Wes James wrote: > On Wed, Jun 2, 2010 at 10:55 AM, Oliveiros > wrote: >> Hi, >> Have you already tried this out? >> >> select MAX(page_count_count) - MIN(page_count_count) from page_count group >> by page_count_pdate. >> >> >> Best, >> Oliveiros > > Oliveiros, > > Thx that mostly works. I just tried it and on the days there is only > 1 entry it is 0 since max is the same as min so max - min is 0. Is > there a way to take in to account the 1 entry days? from your original requirement 0 is the correct answer. -- 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] ORDER BY is case insensitive
On 2010-06-22, Bryan White wrote: > I was suprised to find out that ORDER BY is case insensitive. Is > there a way to do a case sensitive ORDER BY clause? use bytea instead of a text type. > This transcript demonstrates what I am seeing: > > bryan=# select * from t order by f; > f > --- > a > b > B > c > (4 rows) try this: select * from t order by replace(f,e'\\', e'')::bytea you may want to index on replace(f,e'\\', e'')::bytea > > bryan=# \q > ~ $ psql -l > List of databases > Name | Owner | Encoding | Collation |Ctype| > Access privileges > -+--+--+-+-+--- > bryan | bryan| UTF8 | en_US.UTF-8 | en_US.UTF-8 | > postgres| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > =c/postgres >: > postgres=CTc/postgres > template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > =c/postgres >: > postgres=CTc/postgres > (4 rows) > > > > -- > Bryan White > -- 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] question about partitioning
On 2010-06-24, Joshua Gooding wrote: > Right now I am in the process of migrating an Oracle DB over to Postgres > 8.4.3. The table is partitioned by size. Is there anyway to partition > the new postgres table by size? I created some partitions for the new > table, but I didn't give postgres any rules to partition by, so I have > 250M test records in one table. Any ideas or thoughts on how to build > the rules for the table by size would be greatly appreciated. by size of what? -- 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] Round integer division
On 2010-06-25, Lee Hachadoorian wrote: > Is it documented anywhere that floating-point numbers round > "scientifically", that is 0.5 rounds to the nearest even number? That's swiss rounding. And no, as I understand it documented that most arithmetic) is platform specific. Postgres is written in C and the relevant portions of the C standards douments (and discussions thereof) give a good picture of the functioning of postgres arithmetic. -- 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] Round integer division
On 2010-06-25, Lee Hachadoorian wrote: > > > On 06/25/2010 07:00 PM, Scott Marlowe wrote: >> That all floating point representations are approximate? >> > But if it's error due to approximation, shouldn't the result be random? > I tried this for a handful of larger numbers, and it appears to > consistently round to the even number. Wouldn't that have to be > intentionally programmed that way? Yes it is, an enginneer at intel made that decision in the 70s when the 8087 coprocessor was developed. If you're not running on x86-like hardware the round operation may do something different. -- 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] multi table import from 1 denormalized source table
On 2010-11-16, Andreas wrote: > Hi, > > I frequently get tables from spreadsheets to import into the DB. > > Usually it looks like this: > A1, A2, A3, A4, B1, B2, B3, with optional C1, C2, D1, D2, ... > > and there is a 1:n relation between A and B. > If provieded the C would be 1:1 to A and D 1:1 to B. > > Up until now I let a VBA script order the source table by A, then scan > the table line by line and create a new entry in the target table A* and > fetch its serial ID everytime the script figures that A changed. > With this IDa create 1 C* and as many B*s until A changes again ... and > of course fetch IDb to attach the D* records with a foreign key column. > > Now I'm trying to get away w/o the VBA stuff. > > Is there a clever way to split such denormalized sources while still > obtaining the needed IDs to connect everything? create a temporary table and put the IDS in there. then do a select distinct to populate table B, do an update from to put the IDs from B into the temp table then do a select to populate table A etc... -- ⚂⚃ 100% natural -- 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] subselect and left join not working?
On 2010-11-29, Jorge Arenas wrote: > select zona_id from zonas where zona_id not in (select zona_id from usuarios ### ###### > where per_id =2) select 'FRED' from from usuarios where per_id =2 what'shappening is your not in subquery is being 'corrupted' by the surrounding query, the expression zona_id is being replaced with the value from the main query. so the inner query return multiple copies of the value from the outer query and the not-in fails. to avoid that confusion do it this way: select zona_id as z from zonas where z not in (select zona_id from usuarios where per_id =2) or this way: select zona_id from zonas where zona_id not in (select usuarios.zona_id from usuarios where per_id =2) or this way: select zona_id from zonas where zona_id not in (select u.zona_id from usuarios as u where per_id =2) -- ⚂⚃ 100% natural -- 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] DELETE WHERE EXISTS unexpected results
On 2010-11-30, Jeff Bland wrote: > This is a multipart message in MIME format. > --=_alternative 007A6509852577EB_= > Content-Type: text/plain; charset="US-ASCII" > > I want to delete certain rows from table USER_TBL. > Two tables are involved. USER_TBL and OWNER_TBL. delete ... using was invented for this purpose. > In the end I expect the USER_TBL to not contain the 3 HOME entries. > But what is happening is the whole USER_TBL is empty after the query. your subselect is being effected by the table used in the delete. -- ⚂⚃ 100% natural -- 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] Union Question
On 2010-12-03, Shaun McCloud wrote: > --_000_7742DD496427B743BC8B7BBF6D380BA0A2F114EXCHANGE10geocomm_ > Content-Type: text/plain; charset="us-ascii" > Content-Transfer-Encoding: quoted-printable > > Hello, > > I need to union three PostgreSQL tables and this won't be a problem but the= > tables are on different servers. Basically, I have an administrative serv= > er that needs the tables viewable in a web administrator and three query se= > rvers that log the needed data locally. Is there a way I can do this witho= > ut using Slony-I to replicate the data to the administrative server? modify the web adminsitrator to connect to the three servers and do the union itself. -- ⚂⚃ 100% natural -- 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] The best option to insert data with primary id
On 2010-12-06, - wrote:
> --0016364d26cf7fa4970496bf2224
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hi everyone,
> I have a question about how best to insert and manipulate the table with
> primary key id for better productivity. I need to insert data into the table
> and get last id.
>
> 1. First option to take counter Postgres SEQUENCE:
> INSERT INTO table (id, ...) VALUES ((SELECT nextval ('seq_table')), ...)
> RETURNING (SELECT currval ('seq_table')) AS id
>
> Only thing I see, that if the row is not inserted, the counter is
> incremented every time when called. Then they will have empty unused id in
> the table and ID number will grow much. There will be many records. This id
> int8 type declared with length 64.
> Is there any option to occupy empty sequence records. I have to worry about
> this?
(assuming the default for id is nextval ('seq_table'))
INSERT INTO table ( id, ...) VALUES ( default, ...) RETURNING id;
or you can leave id and default out of the left half:
INSERT INTO table ( ...) VALUES ( ...) RETURNING id;
> 2. Second option is to take control of id and
> INSERT INTO table (id, ...) VALUES ((SELECT MAX (id) +1 FROM table), ...)
> RETURNING (SELECT MAX (id) +1 FROM table) AS id
you run into concurrency issues that way. (two concurrent inserts
could pick the same ID, one will fail with an error)
> Quero your opinions on how best to insert data to have less maintenance and
> better productivity with concurrent users.
> Thank you very much.
INSERT INTO table ( ...) VALUES ( ...) RETURNING id;
Use the sequence, that's what they were designed for.
Let id get the default value and pull that from the returning.
you will get gaps in the serquence due to failed or cancelled
transactions but there will probably not be many gaps.
--
⚂⚃ 100% natural
--
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] Regular Expression Match Operator escape character
On 2010-12-08, Gnanakumar wrote: > Hi, > > We're running PostgreSQL v8.2.3 on RHEL5. > > In some places in our application, we use Regular Expression Match Operator > (~* => Matches regular expression, case insensitive) inside WHERE criteria. > > Example: > SELECT ... > FROM ... > WHERE (SKILLS ~* > '(^|\\^|\\||[^0-9|^a-z|^A-Z]|$)C#(^|\\^|\\||[^0-9|^a-z|^A-Z]|$)' > OR SKILLS ~* > '(^|\\^|\\||[^0-9|^a-z|^A-Z]|$).NET(^|\\^|\\||[^0-9|^a-z|^A-Z]|$)') > > In this case, we're trying to search/match for either "C#" OR ".NET" in > SKILLS column. > > My question here is, do I need to escape the characters "#" and "." here? yes. ( '.' especially, I don't think '#' has a special meaning in regex) but as postgres uses posix extended regex simply escaping every non-letter character is safe. (^|\\^|\\||[^0-9|^a-z|^A-Z]|$) seems to be another way to write (^|$|[^0-9a-zA-Z]) both of which are locale dependant but that may not be an issue for you. -- ⚂⚃ 100% natural -- 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] constraint with check
On 2010-12-13, Viktor Bojović wrote:
> --0015175cd20209e2030497532e39
> Content-Type: text/plain; charset=UTF-8
> Content-Transfer-Encoding: quoted-printable
>
> Hi
> im trying to create foreign key constraint which checks not only the
> existence of key in foreign table, but it has to check if node field has
> value=3Dtrue
> but i don't know how to do that, so im asking for help.
> alter table wg.nc
> add CONSTRAINT "nc_fk" FOREIGN KEY ("Id1")
> REFERENCES "wg"."entities"("Id"),
> check (node=3Dtrue)
you can't do that.
add a node column to wg (and a check node is not distinct fron true constaint
and default true) then do this
alter table wg.nc
add CONSTRAINT "nc_fk" FOREIGN KEY ("Id1",node)
REFERENCES "wg"."entities"("Id",node);
--
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] UPDATE in a specific order
> I need to make update of table1 with data on table2 in the order of id > of table2 that looks like EAV. is it? > I=B4m trying to do an update like this: that's not going to work. perhaps you can rewrite the from part to only return one row for every table1_fk, this one row will combine several rows from table2 > The "FROM ( SELECT * FROM table2 ORDER BY id ) tHist" is a try to > force a specific order on table2 to update table1 > but this isn=B4t working. will only work if the optimiser picks index join on table 1 > There are some way to do this with a UPDATE statement ? to do it with an update statement you need no more than one rows in the from for each row in the target. easiest non update statement approach is probably to use a plpgsql function with a loop. basically you need to find another way to do it. -- 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] foreign key question
On 2011-01-05, Gary Stainburn wrote: > On Wednesday 05 January 2011 09:53:43 Gary Stainburn wrote: >> Now I want to set up a new access level table specific to the itinerary, >> along the lines of >> >> u_id int4 not null references users(u_id) >> fl_level int4 not null references facility_levels(16, fl_level) >> >> Firstly, is this possible, and secondly how would I do it? > > I've managed a work-around by creating a column that defaults to 16 and then > used ALTER TABLE ADD CONSTRAINT to add a foreign key with two pairs of keys. > > This feels wrong though as my table now has a column that is ultimately > redundant, and worse can be changed to a wrong value. > > Ok, I've sorted the last bit by adding a check constraint to make sure it > always contains 16, but it still feels wrong. it feels wrong that's because it's not normalised, the column with the 16's probably should not be there. or possibly it should have rows with other values too. look at how this table is useful and look for a more general way to do it. -- ⚂⚃ 100% natural -- 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] pattern matching with dates?
On 2011-01-05, Good, Thomas wrote:
> This dubious query worked well previously:
> select * from db_log where log_date LIKE '2011-01-%';
> (currently works on bluehost.com where they run 8.1.22)
>
> Can someone offer a (preferably ANSI compliant) way to do this on 8.4.5?
where date_trunc( log_date,'month') = '2011-01-01'::timestamp;
or standard
where extract( 'year' from log_date) = 2011 and extract ( 'month' from
log_date) = 1;
this:
where cast( log_date as varchar ) like '2011-01-%'
is as far as I can tell standard, but is almost certainly non-portable
as it is dependant of the character format used for casting dates to
varchar.
> I realize that >= and so on work well (which may explain why the docs
> are pretty silent about pattern matching with dates) but sometimes it's nice
> to
> treat the (ISO) date as a string.
">= etc" will outperform date_trunc, like , and extract if the date column
is indexed. the performance of % can be improved in recent versions by
indexing on the expression (log_date::text)
best performance is probably
where log_date between '2011-01-01'::date and '2011-01-01'::date + '1 month -
1 day' ::interval;
or standard (I think)
where log_date between cast('2011-01-01' as date) and cast ( '2011-01-01' as
date) + cast ( '1 month - 1 day' as interval) ;
--
⚂⚃ 100% natural
--
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] Issue with postgres connectivity
On 2011-01-21, Arindam Hore wrote: > We are accessing database using ip address. try adding the IP addresses of some of the clients as seen by the server to /etc/hosts on the server. see if that helps. try connecting to the server locally using 'su postgres -c psql' see if that's slow too. -- ⚂⚃ 100% natural -- 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] question about reg. expression
On 2011-01-18, andrew1 wrote:
> hi all,
>
> these return t:
> select 'ab' ~ '[a-z]$'
this matches the b and the end of the string
> select 'ab' ~ '^[a-z]'
this matches the start of the string and the a
> select 'ab' ~ '^[a-z]$' returns f
> Can't I use ^ and $ at the same time to match, in this case?
> thanks.
the above expression only succeeds if the string is one character long
use '+' '*' or '{2}' etc after the '[a-z]' to allow it to match several letters
or use '^[a-z]|[a-z]$' to match any sting that starts or ends with a
letter.
what are you trying to find?
--
⚂⚃ 100% natural
--
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] Compare the resulta of a count sql into bash
On 2011-01-26, manuel antonio ochoa wrote:
> --0015174be152ceb275049ac2dc95
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hello,
> I have the next :
>
> COUNTONE=`/var/lib/pgsql/bin/./psql -U 'Thor' -d princlocal -p 5432 -h
> 192.170.1.82 -c "select count(*) from monterrey.${NOMBRETB}"`
> COUNTTWO=`/var/lib/pgsql/bin/./psql -U 'Thor' -dprinclocal -p 5432 -h
> 192.170.1.82 -c "select count(*) from monterrey.$nombre where recibo
> between '$FI' and '$FF'"
>
> I want to compare the result countone with countwo how does it works ?
you need -t
COUNTONE=`/var/lib/pgsql/bin/./psql -t -U 'Thor' -d princlocal -p 5432 -h
192.170.1.82 -c "select count(*) from monterrey.${NOMBRETB}"`
COUNTTWO=`/var/lib/pgsql/bin/./psql -t -U 'Thor' -d princlocal -p 5432 -h
192.170.1.82 -c "select count(*) from monterrey.$nombre where recibo between
'$FI' and '$FF'"`
or like this:
CONN="user=Thor database=princlocal port=5432 host=192.170.1.82"
PSQL=/var/lib/pgsql/bin/psql
COUNTONE=`$PSQL "$CONN" -t -c "select count(*) from monterrey.${NOMBRETB}"`
COUNTTWO=`$PSQL "$CONN" -t -c "select count(*) from monterrey.$nombre where
recibo between '$FI' and '$FF'"`
--
⚂⚃ 100% natural
--
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] something simple but I can't
On 2011-01-29, John Fabiani wrote: > Hi guys, > I trying to return a 0.00 from a function it there are no records found else > return the amount. > select sum(aropen_paid) into _paidamt FROM public.aropen where > aropen_applyto is not null and (aropen_applyto = $1) ; > > IF (FOUND) THEN > RETURN _paidamt ; > END IF; > > RETURN 0.00 ; > But all I get is either a blank or the amount paid. What am I doing wrong??? > Johnf how many rows does the query return when no rows match the where? It returns 1 that looks like ( NULL ). it return 1 row, which is more than zero thus FOUND is TRUE. you can fix your function by changing the IF to IF _paidamt IS NOT NULL but if you change the sum to coalesce(sum(aropen_paid),0.00) you can do the task more simply like this: create or replace function danmeans_getpayments(text) returns numeric as $BODY$ select coalesce(sum(aropen_paid),0.00) FROM public.aropen where aropen_applyto is not null and (aropen_applyto = $1) ; $BODY$ LANGUAGE 'sql' ; -- ⚂⚃ 100% natural -- 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] DELETE FROM takes forever
On 2011-02-10, Josh wrote: > Hi > > I'm trying to do a DELETE FROM on my large table (about 800 million > rows) based on the contents of another, moderately large table (about > 110 million rows). The command I'm using is: > > DELETE FROM records WHERE id NOT IN (SELECT id FROM unique_records); > > This process ran for about two weeks before I decided to stop it -- it > was dragging down the DB server. I can understand long-running > processes, but two weeks seems a bit much even for a big table. I find that scripting deletes of smaller numbers of records can help here, long-running queries do bad things to the efficiency of postgres. on strategy that could work for your task would be to create a temp table first: create temp table planned_deletions as select id from records except SELECT id FROM unique_records; create index badids on planned_deletions(id); the repeatedly delete from records where id in ( select id from planned_deletions limit 1 order by id); delete from planned_deletions where id in ( select id from planned_deletions limit 1 order by id); until there are none left. possibly pausing a few seconds between each slug if there is a heavy load on the server (that you were able to run the query for 2 weeks suggests that there may not be). > Is this the best way to approach the problem? Is there a better way? > > Some background: The server is version 8.3, running nothing but Pg. > The 'records' table has 'id' as its primary key, and one other index > on another column. The table is referenced by just about every other > table in my DB (about 15 other tables) via foreign key constraints, > which I don't want to break (which is why I'm not just recreating the > table rather than deleting rows). Most of the dependent tables have ON > DELETE CASCADE. The 'unique_records' table is a temp table I got via > something like: SELECT DISTINCT (other_column) id INTO unique_records > FROM records if you can accept the down-time I would drop the constraints (if you don't have them on file do a pg_dump --schema-only , and grep it for the ADD CONSTRIANT commands, use sed or similar to create matching DROP CONSTRAINT commands, run them) then rebuild the table then reintroduce the constraints, keep a copy of the grep output above -- ⚂⚃ 100% natural -- 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] UTF characters compromising data import.
On 2011-02-08, Gavin Beau Baumanis wrote: > I understand the error message - but what I don't know is what I > need to set the encoding to - in order to import / use the data. if you run it through iconv --from-code=ASCII -to-code=UTF8 -c it'll strip out all the non-ascii symbols, without knowing the encoding it's impossible to assign any useful meaning to them. This step may render your data useless, it would be much better to find out what the encoding should be. perhaps you can figure it out by observation? -- ⚂⚃ 100% natural -- 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] Determine length of numeric field
On 2011-02-15, Tony Capobianco wrote:
> I'm altering datatypes in several tables from numeric to integer. In
> doing so, I get the following error:
>
> dw=# \d uniq_hits
> Table "support.uniq_hits"
>Column | Type | Modifiers
> +-+---
> sourceid | numeric |
> hitdate| date|
> total | numeric |
> hitdate_id | integer |
> Indexes:
> "uniq_hits_hitdateid_idx" btree (hitdate_id), tablespace
> "support_idx"
> Tablespace: "support"
>
> esave_dw=# alter table uniq_hits alter sourceid type int;
> ERROR: integer out of range
>
> Sourceid should not be more than 5 digits long. I'm able to perform
> this query on Oracle and would like something similar on postgres 8.4:
>
> delete from uniq_hits where sourceid in (select sourceid from uniq_hits
> where length(sourceid) > 5);
delete from uniq_hits where sourceid in (select sourceid from uniq_hits
where length(sourceid::text) > 5);
or even: delete from uniq_hits where length(sourceid::text) > 5;
but using length on numbers is usually the wrong way.
do this instead: delete from uniq_hits where abs(sourceid) > 2^32-1;
Which will hit all the ones that can't be converted.
You may want to do a select first to see what you're deleting.
> I haven't had much luck with the length or char_length functions on
> postgres.
The length functions only work with strings. using them on numbers is
usually the wrong thing as there is not a 1 to 1 mapping between
strings an numbers.
Strings of length only 3 can be out of range for integer (eg: '9e9'),
(but numerics never look like that, larger floats can though)
care to guess the result of this query?
select '9000'::float, length('9000'::float::text);
--
⚂⚃ 100% natural
--
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] converting big int to date
On 2011-03-23, Sree wrote: > --90e6ba2123fbe15f02049f2ccf73 > Content-Type: text/plain; charset=ISO-8859-1 > > How can i convert bigint to date format. > > bigint=6169625280 that's got the right number of zeros to be a date expressed as microseconds since epoch in which case the magic spell is select 'epoch'::timestamptz + '1s'::interval * (6169625280::bigint /10.0); gives me a date in 1989 if it's some from a microsoft inspored system instead of AT&T inspired you might need to add '10 years'::interval to the result. -- ⚂⚃ 100% natural -- 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] converting big int to date
On 2011-03-26, Jasen Betts wrote: > that's got the right number of zeros to > be a date expressed as microseconds since epoch except the code which produces a likely data treats it as nanoseconds -- ⚂⚃ 100% natural -- 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] convert in GMT time zone without summer time
On 2011-04-15, LaraK wrote:
> Hello,
>
> I want write a function that converts a timestamp with time zone to the UTC
> zone. But it should all be stored in the winter time.
>
> For example, it must now, in the summer, the German time back by 2 hours and
> in the winter time only 1 hour. But it expects only back one hour.
>
> Is there a function or a specific time zone?
if I undestand your goal correctly you want to subtract the daylight
savings offset from the given timezone if daylight-savings is in use
in the current time locale.
you can detect daylight-savings by setting testing the timezone offset
at 3 month intervals ( timestamp, timestamp+3months timestamp-3months,
timestamp+6months, timestamp-6months)
the one(s) of them with the least (most negative) offset from UTC will
represent non daylight-saving time.
if your given time has a different offset it's daylight saving time,
add the difference.
calling:
> [CODE]
> SELECT
> to_char(CONVERT_TO_UTC(to_timestamp('2011-03-22 14:17:00', '-MM-DD
> hh24:MI:SS'), 'CET'), '-mm-dd hh24:MI:SS') AS winter,
> to_char(CONVERT_TO_UTC(to_timestamp('2011-04-22 14:17:00', '-MM-DD
> hh24:MI:SS'), 'CET'), '-mm-dd hh24:MI:SS') AS summer
> [/CODE]
>
> must come out:
> [CODE]
> WINTER| SUMMER
> +-
> 2011-03-22 13:17:00 | 2011-04-22 12:17:00
> [/CODE]
that test case is ambiguous your inputs are timespamptz but
have an unspecified timezone (and so get the zone appropriate to
your time locale). I'm assuming your time locale is "Europe/Berlin"
and you really mean the following:
SELECT to_char(CONVERT_TO_UTC( '2011-03-22 14:17:00+01'::timestamptz
,'CET'),'-mm-dd hh24:MI:SS') AS winter, to_char(CONVERT_TO_UTC(
'2011-04-22 14:17:00+02'::timestamptz ,'CET'),'-mm-dd hh24:MI:SS')
AS summer;
CREATE OR REPLACE FUNCTION
CONVERT_TO_UTC ( timestamptz, text) returns timestamp as
$$ SELECT $1 at time zone 'UTC'; $$
language sql;
In that this function does not use the second parameter it may not be
what you want, on the other hand it's function matches it's name well.
what are you trying to do?
--
⚂⚃ 100% natural
--
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] Get id of a tuple using exception
On 2011-04-14, f vf wrote: > --000e0cd2bf6a60c30804a0dec84b > Content-Type: text/plain; charset=ISO-8859-1 > > Hello, > i'm using a pl/sql procedure and I prevent inserting duplicate tuples using > an exception for example: > > BEGIN >INSERT INTO "Triples"(id, subject, predicate, "object") > VALUES (id, sub_i, pred_i, obj_i); > * EXCEPTION WHEN unique_violation THEN > --do something. > > *In some cases I have interest in getting the id of the tuple that was > already in the table when the exception is triggered. Is there a way for the > EXCEPTION to return that id instead of using a select to know wich was the > id of the triple already existing in the table? if the unique violation is on the ID column that's easy, if it's on some other constraint then no there's no way to get the id. do a select first looking for the colliding row then fall back to an insert. there may be weaknesses with this, it depends on why you need the Id. -- ⚂⚃ 100% natural -- 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] help on select
On 2011-04-20, Saulo Venâncio wrote:
> --bcaec52e65e9b2f22304a15f3840
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: quoted-printable
>
> Hi guys,
> I need your help.
> I have a table called medidas, in this table i have some ocurrences that ha=
> s
> id_medida(primary key) id_ponto (sec_key) and also datetime field as
> timestamp.
> i would like to know from a set of idpontos, e.g. 10,11,23,24 how can i
> get the most recent date that is common to all??
> for example, if idponto das date 2011-02-03 but none of others have this
> date in the db i dont want this. i want one common for all..
> thanks.
the trick seems to be to GROUP BY datetime
and to use a HAVING clause to reject the unwanted groups using
count(distinct()) to ensure coverage of the list.
-- a table
create temp table medidas(id_medida serial,id_ponto integer,datetime timestamp);
-- some test data.
insert into medidas (id_ponto,datetime) select
floor(random()*30+1),('today'::timestamp +
floor(generate_series(0,10)/10)*'1s'::interval);
-- the query:
-- note you need to paste the list of number in two different places
-- in the query, postgres only counts the length once.
select datetime
from medidas
where id_ponto in (10,11,23,24,27)
group by datetime
having count(distinct(id_ponto)) = array_length( array[10,11,23,24,27],1)
order by datetime desc limit 1;
-- confirmation
select * from medidas where datetime = (
select datetime
from medidas
where id_ponto in (10,11,23,24,27)
group by datetime
having count(distinct(id_ponto)) = array_length(array[10,11,23,24,27],1)
order by datetime desc limit 1
)
order by id_ponto;
what's this for?
Are you looking at keno results to see how recently your pick would have won?
--
⚂⚃ 100% natural
--
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] self join
On 2011-05-14, Seb wrote: > Hi, > > This probably reflects my confusion with how self joins work. > > Suppose we have this table: > If I want to get a table with records where none of the values in column > b are found in column a, I thought this should do it: use the "NOT IN" operator with a subquery to retch the disallowed values. select * from tmp where a NOT IN (select b from tmp); -- ⚂⚃ 100% natural -- 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] self join
On 2011-05-16, Steve Crawford wrote: > On 05/14/2011 07:36 PM, Jasen Betts wrote: >> >> use the "NOT IN" operator with a subquery to retch the disallowed >> values > Hmmm, "retch" as a synonym for "output"? I've seen more than one case > where that is an appropriate description. :) :) was a typo for 'fetch' -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: Order of evaluation in triggers for checks on inherited table partitions
On 2011-05-27, Kevin Crain wrote: > I am trying to create a trigger on updates to a table that is > partitioned. The child tables are partitioned by month and include > checks on a timestamp field. > However when I try to update an existing record with a > timestamp that would place it in a child table different from the > child table it is in I get an error due to the check on the child > table it is currently in. My best guess as to what is happening is > that the trigger is evaluating the check before it evaluates the > trigger function and thus cannot tell that the update to the original > table should never take place. I have included an example below. The > error that results is "new row for relation "t_foo_2011_6" violates > check constraint "t_foo_2011_6_f_timestamp_check"" the problem is the check is running before the trigger. perhaps you can use a rule instead of a trigger? -- ⚂⚃ 100% natural -- 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] selecting records X minutes apart
On 2011-06-03, lists-pg...@useunix.net wrote: > > IDTS (HH:MM) > --- > 0 20:00 > 0 20:05 > 0 20:10 > 1 20:03 > 1 20:09 > > > Does my question make sense? no, why is (1,20:04) excluded, but (0,20:05) included? both records are 5 minutes from the newest. -- ⚂⚃ 100% natural -- 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] ANY for Array value check
On 2011-06-10, Emi Lu wrote:
> Good morning,
>
> String array compare command, I forgot how to do it.
>
> E.g.,
> create table z_drop(id varchar[]);
> insert into z_drop values('{"a1", "a2", "b1", "b2", "b3"}');
>
> I'd like to do:
>
> select * from z_drop where id = any('a1', 'b1');
use the array overlap operator:
select * from z_drop where id && ARRAY['a1'::varchar, 'b1'];
If you define the column as text[] instead of varchar you don't need
the ::varchar cast above. there's no postgres reason to prefer
(unbounded) varchar to text.
--
⚂⚃ 100% natural
--
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] Storage of Indian Language text in postgresql through PHP
On 2011-06-14, INDER wrote:
> Hello Everyone. I am new to this group and as well as to the Postgres
> also. Can anybody tell me that how to insert hindi text into postgres
> that a user has entered from html input with the use of PHP. Please I
> am waiting for the reply.
add this PHP before any content is emitted:
header('Content-type: text/html; charset=utf-8');
or set a web servr option that has the same effect.
--
⚂⚃ 100% natural
1
--
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] pagination problem in postgresql need help
On 2011-06-17, hatem gamal elzanaty wrote: > hi, > please see this code > > select aiah_number.aiah_number_id, aiah_number.aiah_number, ... > order by rank_value desc limit 1 offset 0; > > and this code > > select aiah_number.aiah_number_id, aiah_number.aiah_number, ... > order by rank_value desc limit 1 offset 1; > suppose i have 200 hundred record and i want to display records in pages > one record per page i'm facing a problem even if i'm running the script > through phppgadmin it's only display the first record in the page only > no more no less in the php code it display record 1 and records byound > 180 and so on > > can you help in that issue ? check that the PHP query is actualy what you think it is. -- ⚂⚃ 100% natural -- 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] interesting sequence
On 2011-07-06, Kevin Crain wrote: > That's why you need to do this inside a function. Basically just make > an insert function for the table and have it calculate the count and > do the insert in one transaction. you will still get duplicates, so include code in the function to retry if there is an error. -- ⚂⚃ 100% natural -- 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] Looking for a "show create table " equivalent
On 2011-07-12, B.Rathmann wrote: > Hello, > > I've been trying to find out how to find out which sql was run to create > a certain table. That's like trying to find what change was used to create $1.83 there several possible answers all but one of them wrong, but many of them may . > As I need this in a program which may access the database remotely, > using pg_dump --schema-only or psql is not an option > (the system my program runs on may not even have those tools installed). why do you think you need this information? > Looking at the source of pgadmin3 it seems as if the program collects > all info about the given table (columns, indeces etc) and creates the > needed SQL by itself, is there no easier way? > > E.g. MySQL offers a simple "show create table ". I am using > Postgresql 9.0.4 on Gentoo. can you find what you need to know in the information schema? http://www.postgresql.org/docs/8.4/static/information-schema.html It's an industry standard, and thus should work with every SQL database. -- ⚂⚃ 100% natural -- 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] to_char() accepting invalid dates?
On 2011-07-18, Thomas Kellerer wrote:
> Hi,
>
> I just noticed that to_char() will "accept" invalid dates such as 2011-02-31
> and "adjust" them accordingly:
>
> postgres=> select to_date('20110231', 'mmdd');
>
>to_date
>
> 2011-03-03
> (1 row)
>
> is there a way to have to_date() raise an exception in such a case?
it's possible the odd behaviour you get is required by some standard.
however 'mmdd' is a format that postgres understands natively,
so just cast the string to date.
jasen=# select '20110303'::date;
date
2011-03-03
(1 row)
jasen=# select '20110231'::date;
ERROR: date/time field value out of range: "20110231"
LINE 1: select '20110231'::date;
^
postgres also understands -MM-DD and possilby a locale
dependant form with the year last
--
⚂⚃ 100% natural
--
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] Generic design: char vs varchar primary keys
On 2011-08-03, Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?=
=?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) wrote:
> Hi,
>
> Can you point me to any pages that explain the difference between using,
> say CHAR(8) vs VARCHAR(8) as the primary key for a table? Is there any
> impact on the database in terms of:
look in the data types chapter of the manual
> - Speed of indexed retrieval
> - Speed of join from a referencing table
> - Storage (I presume VARHAR(8) would have a slight edge, in general)
> - Any other issue
Unless you need the padding, and/or the length constraints use text.
("usr/share/doc/postgresql-doc-8.4/html/datatype-character.html")
. Tip: There is no performance difference among these three types,
. apart from increased storage space when using the blank-padded type,
. and a few extra CPU cycles to check the length when storing into a
. length-constrained column. While character(n) has performance
. advantages in some other database systems, there is no such advantage
. in PostgreSQL; in fact character(n) is usually the slowest of the
. three because of its additional storage costs. In most situations text
. or character varying should be used instead.
--
⚂⚃ 100% natural
--
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] Generic design: char vs varchar primary keys
On 2011-08-04, Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?= =?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=) wrote: > Thanks, that's useful for benchmarking the various textual data types. > Anything specific about using CHAR vs VARCHAR for primary keys that are > going to be referenced from multiple tables that comes to mind? that page he pointed you to says they are all stored the same. (excepte char(8) is padded and that may bloat the index a bit) -- ⚂⚃ 100% natural -- 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] which is better: using OR clauses or UNION?
On 2011-08-16, adam_pgsql wrote:
>
> Hi,
>
> I have a query hitting a table of 25 million rows. The table has a
>text field ('identifier') which i need to query for matching rows. The
>question is if i have multiple strings to match against this field I
>can use multiple OR sub-statements or multiple statements in a UNION.
>The UNION seems to run quicker is this to be expected? or is there
>anything else I can do improve the speed of this query? Some query
>details:
> WHERE
> ( lower(identifier) LIKE lower('BUGS001884677') OR
>lower(identifier) LIKE lower('BUGS001884678') OR
>lower(identifier) LIKE lower('BUGS001884679') OR
>lower(identifier) LIKE lower('SpTIGR4-2210 (6F24)') )
> ORDER BY a.identifier;
don't use like use regex.
...
WHERE identifier ~*
E'^(BUGS001884677|BUGS001884678|BUGS001884679|SpTIGR4-2210
\\(6F24\\))$'
or
where lower(identifier) ~*
lower(E'^(BUGS001884677|BUGS001884678|BUGS001884679|SpTIGR4-2210
\\(6F24\\))$')
on the other hand you aren't doing any pattern stuff. - you you could
just use 'in':
WHERE lower(identifier) in
(lower('BUGS001884677'),
lower('BUGS001884678'),
lower('BUGS001884679'),
lower('SpTIGR4-2210(6F24)')
)
or if you need like, use like any:
WHERE lower(identifier) like ANY
(lower('BUGS001884677'),
lower('BUGS001884678'),
lower('BUGS001884679'),
lower('SpTIGR4-2210(6F24)')
)
> Also which should scale better if I add more strings to match? would there be
> any better design patterns for this problem?
use one of the above: preferably in, else regex, or failing that like any.
"= any" will also work but I don't thing it will ever be better than "in"
--
⚂⚃ 100% natural
--
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] exclusion constraint for ranges of IP
On 2011-08-21, Herouth Maoz wrote: > Hi, > > I'm designing a new database. One of the table contains allowed IP ranges for > a customer (Fields: customer_id, from_ip, to_ip) which is intended to check - > if an incoming connection's originating IP number falls within the range, it > is identified as a particular customer. > > Naturally, I'd like to have constraints on the table that prevent entering of > ip ranges that overlap. Is there a way to do that with exclusion constraints? > Or do I have to define a new type for this? > > Herouth if you can use CIDR instead of ranges it should be relatively simple -- ⚂⚃ 100% natural -- 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] Confused about writing this stored procedure/method.
On 2011-08-22, JavaNoobie wrote: > Hi All, > I'm trying to write a stored procedure /function to re-order a set of > calendar months.I have a set of calendar months stored from January to > December in my tables. And as of now when I do order by on this column , > the data is ordered alphabetically , starting April, august etc. and so on > I want to order these months starting from April through March in order to > sync with the financial calendar . I'm trying to write a stored procedure to > do the same (I'm not aware of any other method that Postgres offers this > reordering , if there's any , please do let me know!). order by (case month when 'January' then 1 when 'February' then 2 ...[I'm too lazy to type the rest]... when 'December' then 12 end) get the idea? (except change the numbers to match financial calendar) you can index on that expression too if you have them as numbers instead of words you can use an array instead of the case. For as task like this an SQL function may be more efficient than a PLPGSQL function. -- ⚂⚃ 100% natural -- 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] exclusion constraint for ranges of IP
On 2011-08-23, Herouth Maoz wrote: > EXCLUDE USING GIST ( customer_id WITH =, is_default WITH AND ) > Basically, each customer can have several rows in this table, but only = > one per customer is allowed to have is_default =3D true. Is this exclude = > constraint correct? I don't really understand exclude, but instead of EXCLUDE... I would do CREATE UNIQUE INDEX "invoice_definitions-unique-default" ON invoice_definitions(customer_id) WHERE is_default; Which would create a smaller (and probably faster) BTREE index containing only the rows with is_default true. There seems to be no way to create this in the create-table command. (using 8.4 here) -- ⚂⚃ 100% natural -- 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] where col1 not ilike ('str1%', 'str2%'... 'strN%') support?
On 2011-08-30, Emi Lu wrote:
> Hi Tom,
>
select * from tablename
where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%']));
>>
>>> If next version could have "not ilike ('', '')" added into window
>>> functions, that's will be great!
>>
>> Why? And what's this got to do with window functions?
>
> First, where not (col1 ~~* any(array['str1%', 'str2%'... 'strN%'])) will
> work for me.
>
> But I feel " ilike ('str1', ... 'strN')" is more intuitive, isn't it?
no, "('str1', ... 'strN')" looks a lot like a tuple.
if you know these strings beforehand use a single regular expression
instead because ilike is just regex in drag (postgres uses regex to do
ilike), and while ilike can only check one pattern at a time regex can
check several simultaneously.
foo ~* '^(str1|str2|str3...|strN)$'
--
⚂⚃ 100% natural
--
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] Want some basic compare of data type on PostgreSQL and MySQL
On 2011-09-01, bhavesh1385 wrote: > Hello All, > > I Want some basic compare of data type on PostgreSQL and MySQL. > > [1] How to make Primary Key as a Auto Increment...? you can't, use the pseudo-type serial (or bigserial) instead which does something similar, but subtly different. > [2] Suppose I want to put 'ENUM' data type then how i can do that ..? I think you have to create an enum type first. > [3] Please suggest me basic Data type Comparesion between PostgreSQL and > MySQL .. like :- > > PostgreSQL MySQL > - > characte varying VARCHAR > integer INT postgres understands varchar and int as well as aliases fior the expected types > like that i want all the possible data type comparision. assuming you understand mysql types already: http://www.postgresql.org/docs/9.0/interactive/datatype.html -- ⚂⚃ 100% natural -- 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] Dynamic sql
On 2011-09-10, Gabriel Filipiak wrote: > --bcaec517adbceea3c804ac90a376 > Content-Type: text/plain; charset=ISO-8859-1 > > Hi, this is my first on this list. > > I want to know the basics of dynamic sql especially in PostgreSQL. I was > googling for a while but have no luck for getting a good described examples. > Maybe someone here could give me some links to the materials from which you > where studying this subject. Usually I go to the manual first. http://www.postgresql.org/docs/9.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN some things to consider: use quote_identifier and quote_literal when passing names and values into the sql statements: you can't pass variables in, only their values. -- ⚂⚃ 100% natural -- 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] Edit multiple rows concurrent save
On 2011-09-29, Péter Szabó wrote: > users_has_cards.auctions + users_has_cards.decks never can be higher > then users_has_cards.total. It should be also granted that > users_has_cards.total - users_has_cards.auctions number of cards from > a specific type can be in any decks. > > The deck assembly input comes from web, and parsed with PHP, so it is > absolutely not trusted. > > I am started to write a PL/PgSQL function to handle the save of a > deck, but it seems unable to solve this issue. I don't know how to go > forward, so any idea is appreciated. so users_has_cards has a check ( total >= decks + auctions ) and decks_has_cards needs triggers on insert, update, and delete that manipulate the decks column of the users_has_cards record that corresponds to that card. This is probably goiung to make updates to the decks_has_cards table about 10 times slower (should still be faster than you could have done in PHP) -- ⚂⚃ 100% natural -- 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] Number timestamped rows
On 2011-11-02, Jan Peters wrote: > Dear all, > maybe a stupid question, but: I have a table that is ordered like this: > Tables aren't ordered. Sometimes they may seem to be ordered, but they seldom stay that way for long. > and I would like to number them according to their timestamps like this: > How would I do this with an UPDATE statement (e.g.) in pgsql? If you want ordered data use an ORDER BY clause in the select, there is no other reliable way. -- ⚂⚃ 100% natural -- 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] the use of $$string$$
On 2011-11-07, Richard Huxton wrote: > On 05/11/11 00:12, John Fabiani wrote: > OK, so it seems psycopg is quoting your strings for you (as you'd > expect). It's presumably turning your query into: > ... values (E'123', $$E''$$) > So - the $$ quoting is unnecessary here - just use the % placeholders. > > Incidentally, should it be %s for the numeric argument? psycopg2 seems to only accept %s as a place-holder, it's not printf it just looks a bit like it. -- ⚂⚃ 100% natural -- 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] Partitionning + Trigger and Execute not working as expected
On 2011-11-08, Sylvain Mougenot wrote:
> --f46d043c7fbad4a6b104b1357041
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: quoted-printable
>
> Hello,
> I'm trying to use table partitionning on a table called JOB.
> Each month a new table is created to contain the rows created on that month=
> .
> ex : JOB_2011_11 for rows created during november 2011.
>
> To do that I followed this advices on that page :
> http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html
> I also would like to create code dynamically into the trigger in order to
> have all "INSERT INTO" inheritated tables (tables like JOB__MM) queries
> done.
>
> But I can't make it work. I've an error when the insert is done using
> EXECUTE.
> *Working :* INSERT INTO job_2011_11 values (NEW.*);
> *Not Woking : *EXECUTE 'INSERT INTO '|| currentTableName || ' values
> (NEW.*)';
>
> Could someone tell me how to make this EXECUTE work?
EXECUTE 'INSERT INTO '|| currentTableName || ' select
('||quote_literal(NEW)||'::job%ROWTYPE).*';
or
EXECUTE 'INSERT INTO '|| currentTableName || ' values ($1.*)' USING NEW;
--
⚂⚃ 100% natural
--
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] Updatable view should truncate table fields
On 2011-11-08, Russell Keane wrote: > > We can extend the table to accept more than 5 characters but the view must = > return 5 characters. > If we try to extend the table to accept, say, 10 characters the view will d= > isplay 10. > If I also cast the view field to 5 characters then any insert with more tha= > n 5 characters still fails. > > Any ideas??? re-load the view and functions, they are sill defined with the char(5) column (you'll probably yneed to drop them all (but not the table) first). plpgsql functions are partially compiled at the time they are defined subsequent modifictions to the datatypes in their definition will cause errors until they are re-defined -- -- 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] updating a sequence
On 2011-11-16, Scott Marlowe wrote:
> You need to wrap a subselect in ():
>
> select setval('foo', (select max(some_id) from some_table));
I prefer to do it in once select like this:
select setval('foo', max(some_id)) from some_table;
--
⚂⚃ 100% natural
--
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] conditional FROM
On 2011-12-10, Richard Klingler wrote: > Good day... > > I'm trying to build a query for PGSQL 9.1 where a table has two > references with only one being used depending of the type of entry.. > > For example, the table has following simplified structure: > > portid primary key > port2node index to table node > port2card index to table card > > So how can I do a conditional FROM clause in the query depending on the > column port2node and port2card? > If port2card is Null or 0 I don't want it in the FROM clause as the > query will return unneccessary duplicate > row...the same goes for port2node being Null or 0... use left outer join. SELECT * FROM port LEFT OUTER JOIN node ON node.nodeid=port.port2node LEFT OUTER JOIN card ON card.cardid=port.port2card or something like that. You may find coalesce() useful to combine columns where node and card both carry equivalent information. -- ⚂⚃ 100% natural -- 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] Question on imports with foreign keys
On 2011-12-08, Andreas wrote:
> Hi,
>
> suppose you need to import a csv with standard ciolums like name,
> adress, phone, ... and some additional text columns that need to be
> split off into referenced tables.
...
> How is the easiest way to to find the customer.id of the new customers
> so I can insert the projectinfos?
create table tmp.customer (id integer, name text, addr text)
copy tmp.customer ( id,name,addr ) from stdin ;
...
alter table tmp.customer add column new_id integer default
nextval('customer_id.seq'::regclass);
(here the default is the same default that the customer table uses for
its id.)
now you can use "insert ... select ..." to insert these new records
explicitly using new_id to fill the id column of the customer table.
iport the other csv data into similar tables also
and use join on the old id in tmp.customer to get the new id for
copying the other imported tabled.
--
⚂⚃ 100% natural
--
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] partitions versus databases
On 2011-12-08, chester c young wrote: > have an db with about 15 tables that will handle many companies. no data > overlap between companies. is it more efficient run-time to use one database > and index each row by company id, and one database and partition each table > by company id, or to create a database for each company? > > it is a web-based app using persistent connections. no copying. > if you know you will never want to aggregate data across several companies. databases are cheap, portable, easily duplicated, and self-contained, can easily be dumped, restored, and dropped individually, go with one per company. if there's a possibility you may want to merge two companies, or aggregate data in some other way you want to put them all in the same database so that sequences can be shared to ensure that ids are unique etc... you still have the option of partitioning by schema, table name, or just by tagging each record. -- ⚂⚃ 100% natural -- 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] internal format of timstamp?
On 2011-12-29, Lars Gustafsson wrote: > Hi, > > I am trying to recover a lot of deleted rows from a database ( pg > 8.2.3 ) , not my database, I promise….. >> When using the tool pgfsck I get good results, but timestamp is not >> implemented. > > When trying to export as int8 i get fx. 4735129360236469258 > representing december 29, 2011, 16:30 > > But how should I do the conversion from the numeric value to the actual > timestamp ? possibly that's a floating point timestamp try it as float8 instead of int8. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: Current transaction is aborted, commands ignored until end of transaction block
On 2011-12-30, Jan Bakuwel wrote: > This is a cryptographically signed message in MIME format. > > What annoys me is that I don't think that a constraint violation made by > a user should result in an aborted transaction. There is probably a very > good reason to do that however the logic escapes me... the reason for it is it allows several updates (or other DML) to be run without checking for success and then success only checked at the commit stage. this makes it easier to the DBA to enforce databse consistancy against wayward applications and not suffer from partial inserts. > Of course I can start testing existing values in the database before > accepting them in the user interface but that's putting the horse behind > the cart. I much rather use the constraints at the database level to > tell me a particular update can't be done and do that without loosing > everything else I happened to have done in that transaction until that > point. > > Any suggestions? checkpoints can probably do what you want, but long-lived transactions are a bad idea in general, especially if you expect to have several physical users accessing your database simultaneously. -- ⚂⚃ 100% natural -- 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] lo_import
On 2012-01-03, Maurício Cruz wrote: > Hi all, > > I'm trying to use lo_import to import a file into my database, if I > execute from postgres runing in my local machine > it works perfectly, but if I do it in the postgres runing in the server, > it says "No such file or directory" > > I Guess postgres only see file on the machine it is runing and not > through the network... It can only see the files the database server can see. > I will have to upload the file into the server and then use import ? that would work > is there any other way ? the "\lo_import" command in psql, which reads files as the current user anp pushes them through the database connection. the best way is probably to add the file import feature to your appplication. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
