[SQL] Comparing a string against an XPath result set
Hi, I'm trying to check whether a given string is part of an XPath result set. I have encountered some performance prob- lems in the process, but before I track those down further, I'd like to see if I've chosen the right path to begin with: | SELECT * FROM $TABLE | WHERE $COLUMN = ANY((xpath($EXPR, xmlparse(DOCUMENT $DOC)))::TEXT[]); In this case, xpath() returns an array of XML fragments made of a "pure" text child each. I cannot compare XML fragments with anything, so the conversion to TEXT[] seems to be the only way I could check whether $COLUMN is a part of those. Is there any other way I could tackle this? Tim -- 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... ?
(anonymous) 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 Have a look at CREATE RULE. Tim -- 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... ?
(anonymous) wrote: >> > 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 >> Have a look at CREATE RULE. > Ok, you mean I could create a rule for the table, then I let the > script go into my "black box", > do the original selects, but get a manipulated result set back, then I > drop the rule so > that the blackbox of scripts can get the not manipulated result set > back? No, I meant that you should have a look at CREATE RULE. From a design perspective, I'd probably rename the old table and put an updatable view in its place. > I need to sometimes get the result "output from query" and > sometimes not > and it would be awesomw to get it with the same query, but maybe by > setting > a rule or similar. Best from performance view would be to make > something > more permanent. Is it possible to activate/deactivate a rule? Of course, DROP RULE. Tim -- 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
Alvaro Herrera wrote: >> > 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. > I wouldn't write it in C but rather Perl or Python, but whatever suits > your fancy should work (Visual Basic anyone?). The advantages to using > a listener program instead of doing it in a trigger or something like > that are: > - transaction semantics are kept; you don't send an email only to find > out your transaction has been rolled back for whatever reason, and then > send a second email when the transaction is replayed > - you don't block the database system just because your mail server is > down > - the email can be sent on whatever schedule fits the listener program > - the listener client can run elsewhere, not only in the database server > - any further external processing can take place at that time, without > bothering the database server > - other stuff I don't recall ATM The main disadvantage in using a listener is that it is your responsibility to make sure that the listener is listening 24/7 - from before the database accepts other connections, through network failures, bugs, etc. - otherwise notifica- tions will be lost. Therefore I find it much more reliable (and easier to program) to copy the relevant data to a table "mailqueue" (or whatever) and then process that queue every other minute. Tim -- 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
"Relyea, Mike" wrote: >> The goal is, where uid not equals to 'janvleuven10' a new >> record should be inserted with the uid, and registered=0 > So if a record is found you want to update it and if a record isn't > found you want to insert it. I think you'll probably want to use > plpgsql http://www.postgresql.org/docs/8.4/static/plpgsql.html or some > other language like Jasen suggested. I don't know of a way to do this > with straight sql. Something along the lines of: | UPDATE table SET attribute = 'something' WHERE primary_key = 'id'; | INSERT INTO table (primary_key, attribute) SELECT 'id', 'something' WHERE 'id' NOT IN (SELECT primary_key FROM table); should achieve that. Tim -- 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] FW: simple? query
Jan Verheyden wrote: > Thanks for the suggestion, the only problem is, if primary key is used then > each row should be unique what is not true; since I have a column > 'registered' what only can be 1 or 0... > [...] I have no idea what you are trying to say. Tim -- 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] Lag and lead window functions order by weirdness
Thom Brown wrote: > [...] > Having a look around, it looks as if Postgres might be misbehaving. > According to this page, > http://my.safaribooksonline.com/0596004818/sqlnut2-CHP-4-SECT-3, the ORDER > BY in the window function's clause shouldn't be having this ordering effect: > "Furthermore, the order within these groups is defined by an ordering > clause, but that order only affects function evaluation, and has no effect > on the order in which rows are returned by the query." > The behaviour is unexpected from my perspective, but obviously there are > workarounds. Is anyone able to confirm any of this? AFAIR, others have already pointed out that without an "OR- DER BY" clause PostgreSQL can return the result set in *any* "order" it deems fit. So why don't you use one? Tim -- 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] loading a file into a field
Brian Sherwood wrote: > I am looking to play around with the postgres XML functions, but can't > seem to find a way to load my data. > I have a number of XML files: file1.xml, file2.xml, etc All files > follow the same xml format (DTD?) > I would like to create the following table: > CREATE TABLE configs ( > filenamevarchar(80) PRIMARY KEY, > config xml > ); > and load each file into the xml field, but can't seem to find a way. > (I think I need something similiar to lo_import, but have not found anything) There is no function to do that in the standard distribu- tion. You have to solve that in your client application. Theoretically, you could try to do it in a server-side user function, but permissions, local vs. remote file systems & Co. usually only create major headaches. Tim -- 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
Michael Gould wrote:
> 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.
> [...]
Based on your requirements, you want to hire some cheap Eng-
lish native speaker with lots of cultural knowledge.
Tim
--
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] complex join question
Louis-David Mitterrand wrote: > Here is my schema: > cruise_line -> ship(id_line) -> cruise_type(id_ship) -> cruise(id_cruise_type) > -> price(id_cruise, id_currency) <- currency (USD,GBP,EUR,CAD) > (a 'cruise' is a 'cruise_type' + a date) > I am trying to display a count of cruise's for each ship and each > currency even if that count is 0. > But I am having trouble building the query, as some 'cruise's might not > (yet) have a 'price' in all currencies and so no link to 'currency'. You're probably looking for a "LEFT JOIN". Tim -- 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] Can i force deletion of dependent rows?
Shruthi A wrote: > I have 2 tables (A and B) where the table B has a foreign key reference to > table A. Like this: > create table A (x int primary key); > create table B (y int primary key, z int references A (x) ); > As you can see, i've not specified any further 'on delete' clause to the > foreign key constraint like 'on delete cascade' or 'on delete restrict' or > 'on delete set null' or 'on delete set default'. This means that the > default behavior is followed ie 'on delete restrict' holds by default. > Now I want to delete a particular row from table A. Is there any way I can > force deletion of all the dependent rows in table B? > Note that the tables have already been created in the above manner and > populated. I want to do this deletion through DML only ie without any > change in the table definition. "DELETE FROM B WHERE z = 'your x';"? Tim -- 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] Can i force deletion of dependent rows?
Shruthi A wrote: > Thanks people, but the trigger doesnt really solve my problem. You see, > there is actually a CHAIN of dependencies where B references A, C references > B, D and E reference C and so on... So if i start writing triggers for all > these, there'll be a huge process to follow. I'm not strictly against it, i > might use it as a last resort, but i'm asking if a simple DML statement > would solve my problem. > Dear Shoaib, I did mention in a note that I don't want to recreate the 2 > tables with the 'on delete cascade' clause. The tables are ALREADY created > and populated. And they are huge. So i cannot afford to drop and recreate > them. I want an option in DML only. If that is possible that is. You don't have to drop and recreate them, you could just temporarily change the foreign key definition (untested): | BEGIN WORK; | ALTER TABLE B DROP CONSTRAINT B_z_FKey; | ALTER TABLE B ADD CONSTRAINT B_z_FKey FOREIGN KEY(z) REFERENCES A(x) ON DELETE CASCADE; | DELETE FROM A WHERE x = 'your x'; | ALTER TABLE B DROP CONSTRAINT B_z_FKey; | ALTER TABLE B ADD CONSTRAINT B_z_FKey FOREIGN KEY(z) REFERENCES A(x) ON DELETE NO ACTION; | COMMIT WORK; But obviously this is no better than a simple "DELETE FROM B WHERE z = 'your x';" as you still have to name the dependen- cy chain yourself. You can query the information_schema to build this chain (e. g. cf. http://sqlserver2000.databases.aspfaq.com/schema-how-do-i-find-all-the-foreign-keys-in-a-database.html>), but I would invest more time in rethinking your processes. Tim -- 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 or LEFT JOIN?
Louis-David Mitterrand wrote: > Here is the basic schema: > -->id_ship>--- > || > [SHIP]->id_ship->[CABIN]->id_cabin->[PRICE]<-id_cruise<-[CRUISE] > It's a database of cruise prices. > Each 'price' object has a reference to 'cabin' and 'cruise' > 'cabin' belongs to a 'ship', so does 'cruise' > I'm trying to select all cabins of cruise N°1 with prices OR nothing if > there is no price (meaning cabin not available). I want all cabins > listed, price or no price. > Also when doing the query I don't have the id_ship, only the id_cruise. > What is the best way of doing it? UNION or LEFT JOIN? I tried the latter > without success and am unsure on how do do the former. Was does "without success" mean? The objective seems to be straight-forward: - Select all cabins that belong to the ship that belongs to the cruise id_cruise. - Left join that with the prices of the cruise id_cruise. Tim -- 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 max() make null as biggest value?
Feixiong Li wrote: > I am newbie for sql, I have a problem when using max() > function, I need get null when there are null in the value > list, or return the largest value as usual, who can do > this? > i.e. max([1,2,3,4,5]) => 5 > max([1,2,3,4,5,null]) => null You can cheat a bit: | tim=# CREATE TABLE MaxTest (i INT); | CREATE TABLE | tim=# INSERT INTO MaxTest (i) VALUES (1), (2), (3), (4), (5), (NULL); | INSERT 0 6 | tim=# SELECT Maxi FROM (SELECT i AS Maxi FROM MaxTest ORDER BY i DESC NULLS FIRST LIMIT 1) AS SubSelect; | maxi | | (null) | (1 Zeile) | tim=# DELETE FROM MaxTest WHERE i IS NULL; | DELETE 1 | tim=# SELECT Maxi FROM (SELECT i AS Maxi FROM MaxTest ORDER BY i DESC NULLS FIRST LIMIT 1) AS SubSelect; | maxi | -- | 5 | (1 Zeile) | tim=# You can also use FIRST_VALUE() (or LAST_VALUE()) if that's more to your liking. Be careful though with empty sets: | tim=# DELETE FROM MaxTest; | DELETE 5 | tim=# SELECT Maxi FROM (SELECT i AS Maxi FROM MaxTest ORDER BY i DESC NULLS FIRST LIMIT 1) AS SubSelect; | maxi | -- | (0 Zeilen) | tim=# Tim -- 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] regexp_replace and search/replace values stored in table
Leif Biberg Kristensen wrote: > [...] > So far, so good. But when I try to do the actual expansion, I'm stumped. > pgslekt=> select regexp_replace((select source_text from sources where > source_id=23091), (select quote_literal(short_link) from short_links where > link_type = 'sk'), (select quote_literal(long_link) from short_links where > link_type = 'sk'), 'g'); > regexp_replace > > [sk=25658|67|side 66a]. Vabakken under Klyve vestre i Solum 07.07.1784: > [p=6947|Isach Jonsen]. > (1 row) > What am I missing? For starters, omit the call to quote_literal (). Tim -- 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] import ignoring duplicates
Mark Fenbers wrote: > I am using psql's \copy command to add records to a database > from a file. The file has over 100,000 lines. > Occasionally, there is a duplicate, and the import ceases > and an internal rollback is performed. In other words, no > data is imported even if the first error occurs near the end > of the file. > I am looking for an option/switch to tell psql (or the \copy > command) to skip over any duplicate key constraint > viloations and continue to load any data that doesn't > violate a duplicate key constraint. Is there such an > option? No. You can either disable the constraint temporarily, im- port the data, fix any duplicates and re-enable the con- straint, or you can load the data in a temporary table and then transfer only the valid data. With only 10 records I would opt for the latter. Tim -- 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
Kenneth Marshall wrote: > It works using 'now' and I assume that since curtime is > of type DATE that the assignment casts the return automatically > to type DATE. Thank you for the ideas. > [...] What's wrong with Pavel's correct and to-the-point answer? Tim -- 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
Brian Modra wrote: >>> It works using 'now' and I assume that since curtime is >>> of type DATE that the assignment casts the return automatically >>> to type DATE. Thank you for the ideas. >>> [...] >> What's wrong with Pavel's correct and to-the-point answer? > No need actually to cast... just use current_date without the quotes. > Its not a string. Where did Pavel suggest to cast or use a string? Tim -- 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] safely exchanging primary keys?
Louis-David Mitterrand wrote:
> I have this function which swaps primary keys for cabin_types (so that
> id_cabin_type ordering reflects natural data ordering):
> CREATE OR REPLACE FUNCTION swap_cabin_types(id1 integer, id2 integer)
> RETURNS integer
> AS $$
> declare
> tmp integer;
> begin
> tmp := nextval('cabin_type_id_cabin_type_seq');
> update cabin_type set id_cabin_type=tmp where id_cabin_type=id1;
> update cabin_type set id_cabin_type=id1 where id_cabin_type=id2;
> update cabin_type set id_cabin_type=id2 where id_cabin_type=tmp;
> return tmp;
> end;
> $$
> LANGUAGE plpgsql;
> 'id_cabin_type' is a foreign key for two other tables, 'cabin_category'
> and 'alert_cabin_type', which have an "on update cascade" clause.
> When I run that function it seems the foreign keys are not properly
> updated and the data ends up in a mess.
> Did I forget something?
What does "are not properly updated" mean? Anyhow, why don't
you use something simple like (untested):
| UPDATE cabin_type
| SET id_cabin_type =
| CASE
| WHEN id_cabin_type = id1 THEN
| id2
| ELSE
| id1
| END
| WHERE id_cabin_type IN (id1, id2);
Tim
--
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] safely exchanging primary keys?
Louis-David Mitterrand wrote: >> > you use something simple like (untested): >> > >> > | UPDATE cabin_type >> > | SET id_cabin_type = >> > | CASE >> > | WHEN id_cabin_type = id1 THEN >> > | id2 >> > | ELSE >> > | id1 >> > | END >> > | WHERE id_cabin_type IN (id1, id2); >> Nice, thanks. > Ah, but this won't work as the UNIQUE PK constraint is in force. Oh, yes, you're right, I didn't have that premise in mind. Tim -- 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] inner join and limit
Michele Petrazzo - Unipex wrote: > I have two table that are so represented: > t1: > id int primary key > ... other > t2: > id int primary key > t1id int fk(t1.id) > somedate date > ... other > data t1: > 1 | abcde > 2 | fghi > data t2: > 1 | 1 | 2010-05-23 > 2 | 1 | 2010-05-24 > 3 | 1 | 2010-05-25 > 4 | 2 | 2010-05-22 > 5 | 2 | 2010-05-26 > I'm trying to create a query where the data replied are: > join t1 with t2 and return only the LIMIT 1 (or N) of the > t2, with date order (of t2). > Data should be: > t1.id | t2.id | t2,somedate > 1 | 3 | 2010-05-25 > 2 | 5 | 2010-05-26 > As said, I'm trying, but without success... > Can be done for you? In addition to Luigi's and Rolando's responses, there are of course the always glorious "DISTINCT ON" for the "LIMIT 1" case: | SELECT DISTINCT ON (t1.id) t1.id, t2.id, t2.somedate FROM t1 JOIN t2 ON t1.id = t2.t1id ORDER BY t1.id, somedate DESC; and window functions for the generic one: | SELECT t1_id, t2_id, t2_somedate FROM | (SELECT t1.id AS t1_id, | t2.id AS t2_id, | t2.somedate AS t2_somedate, | ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t2.somedate DESC) AS rn | FROM t1 JOIN t2 ON t1.id = t2.t1id) AS SubQuery | WHERE rn <= 2; Tim -- 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.
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}" (with the
headaches of "A{1000,2000}" left as an exercise to the read-
er :-)).
Tim
--
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] what does this do
John wrote:
> I have a piece of python code that excutes a SQL statement:
> apitempCur.execute("select * from jfcs_balancedue('%s') f(enrolleeid varchar,
> course_cost decimal, paid_amt decimal)" % (enrollIds,));
> The "enrollids" is a list of primary keys and the "jfcs_balancedue" is a user
> defined function. What I don't understand is the "f(enrolleeid
> varchar, ...)" I have no idea what it's for? Would some kind soul educate
> me.
You can omit the "AS" from "table_name AS alias
(column_alias, ...)", but AFAIK PostgreSQL doesn't support
specifying a data type for each column. Which DBMS is this
code used for?
Tim
--
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] what does this do
Richard Broersma wrote:
>>> I have a piece of python code that excutes a SQL statement:
>>> apitempCur.execute("select * from jfcs_balancedue('%s') f(enrolleeid
>>> varchar,
>>> course_cost decimal, paid_amt decimal)" % (enrollIds,));
>>> The "enrollids" is a list of primary keys and the "jfcs_balancedue" is a
>>> user
>>> defined function. What I don't understand is the "f(enrolleeid
>>> varchar, ...)" I have no idea what it's for? Would some kind soul educate
>>> me.
>> You can omit the "AS" from "table_name AS alias
>> (column_alias, ...)", but AFAIK PostgreSQL doesn't support
>> specifying a data type for each column. Which DBMS is this
>> code used for?
> Well, it doesn't support data-types in the alias declaration for all
> set returning relations with the exception of a set returning function
> (i.e. store procedure). The from clause has a give-away that this is
> a set returning function: "jfcs_balancedue('%s')" since it has a
> parameter.
> Notice the function name section taken from the from clause:
> http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FROM
Another lesson learned :-). But it applies strictly to *re-
cord* returning functions, doesn't it? Because I had tested
generate_series() prior to my reply:
| tim=# SELECT ID FROM generate_series(1, 2) AS G(ID);
| id
|
| 1
| 2
| (2 Zeilen)
| tim=# SELECT ID FROM generate_series(1, 2) AS G(ID INT);
| ERROR: a column definition list is only allowed for functions returning
"record"
| ZEILE 1: SELECT ID FROM generate_series(1, 2) AS G(ID INT);
| ^
| tim=#
but didn't follow the (now obvious) clue ...
Tim
--
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] error on line 1 trying to execute a script using psql
Steven Dahlin wrote:
> I am attempting to generate a temporary function to execute some dml with
> the following script:
> create or replace function setuplicense() returns integer as $$
> declare
> hwcustid integer := 0;
> retvalinteger := 0;
> begin
> insert into license.customer
> ( customer_id ) values ( hwcustid );
> commit;
> return retval;
> end;
> $$
> LANGUAGE plpgsql;
> select setuplicense();
> When running this with psql I get the following:
> Error: syntax error at or near "create"
> Does anyone have a suggestion as to what is causing this?
Your editor prepends the file with a byte-order mark ("BOM")
that PostgreSQL chokes on (bug #5398). This will be fixed in
9.0 (cf.
http://developer.postgresql.org/pgdocs/postgres/release-9-0.html#AEN99331>);
until then you either have to configure your editor not to
save the BOM or chop off the first three bytes yourself
(with tail, sed, Perl & Co.).
Tim
--
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] enforcing constraints across multiple tables
Andrew Geery wrote: > [...] > My questions are: > (A) Is there a way to check (2) above using a constraint and not a trigger? > (B) Is there an easier way to solve this problem? Does the complicated > nature of the solution make the design poor? > (C) Should I not worry about this constraint at the DB level and just > enforce it at the application level? > [...] I don't know about (A) and (B), but (C) is the solution of choice in most complex cases. It's usually *much* easier to enforce than with constraints and triggers (you have to do it at application level anyway as well as you probably don't want to pass PostgreSQL's errors directly to the user) and your mind doesn't become twisted too much. I would schedule regular tests on the data set though to ensure that you no- tice problems early on. Tim -- 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 select text field as interger
Jerrel Kemper wrote: > My table consist off the following fields > CREATE TABLE test > ( > id bigserial NOT NULL, > name character varying(100), >CONSTRAINT logs_pkey PRIMARY KEY (log_id) > ) > with value : > IDName > 1 001 > 201 > 3 1 > 411 > Select name from test where name = '1' ; > results in : > Name > 1 > If the 'Name' field was define as a serial the result set ought to be > Name > 001 > 01 > 1 > Question? > How can I have the same result set as above > Name > 001 > 01 > 1 > with the name field defined as character varying. Ah! Your excentric formatting and the reference to "serial" had me wondering if the indentation had any meaning. You can select the rows where the "Name" field is numerically equal to 1 by casting it to an integer and then comparing it to 1, i. e.: | SELECT Name FROM Test WHERE Name::INT = 1; Tim -- 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 find events within a timespan to each other?
(anonymous) wrote: > is there a way to find events in a log that happen within a > certain timespan to each other? > Log is like this > event_idinteger not null default 0 primary key > event_type_idinteger not null default > user_idinteger not null default 0 > event_ts timestamp(0) > I need every event of a type that happened more often than > one time within 5 minutes of another one of the same user. > 173 1 ... 12:00 > 182 > 193 1 ... 13:03 > 203 2 ... 13:03 > 213 1 ... 13:04 > 222. > 233 1 ... 13:05 > 242 1 ... 13:06 > E.g. the checked event_typ_id may be 3 then the result > should be line 19, 21, 23 You can use window functions and check whether the preceding or following timestamp is within range: | tim=# SELECT event_id, event_type_id, user_id, event_ts | tim-# FROM (SELECT event_id, | tim(#event_type_id, | tim(#user_id, | tim(#event_ts, | tim(#LAG(event_ts) OVER (PARTITION BY user_id, event_type_id ORDER BY event_ts ASC) AS PrecedingTimestamp, | tim(#LAG(event_ts) OVER (PARTITION BY user_id, event_type_id ORDER BY event_ts DESC) AS FollowingTimestamp | tim(# FROM TestEvents) AS SubQuery | tim-# WHERE (PrecedingTimestamp IS NOT NULL AND event_ts - PrecedingTimestamp <= '5 minutes') OR | tim-# (FollowingTimestamp IS NOT NULL AND FollowingTimestamp - event_ts <= '5 minutes'); | event_id | event_type_id | user_id | event_ts | --+---+-+- |23 | 3 | 1 | 2010-01-01 13:05:00 |21 | 3 | 1 | 2010-01-01 13:04:00 |19 | 3 | 1 | 2010-01-01 13:03:00 | (3 Zeilen) | tim=# Tim -- 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] grouping subsets
Richard Huxton wrote: >>> What I want to get is the values grouped by "subset", where a subset is a >>> set of rows with identical column until the colum changes. >>> Is there a way to get >>> | 2 | B | >>> | 4 | C | >>> | 4 | B | >>> | 3 | D | >>> by SQL only? >> I think, the problem is that there are 2 identical groups. I think, you >> can write a pl/pgsql-proc, selecting all ordered by the date-field and >> walking through the result to do the grouping, checking if the 2nd >> column is different from the previous. >> With plain SQL it's maybe possible too, but i don't know how ... > It should be do-able in 8.4 onwards, look into windowing > functions. In particular the lag() function: > SELECT > mycode, > mydate, > lag(mycode) OVER (ORDER BY mydate) AS prev_code > FROM > mytable > ORDER BY mydate; > It should be possible to use that as a subquery with an > outer query that compares mycode=prev_code to get a run > length. Hmmm. Can the outer query be done without using "WITH RECURSIVE"? Tim -- 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] grouping subsets
Rainer Stengele wrote: > yes, the date is always incremented - but anyway the date > column is not really the point! Actually the first tow > columns are relevant. I want them gouped together as > indicated, adding up column 1 in the blocks with identical > second column, but not adding up over all the rows. > [...] If the date column wasn't relevant, how would you group the first two columns? Tim -- 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] Aggregates (last/first) not behaving
Wes Devauld wrote: > I believe I lost the flavour of what I'm doing when I constructed this > example. I'm not interested in the timepoint as much as the value that is > attached to it. I need to be able to find the last chronological record for > a given day. > I can get the value for which I am looking in two steps: > select max(t) as t into table last_of_day from test group by extract(day > from t); > select last_of_day.t, test.v from last_of_day, test where last_of_day.t = > test.t; > I was fairly happy when first() and last() were discovered, as these two > steps could be merged, until the table grows too large and the query planner > decides to sort the results before they are aggregated. > I was searching for a way to keep using last() and keeping the extraction to > a single step, although the more I fight with it, the less I think that it > is worth it. If you have any further suggestions, I would appreciate > hearing them. > [...] Perhaps you could /concisely/ rephrase your problem. Finding the first/last value per group with/without window functions is a common problem, and there are lots of solutions to it. But few people will wade through lots of text to find out what's bothering you. For example, you can query the "last" values per day along the lines of (untested): | SELECT EXTRACT(day FROM t), v | FROM test | WHERE t IN (SELECT MAX(t) FROM test | GROUP BY EXTRACT(day FROM t)); Obviously, this doesn't "keep using last()", so I don't know whether it's good or bad for you. Tim -- 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] Duplicates Processing
Gary Chambers wrote: > I've been provided a CSV file of parts that contains duplicates of > properties (e.g. resistors have a wattage, tolerance, and temperature > coefficient property) of those parts that differ by a manufacturer > part number. What I'd like to do is to process this file and, upon > encountering one of the duplicates, take that part with its new part > number and move it to a part substitutes table. It seems like it > should be pretty simple, but I can't seem to generate a query or a > function to accomplish it. I'd greatly appreciate any insight or > assistance with solving this problem. Thank you very much in advance. You can - for example - create a query with a call to ROW_NUMBER() and then process the matching rows (untested): | INSERT INTO substitutes ([...]) | SELECT [...] FROM | (SELECT *, | ROW_NUMBER() OVER (PARTITION BY wattage, tolerance, temperature | ORDER BY part_number) AS RN | FROM parts) AS SubQuery | WHERE RN > 1; | DELETE FROM parts | WHERE primary_key IN | (SELECT primary_key FROM | (SELECT *, | ROW_NUMBER() OVER (PARTITION BY wattage, tolerance, temperature |ORDER BY part_number) AS RN | FROM parts) AS SubQuery |WHERE RN > 1); Tim -- 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 remove a set of characters in text-columns ?
(anonymous) wrote:
> how can I remove a set of characters in text-columns ?
> Say I'd like to remove { } ( ) ' " , ; . : !
> Of course I can chain replace ( replace ( replace ( replace
> ( ... , '' ) and replace the chars one by one against
> an empty string ''.
> There might be a more elegant way.
> Is there ?
Besides the regexp_replace() solution mentioned by Charlie
and Steve, you can also use TRANSLATE():
| tim=# SELECT TRANSLATE('a{b''c"d!f', '{}()''",;.:!', '');
| translate
| ---
| abcdf
| (1 Zeile)
| tim=#
Tim
--
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 with regexp-query
Johann Spies wrote:
> I am struggling a bit to do the following type of update in a table.
> I want the content of a field updated like this:
> Original:
> '0894396e-16bf-4e63-aa52-97fe7031eec9||50a6b47c-f69c-414d-bcb6-14bbe403de5f|||'
> After update:
> '|0894396e-16bf-4e63-aa52-97fe7031eec9|50a6b47c-f69c-414d-bcb6-14bbe403de5f|'
> in other words: change all multiple adjacent occurences of '|' to only 1.
> I have tried the following query but it fails:
> select id, regexp_replace(category, (E'\|{2,}'), E'\|') as category from
> akb_articles limit 100
> This ends with 'ERROR: invalid regular expression: quantifier operand
> invalid'.
> I would apreciate some help with this one please.
You need to double the backslashes (e. g. "E'\\|{2,}'");
otherwise the parser will "eat" the first backslash and pass
just "|{2,}" as the second argument to regexp_replace().
Tim
--
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.
(anonymous) wrote: > [...] > I intend pass the number of the month(say 1 for January , 2 for February > etc>) as the parameter to this method and return a number that corresponds > to the index of the month in the new order , say I pass 8 for August , I > return 11 , in order to get the index of August in he financial year > calendar somewhat like this > [...] You don't need any function for that, just use "ORDER BY Month < 4, Month". Tim -- 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] need magic to shuffle some numbers
(anonymous) wrote: > there is a table that has among others a integer primary key > "id" and another integer column "prio" as well as an integer > "group_id". > I'd like to invert the values of the prio-column for one of the groups. > The prio numbers start with 3 and there are 1159 different > prios in this group. > At least every value appeares only once. :) > Is there an elegant way to switch the prio values around so > that every record with the first prio gehts the last and > vice versa? > Then the records with the second smallest prio get the > second-to-last biggest value and v.v. If you just want to reverse the priorities: | UPDATE TestTable | SET prio = (SELECT MIN(prio) FROM TestTable WHERE group_id = 'testgroup') + | (SELECT MAX(prio) FROM TestTable WHERE group_id = 'testgroup') - | prio | WHERE group_id = 'testgroup'; Tim -- 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] Add one column to another
(anonymous) wrote: > I have to deal with a table which contains: > first_name > surname > email1 > email2 > ... and I would like to create a view which combines both email columns thus: > first_name > surname > email > It looks simple but I can't think of an obvious query. Try: | SELECT first_name, surname, email1 AS email FROM testtable WHERE email1 IS NOT NULL UNION ALL | SELECT first_name, surname, email2 AS email FROM testtable WHERE email2 IS NOT NULL; Tim -- 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] Window function sort order help
Dianna Harter wrote: > Having trouble getting a window function to sort correctly. > Given this data > consumer_id | move_date | history_timestamp > -++ >12345| 2008-01-05 | 2007-12-11 06:02:26.842171 >12345| 2008-02-29 | 2008-02-05 07:22:38.04067 >12345| 2008-02-29 | 2008-07-11 09:03:42.44044 >23456| 2009-01-01 | 2008-11-12 07:33:32.656658 <-- >23456| 2009-01-28 | 2008-11-14 01:57:40.264335 >23456| 2009-01-01 | 2008-12-04 17:14:20.27 <-- >23456| 2009-01-01 | 2008-12-31 00:33:37.204968 >23456| 2009-01-01 | 2011-06-08 04:16:41.646521 >34567| 2010-05-07 | 2010-06-08 05:14:43.842172 > I'm trying to get the timestamp when the consumer last changed their > move_date. (Notice consumer_id 23456 set their move_date to 2009-01-01 then > changed and then changed it back. In the end, I want the timestamp from when > they changed it to 2009-01-01 the second time.) > My thought was to do an intermediary step to find the timestamp for each time > it switched. From there I can grab the max(timestamp) for each consumer. > [...] > Any suggestions to get the order by to occur first then the partition by or > maybe there another approach that I could use? If I understand the question correctly, try: | SELECT DISTINCT ON (consumer_id) consumer_id, move_date, history_timestamp |FROM (SELECT consumer_id, move_date, history_timestamp, | LAG(move_date) OVER (PARTITION BY consumer_id ORDER BY consumer_id, history_timestamp) AS previous_move_date | FROM consumer_hist) AS SubQuery |WHERE move_date IS DISTINCT FROM previous_move_date |ORDER BY consumer_id, history_timestamp DESC; Tim -- 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] Passing function parameters to regexp_replace
Leif Biberg Kristensen wrote:
>> UPDATE sources SET source_text = regexp_replace(source_text,
>> E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like
>> '%n="%$2%">%';
> Sorry, I pasted a literal replacement, and substituted the parameters by hand.
> The expression should of course be
> UPDATE sources SET source_text = regexp_replace(source_text,
> E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like
> '%n="%$1%">%'
Try:
> UPDATE sources SET source_text = regexp_replace(source_text,
> CONCAT(E'n="(.*?)', $1, E'(.*?)"'), CONCAT(E'n="\\1', $2, '\\2"', 'g') where
> source_text like
> CONCAT('%n="%', $1, '%">%')
If $1 and $2 (can) include meta characters, you have to es-
cape them properly.
Please consider that regexp_replace() uses POSIX Regular
Expressions while LIKE uses a different syntax. If possible,
I would replace the LIKE expression with its "~" equivalent
so chances of confusion are minimized.
Tim
--
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] Passing function parameters to regexp_replace
Leif Biberg Kristensen wrote:
> On Saturday 17. September 2011 19.07.03 Tim Landscheidt wrote:
>> Leif Biberg Kristensen wrote:
>> > UPDATE sources SET source_text = regexp_replace(source_text,
>> > E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like
>> > '%n="%$1%">%'
>> Try:
>> > UPDATE sources SET source_text = regexp_replace(source_text,
>> > CONCAT(E'n="(.*?)', $1, E'(.*?)"'), CONCAT(E'n="\\1', $2, '\\2"', 'g')
>> > where source_text like CONCAT('%n="%', $1, '%">%')
> The function CONCAT doesn't exist i PostgreSQL.
Eh, yes, of course.
> And I can't get it to work
> with EXECUTE and standard concatenation either:
> [...]
What do you need the EXECUTE for? Just drop it.
Tim
--
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 calculate differences of timestamps?
(anonymous) wrote: > How could I calculate differences of timestamps in a log-table? > Table log ( user_id integer, login boolean, ts timestamp ) > So login = true would be a login-event and login = false a logout. > Is there a way to find the matching login/logout to > calculate the difference? This is similar to the question Dianna asked some time ago: | SELECT user_id, |prev_ts AS login_ts, |ts AS logout_ts |FROM (SELECT user_id, | LAG(login) OVER (PARTITION BY user_id ORDER BY ts) AS prev_login, | LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) AS prev_ts, | login, | ts FROM log) AS SubQuery |WHERE prev_login AND NOT login; > Or is there a better table "design" to do this? That depends on your requirements and your application de- sign. The query above requires a full table scan which may kill performance in some circumstances. Of course, any design has to deal with the possibility of an event not having been logged, multiple logins, etc. The query above just forms pairs based on temporal proximity. Tim -- 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] postgres sql help
Harald Fuchs wrote:
>> hi, i am fairly new in postgresql, so if anyone can help me would be great
>> if i simply do:
>> select ver_no
>> from version
>> order by ver_no
>> the result will be something like this:
>> .1.3.1
>> .1.3.2.5.
>> .1.4.1.7.12
>> .1.4.11.14.7.
>> .1.4.3.109.1.
>> .1.4.8.66.
>> so as you can see first 3 lines are ok, but how to make 1.4.3.109 come
>> before 1.4.11 because the third level "3" is smaller than "11".
> The query
> SELECT ver_no
> FROM version
> ORDER BY string_to_array(ver_no, '.', '')::int[]
> should do what you want.
Really neat! :-) For pre-9.1, you have to "ORDER BY
string_to_array(TRIM('.' FROM ver_no), '.')::int[];",
though.
Tim
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Getting a list of a table's attributes that are sortable sorted by uniqueness
Hi, I frequently use pg_dump to dump databases and compare them with diff. To get rid of most "false positives", I'd like to patch pg_dump to sort the table so that its dumped order isn't changed more than necessary by insertions & Co. So I'm looking for a query that will return a list of a table's attributes that are sortable (e. g. no XML fields) and sorted by "uniqueness", i. e. first attributes repre- senting the primary key, then other unique keys, then the rest. Before I dive into the depths of PostgreSQL's system cata- logues, has anyone already solved this problem? TIA, Tim -- 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
Maurício Cruz wrote: > 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... > I will have to upload the file into the server and then use > import ? is there any other way ? Create a large object and then write to it? What driver are you using? Tim -- 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] Fwd: i want small information regarding postgres
Alvaro Herrera wrote: >> would someone with the appropriate authority please unsubscribe this >> person's email address from this list so we don't all get a bounce message >> after every email we send to the list? Thanks. > Just did it. In the future, please email sysadm...@postgresql.org with > mailing list complaints, as I don't read this list (or indeed many > others) I had reported this to pgsql-sql-ow...@postgresql.org. Where do that end up? Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Token separation
Hi, I just tried to input a hexadecimal number in PostgreSQL (8.4) and was rather surprised by the result: | tim=# SELECT 0x13; | x13 | - |0 | (1 Zeile) | tim=# SELECT 0abc; | abc | - |0 | (1 Zeile) | tim=# The documentation says: | A token can be a key word, an identifier, a quoted identifi- | er, a literal (or constant), or a special character symbol. | Tokens are normally separated by whitespace (space, tab, | newline), but need not be if there is no ambiguity (which is | generally only the case if a special character is adjacent | to some other token type). Is this behaviour really conforming to the standard? Even stranger is what MySQL (5.1.59) makes out of it: | mysql> SELECT 0x40; | +--+ | | 0x40 | | +--+ | | @| | +--+ | 1 row in set (0.00 sec) | mysql> SELECT 0abc; | ERROR 1054 (42S22): Unknown column '0abc' in 'field list' | mysql> Tim -- 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] Token separation
Tom Lane wrote: >> [ "0x13" is lexed as "0" then "x13" ] >> Is this behaviour really conforming to the standard? > Well, it's pretty much the universal behavior of flex-based lexers, > anyway. A token ends when the next character can no longer sensibly > be added to it. I know, but - off the top of my head - in most other lan- guages "0abc" will then give a syntax error. > Possibly the documentation should be tweaked to mention the > number-followed-by-identifier case. Especially if you consider such cases: | tim=# SELECT 1D1; SELECT 1E1; SELECT 1F1; | d1 | | 1 | (1 Zeile) | ?column? | -- |10 | (1 Zeile) | f1 | | 1 | (1 Zeile) | tim=# I don't think it's common to hit this, but the documentation surely could use a caveat. I will write something up and submit it to -docs. Thanks, Tim -- 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 a list of a table's attributes that are sortable sorted by uniqueness
I wrote: > I frequently use pg_dump to dump databases and compare them > with diff. To get rid of most "false positives", I'd like > to patch pg_dump to sort the table so that its dumped order > isn't changed more than necessary by insertions & Co. > So I'm looking for a query that will return a list of a > table's attributes that are sortable (e. g. no XML fields) > and sorted by "uniqueness", i. e. first attributes repre- > senting the primary key, then other unique keys, then the > rest. > Before I dive into the depths of PostgreSQL's system cata- > logues, has anyone already solved this problem? Progress report: The query: | SELECT attname, |attnum, |keyrank, |columnrankinkey | FROM pg_attribute | LEFT JOIN | (SELECT DISTINCT ON (tableid, |columnnr) indrelid as tableid, | indkey [subscript] AS columnnr, | CASE | WHEN indisprimary THEN 0 | WHEN indisunique THEN 1 | ELSE 2 | END as keyrank, | subscript as columnrankinkey |FROM | (SELECT indrelid, | indkey, | generate_subscripts(indkey, 1) as subscript, | indisprimary, | indisunique | FROM pg_index | ORDER BY indrelid, |indkey, |indisprimary DESC, indisunique DESC) AS s |ORDER BY tableid, columnnr, CASE |WHEN indisprimary THEN 0 |WHEN indisunique THEN 1 |ELSE 2 |END, columnrankinkey) AS s2 ON attrelid = tableid | AND attnum = columnnr | WHERE attrelid = 'tablename'::regclass | AND NOT attisdropped | AND attnum > 0 | ORDER BY keyrank, | columnrankinkey, | attnum; does almost what I want except: - Attributes that can't be sorted (XML) aren't skipped, and - "UNIQUE(A, B)" and "UNIQUE(C, D)" would give "A, C, B, D" (untested) so the "rank" of a non-primary key has yet to be included. Stay tuned. Tim (looking forward to "UNNEST ... WITH ORDINALITY") -- 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] time interval math
"Edward W. Rouse" wrote:
> Hehe, thanks, I played around and ended up with this:
> round(SUM(extract('epoch' from (time_out - time_in
> I will have to do the division outside of the query, but that's really a
> minor issue.
You can always use subqueries.
> Knowing the total in seconds was the big roadblock. And
> converting back is easier (a / 3600 + ":" + a / 60 + ":" + a % 60)
> [...]
PostgreSQL has also:
| tim=# SELECT 3661::TEXT::INTERVAL;
| interval
| --
| 01:01:01
| (1 Zeile)
| tim=#
Tim
--
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 split up phone numbers?
(anonymous) wrote: > is there a way to split up phone numbers? > I know that's a tricky topic and it depends on the national > phone number format. > I'm especially interested in a solution for Germany, Swizerland and Austria. > I've got everything in a phone number column that makes hardly sense like: > +49432156780 > 0049 4321 5678 0 > 04321/5678-0 > and so on... > Those 3 samples are actually the same number in different notations. > Aim would be to get a normalized number split up in 4 seperate columns > nr_nation > nr_city > nr_main > nr_individual > so I end up with > 49 4321 5678 0 for central > 49 4321 5678 42 for Mr. Smith > Is this doable? > It would be a start to at least split off nr_nation and nr_city. I seem to faintly remember that there are some anomalies in the German area codes, i. e. "+49 xy" may be city A, with "+49 xyz" being city B. Obviously, you will also not be able to differentiate between normal and extension numbers if they aren't properly marked up in the input. Tim -- 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] Can I read the data without commit
John Fabiani wrote: > Yes I understand - but I want to know is it possible? Can I read the data I > just inserted without a commit. > [...] Are you talking about a web application? Then no, you'll have to code that yourself. Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
