[GENERAL] About table schema
Hi, does anyone know how to get the schema information of a table. More specifically, I would like to know 1. which field(s) are primary keys? 2. the data type of each field of a table? 3. If a field is a foreign key, what field/table it is referring to. Thanks. Wei __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] About table schema
Am Mittwoch, 26. Mai 2004 00:36 schrieb Wei Shi: > Hi, does anyone know how to get the schema information > of a table. More specifically, I would like to know > > 1. which field(s) are primary keys? > 2. the data type of each field of a table? > 3. If a field is a foreign key, what field/table it > is referring to. connect to your database with 'psql' and type \d tablename which gives you all information you asked for. Type \? for a list of other commands within psql and take a look at 'man psql' kind regards, janning ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] unix_socket
I'm trying to configure postfix to use the postgres database for lookups. Since I have a localhost based server, I was going to use unix_sockets. The postgres docs call for only a /directory. unix_socket_directory (string) Specifies the directory of the Unix-domain socket on which the server is to listen for connections from client applications. The default is normally /tmp, but can be changed at build time. The postfix docs call for a /directory/name. inet: for TCP connections (default). Example: hosts = host1.some.domain host2.some.domain hosts = unix:/file/name -- How do I get this sorted out so that I can make a unix connection? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] unix_socket
Tom Allison <[EMAIL PROTECTED]> writes: > I'm trying to configure postfix to use the postgres database for lookups. > Since I have a localhost based server, I was going to use unix_sockets. > The postfix docs call for a /directory/name. >inet: for TCP connections (default). Example: >hosts = host1.some.domain host2.some.domain >hosts = unix:/file/name I suspect you want to say unix:/tmp/.s.PGSQL.5432 (adjust if you are using a nonstandard port number) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] unix_socket
Tom Allison wrote: I'm trying to configure postfix to use the postgres database for lookups. Since I have a localhost based server, I was going to use unix_sockets. The postgres docs call for only a /directory. unix_socket_directory (string) Specifies the directory of the Unix-domain socket on which the server is to listen for connections from client applications. The default is normally /tmp, but can be changed at build time. I found that my Debian installation had unix_socket_directory built to unix_socket_directory='' and in order to use sockets, you have to explicitly call it out. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] timestamps
How would I specify a field that's to be updated to current_time everytime the row is created/altered? Is there some way to put this 'update' property into the table instead of running some query to do it? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Poatgresql database on more than one disk
Barry wrote: Hi All, I am a newcommer to Postgresql, currently I am looking at moving a Pick based application across to PostgreSQL. I am using RH Linux and Postgresql 7.3.6 The test system I am using has a 2 channel raid card with a disk pack connected to each channel. The OS and Postgresql sits on its own internal disk. I would like to be able to use one disk pack for development and the other for end user testing / verification (the tape drive can only backup one disk pack completely, not both) I have spent a fair amount of time researching how I can run two databases, one on each disk pack but have not been able to find a solution. Is it possible to configure Postgresql to have seperate databases on seperate disks ? Not easily as will be with the Table Space feature that most probably will be present on 7.5 With 7.3.6 what you can do is move your db and create a link in the original place: -bash-2.05b$ oid2name All databases: - 17142 = kalman 19185 = photodb 27895 = empdb 1 = template1 17141 = template0 5776262 = logs -bash-2.05b$ pwd /var/lib/pgsql/data/base -bash-2.05b$ ll total 32 drwx--2 postgres postgres 4096 Feb 8 15:18 1 drwx--2 postgres postgres 4096 Feb 8 03:56 17141 drwx--2 postgres postgres 4096 May 25 19:37 17142 drwx--2 postgres postgres 8192 Feb 8 15:58 19185 drwx--3 postgres postgres 8192 May 16 02:46 27895 drwx--2 postgres postgres 4096 May 18 00:06 5776262 in my case if I want the database kalman to another disk what I have to do is: stop the engine, move the directory /var/lib/pgsql/data/base/17142 to the new location and create the link. With the same mechanism you can also place different tables in different disks. I hope this help you. Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] timestamps
--On Tuesday, May 25, 2004 20:52:22 -0400 Tom Allison <[EMAIL PROTECTED]> wrote: How would I specify a field that's to be updated to current_time everytime the row is created/altered? Is there some way to put this 'update' property into the table instead of running some query to do it? You are looking for a trigger. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp52eVIo5gTa.pgp Description: PGP signature
Re: [GENERAL] timestamps
Tom Allison <[EMAIL PROTECTED]> writes: > How would I specify a field that's to be updated to current_time > everytime the row is created/altered? Create a trigger. There are some good examples in the PL/pgSQL docs. > Is there some way to put this 'update' property into the table instead > of running some query to do it? Triggers are the way to go in Postgres. -Doug ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] timestamps
Doug McNaught wrote: Tom Allison <[EMAIL PROTECTED]> writes: How would I specify a field that's to be updated to current_time everytime the row is created/altered? Create a trigger. There are some good examples in the PL/pgSQL docs. Is there some way to put this 'update' property into the table instead of running some query to do it? Triggers are the way to go in Postgres. -Doug Thanks for the pointer. That should be enough. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] About table schema
Is there a function interface to get this information other than from "psql" comman line interface? Thanks. Wei --- Janning Vygen <[EMAIL PROTECTED]> wrote: > Am Mittwoch, 26. Mai 2004 00:36 schrieb Wei Shi: > > Hi, does anyone know how to get the schema > information > > of a table. More specifically, I would like to > know > > > > 1. which field(s) are primary keys? > > 2. the data type of each field of a table? > > 3. If a field is a foreign key, what field/table > it > > is referring to. > > connect to your database with 'psql' and type > > \d tablename > > which gives you all information you asked for. Type > \? for a list of other > commands within psql and take a look at 'man psql' > > kind regards, > janning > > ---(end of > broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] About table schema
Wei Shi wrote: Is there a function interface to get this information other than from "psql" comman line interface? If you start psql with the '-E' switch, you'll see the queries it generates to display the meta-data associated with the '\' command you issue. You may also query the tables and views in the information_schema for what you want. Mike Mascari ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] About table schema
This is great. Thanks. Wei --- Mike Mascari <[EMAIL PROTECTED]> wrote: > Wei Shi wrote: > > Is there a function interface to get this > information > > other than from "psql" comman line interface? > > If you start psql with the '-E' switch, you'll see > the queries it > generates to display the meta-data associated with > the '\' command > you issue. > > You may also query the tables and views in the > information_schema > for what you want. > > Mike Mascari > > > ---(end of > broadcast)--- > TIP 2: you can get off all lists at once with the > unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] planer don't use index. bad plan for where id = x or id in (select ...)
Hello, Pg make query 1. and 2. very fast (use index), but for query 3. dont use index. I can solve its using select union, but I readed so pg 7.5 don't problem with OR operator. I use cvs pg. I used vacuum analyze first. table sp_op_product has 15K rows, sp_op_uct 37K rows; regards Pavel Stehule query 1. intra=# explain analyse select * from sp_op_uct where sp_op_id in (select sp_op_id from sp_op_produkt where bal_zak = 34123); QUERY PLAN -- Nested Loop (cost=7.81..74.59 rows=17 width=371) (actual time=0.241..0.249 rows=1 loops=1) -> HashAggregate (cost=7.81..7.81 rows=12 width=4) (actual time=0.118..0.120 rows=1 loops=1) -> Index Scan using sp_op_product_bal_zak on sp_op_produkt (cost=0.00..7.78 rows=12 width=4) (actual time=0.077..0.082 rows=1 loops=1) Index Cond: (bal_zak = 34123) -> Index Scan using sp_op_uct_sp_op_id on sp_op_uct (cost=0.00..5.54 rows=2 width=371) (actual time=0.068..0.072 rows=1 loops=1) Index Cond: (sp_op_uct.sp_op_id = "outer".sp_op_id) Total runtime: 1.846 ms query 2. intra=# explain ANALYZE select * from sp_op_uct where sp_op_id = 34123; QUERY PLAN Index Scan using sp_op_uct_sp_op_id on sp_op_uct (cost=0.00..5.54 rows=2 width=371) (actual time=0.066..0.072 rows=1 loops=1) Index Cond: (sp_op_id = 34123) Total runtime: 0.287 ms slow query 3. intra=# EXPLAIN ANALYZE select * from sp_op_uct where sp_op_id = 34123 or sp_op_id in (select sp_op_id from sp_op_produkt where bal_zak = 34123) ; QUERY PLAN -- Seq Scan on sp_op_uct (cost=7.81..1283.83 rows=18602 width=371) (actual time=3.176..240.379 rows=2 loops=1) Filter: ((sp_op_id = 34123) OR (hashed subplan)) SubPlan -> Index Scan using sp_op_product_bal_zak on sp_op_produkt (cost=0.00..7.78 rows=12 width=4) (actual time=0.441..0.449 rows=1 loops=1) Index Cond: (bal_zak = 34123) Total runtime: 240.868 ms Fast query 4. intra=# explain ANALYZE select * from sp_op_uct where sp_op_id in (select sp_op_id from sp_op_produkt where bal_zak = 34123) union select * from sp_op_uct where sp_op_id = 34123; QUERY PLAN -- Unique (cost=80.72..82.39 rows=19 width=371) (actual time=0.857..0.868 rows=2 loops=1) -> Sort (cost=80.72..80.77 rows=19 width=371) (actual time=0.852..0.854 rows=2 loops=1) Sort Key: sp_op_id, perioda, uozac, uokon, setup, timely, uamount, "LIMIT", krok, kdy, uctuj, tarif, jednotka, merfunc, tarif_id, hl_cinnost, merene, typ_fakturace, region, prod_group, rychlost, sdilene, produkt, vl_sluzba, per_jedn, vpn, prov_inst, pevne_komutovane, spravnost, fakt_text, pres_rychlost, pm, pm2, sp_kvalita -> Append (cost=7.81..80.32 rows=19 width=371) (actual time=0.247..0.317 rows=2 loops=1) -> Subquery Scan "*SELECT* 1" (cost=7.81..74.76 rows=17 width=371) (actual time=0.244..0.256 rows=1 loops=1) -> Nested Loop (cost=7.81..74.59 rows=17 width=371) (actual time=0.197..0.205 rows=1 loops=1) -> HashAggregate (cost=7.81..7.81 rows=12 width=4) (actual time=0.096..0.098 rows=1 loops=1) -> Index Scan using sp_op_product_bal_zak on sp_op_produkt (cost=0.00..7.78 rows=12 width=4) (actual time=0.040..0.044 rows=1 loops=1) Index Cond: (bal_zak = 34123) -> Index Scan using sp_op_uct_sp_op_id on sp_op_uct (cost=0.00..5.54 rows=2 width=371) (actual time=0.045..0.050 rows=1 loops=1) Index Cond: (sp_op_uct.sp_op_id = "outer".sp_op_id) -> Subquery Scan "*SELECT* 2" (cost=0.00..5.56 rows=2 width=371) (actual
