column "id" is of type integer but expression is of type character

On Thursday 08 January 2009, "Andrus" <kobruleht2@hot.ee> wrote:

Commands:

create temp table test ( id int, baas char(10) );
create temp table lisa ( id int, baas char(10) );
alter table lisa drop column id;
INSERT INTO test SELECT * FROM lisa;

How to fix ?

INSERT INTO test (baas) SELECT baas FROM lisa;

--
Current Peeve: The mindset that the Internet is some sort of school for
novice sysadmins and that everyone -not- doing stupid dangerous things
should act like patient teachers with the ones who are. -- Bill Cole, NANAE

On 08/01/2009 19:04, Andrus wrote:

create temp table test ( id int, baas char(10) );
create temp table lisa ( id int, baas char(10) );
alter table lisa drop column id;
INSERT INTO test SELECT * FROM lisa;
drop table lisa;

Cause error

ERROR: column "id" is of type integer but expression is of type character
HINT: You will need to rewrite or cast the expression.

Well, you've dropped the integer column from test, so now the INSERT
command is trying to stuff the char(10) value from test into the integer
column in lisa - which is what the error message is telling you.

I've no idea, in any case, whether you can expect SELECTing two columns
into a one-column table to work.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Andrus wrote:

Commands:

create temp table test ( id int, baas char(10) );
create temp table lisa ( id int, baas char(10) );
alter table lisa drop column id;
INSERT INTO test SELECT * FROM lisa;
drop table lisa;

Cause error

ERROR: column "id" is of type integer but expression is of type character
HINT: You will need to rewrite or cast the expression.

How to fix ?

Don't use SELECT * - list the columns you want to insert. I can't think
why you would do that (you'll end up with nulls in the id column) but it
will work.

--
Richard Huxton
Archonet Ltd

"Raymond O'Donnell" <rod@iol.ie> writes:

I've no idea, in any case, whether you can expect SELECTing two columns
into a one-column table to work.

It won't, but the other error happens to be detected first.

regards, tom lane

Thank you.

Well, you've dropped the integer column from test, so now the INSERT
command is trying to stuff the char(10) value from test into the integer
column in lisa - which is what the error message is telling you.

INSERT INTO test SELECT * FROM lisa;

I need that test table primary key column (id) values are populated
automatically with new ids from serial sequence, but all other column values
are duplicatated.

In reality those tables contain large number of columns and some column
names may be not known at script creation time.

So it is not possible not create column list instead of *

How to force PostgreSql to match columns by name, not by position so that
this command will work ?

Andrus.

Richard,

Don't use SELECT * - list the columns you want to insert. I can't think
why you would do that (you'll end up with nulls in the id column) but it
will work.

In real table id is defined as

id serial primary key

so I excpect that it will be populated with correct values.
List of columns are not exactly known at script creation time (in customer
sites customers may add additional columns to table),
so using column list is not possible.

Ony way seems to generates SELECT column list dynamically at run time, but
I'm looking for nicer solution to force PostgreSql to match columns by name.

Andrus.

Andrus wrote:

Richard,

Don't use SELECT * - list the columns you want to insert. I can't think
why you would do that (you'll end up with nulls in the id column) but it
will work.

In real table id is defined as

id serial primary key

so I excpect that it will be populated with correct values.
List of columns are not exactly known at script creation time (in
customer sites customers may add additional columns to table),
so using column list is not possible.

Ony way seems to generates SELECT column list dynamically at run time,
but I'm looking for nicer solution to force PostgreSql to match columns
by name.

There isn't one. That's not how SQL works. You need to know what columns
your tables have.

If you want to update the primary key just do something like:

INSERT INTO t1 SELECT * FROM t2;
UPDATE t1 SET id = DEFAULT;

Although if you don't know what your columns are called I can't see how
you can figure out that you have a single-column pkey with
auto-incrementing default.

--
Richard Huxton
Archonet Ltd

On 08/01/2009 20:10, Andrus wrote:

Thank you.

Well, you've dropped the integer column from test, so now the INSERT
command is trying to stuff the char(10) value from test into the integer
column in lisa - which is what the error message is telling you.

INSERT INTO test SELECT * FROM lisa;

Oops - my mistake - I read it the wrong way around.

In reality those tables contain large number of columns and some column
names may be not known at script creation time.

So it is not possible not create column list instead of *

How to force PostgreSql to match columns by name, not by position so that
this command will work ?

I don't think you can - here's what the docs[1]http://www.postgresql.org/docs/8.3/static/sql-insert.html for INSERT say:

<quote from docs>
The target column names can be listed in any order. If no list of column
names is given at all, the default is all the columns of the table in
their declared order; or the first N column names, if there are only N
columns supplied by the VALUES clause or query. The values supplied by
the VALUES clause or query are associated with the explicit or implicit
column list left-to-right.
</quote from docs>

So it seems that the association between columns is based on order, not
on name.

Ray.

[1]: http://www.postgresql.org/docs/8.3/static/sql-insert.html

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

There isn't one. That's not how SQL works. You need to know what columns
your tables have.

If you want to update the primary key just do something like:

INSERT INTO t1 SELECT * FROM t2;
UPDATE t1 SET id = DEFAULT;

Although if you don't know what your columns are called I can't see how
you can figure out that you have a single-column pkey with
auto-incrementing default.

I know most column names.
Customer can add few columns to tables which are not known to me at design
time.

The command

INSERT INTO t1 SELECT * FROM t2;

causes primary key violation since t2 is subset of t1 and thus has primary
key values which are already present in t1.
So update is not possible.
Only way I see is to generate script dynamically at runtime containing all
columns excluding id column:

INSERT INTO t1 ( c1, c2, ..., cn )
SELECT c1,c2, ...., cn
FROM t2;

or

INSERT INTO t1 ( id, c1, c2, ..., cn )
SELECT DEFAULT, c1,c2, ...., cn
FROM t2;

in this case id column is populated automatically from sequence.
This is very ugly solution since requires dynamic script creation from pg
metadata instead of writing simple query.

Andrus.

Ray,

I don't think you can - here's what the docs[1] for INSERT say:

<quote from docs>
The target column names can be listed in any order. If no list of column
names is given at all, the default is all the columns of the table in
their declared order; or the first N column names, if there are only N
columns supplied by the VALUES clause or query. The values supplied by
the VALUES clause or query are associated with the explicit or implicit
column list left-to-right.
</quote from docs>

So it seems that the association between columns is based on order, not
on name.

Solution seems to force id column to be last column in table.
In this case drop column id drops last columns and insert should work.

Any idea how to force id to be last column in table ?
Is it possible to create updatable view where id is last column ?

Andrus.

Andrus wrote:

There isn't one. That's not how SQL works. You need to know what columns
your tables have.

If you want to update the primary key just do something like:

INSERT INTO t1 SELECT * FROM t2;
UPDATE t1 SET id = DEFAULT;

Although if you don't know what your columns are called I can't see how
you can figure out that you have a single-column pkey with
auto-incrementing default.

I know most column names.
Customer can add few columns to tables which are not known to me at
design time.

I'm confused now.

You've defined a table with columns (id, c1, c2, c3) and the customer
adds columns (c4, c5) - so far so good. You now want to send out some
updates to the customer - ok.

But - you're supplying the customer with values for (c4,c5) even though
you don't know what the columns are for, their types or their names? How?

If you're only supplying values for your columns then you could just
name them.

The command

INSERT INTO t1 SELECT * FROM t2;

causes primary key violation since t2 is subset of t1 and thus has
primary key values which are already present in t1.
So update is not possible.

Ah, t1 isn't the final table, it's an empty temporary table that you are
importing into. That lets you do whatever tidying to need to before
inserting to the "live" table.

--
Richard Huxton
Archonet Ltd