[SQL] Selecting values from comma separated string
Hi all, I want to write a function that takes as param a comma separated values string and perform a select matching these values. Here is the string '1,3,7,8' And I wan to perform a : SELECT * FROM my_table WHERE id IN (1, 3, 7, 8); Does anyone have a clue ? Thanks Nacef
Re: [SQL] Selecting values from comma separated string
Here I come again to ask how can I pass an array of values to a pgsql
function when I call this function from a delphi program for example.
Nacef
On Wed, Aug 26, 2009 at 3:05 PM, Tom Lane wrote:
> "A. Kretschmer" writes:
> > In response to Nacef LABIDI :
> >> I want to write a function that takes as param a comma separated values
> string
> >> and perform a select matching these values.
>
> > Use EXECUTE sql_string,
>
> Safer to use string_to_array, for instance
>
>... WHERE id = ANY(string_to_array('1,3,7,8', ',')::int[]) ...
>
> Of course this just begs the question of why the OP doesn't use an
> array in the first place.
>
>regards, tom lane
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Nacef LABIDI
[email protected]
[SQL] Get the last inserted id
Hi all, I was wondering if postgres can return the last ID inserted for a table which the primary key is autoincremented. I need to retrieve this ID in my code to continue processing on that inserted row. Thanks to all
Re: [SQL] Get the last inserted id
Nice idea but how can I use this in a frontend that I am developing using Delphi and ADO objects ? On Fri, Apr 11, 2008 at 7:41 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Fri, Apr 11, 2008 at 11:23 AM, Nacef LABIDI <[EMAIL PROTECTED]> wrote: > > Hi all, > > > > I was wondering if postgres can return the last ID inserted for a table > > which the primary key is autoincremented. I need to retrieve this ID in > my > > code to continue processing on that inserted row. > > smarlowe=# create table test (a serial primary key, b text); > NOTICE: CREATE TABLE will create implicit sequence "test_a_seq" for > serial column "test.a" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "test_pkey" for table "test" > CREATE TABLE > smarlowe=# insert into test (a,b) values (DEFAULT,'test') returning a; > a > --- > 1 > (1 row) > > INSERT 0 1 > > Note that there are other ways of doing it, but I really like the > returning clause. >
[SQL] After updating dataset the record goes to the end of the dataset
Hi all, I am using Postgres in a Delphi application through ODBC. I am having an issue with updating records. When I create a dataset to get the records in a table then after I update one of these records and then refresh the dataset, the record goes to the end of the dataset. This is disappointing when editing records on a DBGrid, where users find their updated records jump to the end of the grid. Even after restarting the application, the updated record keeps showing at the end of the grid. I have tried the same thing with SQL Server and it works normally, so I thought it was a postgres behvior. Has anyone an idea about what could be the cause of such a behavior. Thanks to all in advance. Nacef
Re: [SQL] After updating dataset the record goes to the end of the dataset
Yes I don't issue any sort statement, and I indeed want the data to be show as it is stored in the database. But after updating a row (I don't update the ID, just some fields), it keeps its same place on the DB but jumps to the end of the dataset and by the way to the end of the DBGrid. Nacef On Tue, Apr 22, 2008 at 12:06 PM, Roland Voegtli <[EMAIL PROTECTED]> wrote: > Has anyone an idea about what could be the cause of such a behavior. > > > > You don't issue any sort statement with the query, obviously. So you get > the records the way they are stored in the database. > > cheers > (R) > >
Re: [SQL] After updating dataset the record goes to the end of the dataset
You are right about the fact that the rows don't keep the order they where created with at the start. I have verfied this. I will explain more my case : I am writing an application where here is some resources to plan events on. I want to provide the user with the ability to customize the order in which resources are displayed on the screen. So there is a mapping between the rows positions in the dataset and they display positions. After updating one of these resources the row jumps to the end of the dataset so does the resource on the display. And the order gets scrambled. I cannot set this order in the databe since it is customizable for each user. When I have tested this with SQLServer it works well, since the rows doesn't change position on the DB. I hope that you understand my issue and I will provide any explanations if someting isn't clear enough. Thanks to all Nacef On Tue, Apr 22, 2008 at 12:15 PM, hubert depesz lubaczewski < [EMAIL PROTECTED]> wrote: > On Tue, Apr 22, 2008 at 12:10:41PM +0200, Nacef LABIDI wrote: > > Yes I don't issue any sort statement, and I indeed want the data to be > show > > as it is stored in the database. But after updating a row (I don't > update > > the ID, just some fields), it keeps its same place on the DB but jumps > to > > the end of the dataset and by the way to the end of the DBGrid. > > why do you think it stays in the same place in db? > > besides - without "order by" you cannot depend on the order of rows. > basically i treat them as in "random" order (which is not true, but > helps me remember to never count on the "default" ordering. > > depesz > > -- > quicksil1er: "postgres is excellent, but like any DB it requires a > highly paid DBA. here's my CV!" :) > http://www.depesz.com/ - blog dla ciebie (i moje CV) >
Re: [SQL] After updating dataset the record goes to the end of thedataset
Thank you all for your useful comments. I have tried to order my records as you said and got it work as I wanted. Thank you again On Tue, Apr 22, 2008 at 2:56 PM, Bart Degryse <[EMAIL PROTECTED]> wrote: > Probably what you mean is that you want the order in which the records > were > originally inserted into the database. In that case add a serial to your > table and > fetch the records like > SELECT * FROM tbl WHERE 1=1 ORDER BY [myserialfield] > It's not foolproof but will in many cases come close to what you seem to > be trying > to achieve by not using 'order by'. > > Nacef LABIDI wrote: > > Yes I don't issue any sort statement, and I indeed want the data to be > show > > as it is stored in the database. >
[SQL] First day of month, last day of month
Hi all, I want to select data from a table according to a date column in my table. I want to select all the rows which have date in the current month. So is there a way to get from the actual date the first day and the last day of the month or is there a better method to retrieve all the rows with dates in the current month. Thanks to all
Re: [SQL] First day of month, last day of month
Actually I want to select all rows whith dates between first day of the
month 00:00:00 and last date of the month 23:59:59
On Thu, Apr 24, 2008 at 4:15 PM, Colin Wetherbee <[EMAIL PROTECTED]>
wrote:
> Frank Bax wrote:
>
> > Frank Bax wrote:
> >
> > > Nacef LABIDI wrote:
> > >
> > > > is there a better method to retrieve all the rows with dates in the
> > > > current month.
> > > >
> > >
> > > select * from mytable where extract(month from mydate) = extract(month
> > > from now()) and extract(year from mydate) = extract(year from now());
> > >
> >
> > Sorry; I was not thinking clearly - date_trunc is better for this:
> >
> > select * from mytable where date_trunc('month',mydate) =
> > date_trunc('month',now());
> >
>
> I have some code that uses extract() for this sort of thing. Would you
> mind explaining how date_trunc() is better for this?
>
> Most of my extract() results end up in drop-down boxes in HTML.
>
> Thanks.
>
> Colin
>
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Re: [SQL] First day of month, last day of month
It would be rather :
test=*# select date_trunc('month', current_date), date_trunc('month',
current_date)+'1month':
>
> :interval-'1sec'::interval;
"2008-04-01 00:00:00+02";"2008-04-30 23:59:59+02"
On Thu, Apr 24, 2008 at 3:15 PM, A. Kretschmer <
[EMAIL PROTECTED]> wrote:
> am Thu, dem 24.04.2008, um 14:59:47 +0200 mailte Nacef LABIDI folgendes:
> > Hi all,
> >
> > I want to select data from a table according to a date column in my
> table. I
> > want to select all the rows which have date in the current month. So is
> there a
> > way to get from the actual date the first day and the last day of the
> month or
>
> test=*# select date_trunc('month', current_date), date_trunc('month',
> current_date)+'1month'::interval-'1day'::interval;
> date_trunc |?column?
> +
> 2008-04-01 00:00:00+02 | 2008-04-30 00:00:00+02
>
>
>
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
[SQL] currval of sequence "my_seq" is not yet defined in this session
Hi all, I am writing some code to get the current value of a sequence using the currval function to use this value in a following insert on another table. But I am having this message "currval of sequence "my_seq" is not yet defined in this session". Can someone explain me what is the problem with that and by the way explain me the definition of a session in postgres. I have tried to avoid he problem using a stocked function and a trigger but I really want to avoid using triggers for debugging issues. Thanks to all Nacef
[SQL] Substract queries
Hi all, I was wondering how can I substract result between select queries. I mean I want to issue a query that does this : (select * from mytable where condition1) - (select * from mytable where condition2) Thanks to all Nacef
Re: [SQL] Substract queries
Thanks to all the EXEPT keyword is what I was looking for On Thu, May 22, 2008 at 5:36 PM, Niklas Johansson <[EMAIL PROTECTED]> wrote: > > On 22 maj 2008, at 17.15, Nacef LABIDI wrote: > >> I was wondering how can I substract result between select queries. I mean >> I want to issue a query that does this : >> (select * from mytable where condition1) - (select * from mytable where >> condition2) >> > > If you (as implied above) query the same table in both cases, just do: > > SELECT * FROM mytable WHERE condition1 AND NOT condition2 > > Otherwise, use EXCEPT: > > SELECT * FROM mytable1 WHERE condition1 > EXCEPT > SELECT * FROM mytable2 WHERE condition2 > > in which case both queries must return the same type of rows. > > > > > Sincerely, > > Niklas Johansson > > > >
[SQL] I invite you to join my Ziki Network !
Bonjour, connaissez-vous Ziki.com ? Ziki est le lieu où chacun peut se promouvoir librement. Promouvoir votre identité, vos contenus, vos compétences, vos services... Etre référencé en 1ère position sur le moteur de recherche Google. Découvrir et contacter gratuitement des personnes qui vous ressemblent. Check out my Ziki at : http://www.ziki.com/en/nacef-labidi?invitation_code=326caad1bf53e404456b406f3da5867d Then use this link to accept the invitation and register for your FREE Ziki account: http://www.ziki.com/en/signup?invitation_code=326caad1bf53e404456b406f3da5867d (or copy and paste the link into your browser's address bar) It's free, fun and easy! Create your webpage now and join my network. See you there !
[SQL] Profiling postgresql queries
Hi all, I was wondering if there is any GUI or command line based tool running on windows that could help me to profile PostgreSQL queries performance. It should be able to do live profiling since I want to profile the accesses that my application is doing on the Postgres database. Thanks to all Nacef
[SQL] Select function with set return type
Hi all, I am writing some functions with retrun type as a SETOF of a datatype that I have defined. How can I test them with a select statement. Doing select my_function(); return set valued function called in context that cannot accept a set Thanks to all Nacef
[SQL] Concat field result in select query
Hi all, I was wondering if there is a way to concatenate the results of a field in a select to return it as a single string. Example : my_field -- 1 2 3 select concat_something(my_field) from my_table group by something; the result expected would be someting like that : 1/2/3 (with a separator it would be really nice) I hope that I am not asking for too much ;) Thanks to all
Re: [SQL] Concat field result in select query
any explanations ? On Fri, Aug 22, 2008 at 6:07 PM, Julien Cigar <[EMAIL PROTECTED]> wrote: > array_accum ? > > On Fri, 2008-08-22 at 15:40 +0200, Nacef LABIDI wrote: > > Hi all, > > > > I was wondering if there is a way to concatenate the results of a > > field in a select to return it as a single string. > > > > Example : > > > > my_field > > -- > > 1 > > 2 > > 3 > > > > select concat_something(my_field) from my_table group by something; > > the result expected would be someting like that : 1/2/3 (with a > > separator it would be really nice) > > > > I hope that I am not asking for too much ;) > > > > Thanks to all > > > -- > Julien Cigar > Belgian Biodiversity Platform > http://www.biodiversity.be > Université Libre de Bruxelles (ULB) > Campus de la Plaine CP 257 > Bâtiment NO, Bureau 4 N4 115C (Niveau 4) > Boulevard du Triomphe, entrée ULB 2 > B-1050 Bruxelles > Mail: [EMAIL PROTECTED] > @biobel: http://biobel.biodiversity.be/person/show/471 > Tel : 02 650 57 52 > >
Re: [SQL] Concat field result in select query
Thank you, this is all what I need On Fri, Aug 22, 2008 at 4:10 PM, Guillaume Lelarge <[EMAIL PROTECTED]>wrote: > Nacef LABIDI a écrit : > > Hi all, > > > > I was wondering if there is a way to concatenate the results of a field > > in a select to return it as a single string. > > > > Example : > > > > my_field > > -- > > 1 > > 2 > > 3 > > > > select concat_something(my_field) from my_table group by something; > > the result expected would be someting like that : 1/2/3 (with a > > separator it would be really nice) > > > > I hope that I am not asking for too much ;) > > > > Something like: > > SELECT array_to_string(array(select myfield from mytable), ',')); > > For example: > > sherkin=# select * from t1; > c1 | c2 > + > 1 | > 2 | 3 > 2 | 4 > 2 | 5 > (4 lignes) > > sherkin=# SELECT array_to_string(array(select c1 from t1), ','); > array_to_string > - > 1,2,2,2 > (1 ligne) > > > -- > Guillaume. > http://www.postgresqlfr.org > http://dalibo.com >
[SQL] Performing intersection without intersect operator
Hi all, I want to perform an intersection between several select queries but without using the INTERSECT keyword. select userid from orders where productid=1 INTERSECT select userid from orders where productid=2 I want to transform it without the INTERSECT. Thanks to all Nacef
