Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2008
    Posts
    1

    Unanswered: [PSQL] Copy Data from Table to Table

    Hello @ all,

    as a nube in DB-Stuff i hope u can help me.
    I need to move Data from one table (not the complete table, just one or morecolumns) to another (in the same DB).

    INSERT...SELECT... didnt work.
    COPY... also didnt work, it seems that COPY just works with files and one table
    (moving data from file to table)

    So a little more concretely:

    I have two tables. The one (sourse) hase some Columns which i need in another table.
    In this second table are also columns which shall be set to NULL, in this "copy-process".

    THX
    and sorry for my poor english

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Helios Co.
    INSERT...SELECT... didnt work.
    Without showing the SQL that you used, no one will be able to help.
    Copying from one table to another using INSERT ... SELECT will work, even if you need to insert NULL values.

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    First, get the select query working. Then, append it to the insert statement as the subquery.

    (and, as shammat said, POST the non-working SQL.)

    Without seeing the SQL, and as a pure guess - PostgreSQL IS case sensitive in regards to object naming, with a default of lower case.
    Last edited by loquin; 01-08-08 at 12:06.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by loquin
    PostgreSQL IS case sensitive in regards to object naming, with a default of lower case.
    But only if you quote the objects (e.g. "TheTable") which is compliant with the ANSI Standard

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    yes, but if you don't quote a mixed/upper case object name, pg sql won't find it.

    Other databases, and many programming languages are either case insensitive, may be configured to automatically fold the case, or may be configured to be case insensitive.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by loquin
    yes, but if you don't quote a mixed/upper case object name, pg sql won't find it.
    Only if you created it with quotes.
    Other databases, and many programming languages are either case insensitive, may be configured to automatically fold the case, or may be configured to be case insensitive.
    That might be true, but the ANSI standard requires that identifiers are case-insensitive when not quoted and case sensitive when beeing quoted.
    And that's exactly what PG does.

    The only thing where PG is not compliant is it's folding to lower case. The ANSI standard requires a folding to uppercase when not quoted.

    So there is nothing special about the way PG handles identifiers.
    If a DBMS is always case sensitive it simply is non-standard.

    As a rule of thumg one should simply never use (double) quotes in SQL statements and the statements will run happily on each standard-compliant DBMS.

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by shammat
    ...
    That might be true, but the ANSI standard requires that identifiers are case-insensitive when not quoted and case sensitive when beeing quoted.
    And that's exactly what PG does....
    I have a table named Test in schema sample, created with pgadmin. (and, if you hadn't created it with quotes, or with pgAdmin, it wouldn't be mixed case, since it gets folded to lower case if you don't quote it...)

    Code:
    Select * from sample.Test
    produces
    Quote Originally Posted by pg
    ERROR: relation "sample.test" does not exist

    ********** Error **********

    ERROR: relation "sample.test" does not exist
    SQL state: 42P01
    However,

    Code:
    Select * from sample."Test"
    works fine.

    So, 'case insensitive when not quoted' does NOT seem to apply to pg... If it were, it would find the mixed-case object. pg simply folds the identifier name to lower-case before it queries. IF pg were case insensitive, it would effectively fold both object name and identifier name to the same case before querying. Instead, it folds the identifier name only. Obviously, this will NOT result in a 'case insensitive' query.
    Last edited by loquin; 09-17-08 at 16:37.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by loquin
    So, 'case insensitive when not quoted' does NOT seem to apply to pg...
    Of course it does.
    The way the object is created defines whether it's case sensitive or not.
    And PGAdmin always creates the tables with quotes, so any object created with PGAdmin will be created in a case-sensitive manner.

    The standard defines that any object created with quotes is case sensitive. Any object created without quotes is case-insensitive.

    And that's precisely the way PG works. Just like Oracle, DB/2, Derby, Firebird, HSQLDB, H2. Even MySQL and MS SQL Server to some extent.

    In MySQL it depends on the storage engine and the oparating system whether non-quoted names are case sensitive (MyISAM with *nix means case sensitive name always). And with SQL Server it depends on the collation defined for the database. So actually there you can change the behaviour on a daily basis if you feel like it.


    Apart from those two (but they don't care about standards anyway) I don't know any DBMS that does not follow the ANSI standard.

    The only place where PG violates the standard, is that it folds the names to lower case whereas the standard requires upper case.

    The standard requires the following:

    CREATE TABLE test (...);
    SELECT * FROM TeST --> works
    SELECT * FROM TEST --> works;

    CREATE TABLE "Test" (...)
    SELECT * FROM test --> should not work
    SELECT * FROM TEST --> should not work
    SELECT * FROM "Test" --> has to work.

    PG violates the following rule:

    CREATE TABLE test (...)
    SELECT * FROM "TEST" --> should work according to the standard

  9. #9
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    shammat, although it may meet the letter of the ansi spec, IMO, if pg were truly case insensitive, when querying, it would find the ANY case, upper, lower, or mixed, of an object when you do not quote the identifier. If you DO quote the identifier, then that should indicate that you need to find an object explicitly matching the exact case you have specified. i.e. assuming table name of "Test" (created with quotes)

    Select * from test
    Select * from TEST
    Select * from tesT
    Select * From "Test"

    should all find the table, but

    Select * from "TEst"

    should not.

    And, in looking back at the original problem, THIS is what I was pointing out to the OP... if the objects were created with mixed cased naming, you would need to match the case exactly when querying, else pg will not find the object....
    Last edited by loquin; 09-18-08 at 16:47.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •