[SQL] Insert into a date field
Hi list, I am trying to insert a record in a table with a date field but the postgresql reports me the following error: ERROR: column "data_nascimento" is of type date but expression is of type integer SQL state: 42804 Hint: You will need to rewrite or cast the expression. I tryed like this: '1963-09-01' '1963-09-01'::date 1963-09-01 And notthing Thanks in advance. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] Insert into a date field
Ezequias Rodrigues da Rocha wrote: Hi list, I am trying to insert a record in a table with a date field but the postgresql reports me the following error: ERROR: column "data_nascimento" is of type date but expression is of type integer SQL state: 42804 Hint: You will need to rewrite or cast the expression. I tryed like this: '1963-09-01' '1963-09-01'::date 1963-09-01 Show us the whole statement - this doesn't look like the error to me. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Insert into a date field
Just tried the following way, that might help you:
postgres=# create table testing (a date);
CREATE TABLE
postgres=# insert into testing values (to_date('1963-09-01', '-MM-DD'));
INSERT 0 1
postgres=# select * from testing;
a
1963-09-01
(1 row)
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 2/2/07, Richard Huxton wrote:
Ezequias Rodrigues da Rocha wrote:
> Hi list,
>
> I am trying to insert a record in a table with a date field but the
> postgresql reports me the following error:
>
> ERROR: column "data_nascimento" is of type date but expression is of
type
> integer
> SQL state: 42804
> Hint: You will need to rewrite or cast the expression.
>
>
> I tryed like this:
>
>
> '1963-09-01'
> '1963-09-01'::date
> 1963-09-01
Show us the whole statement - this doesn't look like the error to me.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Duplicate records
Hi list, I am making some data minning and would like to know if someone tell me how to retrieve the duplicate records based in one or two fields of my table. Any help would be welcomed. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] Insert into a date field
Now it's ok thank you.
2007/2/2, Shoaib Mir <[EMAIL PROTECTED]>:
Just tried the following way, that might help you:
postgres=# create table testing (a date);
CREATE TABLE
postgres=# insert into testing values (to_date('1963-09-01',
'-MM-DD'));
INSERT 0 1
postgres=# select * from testing;
a
1963-09-01
(1 row)
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 2/2/07, Richard Huxton wrote:
> Ezequias Rodrigues da Rocha wrote:
> > Hi list,
> >
> > I am trying to insert a record in a table with a date field but the
> > postgresql reports me the following error:
> >
> > ERROR: column "data_nascimento" is of type date but expression is of
> type
> > integer
> > SQL state: 42804
> > Hint: You will need to rewrite or cast the expression.
> >
> >
> > I tryed like this:
> >
> >
> > '1963-09-01'
> > '1963-09-01'::date
> > 1963-09-01
>
> Show us the whole statement - this doesn't look like the error to me.
>
> --
>Richard Huxton
>Archonet Ltd
>
> ---(end of broadcast)---
>
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org
>
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/
Re: [SQL] Duplicate records
That has been asked in the past a number of time too, you can look at the following for details --> http://archives.postgresql.org/pgsql-novice/2006-06/msg00093.php -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 2/2/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: Hi list, I am making some data minning and would like to know if someone tell me how to retrieve the duplicate records based in one or two fields of my table. Any help would be welcomed. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] Duplicate records
select * from mytable A, (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1) B where A.field1 = B.field1 and A.field2 = B.field2 >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED]> 2007-02-02 14:48 >>> Hi list, I am making some data minning and would like to know if someone tell me how to retrieve the duplicate records based in one or two fields of my table. Any help would be welcomed. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] Duplicate records
Thank you but are you talking of two tables ? I intent to check in only one table. Please explain to me. Ezequias 2007/2/2, Bart Degryse <[EMAIL PROTECTED]>: select * from mytable A, (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1) B where A.field1 = B.field1 and A.field2 = B.field2 >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED]> 2007-02-02 14:48 >>> Hi list, I am making some data minning and would like to know if someone tell me how to retrieve the duplicate records based in one or two fields of my table. Any help would be welcomed. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] Duplicate records
Where do you see the second table in it? I guess here: A = mytable B = (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1) So that is all around one table that is 'mytable', where A and B are just the aliases. -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 2/2/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: Thank you but are you talking of two tables ? I intent to check in only one table. Please explain to me. Ezequias 2007/2/2, Bart Degryse < [EMAIL PROTECTED]>: > > select * > from mytable A, (select field1, field2, count(*) from mytable group by > field1, field2 having count(*) > 1) B > where A.field1 = B.field1 and A.field2 = B.field2 > > >>> "Ezequias Rodrigues da Rocha" < [EMAIL PROTECTED]> 2007-02-02 > 14:48 >>> > Hi list, > > I am making some data minning and would like to know if someone tell me > how to retrieve the duplicate records based in one or two fields of my > table. > > Any help would be welcomed. > > -- > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > Atenciosamente (Sincerely) > Ezequias Rodrigues da Rocha > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- > > A pior das democracias ainda é melhor do que a melhor das ditaduras > The worst of democracies is still better than the better of > dictatorships > http://ezequiasrocha.blogspot.com/ > -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] Duplicate records
The only table in my query is "mytable". The part (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1) counts how many records have the same field1 and field2 and returns a record per combination of field1 and field2 that have more than one (= duplicates) The rest uses that information to filter all fields of all the records from that same table having such a combination of field1 and field2 This version is even a little shorter: select * from mytable A, (select field1, field2 from mytable group by field1, field2 having count(*) > 1) B where A.field1 = B.field1 and A.field2 = B.field2 >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED]> 2007-02-02 14:59 >>> Thank you but are you talking of two tables ? I intent to check in only one table. Please explain to me. Ezequias 2007/2/2, Bart Degryse < [EMAIL PROTECTED]>: select * from mytable A, (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1) B where A.field1 = B.field1 and A.field2 = B.field2 >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED] ( mailto:[EMAIL PROTECTED] )> 2007-02-02 14:48 >>> Hi list, I am making some data minning and would like to know if someone tell me how to retrieve the duplicate records based in one or two fields of my table. Any help would be welcomed. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] Duplicate records
Ezequias Rodrigues da Rocha wrote: Hi list, I am making some data minning and would like to know if someone tell me how to retrieve the duplicate records based in one or two fields of my table. Any help would be welcomed. You can use a simple query like this one: select t1.id, t2.id from table t1, table t2 where t1.record1 = t2.record1 and t1.record2 = t2.record2 and t1.id != t2.id This one lists the ids of the matching records. You may need to fine tune it to your needs, e.g. use distinct or replace the != with <. Zizi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Duplicate records
You are correct. Sorry my mistake. The SQL statement is correct and I find the duplicate records. ps: I just think postresql could make this easyly. Don't you think ? Any function or anything else. Thank you so much. Ezequias 2007/2/2, Shoaib Mir <[EMAIL PROTECTED]>: Where do you see the second table in it? I guess here: A = mytable B = (select field1, field2, count(*) from mytable group by field1, field2 having count(*) > 1) So that is all around one table that is 'mytable', where A and B are just the aliases. -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 2/2/07, Ezequias Rodrigues da Rocha < [EMAIL PROTECTED]> wrote: > > Thank you but are you talking of two tables ? I intent to check in only > one table. > > Please explain to me. > > Ezequias > > 2007/2/2, Bart Degryse < [EMAIL PROTECTED]>: > > > > select * > > from mytable A, (select field1, field2, count(*) from mytable group by > > field1, field2 having count(*) > 1) B > > where A.field1 = B.field1 and A.field2 = B.field2 > > > > >>> "Ezequias Rodrigues da Rocha" < [EMAIL PROTECTED]> > > 2007-02-02 14:48 >>> > > Hi list, > > > > I am making some data minning and would like to know if someone tell > > me how to retrieve the duplicate records based in one or two fields of my > > table. > > > > Any help would be welcomed. > > > > -- > > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > > Atenciosamente (Sincerely) > > Ezequias Rodrigues da Rocha > > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- > > > > A pior das democracias ainda é melhor do que a melhor das ditaduras > > The worst of democracies is still better than the better of > > dictatorships > > http://ezequiasrocha.blogspot.com/ > > > > > > -- > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > Atenciosamente (Sincerely) > Ezequias Rodrigues da Rocha > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- > A pior das democracias ainda é melhor do que a melhor das ditaduras > The worst of democracies is still better than the better of > dictatorships > http://ezequiasrocha.blogspot.com/ -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] Duplicate records
> > ps: I just think postresql could make this easyly. Don't you think ? Any > function or anything else. What's hard about the self-join? That's how SQL works. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Nocount and scope_identity()
I'm converting a MSSQL query to postgresql. It's something like this: SET NOCOUNT ON; INSERT INTO table_name([list]) VALUES([list]); SELECT SCOPE_IDENTITY() as newId; I get an error on the NOCOUNT statement: "syntax error at or near "on" at character 13" So, I'm wondering if NOCOUNT is supported in Postgres at all. If it's not, what's the alternative? If it is, what is wrong with my syntax? I haven't gotten there yet, but I'm also wondering if the SCOPE_IDENTITY() method is going to work or not. TIA -Jeff This email is intended only for the individual or entity to which it is addressed. This email may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or any attachments by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you are not the intended recipient of this message or the employee or agent responsible for delivery of this email to the intended recipient, please notify the sender by replying to this message and then delete it from your system. Any use, dissemination, distribution, or reproduction of this message by unintended recipients is strictly prohibited and may be unlawful. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] to_chat(bigint)
Hi list, Now I noticed that it is impossible to convert a bigint field to char with the function to_char. Is it correct ? If not please tell me how to convert a bigint using to_char. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] to_chat(bigint)
Ezequias Rodrigues da Rocha wrote: Hi list, Now I noticed that it is impossible to convert a bigint field to char with the function to_char. Is it correct ? If not please tell me how to convert a bigint using to_char. Couple ways I can see immedately: select 123123123123123123123::BIGINT::TEXT; or select to_char(12312312312312312312::BIGINT,'fm99'); ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Nocount and scope_identity()
On Fri, Feb 02, 2007 at 11:49:30AM -0600, Demel, Jeff wrote: > So, I'm wondering if NOCOUNT is supported in Postgres at all. If it's > not, what's the alternative? If it is, what is wrong with my syntax? Not as far as I know. What's it supposed to do? > I haven't gotten there yet, but I'm also wondering if the > SCOPE_IDENTITY() method is going to work or not. I doubt it. What does it do? A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Nocount and scope_identity()
> So, I'm wondering if NOCOUNT is supported in Postgres at all. If it's > not, what's the alternative? If it is, what is wrong with my syntax? Andrew wrote: Not as far as I know. What's it supposed to do? It suppresses the rowcount returned after the query runs. > I haven't gotten there yet, but I'm also wondering if the > SCOPE_IDENTITY() method is going to work or not. I doubt it. What does it do? It returns the id of the record just inserted. -Jeff This email is intended only for the individual or entity to which it is addressed. This email may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or any attachments by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you are not the intended recipient of this message or the employee or agent responsible for delivery of this email to the intended recipient, please notify the sender by replying to this message and then delete it from your system. Any use, dissemination, distribution, or reproduction of this message by unintended recipients is strictly prohibited and may be unlawful. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Question regarding multibyte.
Hi, I am new to postgres. We are using 8.2 release of postgres. Recently we converted our database to multibyte on our dev machine. we want to test the following. 1) How will I insert multibyte from our php? Do we need to use any special encoding? 2) I am using psql command line to get the data into a flat file and push to our data warehouse We use IBM redbrick warehouse. 3) If I have multibyte, I know the output from the psql will scatter to many lines. How can we avoid that. What is the sql command ? I need 1 record per row to supply to our warehouse. Can somebody help me? Regards skarthi _ Valentines Day -- Shop for gifts that spell L-O-V-E at MSN Shopping http://shopping.msn.com/content/shp/?ctId=8323,ptnrid=37,ptnrdata=24095&tcode=wlmtagline ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Nocount and scope_identity()
On Fri, Feb 02, 2007 at 01:38:40PM -0600, Demel, Jeff wrote: > Not as far as I know. What's it supposed to do? > > It suppresses the rowcount returned after the query runs. There isn't a way to do that, although there is a way in psql, for example, not to get all that formatting. You want the \pset commands. The count isn't automatically delivered with most APIs (i.e. if you want to know how many rows you touched, you usually have to ask explicitly). > > SCOPE_IDENTITY() method is going to work or not. > I doubt it. What does it do? > > It returns the id of the record just inserted. Ah. Well, there's no in-principle notion if "id" in Postgres (OIDs are optional as of several versions ago). But if you have a primary key that's an integer you populate using a sequence, then you can use the currval() function to get the value of the sequence. There's no race condition there unless you are sharing a pooled connection (and if transactions mean anything to you, you'd better not be doing). A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Nocount and scope_identity()
Andrew Sullivan <[EMAIL PROTECTED]> writes: > On Fri, Feb 02, 2007 at 01:38:40PM -0600, Demel, Jeff wrote: SCOPE_IDENTITY() method is going to work or not. >>> I doubt it. What does it do? >> >> It returns the id of the record just inserted. > Ah. Well, there's no in-principle notion if "id" in Postgres (OIDs > are optional as of several versions ago). But if you have a primary > key that's an integer you populate using a sequence, then you can use > the currval() function to get the value of the sequence. Also, as of 8.2 INSERT RETURNING is a good way to collect the value of an auto-generated key field (or any other auto-generated field...) regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
