Re: [SQL] Looking for a "show create table " equivalent
On Tue, Jul 12, 2011 at 2:33 AM, B.Rathmann wrote: > Hello, > > I've been trying to find out how to find out which sql was run to create > a certain table. As I need this in a program which may access the > database remotely, using pg_dump --schema-only or psql is not an option > (the system my program runs on may not even have those tools installed). Look at this for logging: http://www.depesz.com/index.php/2011/05/06/understanding-postgresql-conf-log/ -wes -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Looking for a "show create table " equivalent
On Tue, Jul 12, 2011 at 2:33 AM, B.Rathmann wrote: > Hello, > > I've been trying to find out how to find out which sql was run to create > a certain table. As I need this in a program which may access the > database remotely, using pg_dump --schema-only or psql is not an option Maybe turning on some more verbose logging you can see what command is doing that. I've poked around in the pg docs, but don't see it right off. -wes -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Looking for a "show create table " equivalent
On Tue, 2011-07-12 at 10:33 +0200, B.Rathmann wrote: > [...] > I've been trying to find out how to find out which sql was run to create > a certain table. As I need this in a program which may access the > database remotely, using pg_dump --schema-only or psql is not an option > (the system my program runs on may not even have those tools installed). > Looking at the source of pgadmin3 it seems as if the program collects > all info about the given table (columns, indeces etc) and creates the > needed SQL by itself, is there no easier way? > No. pgAdmin does it this way because it has no other way to do it. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- 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] Max column number.
Have you tried changing the block size? http://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F On Wed, Jul 13, 2011 at 11:03 AM, Miguel Angel Conte wrote: > I have the metadata in the same csv. > > On Wed, Jul 13, 2011 at 3:00 PM, Kevin Crain wrote: >> >> How are you determining the data types for these columns? >> >> On Wed, Jul 13, 2011 at 8:45 AM, Miguel Angel Conte >> wrote: >> > Hi, >> > Thanks for your interest. This app load scv files which change every day >> > (sometimes the columns too). The sizes of these files are in avg 15MB. >> > So, >> > We load something like 100MB each day. We tried to find a better >> > solution >> > but we couldn't, becouse one of the our requirement is not to use a lot >> > of >> > space. Also, the app is used to consult these information, and for our >> > particular type of select's queries, we get the best performance if the >> > information is all into a same row. >> > >> > On Wed, Jul 13, 2011 at 3:28 AM, Kevin Crain >> > wrote: >> >> >> >> I still can't imagine why you'd ever need this...could you explain >> >> what this does? I'm just curious now >> >> >> >> On Tue, Jul 12, 2011 at 10:55 PM, Kevin Crain >> >> wrote: >> >> > This is an unfortunate situation, you shouldn't be required to do >> >> > this, the people generating your requirements need to be more >> >> > informed. I would make damn sure you notify the stakeholders in this >> >> > project that the data model is screwed and needs a redesign. I agree >> >> > that you should split this table and do a join if you have no option >> >> > of redesigning this. >> >> > >> >> > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte >> >> > >> >> > wrote: >> >> >> Yes, sure. I mean, I can't change the whole process which creates >> >> >> columns >> >> >> dynamically. >> >> >> >> >> >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen >> >> >> wrote: >> >> >>> >> >> >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: >> >> >>> > Unfortunately It's an inherited data model and I can't make any >> >> >>> > change >> >> >>> > for >> >> >>> > now... >> >> >>> >> >> >>> but by adding columns you *are* making changes to it... >> >> >>> >> >> >>> Reinoud >> >> >>> -- >> >> >>> __ >> >> >>> "Nothing is as subjective as reality" >> >> >>> Reinoud van Leeuwen reinou...@n.leeuwen.net >> >> >>> http://reinoud.van.leeuwen.net kvk 27320762 >> >> >>> __ >> >> >>> >> >> >>> -- >> >> >>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> >> >>> To make changes to your subscription: >> >> >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> >> >> >> >> >> > >> > >> > > > -- 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] Max column number.
Is there any room for improvement in the data types? On Wed, Jul 13, 2011 at 11:03 AM, Miguel Angel Conte wrote: > I have the metadata in the same csv. > > On Wed, Jul 13, 2011 at 3:00 PM, Kevin Crain wrote: >> >> How are you determining the data types for these columns? >> >> On Wed, Jul 13, 2011 at 8:45 AM, Miguel Angel Conte >> wrote: >> > Hi, >> > Thanks for your interest. This app load scv files which change every day >> > (sometimes the columns too). The sizes of these files are in avg 15MB. >> > So, >> > We load something like 100MB each day. We tried to find a better >> > solution >> > but we couldn't, becouse one of the our requirement is not to use a lot >> > of >> > space. Also, the app is used to consult these information, and for our >> > particular type of select's queries, we get the best performance if the >> > information is all into a same row. >> > >> > On Wed, Jul 13, 2011 at 3:28 AM, Kevin Crain >> > wrote: >> >> >> >> I still can't imagine why you'd ever need this...could you explain >> >> what this does? I'm just curious now >> >> >> >> On Tue, Jul 12, 2011 at 10:55 PM, Kevin Crain >> >> wrote: >> >> > This is an unfortunate situation, you shouldn't be required to do >> >> > this, the people generating your requirements need to be more >> >> > informed. I would make damn sure you notify the stakeholders in this >> >> > project that the data model is screwed and needs a redesign. I agree >> >> > that you should split this table and do a join if you have no option >> >> > of redesigning this. >> >> > >> >> > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte >> >> > >> >> > wrote: >> >> >> Yes, sure. I mean, I can't change the whole process which creates >> >> >> columns >> >> >> dynamically. >> >> >> >> >> >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen >> >> >> wrote: >> >> >>> >> >> >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: >> >> >>> > Unfortunately It's an inherited data model and I can't make any >> >> >>> > change >> >> >>> > for >> >> >>> > now... >> >> >>> >> >> >>> but by adding columns you *are* making changes to it... >> >> >>> >> >> >>> Reinoud >> >> >>> -- >> >> >>> __ >> >> >>> "Nothing is as subjective as reality" >> >> >>> Reinoud van Leeuwen reinou...@n.leeuwen.net >> >> >>> http://reinoud.van.leeuwen.net kvk 27320762 >> >> >>> __ >> >> >>> >> >> >>> -- >> >> >>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> >> >>> To make changes to your subscription: >> >> >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> >> >> >> >> >> > >> > >> > > > -- 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] Max column number.
I have the metadata in the same csv. On Wed, Jul 13, 2011 at 3:00 PM, Kevin Crain wrote: > How are you determining the data types for these columns? > > On Wed, Jul 13, 2011 at 8:45 AM, Miguel Angel Conte > wrote: > > Hi, > > Thanks for your interest. This app load scv files which change every day > > (sometimes the columns too). The sizes of these files are in avg 15MB. > So, > > We load something like 100MB each day. We tried to find a better solution > > but we couldn't, becouse one of the our requirement is not to use a lot > of > > space. Also, the app is used to consult these information, and for our > > particular type of select's queries, we get the best performance if the > > information is all into a same row. > > > > On Wed, Jul 13, 2011 at 3:28 AM, Kevin Crain > wrote: > >> > >> I still can't imagine why you'd ever need this...could you explain > >> what this does? I'm just curious now > >> > >> On Tue, Jul 12, 2011 at 10:55 PM, Kevin Crain > >> wrote: > >> > This is an unfortunate situation, you shouldn't be required to do > >> > this, the people generating your requirements need to be more > >> > informed. I would make damn sure you notify the stakeholders in this > >> > project that the data model is screwed and needs a redesign. I agree > >> > that you should split this table and do a join if you have no option > >> > of redesigning this. > >> > > >> > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte < > diaf...@gmail.com> > >> > wrote: > >> >> Yes, sure. I mean, I can't change the whole process which creates > >> >> columns > >> >> dynamically. > >> >> > >> >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen > >> >> wrote: > >> >>> > >> >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: > >> >>> > Unfortunately It's an inherited data model and I can't make any > >> >>> > change > >> >>> > for > >> >>> > now... > >> >>> > >> >>> but by adding columns you *are* making changes to it... > >> >>> > >> >>> Reinoud > >> >>> -- > >> >>> __ > >> >>> "Nothing is as subjective as reality" > >> >>> Reinoud van Leeuwenreinou...@n.leeuwen.net > >> >>> http://reinoud.van.leeuwen.net kvk 27320762 > >> >>> __ > >> >>> > >> >>> -- > >> >>> 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] Max column number.
How are you determining the data types for these columns? On Wed, Jul 13, 2011 at 8:45 AM, Miguel Angel Conte wrote: > Hi, > Thanks for your interest. This app load scv files which change every day > (sometimes the columns too). The sizes of these files are in avg 15MB. So, > We load something like 100MB each day. We tried to find a better solution > but we couldn't, becouse one of the our requirement is not to use a lot of > space. Also, the app is used to consult these information, and for our > particular type of select's queries, we get the best performance if the > information is all into a same row. > > On Wed, Jul 13, 2011 at 3:28 AM, Kevin Crain wrote: >> >> I still can't imagine why you'd ever need this...could you explain >> what this does? I'm just curious now >> >> On Tue, Jul 12, 2011 at 10:55 PM, Kevin Crain >> wrote: >> > This is an unfortunate situation, you shouldn't be required to do >> > this, the people generating your requirements need to be more >> > informed. I would make damn sure you notify the stakeholders in this >> > project that the data model is screwed and needs a redesign. I agree >> > that you should split this table and do a join if you have no option >> > of redesigning this. >> > >> > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte >> > wrote: >> >> Yes, sure. I mean, I can't change the whole process which creates >> >> columns >> >> dynamically. >> >> >> >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen >> >> wrote: >> >>> >> >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: >> >>> > Unfortunately It's an inherited data model and I can't make any >> >>> > change >> >>> > for >> >>> > now... >> >>> >> >>> but by adding columns you *are* making changes to it... >> >>> >> >>> Reinoud >> >>> -- >> >>> __ >> >>> "Nothing is as subjective as reality" >> >>> Reinoud van Leeuwen reinou...@n.leeuwen.net >> >>> http://reinoud.van.leeuwen.net kvk 27320762 >> >>> __ >> >>> >> >>> -- >> >>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> >>> To make changes to your subscription: >> >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> >> >> > > > -- 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] Max column number.
I can't drop the table. I have to add as many columns as posible and when I exceed the limit I have to create another table. I've tried normalizing but then the join's cost is too big. I always need to use all columns, so getting a all information into a single row it's the most efficient solution. On Wed, Jul 13, 2011 at 1:51 PM, Scott Marlowe wrote: > On Wed, Jul 13, 2011 at 9:45 AM, Miguel Angel Conte > wrote: > > Hi, > > Thanks for your interest. This app load scv files which change every day > > (sometimes the columns too). The sizes of these files are in avg 15MB. > So, > > We load something like 100MB each day. We tried to find a better solution > > but we couldn't, becouse one of the our requirement is not to use a lot > of > > space. Also, the app is used to consult these information, and for our > > particular type of select's queries, we get the best performance if the > > information is all into a same row. > > This definitely sounds like a job for something like hstore. > > Also, can you drop and recreate the table every so often? That would > certainly help. >
Re: [SQL] Max column number.
On Wed, Jul 13, 2011 at 9:45 AM, Miguel Angel Conte wrote: > Hi, > Thanks for your interest. This app load scv files which change every day > (sometimes the columns too). The sizes of these files are in avg 15MB. So, > We load something like 100MB each day. We tried to find a better solution > but we couldn't, becouse one of the our requirement is not to use a lot of > space. Also, the app is used to consult these information, and for our > particular type of select's queries, we get the best performance if the > information is all into a same row. This definitely sounds like a job for something like hstore. Also, can you drop and recreate the table every so often? That would certainly help. -- 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] using explain output within pgsql
On 11/07/11 08:18, Pavel Stehule wrote: 2011/7/10 Uwe Bartels: Hi Pavel, is it posible to get this running even with dynamic sql? I didn't write that. I'm using execute to run this create table probably yes postgres=# do $$ declare x text; begin execute e'explain(format yaml) select * from data where value = \'a\'' into x; raise notice '%', x; end; $$ language plpgsql; NOTICE: - Plan: Node Type: "Seq Scan" Relation Name: "data" Alias: "data" Startup Cost: 0.00 Total Cost: 23.38 Plan Rows: 5 Plan Width: 46 Filter: "((value)::text = 'a'::text)" DO [...] I find that I understand things better if I rephrase things, so I took Pavel's code and converted it to use variables so I could see more clearly what is happening. I think using variables makes the use of 'execute' more understandable. I hope this version is of value to to others, I have included all the code required to run it as a working example. CREATE TABLE data ( id int, value text ); INSERT INTO data (id, value) VALUES (1, 'a'), (2, 'b'); do $$ declare v_sql_querytext; v_sql_explain text; v_result text; begin v_sql_query :=e'SELECT * FROM data d WHERE value = \'a\''; v_sql_explain :=e'EXPLAIN(FORMAT YAML) ' || v_sql_query; execute v_sql_explain into v_result; raise notice 'v_result: %', v_result; end; $$ language plpgsql; Cheers, Gavin
[SQL] newbie question * compare integer in a "where IN" statement
Title: Firma Correo
Hi everybody,
I'm trying to compare in a sentence like this (using PostGres 8.3) :
select * from myTable where id_integer IN ('1,2,3,4')
I want to get the records which key "id_integer" is 1 or 2 or 3 or
4. the type od my "id", of course, is integer.
I've tried many differents ways but I cannot get the result I want.
I would like to cast the integer parameter I cannot change the part
after "IN" it has to be a "string list".
Do I have to use a function ? I would like not to use it.
How can I compare a key (integer) with a lists of values ?
I'm desperate
THANX IN ADVANCE
--
José Ignacio Méndez Yanes
Área de Operaciones / Area of Operations
Phone: +34 916
011 373 / +34 946 416 066
Mobile: +34 666
431 099
Fax: +34 916 011 372 / +34 944 318 286
Alcalde Ángel Arroyo 10, 1ª
Planta. 28904.
Getafe, Madrid
Kanala Bidea, Edif. 103, 1ª
Izda. Parque Tecnológico. 48170. Zamudio, Bizkaia
www.andago.com
Síguenos en: Twitter
- Facebook
- LinkedIn
- YouTube
Antes de imprimir este mensaje, asegúrese
de que es necesario. Consider the environment before
printing this mail.
AVISO LEGAL
ANDAGO
CONSULTING SL / ANDAGO INGENIERÍA, SL le informa que los
datos facilitados por Ud. y utilizados para el envío de esta
comunicación serán objeto de tratamiento automatizado o no en
nuestros ficheros, con la finalidad de gestionar la agenda de
contactos de nuestra empresa y para el envío de comunicaciones
profesionales por cualquier medio electrónico o no. Vd. podrá en
cualquier momento ejercer el derecho de acceso, rectificación,
cancelación y oposición en los términos establecidos en la Ley
Orgánica 15/1999. El responsable del tratamiento es ANDAGO
CONSULTING SL / ANDAGO INGENIERIA SL, con domicilio en C/
ALCALDE ANGEL ARROYO, 10, 28904, Getafe (Madrid).
El contenido de
esta comunicación, así como el de toda la documentación anexa,
es confidencial y va dirigido únicamente al destinatario del
mismo. En el supuesto de que usted no fuera el destinatario, le
solicitamos que nos lo indique y no comunique su contenido a
terceros, procediendo a su destrucción.
DISCLAIMER
The content of
this communication and any attached information is confidential
and exclusively for the use of the addressee. If you are not the
addressee, we ask you to notify to the sender and do not pass
its content to another person, and please be sure you destroy
it.
[SQL] Looking for a "show create table " equivalent
Hello, I've been trying to find out how to find out which sql was run to create a certain table. As I need this in a program which may access the database remotely, using pg_dump --schema-only or psql is not an option (the system my program runs on may not even have those tools installed). Looking at the source of pgadmin3 it seems as if the program collects all info about the given table (columns, indeces etc) and creates the needed SQL by itself, is there no easier way? E.g. MySQL offers a simple "show create table ". I am using Postgresql 9.0.4 on Gentoo. Thanks for your help (if there is any documentation which I haven't found yet, just point me to it). Sincerely Bettina -- 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] Max column number.
On Wed, Jul 13, 2011 at 12:45:45PM -0300, Miguel Angel Conte wrote: > Hi, > > Thanks for your interest. This app load scv files which change every day > (sometimes the columns too). The sizes of these files are in avg 15MB. So, > We load something like 100MB each day. We tried to find a better solution > but we couldn't, becouse one of the our requirement is not to use a lot of > space. Also, the app is used to consult these information, and for our > particular type of select's queries, we get the best performance if the > information is all into a same row. Still, you could normalize the data in a better way, and present it to users or an application with views or stored procedures... Reinoud -- __ "Nothing is as subjective as reality" Reinoud van Leeuwenreinou...@n.leeuwen.net http://reinoud.van.leeuwen.net kvk 27320762 __ -- 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] Max column number.
Hi, Thanks for your interest. This app load scv files which change every day (sometimes the columns too). The sizes of these files are in avg 15MB. So, We load something like 100MB each day. We tried to find a better solution but we couldn't, becouse one of the our requirement is not to use a lot of space. Also, the app is used to consult these information, and for our particular type of select's queries, we get the best performance if the information is all into a same row. On Wed, Jul 13, 2011 at 3:28 AM, Kevin Crain wrote: > I still can't imagine why you'd ever need this...could you explain > what this does? I'm just curious now > > On Tue, Jul 12, 2011 at 10:55 PM, Kevin Crain > wrote: > > This is an unfortunate situation, you shouldn't be required to do > > this, the people generating your requirements need to be more > > informed. I would make damn sure you notify the stakeholders in this > > project that the data model is screwed and needs a redesign. I agree > > that you should split this table and do a join if you have no option > > of redesigning this. > > > > On Tue, Jul 12, 2011 at 4:29 PM, Miguel Angel Conte > wrote: > >> Yes, sure. I mean, I can't change the whole process which creates > columns > >> dynamically. > >> > >> On Tue, Jul 12, 2011 at 6:12 PM, Reinoud van Leeuwen > >> wrote: > >>> > >>> On Tue, Jul 12, 2011 at 03:08:36PM -0300, Miguel Angel Conte wrote: > >>> > Unfortunately It's an inherited data model and I can't make any > change > >>> > for > >>> > now... > >>> > >>> but by adding columns you *are* making changes to it... > >>> > >>> Reinoud > >>> -- > >>> __ > >>> "Nothing is as subjective as reality" > >>> Reinoud van Leeuwenreinou...@n.leeuwen.net > >>> http://reinoud.van.leeuwen.net kvk 27320762 > >>> __ > >>> > >>> -- > >>> 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] combining strings to make a query
Thank you for the tsvectors info! -wes On Wed, Jul 13, 2011 at 12:11 AM, Kevin Crain wrote: > You can do full-text search in postgres now using ts_vectors. I'd > recommend going that route. Doing like comparisons is not a good idea > if you don't know the first part of the string you are searching > forIt appears to be much faster from my experience to search for > ab% than it is to search for %ab%. > > On Tue, Jul 12, 2011 at 7:51 PM, Wes James wrote: >> I'm using Erlang and postgresql to build a web interface. When I >> create the query string I get something like: >> >> select * from table where field::text ilike '%%' >> >> But when I do that (if someone types in '\' for part of the text >> search), I get a pg log entry to use E'\\' >> >> How would I use E'' with ilike '%%'. ilike E'%\\%' doesn't work. >> >> Thanks, >> >> -wes >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select random lines of a table using a probability distribution
On Wed, Jul 13, 2011 at 03:27:10PM +0200, Jira, Marcel wrote: > Hi! > > Let's consider I have a table like this > > idqualificationgenderageincome > > I'd like to select (for example 100) lines of this table by random, but the > random mechanism has to follow a certain probability distribution. > > I want to use this procedure to construct a test group for another selection. > > Example: > > I filter all lines having the qualification "plumber". > I get 50 different ids consisting of 40 males, 10 females and a certain age > distribution. > > I also get some information concerning the income of the plumbers. > > Now I want to know if the income is more influenced by the gender and age > distribution or by the qualification "plumber". > > Therefore I would like to select a test group (of 50 or more) without any > plumbers. This test group has to follow the same age and gender distribution. > > Then I would be able to compare this groups income statistics with the > plumbers income statistics. > > Is this possible (and doable with reasonable effort) in PostgreSQL? > > Thank you in advance. > > Best regards, > > Marcel Jira > You may want to take a look at pl/R which make the R system available to PostgreSQL as a function language. Regards, Ken -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Select random lines of a table using a probability distribution
Hi! Let's consider I have a table like this idqualificationgenderageincome I'd like to select (for example 100) lines of this table by random, but the random mechanism has to follow a certain probability distribution. I want to use this procedure to construct a test group for another selection. Example: I filter all lines having the qualification "plumber". I get 50 different ids consisting of 40 males, 10 females and a certain age distribution. I also get some information concerning the income of the plumbers. Now I want to know if the income is more influenced by the gender and age distribution or by the qualification "plumber". Therefore I would like to select a test group (of 50 or more) without any plumbers. This test group has to follow the same age and gender distribution. Then I would be able to compare this groups income statistics with the plumbers income statistics. Is this possible (and doable with reasonable effort) in PostgreSQL? Thank you in advance. Best regards, Marcel Jira ? ~~~ * ~~~ ? Mag. Marcel Jira ? Institut für Sozialpolitik, Wirtschaftsuniversität Wien ? +43 1 313 36-5890 ? UZA IV, D 317 ? http://www.wu.ac.at/sozialpolitik/team/wimi/jira ? ~~~ * ~~~
Re: [SQL] newbie question * compare integer in a "where IN" statement
Title: Firma Correo
Hi,
Thank you very much Pavel, that solves my problem
Regards
On 13/07/11 09:50, Pavel Stehule wrote:
Hello
you can try
SELECT .. FROM .. WHERE id = ANY(string_to_array('1,2,3,4,5',','))
other forms are slow
Regards
Pavel Stehule
2011/7/13 Jose Ig Mendez
Hi everybody,
I'm trying to compare in a sentence like this (using
PostGres 8.3) :
select * from myTable where id_integer IN ('1,2,3,4')
I want to get the records which key "id_integer" is 1 or 2
or 3 or 4. the type od my "id", of course, is integer.
I've tried many differents ways but I cannot get the result
I want.
I would like to cast the integer parameter I cannot change
the part after "IN" it has to be a "string list".
Do I have to use a function ? I would like not to use it.
How can I compare a key (integer) with a lists of values ?
I'm desperate
THANX IN ADVANCE
--
José
Ignacio Méndez Yanes
Área
de Operaciones / Area of Operations
Phone: +34 916 011 373
/ +34 946
416 066
Mobile: +34 666 431 099
Fax: +34 916 011 372
/ +34 944
318 286
Alcalde Ángel
Arroyo 10, 1ª Planta. 28904.
Getafe, Madrid
Kanala Bidea,
Edif. 103, 1ª Izda. Parque Tecnológico.
48170. Zamudio, Bizkaia
www.andago.com
Síguenos en: Twitter
- Facebook
- LinkedIn - YouTube
Antes
de imprimir este mensaje, asegúrese de que es
necesario. Consider the environment before
printing this mail.
AVISO
LEGAL
ANDAGO
CONSULTING SL / ANDAGO INGENIERÍA, SL le informa
que los datos facilitados por Ud. y utilizados para el
envío de esta comunicación serán objeto de tratamiento
automatizado o no en nuestros ficheros, con la finalidad
de gestionar la agenda de contactos de nuestra empresa y
para el envío de comunicaciones profesionales por
cualquier medio electrónico o no. Vd. podrá en cualquier
momento ejercer el derecho de acceso, rectificación,
cancelación y oposición en los términos establecidos en
la Ley Orgánica 15/1999. El responsable del tratamiento
es ANDAGO CONSULTING SL / ANDAGO INGENIERIA SL, con
domicilio en C/ ALCALDE ANGEL ARROYO, 10, 28904, Getafe
(Madrid).
El contenido
de esta comunicación, así como el de toda la
documentación anexa, es confidencial y va dirigido
únicamente al destinatario del mismo. En el supuesto de
que usted no fuera el destinatario, le solicitamos que
nos lo indique y no comunique su contenido a terceros,
procediendo a su destrucción.
DISCLAIMER
The content of
this communication and any attached information is
confidential and exclusively for the use of the
addressee. If you are not the addressee, we ask you to
notify to the sender and do not pass its content to
another person, and please be sure you destroy it.
--
José Ignacio Méndez Yanes
Área de Operaciones / Area of Operations
Phone: +34 916
011 373 / +34 946 416 066
Mobile: +34 666
431 099
Fax: +34 916 011 372 / +34 944 318 286
Alcalde Ángel Arroyo 10, 1ª
Planta. 28904.
Getafe, Madrid
Kanala Bidea, Edif. 103, 1ª
Re: [SQL] newbie question * compare integer in a "where IN" statement
Hello
you can try
SELECT .. FROM .. WHERE id = ANY(string_to_array('1,2,3,4,5',','))
other forms are slow
Regards
Pavel Stehule
2011/7/13 Jose Ig Mendez
>
> Hi everybody,
>
> I'm trying to compare in a sentence like this (using PostGres 8.3) :
>
> select * from myTable where id_integer IN ('1,2,3,4')
>
> I want to get the records which key "id_integer" is 1 or 2 or 3 or 4. the
> type od my "id", of course, is integer.
>
> I've tried many differents ways but I cannot get the result I want.
> I would like to cast the integer parameter I cannot change the part after
> "IN" it has to be a "string list".
>
> Do I have to use a function ? I would like not to use it.
>
> How can I compare a key (integer) with a lists of values ?
>
> I'm desperate
>
> THANX IN ADVANCE
>
> --
> --
>
> José Ignacio Méndez Yanes
>
> Área de Operaciones / Area of Operations
>
> Phone: +34 916 011 373 / +34 946 416 066
>
> Mobile: +34 666 431 099
>
> Fax: +34 916 011 372 / +34 944 318 286
> [image: Ándago] Alcalde Ángel Arroyo 10, 1ª Planta. 28904. Getafe,
> Madrid
> Kanala Bidea, Edif. 103, 1ª Izda. Parque Tecnológico. 48170. Zamudio,
> Bizkaia
> www.andago.com
>
> Síguenos en: Twitter -
> Facebook-
> LinkedIn -
> YouTube
> Antes de imprimir este mensaje, asegúrese de que es necesario.
> Consider the environment before printing this mail.
>
> AVISO LEGAL
> --
>
> *ANDAGO CONSULTING SL / ANDAGO INGENIERÍA, SL* le informa que los datos
> facilitados por Ud. y utilizados para el envío de esta comunicación serán
> objeto de tratamiento automatizado o no en nuestros ficheros, con la
> finalidad de gestionar la agenda de contactos de nuestra empresa y para el
> envío de comunicaciones profesionales por cualquier medio electrónico o no.
> Vd. podrá en cualquier momento ejercer el derecho de acceso, rectificación,
> cancelación y oposición en los términos establecidos en la Ley Orgánica
> 15/1999. El responsable del tratamiento es ANDAGO CONSULTING SL / ANDAGO
> INGENIERIA SL, con domicilio en C/ ALCALDE ANGEL ARROYO, 10, 28904, Getafe
> (Madrid).
>
> El contenido de esta comunicación, así como el de toda la documentación
> anexa, es confidencial y va dirigido únicamente al destinatario del mismo.
> En el supuesto de que usted no fuera el destinatario, le solicitamos que nos
> lo indique y no comunique su contenido a terceros, procediendo a su
> destrucción.
>
> DISCLAIMER
> --
>
> The content of this communication and any attached information is
> confidential and exclusively for the use of the addressee. If you are not
> the addressee, we ask you to notify to the sender and do not pass its
> content to another person, and please be sure you destroy it.
>
Re: [SQL] newbie question * compare integer in a "where IN" statement
Jose Ig Mendez, 13.07.2011 09:36:
Hi everybody,
I'm trying to compare in a sentence like this (using PostGres 8.3) :
select * from myTable where id_integer IN ('1,2,3,4')
I want to get the records which key "id_integer" is 1 or 2 or 3 or 4. the type od my
"id", of course, is integer.
I've tried many differents ways but I cannot get the result I want.
I would like to cast the integer parameter I cannot change the part after "IN" it has to
be a "string list".
Do I have to use a function ? I would like not to use it.
How can I compare a key (integer) with a lists of values ?
Just leave out the quotes:
select *
from myTable
where id_integer IN (1,2,3,4)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] newbie question * compare integer in a "where IN" statement
Title: Firma Correo
Hi everybody,
I'm trying to compare in a sentence like this (using PostGres 8.3) :
select * from myTable where id_integer IN ('1,2,3,4')
I want to get the records which key "id_integer" is 1 or 2 or 3 or
4. the type od my "id", of course, is integer.
I've tried many differents ways but I cannot get the result I want.
I would like to cast the integer parameter I cannot change the part
after "IN" it has to be a "string list".
Do I have to use a function ? I would like not to use it.
How can I compare a key (integer) with a lists of values ?
I'm desperate
THANX IN ADVANCE
--
José Ignacio Méndez Yanes
Área de Operaciones / Area of Operations
Phone: +34 916
011 373 / +34 946 416 066
Mobile: +34 666
431 099
Fax: +34 916 011 372 / +34 944 318 286
Alcalde Ángel Arroyo 10, 1ª
Planta. 28904.
Getafe, Madrid
Kanala Bidea, Edif. 103, 1ª
Izda. Parque Tecnológico. 48170. Zamudio, Bizkaia
www.andago.com
Síguenos en: Twitter - Facebook
- LinkedIn
- YouTube
Antes de imprimir este mensaje, asegúrese
de que es necesario. Consider the environment before
printing this mail.
AVISO LEGAL
ANDAGO
CONSULTING SL / ANDAGO INGENIERÍA, SL le informa que los
datos facilitados por Ud. y utilizados para el envío de esta
comunicación serán objeto de tratamiento automatizado o no en
nuestros ficheros, con la finalidad de gestionar la agenda de
contactos de nuestra empresa y para el envío de comunicaciones
profesionales por cualquier medio electrónico o no. Vd. podrá en
cualquier momento ejercer el derecho de acceso, rectificación,
cancelación y oposición en los términos establecidos en la Ley
Orgánica 15/1999. El responsable del tratamiento es ANDAGO
CONSULTING SL / ANDAGO INGENIERIA SL, con domicilio en C/
ALCALDE ANGEL ARROYO, 10, 28904, Getafe (Madrid).
El contenido de
esta comunicación, así como el de toda la documentación anexa,
es confidencial y va dirigido únicamente al destinatario del
mismo. En el supuesto de que usted no fuera el destinatario, le
solicitamos que nos lo indique y no comunique su contenido a
terceros, procediendo a su destrucción.
DISCLAIMER
The content of
this communication and any attached information is confidential
and exclusively for the use of the addressee. If you are not the
addressee, we ask you to notify to the sender and do not pass
its content to another person, and please be sure you destroy
it.
