Re: [GENERAL] column names query

[stimits]

SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name = 'your_name';
 
- Original Message -From: hamann w To: 
pgsql-general@postgresql.orgSent: Thu, 07 Sep 2017 07:18:12 - (UTC)Subject: 
[GENERAL] column names query



Hi,

is there a simple way to retrieve column names from a query - basically the way 
psql addscolumn headings when I do a select?

Best regardsWolfgang Hamann



-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)To make 
changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Queries for Diagramming Schema Keys

[stimits]

...

> Just to add that running psql with the -E switch is REALLY handy for seeing 
> how psql executes queries to
> find how tables etc are put together.

 
I can't actually use that feature to gather the information I'm interested in 
since all I have are tables and data with no formal information on key 
relations and allowed load order. There is so much data in so many tables that 
testing even a single load iteration takes many hours and there are literally 
many thousands of load order combinations possible. Logs of hundreds of 
thousands (or millions) of XML loads would take a very long time to go through, 
and would then only give one possible load order.
 
Automating a diagram of key relations and visualizing it is the first step to 
methodically computing a correct load order, but I can't do that until I figure 
out how to use the system tables to describe (1) columns which are not keys, 
(2) columns which are primary keys not referring to another column, and (3) 
columns which are foreign keys and the table/column they are pointed at. My SQL 
knowledge is somewhat limited and I am struggling with the system tables.

Re: [GENERAL] Queries for Diagramming Schema Keys

[stimits]

...snip...
For all tables and columns I get no output at all. Basically I need all tables 
and columns for which the column is neither a regular primary key, nor a 
foreign key. This will be marked as an attribute in the diagramming program.
 
Your query for primary keys which are not foreign keys seems to be magic :) 
I'll have to dig through some of the tables (nearly 1000 of them) to validate, 
but it seems to work with much less complication than my original query (and I 
think my original had issues...I forgot to mention that I had been relying on 
the columns which implemented values which foreign keys had referenced were 
assumed to have the same column names...mostly this had been true, but not 
always, so my original approach was a guaranteed failure).
 
For foreign keys I need a row with both the table and column which is a foreign 
key, and the specific table and column it maps to. My mapping program will be 
adding a double-linked list among keys for validation and for interactive use 
of an SVG image being produced.
 
So I'm still looking for 
"original_table.fk_column->implementing_table.implementing_column", plus the 
list of "table.column" where "column" is not a key.
 

>I am completely at a loss how I would query for all columns

Does this help?
 
-- TABLES AND COLUMNS
SELECT c.table_schema as schema,   c.table_name as table,
c.ordinal_position as order,   c.column_name as column,   CASE WHEN 
c.data_type IN ('character', 'varchar') THEN c.data_type || '(' || 
c.character_maximum_length || ')'WHEN TRIM(c.data_type) IN 
('numeric')THEN c.data_type || '(' || c.numeric_precision_radix || ',' 
  ||  c.numeric_scale || ')'   ELSE c.data_typeEND,   
c.is_nullable as null,   col_description(t.oid, c.ordinal_position) as 
comment  FROM information_schema.columns c  JOIN pg_class t ON (t.relname = 
c.table_name) WHERE table_schema = 'public'   AND c.table_name = 'album'ORDER 
BY 1, 2, 3;

-- TABLES AND PKEYS
SELECT n.nspname,   t.relname as table,c.conname as pk_name  FROM 
pg_class t  JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype = 'p')  
JOIN pg_namespace n  ON (n.oid = t.relnamespace) WHERE relkind = 'r'   AND 
t.relname NOT LIKE 'pg_%'   AND t.relname NOT LIKE 'sql_%'   ORDER BY 
n.nspname, t.relname, c.conname;
 
-- TABLES and FKEYS
SELECT n.nspname as schema,t.relname as table,c.conname as 
fk_name  FROM pg_class t  JOIN pg_namespace n ON n.oid = t.relnamespace  JOIN 
pg_constraint c ON ( c.conrelid = t.OID AND c.contype = 'f') WHERE relkind = 
'r'   AND t.relname NOT LIKE 'pg_%'   AND t.relname NOT LIKE 'sql_%'   ORDER BY 
n.nspname, t.relname, c.conname;




Melvin DavidsonI reserve the right to fantasize.  Whether or not you wish to 
share my fantasy is entirely up to you. 

[GENERAL] Queries for Diagramming Schema Keys

[stimits]

Hi,
 
I'm trying to write an application (using libpqxx/C++) which creates graphical 
images of large and complex relations between tables (basically an SVG image 
with hot spots for drilling down on details). I need to essentially create 
icons of tables and their columns, and then draw interconnecting lines where 
foreign keys are involved, and to distinctly label primary keys, foreign keys, 
and non-key columns. Eventually this will have knowledge of an XML file loading 
scheme and be able to reverse engineer the required load order (imagine 
approximately 1,000 tables with many foreign keys and file loads which may take 
hours for each failure to load). I need some advice on using 
ANSI/information_schema queries to accomplish this.
 
Thus I have this query to list all tables:
SELECT table_name  FROM information_schema.tablesWHERE table_schema='public'   
AND table_type='BASE TABLE';
 
...this seems to work ok. The next query is to find all foreign keys...this 
seems to sort of work, but may have issues:
SELECTtc.table_name AS local_table,kcu.column_name AS key_column,
ccu.table_name AS fk_table,ccu.column_name AS fk_column FROM 
information_schema.table_constraints AS tc JOIN 
information_schema.key_column_usage AS kcu  ON tc.constraint_name = 
kcu.constraint_nameJOIN information_schema.constraint_column_usage AS ccu   
   ON ccu.constraint_name = tc.constraint_nameWHERE constraint_type = 'FOREIGN 
KEY' AND tc.table_nameIN (SELECT table_name  FROM 
information_schema.tablesWHERE table_schema='public'   AND table_type='BASE 
TABLE');
 
This is my query to find all primary keys which are not foreign keys, and this 
definitely is not 100% correct:
SELECT DISTINCTtc.table_name AS local_table,kcu.column_name AS 
key_columnFROM information_schema.table_constraints AS tc JOIN 
information_schema.key_column_usage AS kcu  ON tc.constraint_name = 
kcu.constraint_nameJOIN information_schema.constraint_column_usage AS ccu   
   ON ccu.constraint_name = tc.constraint_nameWHERE constraint_type = 'PRIMARY 
KEY' AND tc.table_nameIN (SELECT table_name  FROM information_schema.tables 
WHERE table_schema='public'   AND table_type='BASE TABLE')AND (tc.table_name, 
kcu.column_name)NOT IN (SELECTtc.table_name,kcu.column_nameFROM
information_schema.table_constraints AS tc JOIN 
information_schema.key_column_usage AS kcu  ON tc.constraint_name = 
kcu.constraint_nameJOIN information_schema.constraint_column_usage AS ccu   
   ON ccu.constraint_name = tc.constraint_nameWHERE constraint_type = 'FOREIGN 
KEY' AND tc.table_nameIN (SELECT table_name  FROM information_schema.tables 
WHERE table_schema='public'   AND table_type='BASE TABLE'))ORDER BY 
local_table, key_column;
 
I am completely at a loss how I would query for all columns which are neither 
primary nor foreign keys. Would anyone have a suggestion for something like 
this:
SELECT table_name, non_key_column
FROM ...
WHERE ...
ORDER BY table_name, non_key_column
 
Any advice on any of the queries would be appreciated!
 
Thanks!

Re: [GENERAL] Random not so random

[D. Stimits]

Vivek Khera wrote:
DS == D Stimits [EMAIL PROTECTED] writes:

DS If it uses the same seed from the connection, then all randoms within
DS a connect that has not reconnected will use the same seed. Which means
DS the same sequence will be generated each time, which is why it is
DS pseudo-random and not random. For it to be random not just the first
DS call of a new connection, but among all calls of new connection, it
DS would have to seed it based on time at the moment of query and not at
DS the moment of connect. A pseudo-random generator using the same seed
DS will generate the same sequence.
You clearly demonstrate you do not understand the purpose of a seed in
a PRNG, nor how PRNG's in general work.  You want to seed it once and
only once per process, not every time you issue a query.  And nobody
said to use the same seed every time, either.
Sorry, at the time I don't believe PRNG was part of the conversation, 
that came after (maybe I missed that). What I saw were functions based 
on one-way hashes...is that incorrect? For one-way hashes and 
pseudo-random generators using some form of hash a different seed should 
be used or else the pattern will be the same when using the same data. 
From the man page on srandom():

   The srandom() function sets its argument as the seed for a new 
sequence  of  pseudo-random
   integers  to be returned by random().  These sequences are 
repeatable by calling srandom()
   with the same seed value.  If no seed value is provided, the 
random() function is automat-
   ically seeded with a value of 1.

The srandom() caught my eye in the earlier email, not PRNG. You are 
welcome to re-use srandom() without a new seed if you want.

D. Stimits, stimits AT comcast DOT net
---(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

Re: [GENERAL] Random not so random

[D. Stimits]

Tom Lane wrote:
Arnau Rebassa [EMAIL PROTECTED] writes:
 I'm using a debian linux as OS with a 2.4 kernel running on it.

Incidentally, are you reconnecting every time or is it that multiple calls 
in a single session are returning the same record?

 I'm reconnecting each time I want to retrieve a message.

Hmm.  postmaster.c does this during startup of each backend process:
gettimeofday(now, tz);
srandom((unsigned int) now.tv_usec);
If it uses the same seed from the connection, then all randoms within a 
connect that has not reconnected will use the same seed. Which means the 
same sequence will be generated each time, which is why it is 
pseudo-random and not random. For it to be random not just the first 
call of a new connection, but among all calls of new connection, it 
would have to seed it based on time at the moment of query and not at 
the moment of connect. A pseudo-random generator using the same seed 
will generate the same sequence.

D. Stimits, stimits AT comcast DOT net
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] import/export or moving data between databases

[D. Stimits]

Iavor Raytchev wrote:
Dear Tom,
We kind of read all documentation we could find, but that was the only way
we could get -
- export db structure into sql file
- export the records we need into another sql file
- import structure
- turn off triggers
- import the records
- turn on triggers
The main problem is that we export selected records - not the whole database
and not even whole tables. Also we export 5 000 to 15 000 such sets that
have small size - just a fraction of the size of the main database.
Can pg_dump help in that more than we use it?

Why not select into temp tables as a new database then pg_dump the temp, 
followed by restore of temp as though it were the whole thing?


Best,
Iavor
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Sunday, September 19, 2004 6:45 PM
To: Iavor Raytchev
Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] import/export or moving data between databases
Iavor Raytchev [EMAIL PROTECTED] writes:
The problem we face is -

   When we start to import - the triggers are executed - which must not
happen. We found a way to turn the triggers off for the time of import and
then turn them on after the import. However we can turn the triggers off
only per table - so we need the list of tables, but we have not found a
reliable way to get it. We can get them from the pg_ system tables - but
this means if there is a change in them in next version - we need to change
our software, which is not very desirable.
It sounds to me like you have reinvented pg_dump ... and not done it
very well.  Why don't you just use pg_dump?
regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] [INTERFACES] more on undefined reference to 'pg_detoast_datum'

[D. Stimits]

Tom Lane wrote:

 D. Stimits  writes:

 ... My question is, where the heck is
 CurrentMemoryContext and MemoryContextAlloc provided as an
 implementation which I can link with?


 They're inside the backend, and you don't --- you are not trying to
 build a standalone executable with no unresolved symbols, but a shared
 library that can be successfully loaded into the backend.  It's okay
 for such a library to have unresolved references to symbols that will be
 resolved at load time.

 regards, tom lane


Thanks to all that answered this. I found the problem, and it was 
totally unexpected. There were some leftover Makefile lines that were 
used to build a standalone module loader test unit, that test unit does 
not implement palloc. The actual dynamic loadable module was in fact 
working right, but the make was dying early when it so much as 
referenced the old module loader code (a loader used to test/regress 
functions before trying it on a server). I couldn't believe my eyes when 
I saw those old lines in there.

D. Stimits

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] PL/PGSQL for permutations?

[D. Stimits]

Joe Conway wrote:

D. Stimits wrote:

 table field pair. E.G., if I had in table 'one':
 left  right
   =
 a b
 a c
 b d

 ...then I'd need a list of a, b, c, d, and produce a new table:
 left  right
   =
 a b
 a c
 a d
 b a
 b c
 b d
 c a
 c b
 c d
 d a
 d b
 d c
I don't have 7.2 to test on (and you really should upgrade to 7.3.4 if
possible anyway), but why not:
create table t1(f1 text, f2 text);
insert into t1 values('a','b');
insert into t1 values('a','c');
insert into t1 values('b','d ');
select a, b
from
  (select distinct f1 as a from t1 union select distinct f2 from t1)
   as ss1,
  (select distinct f1 as b from t1 union select distinct f2 from t1)
   as ss2
where ss1.a != ss2.b;
 a  | b
+
 a  | b
 a  | c
 a  | d
 b  | a
 b  | c
 b  | d
 c  | a
 c  | b
 c  | d
 d  | a
 d  | b
 d  | c
(12 rows)
This worked quite well, thank you! I'm still in need though of learning 
more about PL/PGSQL, as I have other programming to add (well, I could 
do this in C as a PostgreSQL C function, but I want to stick to PL/PGSQL 
for the moment). I'm still looking for a non-trivial, in-depth, full 
reference to PL/PGSQL. I've found many good introductory or tutorial 
type web pages, but not a full and complete reference to PL/PGSQL. The 
permutations were themselves the easy part, now each permutation has to 
do some non-trivial combinatorics on trigger whenever a change is made.

D. Stimits



---(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] undefined reference to 'pg_detoast_datum'

[D. Stimits]

In PostgreSQL 7.2 (Redhat 7.3 version, so it is patched), I'm trying to 
create a Version-1 C server extension. The Version-0 format works, the 
Version-1 version fails with:
 undefined reference to 'pg_detoast_datum'

According to docs at:
 http://www.postgresql.org/docs/7.2/interactive/xfunc-c.html
...by using Version-1 the pg_detoast_datum is no longer needed. FYI, the 
function being created takes and returns a text argument. Are the docs 
there wrong, or is there a bug in the library build, that causes 
Version-1 to require linking to a lib with pg_detoast_datum?

A google search shows very little concerning the pg_detoast_datum 
undefined reference link error. It is looking more like the V1 has to be 
skipped and I'll have to go back to V0 if I can't get this to work. [and 
unless someone can magically make the default PostgreSQL version on RH 
7.3 change to a newer version of PostgreSQL, then a newer version can't 
be used]

D. Stimits

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] undefined reference to 'pg_detoast_datum'

[D. Stimits]

Joe Conway wrote:

D. Stimits wrote:

 A google search shows very little concerning the pg_detoast_datum
 undefined reference link error. It is looking more like the V1 has to
 be skipped and I'll have to go back to V0 if I can't get this to work.
You haven't shown us your function, so it's a bit difficult to help, but
in any case you do *not* want to use the V0 calling conventions -- they
are deprecated and subject to removal in the future. V1 certainly works
-- I've done many V1 C functions that accept text arguments. Take a look
at some of the contrib folder extensions if you need examples.
 unless someone can magically make the default PostgreSQL version on RH
 7.3 change to a newer version of PostgreSQL, then a newer version
 can't be used]
Why not? I have Postgres 7.3.4 running on my RH 7.3 server. Here are RH
7.3 RPMs:
ftp://ftp8.us.postgresql.org/pub/pgsql/binary/v7.3.4/RPMS/redhat-7.3/
I'll consider this, but it greatly complicates things to require users 
add the non-redhat version. I understand the benefits of doing so, but 
convincing people to do this just to try out a package is not so easy.

Joe


Here is a simple echo function that is being used:

#include pgsql/server/postgres.h
#include string.h
#include pgsql/server/fmgr.h
PG_FUNCTION_INFO_V1(reverse_path);
Datum my_echo(PG_FUNCTION_ARGS)
{
text* arg = PG_GETARG_TEXT_P(0);
text* echo_arg = (text *)palloc(sizeof(arg));

memcpy(
(void *)(echo_arg),
(void *)(arg),
sizeof(arg)
);

PG_RETURN_TEXT_P(echo_arg);
}
Keep in mind that this isn't being run yet, it fails at link stage. As 
another test, I have simplified the above just to test link time (not 
intended to run, just as a test of link/compile):

#include pgsql/server/postgres.h
#include pgsql/server/fmgr.h
PG_FUNCTION_INFO_V1(reverse_path);
Datum my_echo(PG_FUNCTION_ARGS)
{
text* arg = PG_GETARG_TEXT_P(0);

PG_RETURN_TEXT_P(arg);
}
The first function complains at link time of missing link function 
pg_detoast_datum, CurrentMemoryContext, and MemoryContextAlloc, while 
the latter complains only of missing pg_detoast_datum.

In both cases, link libraries are:
  -lkrb5 -lk5crypto -lcom_err -lpq -ldl
Now I am still scratching my head, wondering how it is that 
pg_detoast_datum is a V0 function, and I can compile V0 libraries just 
fine, but can't even compile a V1, which supposedly does not use 
pg_detoast_data? The additional link failures when using palloc make me 
feel there is some mysterious unnamed library that is missing when using 
V1, aside from -lpq. When I go in to compile the src/test/regress/ 
directory of the source from the rpm used in Redhat 7.3, I see no signs 
that anything else is required. Something simple must be missing under 
the PostgreSQL 7.2 version. Is there a separate library that needs 
linking under a V1 interface, in addition to those named above?

D. Stimits

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] C API, PQconnectdb and options Q.

[D. Stimits]

D. Stimits wrote:

I'm using a Redhat version of PostgreSQL 7.2.3 with the C API. Mostly
things work right, but I need more debug output, as I have a query that
works fine from psql, but fails with the C API (perhaps this is because
I use PQescapeString). The only way to know there is an error is that
the insert never happens, and that the system log reports a parse error
at or near the first field of an insert. What I am wonder is (a) how to
use the tty= in the string passed to PQconnectdb, and (b) a reference
URL for what options are available in the options= part of the string
parameters accepted by PQconnectdb. So far all I end up with if I try to
name a file for tty= or a tty from /dev/ for tty= is a core dump.
D. Stimits

I found a partial answer to this...it seems to be a bug in 
PQescapeString(). Turns out that if I do something with an insert using 
quotes for a varchar field, e.g.:
 INSERT INTO foo VALUES ('bar')

...then it escapes this to:
 INSERT INTO foo VALUES (''bar'')
It doesn't like the pair of single quotes.

But I also can't do this, due to requirements of SQL syntax:
 INSERT INTO foo VALUES (bar)
How can I use PQescapeString() with input I would like to make somewhat 
safer via escaping? How would I use PQescapeString() without writing my 
own replacement that works with inserts?

D. Stimits

---(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