Re: [despammed] [SQL] converting varchar to integer

values - ie there can be something like 'xssdkjsd', >'230kdd' or even an empty string etc. test=# select * from foo; t | n --+--- bla | bla1 | 2| (3 rows) test=# update foo set n = substring(t , '[0-9]')::int; UPDATE 3 test=# select * from foo;

Re: [SQL] BirthDay SQL Issue

n visit it: http://a-kretschmer.de/tools/birthday.sql Maybe it is useful for you. It need one argument: a integer, and returns a list of people who has birthday between CURRENT_DATE and this+N days. Regards, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a com

Re: [SQL] sql function

base. ,[ log from psql ] | test=>\i table_copy.sql | CREATE FUNCTION | | test=> select table_copy(); | table_copy | | | (1 Zeile) `---- I say it again: use the fine command line tool psql to learn SQL. Regards, Andreas -- Really, I'm not out to destroy Microsof

Re: [SQL] How to speed up the database query?

??explain 09:43 < rtfm_please> For information about explain 09:43 < rtfm_please> see http://techdocs.postgresql.org/oscon2005/robert.treat/OSCON_Explaining_Explain_Public.sxi 09:43 < rtfm_please> or http://www.gtsm.com/oscon2003/toc.html 09:43 < rtfm_please> or http://www.postgresql.

Re: [SQL] serial in output

ft yh > > Basically, I would like to have one column with integers, from 1 onwards, no > matter how many tables I join in, or the data that I get back. Create a sequence and then "select nextval('your_sequence'), * from foo;" HTH, Andreas -- Really,

Re: [SQL] Create Public Schema

dump and change 'public' to 'foobar' (the new schema-name) 3. restore the schema from dump Now you have a exactly copy from 'public' with name 'foobar'. HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintenti

Re: [SQL] Is it possible to redirect an update/insert/delete to a different table?

rom pears ; id | name +-- 1 | b (1 row) test=# update apples set name = 'c' where id = 1; UPDATE 1 test=# select * from pears ; id | name +-- 1 | c (1 row) http://www.postgresql.org/docs/8.1/interactive/rules-update.html HTH, Andreas -- Really, I'm not ou

Re: [SQL] Is it possible to redirect an update/insert/delete to a different table?

#x27; where id = 1; UPDATE 1 test=# select * from pears ; id | name1 | name2 +---+--- 1 | a | c (1 row) test=# update apples set name1='e', name2='e' where id = 1; UPDATE 1 test=# select * from pears ; id | name1 | name2 +---+--- 1 | e | e (1

Re: [SQL] Is it possible to redirect an update/insert/delete to a different table?

Andy Ballingall <[EMAIL PROTECTED]> schrieb: > Hi Andreas, > > The rule you've given only works for an update which changes the name. > > If I do another update which changed the colour instead of the name, that > rule wouldn't do the right thing. Right. &g

Re: [SQL] How to change database owner in PostgreSQL 7.4?

frank church <[EMAIL PROTECTED]> schrieb: > Hi guys, > > What is the command change database owner in PostgreSQL 7.4? ALTER DATABASE foo OWNER TO blob; In general: start psql and type '\h alter database', and, more general, '\h' and '\?' HT

Re: [SQL] MAX, MIN and arrays

1 | {1,2,3} (3 rows) > I have a table with a column of two-dimensional arrays. What then? The same. HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europ

Re: [SQL] SQL Statement Help needed

ne numbers in it name > membertelephones. A member can have more than one telephone number (home, > work, cell, pager, fax, etc.). I want to print out the telephone numbers of test=# select * from member; id | name ----+- 1 | andreas 2 | anja (2 rows) test=# select * from m

[SQL] Trigger for Large Object Modification

Hello, I created a table with a OID Column to carry the identifier for a Large Object. Now i want to run a trigger is the data of the large object has changed. To do this I created an ON UPDATE-trigger on the table, but the trigger does get fired if i only change the data of the large object (

Re: [SQL] psql client: technique for applying default values to :variables?

gether... How about using the traditional Unix macro processor "m4" instead of psql's variables? E.g., you could write a myscript.sql.m4 file with m4 variables instead of psql ones. You could then use ifdef() to test for previous definitions, or even define a default()-

Re: [SQL] question with times and intervals

1 | 1 | 32:00:00 1 | 2 | 15:59:59 (2 rows) Okay, now i can write a function similar above which returns the interval and the i can sum() this. Thanks, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect.

[SQL] Change definition of a view

with additional column - recreate all rules Any help is welcomed! Thanks, Andreas Roth ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] alter table

xyz alter column id set default nextval('xyz_seq'), alter column foo set default ''; ALTER TABLE HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "I

Re: [SQL] alter table

Maciej Piekielniak <[EMAIL PROTECTED]> schrieb: > Hello Andreas, > > Wednesday, February 15, 2006, 7:54:28 PM, you wrote: > AK> test=# alter table xyz alter column id set default nextval('xyz_seq'), > alter column foo set default ''; > > PGA

Re: [SQL] newbie question

_view ; a | b | c | ab | ac | bc ---+---+---+++ 2 | 3 | 4 | 6 | 8 | 12 (1 row) test=# update foo set a=3; UPDATE 1 test=# select * from foo_view ; a | b | c | ab | ac | bc ---+---+---+++ 3 | 3 | 4 | 9 | 12 | 12 (1 row) HTH, Andreas -- Really, I'm not out to

Re: [SQL] input from a external text file......!

ur_file.sql" to execute the commands within this file. HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."

Re: [SQL] Merging rows into one result?

ld result in the rows > Yo > Stay put > > I would like a single row result in the format of: > Yo Stay put Yes, of corse, this is possible. You need a own aggregate-function. A similar example for this task can you find here: http://www.zigo.dhs.org/postgresql/#comma_aggregate

Re: [SQL] how to get the size of array?

Emi Lu <[EMAIL PROTECTED]> schrieb: > Hello, > > Is there a way that I can get the size of one array ? Yes: http://www.postgresql.org/docs/8.1/interactive/functions-array.html HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unint

Re: [SQL] regarding join

tables: test=# select * from test1 union select * from test3 order by 2,1; name | id ---+ ab| 1 akhil | 1 b | 2 cd| 2 c | 3 ef| 3 d | 4 gh| 4 e | 5 f | 6 (10 rows) HTH, Andreas -- Really, I'm not out to destroy M

Re: [SQL] on select rule

Sergey Levchenko <[EMAIL PROTECTED]> schrieb: > http://www.postgresql.org/docs/8.1/interactive/sql-createtrigger.htmlSELECT > does not modify any rows so you can not create SELECT triggers.Rules > and views are more appropriate in such cases. On 4/14/06, A. Oh yes, i

Re: [SQL] Looking for some help with cascading updates...

not insert it > Check if '01832' exists in table Zip and if not insert it For such task i write a function (plpgsql). This obtains all parameters, do the checks and insert into the other tables and the table with the fk constraints. HTH, Andreas -- Really, I'm not out to des

Re: [SQL] Looking for some help with cascading updates...

then insert into harz_uf (uf,ab,beschreibung,aktiv) values ($1,$2,$4,'t'); end if; insert into harz_ufpos (uf,pos,anzahl,rest,flaeche) values ($1,$3,$5,$5,$6); return 1; end; $$ language 'plpgsql'; HTH, Andreas -- Really, I'm no

Re: [SQL] Looking for some help with cascading updates...

t; --Mark > > > On 4/18/06, AAnnddrreeaass KKrreettsscchhmmeerr <[EMAIL > PROTECTED]> wrote: > Mark True <[EMAIL PROTECTED]> schrieb: Please, without HTML and fullquote... HTH, Andreas -- Really, I'm not out to destroy Micr

[SQL] Porting application with rules and triggers from PG 7.4.x to 8.1.3

again. I found the following statement in the release notes for PostgreSQL 8.0 in section "Migration to version 8.0" which seem to describe this change: "Nondeferred AFTER triggers are now fired immediately after completion of the triggering query, rather than upon finishing the curre

Re: [SQL] Porting application with rules and triggers from PG 7.4.x

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi! Sorry for the late response, but I'm quite busy and I wanted to test this before replying... Bruce Momjian schrieb: > Tom Lane wrote: >> Andreas Haumer <[EMAIL PROTECTED]> writes: >>> How can I get the functionality

Re: [SQL] Multi-column index not used, new flipped column index is

h > columns are given in a WHERE clause. Yeah! The Bitmap Index Scan is a great thing. With some selects i have a performance-boost from 5 to 10, compared with 8.0. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect.

Re: [SQL] Multi-column index not used, new flipped column index is

[EMAIL PROTECTED] <[EMAIL PROTECTED]> schrieb: > Hi Andreas and Markus, > > Bitmap indices sound like a good reason to go from 8.0 to 8.1. Is 8.2 > around the corner, by any chance? IIRC in autumn/winter. If it is finish ;-) > > I searched PG docs to see if I need to

Re: [SQL] i have a problem of privilages

ve the question. This is hard to read. (sorry for my bad english, i know, it's hard to read too) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I wo

Re: [SQL] Table inheritance problem

Gianvito Pio schrieb: Hello, I have 3 tables: persons, operators and persons_position. This is a semplified examples of their structures: CREATE TABLE persons (id varchar NOT NULL, CONSTRAINT "PK_Persons" PRIMARY KEY(id)); CREATE TABLE operators (id varchar NOT NULL, CONSTRAINT "PK_Oper

Re: [SQL] Show CAS, USD first; the left ordered by currency name

st=*# select * from currency order by case when code='USD' then 0 when code = 'CAD' then 1 end, code; code | description --+-- USD | US Dollar CAD | Canadian Dollar ADF | Andorran Franc ANG | NL Antillian Guilder AON | Angolan New Kwanza AUD |

Re: [SQL] on error resume next

Jasmin Dizdarevic wrote: hi, can i use savepoints to realize something like "on error resume next"? i've got the following situation: begin; 1. create view user001.accounts as select * from base.accounts; 2. grant select on user001.accounts to loginuser001; commit; begin; 3. create view

Re: [SQL] short-cutting if sum()>constant

msi77 wrote: > Does PG support CTE? Since 8.4 yes. > You can try it. Sorry, but i don't know how a CTE can help in this case, can you explain that? Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintenti

Re: [SQL] selecting rows tagged with "a" but not "b"

Hi Darrell, SELECT DISTINCT articleID FROM tags WHERE tag = "a" EXCEPT SELECT DISTINCT articleID FROM tags WHERE tag = "b"; Regards, Andreas -Ursprüngliche Nachricht- Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im Auftrag von 8q5tm

Re: [SQL] selecting rows tagged with "a" but not "b"

t; how do I do this? select a.* from article left join tags t on a.articleID=t.articleID where b.tag = 'a'; Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If

Re: [SQL] Triggers on system tables

Gianvito Pio wrote: > Hi all, > is there a way (also strange) to define a trigger on a system table (for > example on pg_class)? No. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (

Re: [SQL] Drop all constraints

foo drop constraint ...). I think, you should start with http://www.postgresql.org/docs/8.4/interactive/catalog-pg-constraint.html Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) &q

[SQL] select points of polygons

;(')::point as point FROM ( SELECT '((0.001329116037,0.007391900417),(0.001371765621,0.1608393682),(0.1502391498,0.00733744679),(0.001500387404,-0.1468751078),(-0.1472653422,0.007425591447))'::polygon as p) as poly è (0.00132

[SQL] ALTER TYPE my_enum AS ENUM ADD ('label10')

an enumerator later on? Can I edit pg_enum? Thanks already, Andreas Gaab ___ SCANLAB AG Dr. Andreas Simon Gaab Entwicklung * R & D Siemensstr. 2a * 82178 Puchheim * Germany Tel. +49 (89) 800 746-513 * Fax +49 (89) 800

Re: [SQL] string functions and operators

Why not using text-function substring: SELECT split_part(123.456::text,'.',1)::integer; SELECT split_part(123.456::text,'.',2)::integer; Regards, Andreas -Ursprüngliche Nachricht- Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im Auf

Re: [SQL] window function to sort times series data?

nd lead group by price_id; price_id | price_old | price_new | date_old | date_new --+---+---++ 1 |11 |12 | 2010-03-19 | 2010-03-26 2 |21 |22 | 2010-03-19 | 2010-03-26 (2 Zeilen) Andreas -- Really, I'

Re: [SQL] INSERT INTO...RETURNING vs SELECT

o wait for 9.1, writeable CTE. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Eu

[SQL] WAL-files restore and nextval('PK')

27;PK') ; commands were executed again, whereas the data rows were already restored and thus leading to higher sequence numbers on the newly restored server? Best regards for any comments! Andreas ___ SCANLAB AG D

Re: [SQL] is there a tutorial on window functions?

://delicious.com/akretschmer/cte Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, German

Re: [SQL] Duplicate rows

1 2 2 3 4 (7 Zeilen) Zeit: 0,145 ms test=*# delete from dups where (ctid, i) not in (select max(ctid), i from dups group by i); DELETE 3 Zeit: 0,378 ms test=*# select * from dups ; i --- 1 2 3 4 (4 Zeilen) Andreas -- Really, I'm not out to destroy Microsoft. That will just b

Re: [SQL] is there a distinct function for comma lists ?

Hi, For the problem 1 perhaps something like select distinct unnest(ARRAY[ 1, 2, 3, 5, 7, 11, 3, 6, 13, 13, 3, 11 ]) Regards, Andreas -Ursprüngliche Nachricht- Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im Auftrag von Andreas Gesendet: Dienstag, 7

Re: [SQL] HowTo divide streetname from house-nr ?

The only chance I see is to combine the information about the localization with the address pattern. regards Andreas On 09/23/2010 09:12 AM, negora wrote: I guess that it's impossible to look for a solution which works on every existing case, specially if you're handling addr

Re: [SQL] identifying duplicates in table with redundancies

t hugely important as these queries are not time-critical. This is only a helper table, which I use to analyze the date prior to populating the destination tables with the data. Regards, Tarlika I guess explain analyze shows up a seq scan. try avoiding to use distinct. use group by instead. regar

Re: [SQL] Overlapping Ranges- Query Alternative

Select Groups, CASE WHEN ColumnA < 20 then 0 ELSE generate_series(20, (floor(ColumnA / 10.0) * 10)::integer ,10) END MinRange from ranges; Best, Andreas Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im Auftrag von Ozer, Pam Gesendet: Donnerstag, 11. November

Re: [SQL] obtaining difference between minimum value and next in size

; product_id | price | difference +---+ 2 | 10.01 | 2 | 10.05 | 0.04 3 | 9.45 | 3 | 11.42 | 1.97 (4 Zeilen) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side e

Re: [SQL] force view column varchar(32) to varchar(128)

Emi Lu wrote: > Hello, > > Is there a way to force the view column change from varhcar(32) to > varchar(128)? No, you have to recreate the view ... Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintent

Re: [SQL] concatenate question

the given name and argument type(s). You might need to add explicit type casts. test=!# rollback; ROLLBACK Zeit: 0,124 ms test=# select 1::text || now()::text; ?column? -------- 12010-12-11 14:42:12.571931+01 (1 Zeile) Andreas -- Really, I'm not out to

Re: [SQL] Table name as a variable in SELECT query

g. Read more: http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I wa

[SQL] aggregation of setof

*** ERROR: set-valued function called in context that cannot accept a set SQL Status:0A000 " Can I convert a 'setof text[]' to a 'text[]'? Alternatively I could use a sub-select, but I am curious if th

Re: [SQL] aggregation of setof

o:viktor.bojo...@gmail.com] Gesendet: Samstag, 29. Januar 2011 09:28 An: Andreas Gaab Betreff: Re: [SQL] aggregation of setof i have never used that type but maybe you can try this; -create function which returns text[], and takse setof text as argument (if possible) -reach every text[] in set of

Re: [SQL] aggregation of setof

while only receiving one row. Regards, Andreas -Ursprüngliche Nachricht- Von: Pavel Stehule [mailto:pavel.steh...@gmail.com] Gesendet: Montag, 31. Januar 2011 10:24 An: Andreas Gaab Cc: pgsql-sql@postgresql.org Betreff: Re: [SQL] aggregation of setof Hello use a array constructor instead

Re: [SQL] UNIQUE on everything except primary key

Did you check check constraints? http://www.postgresql.org/docs/8.4/static/ddl-constraints.html Best Andreas -Ursprüngliche Nachricht- Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im Auftrag von gvim Gesendet: Donnerstag, 3. Februar 2011 16:44 An: pgsql

[SQL] replace_matches does not return {null}

no result at all. Is this behavior expected? How can I work around? I am running postgres 8.4 Andreas _______ SCANLAB AG Dr. Andreas Simon Gaab Entwicklung * R & D Siemensstr. 2a * 82178 Puchheim * Germany Tel. +49

[SQL] unnesting of array of different size explodes memory

settings to prevent this such as "work_mem"? Regards, Andreas ___ SCANLAB AG Dr. Andreas Simon Gaab Entwicklung * R & D Siemensstr. 2a * 82178 Puchheim * Germany Tel. +49 (89) 800 746-513 * Fax +49 (89) 800 746-

Re: [SQL] How to realize ROW_NUMBER() in 8.3?

om t1 left join t2... ... tn > where ... ... > > Just for people using 8.3, this is mimic row_number. Not really... you have to reset the sequence after the select, and i'm not sure about ordering the result. Andreas -- Really, I'm not out to destroy Microsof

Re: [SQL] unnest in SELECT

a select statement "select unnest(vector1) as v1, > unnest(vector2) as v2 from some_table" and vector1 has a length of 3 and > vector2 has a length of 4, the result set will have 12 rows with the > data of vector1 repeating 4 times and vector2 repeating 3 times. Ye

Re: [SQL] Subselects not allowed?

-+-+-+---+--- seq_foo | 99 | 99 |1 | 9223372036854775807 | 1 | 1 | 1 | f | f (1 row) I'm using 9.1Beta, but it works since 9.0, see: http://www.depesz.com/index.php/2009/11/01/waiti

Re: [SQL] Subselects not allowed?

Leif Biberg Kristensen wrote: > On Saturday 11. June 2011 22.09.09 Leif Biberg Kristensen wrote: > > I've written a blog post which I hope may be helpful to others in a similar > > situation: > > > > <http://solumslekt.org/blog/?p=321> > > > >

Re: [SQL] Clever way to check overlapping time intervals ?

Andreas wrote: > Hi, > is there a clever way to check overlapping time intervals ? > An option named n should be taken from date y to y. > The same name is ok for another interval. > > e.g. table : mytab ( d1 date, d2 date, n text, v text ) > > There should be a constrai

Re: [SQL] postgres sql help

-- .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. (6 rows) Time: 0,262 ms test=*# select * from o order by split_part(t,'.',2)::int, split_part(t,'.',3)::int, split_part(t,'.',4)::int; t --- .1.3.1 .1.3.2.5. .1.4.1

Re: [SQL] Group by on Date

00:00:00 | 2011-10-31 00:00:00 | 42 2011-10-31 00:00:00 | 2011-11-03 00:00:00 | 51 2011-11-03 00:00:00 | 2011-11-06 00:00:00 | 39 (7 rows) it fails for more than 1 year, but i hope, you can modify my sql for your own. Andreas -- Really, I'm not out to destroy Microsoft. That will jus

Re: [SQL] Problem with DROP ROLE

You can't do that in THAT way, but you can use dynamic SQL:   test=# select * from drop_role ;    t  foobar (1 row) test=*# do $$ declare r text; begin for r in select t from drop_role loop execute 'drop role ' || quote_ident(r) || ';'; end loop; end; $$language plpgsql;   DO      "Bric

Re: [SQL] Number timestamped rows

vent_c |2 > 2 |0:4 |event_b |3 > 3 |0:1 |event_a |1 > untested: select user_id, timestamp, event, row_number() over (partition by user_id order by timestamp) as "order" from your_table > How would I do this with an UPDATE statement (e.g.) in pgsql? I think, y

Re: [SQL] conditional FROM

e as the > query will return unneccessary duplicate > row...the same goes for port2node being Null or 0... I think, you can't do that, you have to build your query and execute that string. You should use a function to do that. Andreas -- Really, I'm not out to destroy

Re: [SQL] conditional FROM

rdid > and card.card2node = node.nodeid > and node.name like 'nodename%' > ; > > Though I just can't order the rows anymore by inet(arp.ip) anymore... > Any hints on my ordering isn't anylonger possible? select * from (insert the query above

Re: [SQL] Column "..." does not exist (view + union)

o something like: select name from (select name, ctype from ... union select name, ctype from ...) foo order by ctype Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I w

Re: [SQL] Problem with tables and columns names

testTable" you have to use this name everytime. Within psql you have to quote, for instance select * from "testTable" will work, select from testTable doesn't. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect.

Re: [SQL] i want small information regarding postgres

vinodh chowdary wrote: > Hi sir, > > i am using postgres as database for my project. > i want to convert postgres data into excel sheet. > is there any way to do it? > please reply me. This should be possible with ODBC. (But i'm not familiar with it, don'

Re: [SQL] ignore unique violation OR check row exists

t.i is null; INSERT 0 4 Time: 0,464 ms test=*# select * from dest; i --- 1 2 3 4 5 6 7 8 9 (9 rows) I think, that's the fastest way. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Lin

Re: [SQL] sql query problem

NULL, > `user_email` varchar(255) NOT NULL, > `user_gender` varchar(255) NOT NULL, > `refrence` varchar(255) NOT NULL, > `join_date` varchar(255) NOT NULL, > `status` tinyint(1) NOT NULL, > `banned` tinyint(1) NOT NULL, > PRIMARY KEY (`user_id`) > ) That

Re: [SQL] foreign key is it a real key

ain does not appear to > use it as an index (instead of a seq scan it uses a hash join). But the > table > all ready has a FK key contraint on the column. Yes, for large tables you have to create a own index on your fk-column. Andreas -- Really, I'm not out to

Re: [SQL] crosstab help

integer as buckvalue from xchromasun._chromasun_totals(now()::date)') as ct(item_number text, week_of_1 date, week_of_2 date, week_of_3 date) Regards, Andreas -Ursprüngliche Nachricht- Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im Auftrag von John Fabiani

Re: [SQL] crosstab help

As far as I know you must define the numbers (and types) of columns and column headers individually for each query or define some custom function... Andreas -Ursprüngliche Nachricht- Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im Auftrag von John Fabiani

Re: [SQL] how to concatenate in PostgreSQL

Rehan Saleem wrote: > hi , > how can we concatinate these lines and execute sql command > >set sql = 'select user,username, firstname ' > set sql += ' lastname, cardno from table1 where userid=' + 5 sql = sql || ' bla fasel'; || is

Re: [SQL] getting the OS user name

John Fabiani wrote: > Hi, > In my app it is possible to login as one name and use a different name to > login to postgres. > > Is it possible to get the actual OS login name using plsql. Since you can login to an remote database server: no. Andreas -- Really, I'

Re: [SQL] Simple way to get missing number

code ] | test=# select * from emi_lu ; | i | --- | 1 | 2 | 3 | 5 | 6 | 8 | 9 | (7 rows) | | Time: 0,246 ms | test=*# select * from generate_Series(1,10) s left join emi_lu on | (s=emi_lu.i) where i is null; | s | i | +--- | 4 | | 7 | | 10 | | (3 rows) ` this? Andreas -- R

Re: [SQL] Simple way to get missing number

Raj Mathur (राज माथुर) wrote: > > Nice one, but curious about how would this perform if the numbers in > question extended into 7 figures or more? TIAS (Try It And See) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintention

[SQL] Running mean filtering using Window Functions?

not every line with its following two lines. Any suggestions? Best regards Andreas ___ SCANLAB AG Dr. Andreas Simon Gaab Entwicklung * R & D Siemensstr. 2a * 82178 Puchheim * Germany Tel. +49 (89) 800 746-513 * Fax +49

Re: [SQL] How change col name during query to use it in where clause

Marcel Ruff hat am 4. Mai 2012 um 12:25 geschrieben: > Hi, > > is an alias name not usable in the where clause? Exactly. Andreas -- 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] master/detail

te | price | lead | ?column? +-++---+--+-- 4 | 1 | 2012-05-15 |22 | 12 | 10 3 | 1 | 2012-05-10 |12 | 15 | -3 2 | 1 | 2012-05-05 |15 | 10 |5 1 | 1 | 2012-05-01 |10 | | (4 rows) Rega

Re: [SQL] How to limit access only to certain records?

Andreas wrote: > Hi, > > is there a way to limit access for some users only to certain records? > > e.g. there is a customer table and there are account-managers. > Could I limit account-manager #1 so that he only can access customers > only acording to a flag? Yea,

Re: [SQL] Prevent double entries ... no simple unique index

Andreas wrote: > Hi, > > I've got a log-table that records events regarding other objects. > Those events have a state that shows the progress of further work on > this event. > They can be open, accepted or rejected. > > I don't want to be able to insert add

Re: [SQL] Prevent double entries ... no simple unique index

Andreas Kretschmer wrote: > Andreas wrote: > > > Hi, > > > > I've got a log-table that records events regarding other objects. > > Those events have a state that shows the progress of further work on > > this event. > > They can be open, accept

Re: [SQL] Prevent double entries ... no simple unique index

27; - value - but no more. > > Hi, > > A partial index would do the same, but requires less space: > > create unique index on log(state) WHERE state IN (0,1); Right! ;-) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional si

Re: [SQL] can this be done with a check expression?

27;); INSERT 0 1 Time: 0,313 ms test=*# insert into foo values ('name1', '[5,15)'); ERROR: conflicting key value violates exclusion constraint "foo_name_id_range_excl" DETAIL: Key (name, id_range)=(name1, [5,15)) conflicts with existing key (name, id_range)=(name1, [1,9)).

Re: [SQL] [GENERAL] Indexing question

e f2>, instead there should be two entries in the index and ). > > we have a few use cases for the above, perhaps we need to alter the > schema somehow to accommodate the above, 2 separate indexes? One on f1 and one on f2? Andreas -- Really, I'm not out to destroy Microsoft

Re: [SQL] matching a timestamp field

-09-22'; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. Time: 0,156 ms test=!# rollback; ROLLBACK Time: 0,079 ms test=# select now()::date = '2012-09-22'::date; ?column? -- t

Re: [SQL] Reuse temporary calculation results in an SQL update query

ion 1' > WHERE foo; The Duration - field is superfluous ... As far as i know there is no way to re-use the result. Regards, Andreas -- 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] Reuse temporary calculation results in an SQL update query

lation_2 as calc2 > from foo > ) > update foo >set startTime = my_calc.calc1, >stopTime = my_calc.calc2, >duration = my_calc.calc2 - calc1 > where foo.pk = my_calc.pk; > > http://www.postgresql.org/docs/current/static/queries-with.html#QUERIES-WITH-MODIFYING Yeah, with a WITH - CTE, cool ;-) Andreas -- 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] checking the gaps in intervals

nking about a solution with DATERANGE (PostgreSQL 9.2)... Are start and end including or excluding? Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile pe

Re: [SQL] checking the gaps in intervals

Andreas Kretschmer wrote: > Anton Gavazuk wrote: > > > Hi dear community, > > > > Have probably quite simple task but cannot find the solution, > > > > Imagine the table A with 2 columns start and end, data type is date > > > > start

Fwd: Re: [SQL] sql basic question

sorry, only a private replay and not to the list -- Ursprüngliche Nachricht -- Von: Andreas Kretschmer An: Antonio Parrotta Datum: 28. Dezember 2012 um 15:19 Betreff: Re: [SQL] sql basic question Hi, your question was: "What I want to achieve is a result table with min an

<    1   2   3   4   5   >