Thread: How to determine the type of a column
Hi! 1. create table t (i varchar(255)) 2. select ______(i) from t; #(Should return "varchar(255)") So what should I write to ______ to return "varchar(255)" ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak usko Herraan Jeesukseen, niin sin� pelastut. (apt. 16:31) _________________________________________________________________ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail
On Fri, 2003-01-03 at 19:24, Ville Jungman wrote:
> Hi!
>
> 1. create table t (i varchar(255))
> 2. select ______(i) from t; #(Should return "varchar(255)")
>
> So what should I write to ______ to return "varchar(255)"
wrms=# create table tess ( abcd varchar(77) );
CREATE TABLE
wrms=# select relname, attname, typname, typname || '(' || (atttypmod -
4)::text || ')', pg_catalog.format_type(atttypid, atttypmod) from
pg_class, pg_attribute, pg_type where attrelid = pg_class.oid AND
relname = 'tess' AND pg_type.oid = atttypid AND attname = 'abcd';
relname | attname | typname | ?column? | format_type
---------+---------+---------+-------------+-----------------------
tess | abcd | varchar | varchar(77) | character varying(77)
(1 row)
Just FYI, I worked that out in response to your question by looking up
what psql does, using the -E option. This can be very useful for
understanding the data dictionary relationships.
Of course the DD stuff is somewhat subject to change from version to
version.
Regards,
Andrew.
andrew@kant ~/wrms $ psql -E wrms
********* QUERY **********
BEGIN; SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'andrew';
COMMIT
**************************
Welcome to psql 7.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
wrms=# \d tess
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.pg_table_is_visible(c.oid)
AND c.relname ~ '^tess$'
ORDER BY 2, 3;
**************************
********* QUERY **********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_catalog.pg_class WHERE oid = '320015'
**************************
********* QUERY **********
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
a.attnotnull, a.atthasdef, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '320015' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
**************************
Table "public.tess"
Column | Type | Modifiers
--------+-----------------------+-----------
abcd | character varying(77) |
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Survey for nothing with http://survey.net.nz/
---------------------------------------------------------------------
I've read through the docs and I couldn't find many examples of using subselects. I could find out and read all about what they were, but not many examples. If someone could point to a page that does show this, I'd be ever so appreciative! Anyways, I'm redoing a table I imported from MySQL (as part of my conversion to PG from MySQL) and I'm having problems with this. The error I'm getting is that it says you can't select more than one column. Why? Is there another way to do what I'm trying? Here is my query: INSERT INTO "cards_type_temp" ( "card_game", "card_name", "card_table", "record_num" ) VALUES ( 'Vtes', ( SELECT "card_name", "card_type", "record_num" FROM "cards_type" )); Any help or insight would be great! Chris
Running psql with -E seems to reveal (too?) many useful things for a psql-novice like me. Thank You very much! ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak usko Herraan Jeesukseen, niin sin� pelastut. (apt. 16:31) >From: Andrew McMillan <andrew@catalyst.net.nz> >To: Ville Jungman <ville_jungman@hotmail.com> >CC: pgsql-novice@postgresql.org >Subject: Re: [NOVICE] How to determine the type of a column >Date: 03 Jan 2003 23:36:37 +1300 > >On Fri, 2003-01-03 at 19:24, Ville Jungman wrote: > > Hi! > > > > 1. create table t (i varchar(255)) > > 2. select ______(i) from t; #(Should return "varchar(255)") > > > > So what should I write to ______ to return "varchar(255)" > >wrms=# create table tess ( abcd varchar(77) ); >CREATE TABLE >wrms=# select relname, attname, typname, typname || '(' || (atttypmod - >4)::text || ')', pg_catalog.format_type(atttypid, atttypmod) from >pg_class, pg_attribute, pg_type where attrelid = pg_class.oid AND >relname = 'tess' AND pg_type.oid = atttypid AND attname = 'abcd'; > relname | attname | typname | ?column? | format_type >---------+---------+---------+-------------+----------------------- > tess | abcd | varchar | varchar(77) | character varying(77) >(1 row) > > >Just FYI, I worked that out in response to your question by looking up >what psql does, using the -E option. This can be very useful for >understanding the data dictionary relationships. > >Of course the DD stuff is somewhat subject to change from version to >version. > >Regards, > Andrew. > > >andrew@kant ~/wrms $ psql -E wrms >********* QUERY ********** >BEGIN; SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'andrew'; >COMMIT >************************** > >Welcome to psql 7.3, the PostgreSQL interactive terminal. > >Type: \copyright for distribution terms > \h for help with SQL commands > \? for help on internal slash commands > \g or terminate with semicolon to execute query > \q to quit > >wrms=# \d tess >********* QUERY ********** >SELECT c.oid, > n.nspname, > c.relname >FROM pg_catalog.pg_class c > LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace >WHERE pg_catalog.pg_table_is_visible(c.oid) > AND c.relname ~ '^tess$' >ORDER BY 2, 3; >************************** > >********* QUERY ********** >SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules >FROM pg_catalog.pg_class WHERE oid = '320015' >************************** > >********* QUERY ********** >SELECT a.attname, > pg_catalog.format_type(a.atttypid, a.atttypmod), > a.attnotnull, a.atthasdef, a.attnum >FROM pg_catalog.pg_attribute a >WHERE a.attrelid = '320015' AND a.attnum > 0 AND NOT a.attisdropped >ORDER BY a.attnum >************************** > > Table "public.tess" > Column | Type | Modifiers >--------+-----------------------+----------- > abcd | character varying(77) | > > > >-- >--------------------------------------------------------------------- >Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington >WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St >DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 > Survey for nothing with http://survey.net.nz/ >--------------------------------------------------------------------- _________________________________________________________________ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE* http://join.msn.com/?page=features/virus
Hi: I would better try this. This selects only one row at a time. INSERT INTO "cards_type_temp" ( "card_game", "card_name", "card_table", "record_num" ) VALUES ( 'Vtes', ( SELECT "card_name" FROM "cards_type" ), ( SELECT "card_type" FROM "cards_type" ), ( SELECT "record_num" FROM "cards_type" ) ) ); Probably the DB understands that everything between two commas in VALUES is supossed to fit into a single column. So, in the statement you made, if it had worked, you would be setting only two columns, and the DB would have set the last two columns to NULL. Incidentally, I didn't know you could put "" (double quotes?) in a psql statement. I'm surprised that psql even accepted them. I never use "" anywhere and have not had any problem. I use JSP and Java to access psql and I if I had to use " the I would just go crazy while trying to insert correctly all those \" characters in every string. Argh! I hate "". Perhaps you used "" to make this mail clearer, or perhaps you needed it in Mysql. In psql you don't need either especifying every time what columns you are inserting! It's more correct, but sometimes you are in a hurry... --- Chris Boget <chris@wild.net> wrote: > I've read through the docs and I couldn't find many > examples of using > subselects. I could find out and read all about > what they were, but not > many examples. If someone could point to a page > that does show this, > I'd be ever so appreciative! > Anyways, I'm redoing a table I imported from MySQL > (as part of my > conversion to PG from MySQL) and I'm having problems > with this. The > error I'm getting is that it says you can't select > more than one column. > Why? Is there another way to do what I'm trying? > Here is my query: > > INSERT INTO "cards_type_temp" > ( "card_game", "card_name", "card_table", > "record_num" ) > VALUES > ( 'Vtes', ( SELECT "card_name", "card_type", > "record_num" FROM "cards_type" )); > > Any help or insight would be great! > > Chris > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org ===== Enrique Naval Estudiante de Inform�tica de Gesti�n en la Udl (Lleida) Lleida __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
Hi again: I meant you can use this: INSERT INTO cards_type_temp VALUES ( 'Vtes', ( SELECT card_name FROM cards_type ), ( SELECT card_type FROM cards_type ), ( SELECT record_num FROM cards_type ) ) ; I put an extra parenthesis in the other mail. Goodbye. --- Naval Grau <enventa2000@yahoo.com> wrote: > Hi: > > I would better try this. This selects only one row > at > a time. > > > INSERT INTO "cards_type_temp" > ( > "card_game", > "card_name", > "card_table", > "record_num" > ) > VALUES > ( > 'Vtes', > ( SELECT "card_name" FROM "cards_type" ), > ( SELECT "card_type" FROM "cards_type" ), > ( SELECT "record_num" FROM "cards_type" ) > ) > ); > > > Probably the DB understands that everything between > two commas in VALUES is supossed to fit into a > single > column. > > So, in the statement you made, if it had worked, you > would be setting only two columns, and the DB would > have set the last two columns to NULL. > > Incidentally, I didn't know you could put "" (double > quotes?) in a psql statement. I'm surprised that > psql > even accepted them. > > I never use "" anywhere and have not had any > problem. > I use JSP and Java to access psql and I if I had to > use " the I would just go crazy while trying to > insert > correctly all those \" characters in every string. > Argh! I hate "". > > Perhaps you used "" to make this mail clearer, or > perhaps you needed it in Mysql. In psql you don't > need > either especifying every time what columns you are > inserting! It's more correct, but sometimes you are > in > a hurry... > > > > --- Chris Boget <chris@wild.net> wrote: > > I've read through the docs and I couldn't find > many > > examples of using > > subselects. I could find out and read all about > > what they were, but not > > many examples. If someone could point to a page > > that does show this, > > I'd be ever so appreciative! > > Anyways, I'm redoing a table I imported from MySQL > > (as part of my > > conversion to PG from MySQL) and I'm having > problems > > with this. The > > error I'm getting is that it says you can't select > > more than one column. > > Why? Is there another way to do what I'm trying? > > Here is my query: > > > > INSERT INTO "cards_type_temp" > > ( "card_game", "card_name", "card_table", > > "record_num" ) > > VALUES > > ( 'Vtes', ( SELECT "card_name", "card_type", > > "record_num" FROM "cards_type" )); > > > > Any help or insight would be great! > > > > Chris > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > > > ===== > Enrique Naval > Estudiante de Inform�tica de Gesti�n en la Udl > (Lleida) > Lleida > > __________________________________________________ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up > now. > http://mailplus.yahoo.com > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ===== Enrique Naval Estudiante de Inform�tica de Gesti�n en la Udl (Lleida) Lleida __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
On Sat, 2003-01-04 at 02:25, Chris Boget wrote:
> I've read through the docs and I couldn't find many examples of using
> subselects. I could find out and read all about what they were, but not
> many examples. If someone could point to a page that does show this,
> I'd be ever so appreciative!
> Anyways, I'm redoing a table I imported from MySQL (as part of my
> conversion to PG from MySQL) and I'm having problems with this. The
> error I'm getting is that it says you can't select more than one column.
> Why? Is there another way to do what I'm trying? Here is my query:
>
> INSERT INTO "cards_type_temp"
> ( "card_game", "card_name", "card_table", "record_num" )
> VALUES
> ( 'Vtes', ( SELECT "card_name", "card_type", "record_num" FROM "cards_type" ));
>
> Any help or insight would be great!
INSERT INTO "cards_type_temp" ( "card_game", "card_name", "card_table",
"record_num" ) ( SELECT 'Vtes', "card_name", "card_type", "record_num"
FROM "cards_type" );
i.e. You want to move the constant 'vtes' into the sub-select and get
rid of the VALUES( ) around the subselect.
That's probably the most efficient way anyway, but normally there would
be a WHERE ... clause on that subselect so that you don't get all rows
from cards_type inserted. Maybe you want that in this case.
Cheers,
Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Survey for nothing with http://survey.net.nz/
---------------------------------------------------------------------