Thread: column "id" is of type integer but expression is of type character
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. ********** Error ********** ERROR: column "id" is of type integer but expression is of type character SQL state: 42804 Hint: You will need to rewrite or cast the expression. How to fix ? Andrus.
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 ------------------------------------------------------------------
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
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] 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