[SQL] CAST from VARCHAR to INT
(Postgres 7.2.1) I screwed up when I was designing a table a while back and made a column a VARCHAR that referenced (and should have been) an INT. Now I'm trying to correct my mistake, I've created a new table and I'm trying to INSERT INTO...SELECT the data into it, but it's complaining that it can't stick a VARCHAR into an INT. All the values in the column are valid integers (the foreign key sees to that) but even a CAST won't do it. How can I force it to copy/change the values? ======== Luke Pascoe Senior Developer / Systems administrator KMG (NZ) Limited. http://www.kmg.co.nz Mobile: (021) 303019 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] CAST from VARCHAR to INT
Sweet, worked the charm, thanks! P.S. Anyone know why it takes several hours[1] for my posts to come through the list? [1] Posted a message at ~9am friday, it got back to me ~4pm! - Original Message - From: "Bhuvan A" <[EMAIL PROTECTED]> To: "Luke Pascoe" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, January 24, 2003 6:58 PM Subject: Re: [SQL] CAST from VARCHAR to INT > > > (Postgres 7.2.1) > > > > I screwed up when I was designing a table a while back and made a column a > > VARCHAR that referenced (and should have been) an INT. > > > > Now I'm trying to correct my mistake, I've created a new table and I'm > > trying to INSERT INTO...SELECT the data into it, but it's complaining that > > it can't stick a VARCHAR into an INT. All the values in the column are valid > > integers (the foreign key sees to that) but even a CAST won't do it. > > > > How can I force it to copy/change the values? > > > > varchar cannot be casted to integer directly. Rather we can do it this > way: > > => select your_varchar_field::text::int from your_table; > > regards, > bhuvaneswaran > > > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] checking data integrity in a recursive table
Hi, I have a table that references itself to create a tree-like structure, eg: CREATE TABLE tree ( id SERIAL NOT NULL, name VARCHAR(255) NOT NULL, parent INT NULL, customer IN NOT NULL, CONSTRAINT parent_key... CONSTRAINT customer_fk FOREIGN KEY (customer) REFERENCES customer ); ALTER TABLE tree ADD CONSTRAINT FOREIGN KEY (parent) REFERENCES tree; As you can see tree also references the customer table. What I need is a CHECK that will ensuer that any given "tree" row has the same customer as its parent. Remember that "parent" can also be NULL. Or would this be better done as a trigger? TIA! ==== Luke Pascoe KMG (NZ) Limited. http://www.kmg.co.nz Mobile: (021) 303019 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Inserting a tab character
I have a table which defines various possible file delimiters (CHAR(1) NOT NULL), for the moment it'll only contain comma and tab. Inserting a comma is easy, but inserting a tab is proving somewhat more difficult. How do I do it in 'psql'? Luke Pascoe. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
