[SQL] get table schema in psql?
how can I get the schema of a table in psql? in oracle I would do "desc tablename". I remember sql server and ingres having a similar facility. right now the only way I seem to be able to get the information is by using pgaccess. I realize the definition of the table is probably spread across the various pg_ system tables, but I'm thinking there must be some easier way to get the info. in particular, I'm after the list of columns, their data types and lengths, null/non-null, default values, and table indices. thanks, michael __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
[SQL] Error with DISTINCT
testdb=#SELECT DISTINCT table_2.f1, table_1.f2, '2000-08-22' AS testdb-#date FROM table_1, table_2 WHERE table_1.f1 = table_2.f1; ERROR: Unable to identify an ordering operator '<' for type 'unknown' Use an explicit ordering operator or modify the query I must execute this query : testdb=#SELECT table_2.f1, table_1.f2, '2000-08-22' AS date FROM testdb-#table_1, table_2 WHERE table_1.f1 = table_2.f1 GROUP BY testdb-#table_2.f1, table_1.f2; Is it a bug ? Thanks in advance. Jerome.
Re: [SQL] get table schema in psql?
Hi! I think what you're looking for is \d and \d name_of_table You might also want to try \? for a list of all psql commands Regards, Patrik Kudo ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo un\2\1 is xg'`ol Känns det oklart? Fråga på! On Fri, 15 Sep 2000, Michael Teter wrote: > how can I get the schema of a table in psql? > > in oracle I would do "desc tablename". I remember sql > server and ingres having a similar facility. > > right now the only way I seem to be able to get the > information is by using pgaccess. > > I realize the definition of the table is probably > spread across the various pg_ system tables, but I'm > thinking there must be some easier way to get the > info. > > in particular, I'm after the list of columns, their > data types and lengths, null/non-null, default values, > and table indices. > > thanks, > michael > > __ > Do You Yahoo!? > Yahoo! Mail - Free email you can access from anywhere! > http://mail.yahoo.com/ >
Re: [SQL] get table schema in psql?
doh! thanks so much. somehow I thought \h and \? were the same, and I've only been doing \h :) no wonder I couldn't find it. michael --- Patrik Kudo <[EMAIL PROTECTED]> wrote: > Hi! > > I think what you're looking for is > > \d > > and > > \d name_of_table > > You might also want to try > > \? > > for a list of all psql commands > > Regards, > Patrik Kudo > > ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo > un\2\1 is xg'`ol > Känns det oklart? Fråga på! > > On Fri, 15 Sep 2000, Michael Teter wrote: > > > how can I get the schema of a table in psql? > > > > in oracle I would do "desc tablename". I remember > sql > > server and ingres having a similar facility. > > > > right now the only way I seem to be able to get > the > > information is by using pgaccess. > > > > I realize the definition of the table is probably > > spread across the various pg_ system tables, but > I'm > > thinking there must be some easier way to get the > > info. > > > > in particular, I'm after the list of columns, > their > > data types and lengths, null/non-null, default > values, > > and table indices. > > > > thanks, > > michael > > > > __ > > Do You Yahoo!? > > Yahoo! Mail - Free email you can access from > anywhere! > > http://mail.yahoo.com/ > > > __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
Re: [SQL] get table schema in psql?
type \d [tablename] at the psql console Michael Teter wrote: > how can I get the schema of a table in psql? > > in oracle I would do "desc tablename". I remember sql > server and ingres having a similar facility. > > right now the only way I seem to be able to get the > information is by using pgaccess. > > I realize the definition of the table is probably > spread across the various pg_ system tables, but I'm > thinking there must be some easier way to get the > info. > > in particular, I'm after the list of columns, their > data types and lengths, null/non-null, default values, > and table indices. > > thanks, > michael > > __ > Do You Yahoo!? > Yahoo! Mail - Free email you can access from anywhere! > http://mail.yahoo.com/
Re: [SQL] Error with DISTINCT
Jerome Raupach <[EMAIL PROTECTED]> writes: > testdb=#SELECT DISTINCT table_2.f1, table_1.f2, '2000-08-22' AS > testdb-#date FROM table_1, table_2 WHERE table_1.f1 = table_2.f1; > ERROR: Unable to identify an ordering operator '<' for type 'unknown' > Use an explicit ordering operator or modify the query > Is it a bug ? No, I don't think so. The system has no way to intuit what datatype you consider '2000-08-22' to be. SELECT DISTINCT table_2.f1, table_1.f2, '2000-08-22'::date ... would work. regards, tom lane
[SQL] Where to get benchmark testing
I'm not sure this is the right list for this but maybe someone could point me in the right direction. We are getting some DB servers to test performance. I was wondering if anyone knows where we can get a test script of sorts that would allow us to check performance on each of the servers. TIA
Re: [SQL] get table schema in psql?
Hi, there, You just need type psql> \d tablename use \? to get help Michael Teter wrote: > how can I get the schema of a table in psql? > > in oracle I would do "desc tablename". I remember sql > server and ingres having a similar facility. > > right now the only way I seem to be able to get the > information is by using pgaccess. > > I realize the definition of the table is probably > spread across the various pg_ system tables, but I'm > thinking there must be some easier way to get the > info. > > in particular, I'm after the list of columns, their > data types and lengths, null/non-null, default values, > and table indices. > > thanks, > michael > > __ > Do You Yahoo!? > Yahoo! Mail - Free email you can access from anywhere! > http://mail.yahoo.com/ -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Error with DISTINCT
Hi,
try this:
testdb=#SELECT DISTINCT table_2.f1, table_1.f2, date('2000-08-22') AS
testdb-#date FROM table_1, table_2 WHERE table_1.f1 = table_2.f1;
Jerome Raupach wrote:
> testdb=#SELECT DISTINCT table_2.f1, table_1.f2, '2000-08-22' AS
> testdb-#date FROM table_1, table_2 WHERE table_1.f1 = table_2.f1;
> ERROR: Unable to identify an ordering operator '<' for type 'unknown'
> Use an explicit ordering operator or modify the query
>
> I must execute this query :
>
> testdb=#SELECT table_2.f1, table_1.f2, '2000-08-22' AS date FROM
> testdb-#table_1, table_2 WHERE table_1.f1 = table_2.f1 GROUP BY
> testdb-#table_2.f1, table_1.f2;
>
> Is it a bug ?
> Thanks in advance.
>
> Jerome.
--
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.ipinc.com
Re: [SQL] get table schema in psql?
On Fri, 15 Sep 2000, Rommel B. Abaya wrote: > type \d [tablename] at the psql console And if you like that try: \d+ [tablename] Of course it's much more impressive if you've created any COMMENTs. Rod -- Roderick A. Anderson [EMAIL PROTECTED] Altoplanos Information Systems, Inc. Voice: 208.765.6149212 S. 11th Street, Suite 5 FAX: 208.664.5299 Coeur d'Alene, ID 83814
[SQL] installing pgaccess
Anybody know how to compile pgaccess from postgres source files? It must be a configure option, but I can't find it. Keith.
Re: [SQL] installing pgaccess
Keith Wong <[EMAIL PROTECTED]> writes: > Anybody know how to compile pgaccess from postgres source files? I think it's driven by configure --with-tcl. regards, tom lane
[SQL] New operators
I have a front end that builds queries based on a user selection of: Match type (=, <, >, ~, ~~, etc) Ignore/honor upper/lower case Negate the comparison I ended up putting the following operators in the backend so the operators all had parallel types of case/no-case options. This made the front end code much simpler. Would it be helpful to others to make these built-in operators? -- Case insensitive operators (for consistency with like operators) create function likenocase(text,text) returns boolean as ' begin return upper($1) like upper($2); end;' language 'plpgsql' with (iscachable); create operator ~~* (leftarg = text,rightarg = text,procedure = likenocase, negator = !~~*); create function nlikenocase(text,text) returns boolean as ' begin return upper($1) not like upper($2); end;' language 'plpgsql' with (iscachable); create operator !~~* (leftarg = text,rightarg = text,procedure = nlikenocase, negator = ~~*); create function eqnocase(text,text) returns boolean as ' begin return upper($1) = upper($2); end;' language 'plpgsql' with (iscachable); create operator =* (leftarg = text,rightarg = text,procedure = eqnocase, negator = !=*); create function neqnocase(text,text) returns boolean as ' begin return upper($1) != upper($2); end;' language 'plpgsql' with (iscachable); create operator !=* (leftarg = text,rightarg = text,procedure = neqnocase, negator = =*); begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard
[SQL] what utilities do i need for postgres on solaris 7 ??
Hello Everyone, I have an Intel system with solaris 7 installed on it. Now I have d/l'ed postgres 6.5.1 to install it on my system. 1.) What all the utilities are needed before I start compiling and installing the postgres s/w?. I mean to say utilities like gmake. But I could not get gmake from anywhere. 2.) I have read the INSTALL file but could not get how to compile and INSTALL the s/w on Solaris 7. Thanks for all the suggestions. Rajesh. __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
