[Helios Co.]

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

[shammat]

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.

[loquin]

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.

[shammat]

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

[loquin]

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.

[shammat]

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.

[loquin]

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...)

Select * from sample.Test 

produces

Originally Posted by pg

ERROR: relation "sample.test" does not exist

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

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

However,

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.

[shammat]

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

[loquin]

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....