472,374 Members | 1,455 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,374 software developers and data experts.

HOWTO: Get a table or database definition

I guess it would be great if Pgsql had a way to find a database
definition via a system stored procedure like other database platforms

There are two ways I've found so far:

attname as "name", typname as "type", atttypmod - 4 as "size",
relhaspkey as "is_primary_key", *
pg_class AS a
LEFT OUTER JOIN pg_attribute AS b ON (b.attrelid = a.oid)
LEFT OUTER JOIN pg_type AS c ON (b.atttypid = c.oid)
where a.relname = 'names' and b.attstattarget = -1
order by attnum;

....yields great results for a table called 'names'

The other way is:

pg_dump -h localhost -p 5432 -U root -s -C test | grep -i "CREATE" -A
500000 | grep -v "\-\-" | grep -v "\\connect" | grep -v "SET " | tr -s

....shows me a result for host 'localhost', port '5432', user 'root',
database 'test'
Nov 23 '05 #1
2 3521
One other option, which I had forgotten for a long time, was:

\d <object name>

....which can describe many things, although this doesn't give you the
CREATE syntax like a pg_dump can do. Please also note that a pg_dump
can dump output to the screen if you don't specify a file, so if
you're only outputting the schema with "-s -C", it doesn't really
impact a live production database much at all.
Nov 23 '05 #2
Google Mike wrote:
I guess it would be great if Pgsql had a way to find a database
definition via a system stored procedure like other database platforms

There are a few:

nexcerpt=# \df pg_get*def
List of functions
Result data type | Schema | Name | Argument data
text | pg_catalog | pg_get_constraintdef | oid
text | pg_catalog | pg_get_constraintdef | oid, boolean
text | pg_catalog | pg_get_indexdef | oid
text | pg_catalog | pg_get_indexdef | oid, integer,
text | pg_catalog | pg_get_ruledef | oid
text | pg_catalog | pg_get_ruledef | oid, boolean
text | pg_catalog | pg_get_triggerdef | oid
text | pg_catalog | pg_get_viewdef | oid
text | pg_catalog | pg_get_viewdef | oid, boolean
text | pg_catalog | pg_get_viewdef | text
text | pg_catalog | pg_get_viewdef | text, boolean
(11 rows)
It looks like 'pg_get_tabledef' isn't one of them, though.

(Posted from an account used as a SPAM dump. If you really want to get
in touch with me, dump the 'jboes' and substitute 'mur'.)
Jeffery Boes <>< jb***@qtm.net
Nov 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Dylan Nicholson | last post by:
Seems that Oracle 9.2 (using MS ODBC driver) requires extra parentheses when adding multiple columns to a table: ALTER TABLE MyTable ADD (MyColumn1 VARCHAR(255), MyColumn2 VARCHAR(255)) vs ...
by: Toby Austin | last post by:
I'm trying to replace <table>s with <div>s as much as possible. However, I can't figure out how to do the following… <table> <tr> <td valign="top" width="100%">some data that will...
by: Vladimir | last post by:
Hello, I have a table in MS Access database. It has one field (with BYTE datatype) that has several properties set in Lookup tab of table Design View. Display Control = Combo Box. Row Source...
by: Plumer | last post by:
Good morning everyone Using C# I create a bog-standard System.Data.DataTable which I then Add to a System.Data.Dataset (At the moment I'm in the guess-and-test phase so the table has only one...
by: Ray Dukes | last post by:
What I am looking to do is map the implementation of interface properties and functions to an inherited method of the base class. Please see below. ...
by: Killer42 | last post by:
Hi all. Thought I'd share a recent experience with you, as a cautionary tale. I have a reasonably large-ish database (around 400MB) holding a single table. One of the fields is a pointer to a...
by: =?Utf-8?B?QVRT?= | last post by:
HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10g. Please help, I'm trying to write an ASP page to use ADO to run a long query against an Oracle 10g database, to create tables,...
by: Killer42 | last post by:
Hi all. Ok, I'm using VB6 but I think the answer to this (if there is one) is more likely to be found in the Access forum. I have a situation where I've got tens of millions of records, spread...
by: billelev | last post by:
I'm building a database and am a bit stumped about how to construct/link tables. I will describe the current configuration, then present the problem I am trying to solve. Currently: I...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.
by: DizelArs | last post by:
Hi all) Faced with a problem, element.click() event doesn't work in Safari browser. Tried various tricks like emulating touch event through a function: let clickEvent = new Event('click', {...
by: F22F35 | last post by:
I am a newbie to Access (most programming for that matter). I need help in creating an Access database that keeps the history of each user in a database. For example, a user might have lesson 1 sent...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.