[SQL] How to Get Column Names from the Table
Dear All, How to get Column Names from Table in PostgreSQL. Thanks and Regards, Venkat
Re: [SQL] How to Get Column Names from the Table
\d tableName On Wed, Jul 7, 2010 at 11:08 AM, venkat wrote: > Dear All, > >How to get Column Names from Table in PostgreSQL. > > Thanks and Regards, > > Venkat > -- --- Viktor Bojović --- Wherever I go, Murphy goes with me
Re: [SQL] How to Get Column Names from the Table
In response to venkat : > Dear All, > > How to get Column Names from Table in PostgreSQL. select column_name from information_schema.columns where table_name = 'your_table'; Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to Get Column Names from the Table
Thanks alot Andreas...It is working fine... Thanks again. Venkat On Wed, Jul 7, 2010 at 2:44 PM, A. Kretschmer < andreas.kretsch...@schollglas.com> wrote: > In response to venkat : > > Dear All, > > > >How to get Column Names from Table in PostgreSQL. > > select column_name from information_schema.columns where table_name = > 'your_table'; > > > Regards, Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] [GENERAL] How to Get Column Names from the Table
> Use: > \d tablename And what I really like about it is the way you can make a guess about the table name and use * . postgres-# \d mt* Table "public.mt1" Column | Type | Modifiers +-+--- id | integer | Table "public.mt2" Column | Type | Modifiers +-+--- id | integer | Table "public.mt3" Column | Type | Modifiers +-+--- id | integer | Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [GENERAL] [SQL] How to Get Column Names from the Table
Dear Sreelatha. It is working fine. Thanks alot. Thanks and Regards, Venkat On Wed, Jul 7, 2010 at 3:42 PM, Sreelatha G wrote: > Hi, > > To get column names only > > select column_name from information_schema.columns where > table_name='captor_prime_aggregates'; > > Thanks > Sreelatha > On Wed, Jul 7, 2010 at 2:44 PM, Viktor Bojović > wrote: > >> \d tableName >> >> >> On Wed, Jul 7, 2010 at 11:08 AM, venkat wrote: >> >>> Dear All, >>> >>>How to get Column Names from Table in PostgreSQL. >>> >>> Thanks and Regards, >>> >>> Venkat >>> >> >> >> >> -- >> --- >> Viktor Bojović >> --- >> Wherever I go, Murphy goes with me >> > >
Re: [SQL]
On 7/7/2010 12:00 AM, silly sad wrote: > On 07/06/10 21:52, Justin Graf wrote: > >> I wrote an article covering this on the wiki >> >> http://wiki.postgresql.org/wiki/BinaryFilesInDB >> > there are some "red flags" in communication > (particularly reading papers) > one of them is "binary data" which ITSELF IS NONSENCE. > WHAT??? You do understand that if you don't like it you can spend time fixing it. All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. <> -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] strangest thing happened
I am the only developer, DBA etc.. for a small project. Today (yesterday was everything was perfect) many of the sequence numbers fell behind what is the actual PK value. For example the invoice PK sequence current value = 1056 but the table PK was 1071. Nobody (other than myself) knows how to edit/access the postgres server. So 1. Does anyone know how this could have happened?? Other than human interaction. 2. Does anyone have a script to reset the sequences to match the tables? Thanks in advance, Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] strangest thing happened
Are you using PG's sequence/auto increment???
If so.
Once PG fires off the nextval() for the sequence that number is
considered used and gone even if the transaction that called nextval()
is rolled back
Depending on how the app is written nextval() might be called, but allow
the User to cancel the invoice creation before the insert into table is
completed eating up Invoice numbers
To reset Sequences number call
Select setval('Sequence_Name', VAlue_To_Set_To);
Most people ignore this kind of annoyance when sequence numbers jump.
Now if it happens all the time where every X hours eating up Z number of
sequence numbers then one needs to dig into the logs and figure out what
is calling nextval()
Search the logs to see what is calling nextval('My_Sequence')
You may need to turn up logging to find it.
On 7/7/2010 2:59 PM, John wrote:
> I am the only developer, DBA etc.. for a small project. Today (yesterday was
> everything was perfect) many of the sequence numbers fell behind what is the
> actual PK value. For example the invoice PK sequence current value = 1056
> but the table PK was 1071. Nobody (other than myself) knows how to
> edit/access the postgres server. So
>
> 1. Does anyone know how this could have happened?? Other than human
> interaction.
>
> 2. Does anyone have a script to reset the sequences to match the tables?
>
> Thanks in advance,
>
> Johnf
>
>
All legitimate Magwerks Corporation quotations are sent in a .PDF file
attachment with a unique ID number generated by our proprietary quotation
system. Quotations received via any other form of communication will not be
honored.
CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally
privileged, confidential or other information proprietary to Magwerks
Corporation and is intended solely for the use of the individual to whom it
addresses. If the reader of this e-mail is not the intended recipient or
authorized agent, the reader is hereby notified that any unauthorized viewing,
dissemination, distribution or copying of this e-mail is strictly prohibited.
If you have received this e-mail in error, please notify the sender by replying
to this message and destroy all occurrences of this e-mail immediately.
Thank you.
<>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] strangest thing happened
Yes I'm using auto_increment (serial data type). No function, method inserts
PK's anywhere in my code. I'm thinking/guessing it had something to do with
vacumn or the backup. I have been using Postgres for a number of years now
and I never seen this in the past. So I'm really at a loss as how this could
have occurred. The backup is a windows product "exec" and I'm using a
special plug-in from exec for the Linux backup. But I still can't see this
actually happening.
Well I have it running for the moment and I'll have to account black magic as
the cause.
Johnf
On Wednesday 07 July 2010 02:25:13 pm Justin Graf wrote:
> Are you using PG's sequence/auto increment???
>
> If so.
> Once PG fires off the nextval() for the sequence that number is
> considered used and gone even if the transaction that called nextval()
> is rolled back
>
> Depending on how the app is written nextval() might be called, but allow
> the User to cancel the invoice creation before the insert into table is
> completed eating up Invoice numbers
>
> To reset Sequences number call
> Select setval('Sequence_Name', VAlue_To_Set_To);
>
> Most people ignore this kind of annoyance when sequence numbers jump.
> Now if it happens all the time where every X hours eating up Z number of
> sequence numbers then one needs to dig into the logs and figure out what
> is calling nextval()
>
> Search the logs to see what is calling nextval('My_Sequence')
>
> You may need to turn up logging to find it.
>
> On 7/7/2010 2:59 PM, John wrote:
> > I am the only developer, DBA etc.. for a small project. Today (yesterday
> > was everything was perfect) many of the sequence numbers fell behind what
> > is the actual PK value. For example the invoice PK sequence current
> > value = 1056 but the table PK was 1071. Nobody (other than myself) knows
> > how to edit/access the postgres server. So
> >
> > 1. Does anyone know how this could have happened?? Other than human
> > interaction.
> >
> > 2. Does anyone have a script to reset the sequences to match the tables?
> >
> > Thanks in advance,
> >
> > Johnf
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] tsearch2 and wildcards/pattern matching?
Hi all, Is there any sort of pattern matching or partial matching capability in tsearch2? I'd like to be able search for a common prefix (like for order numbers). For instance, tsearch2 will index 'zvt123456' or 'zvt55', but I'd like to be able to find both of them in one query using something akin to 'zvt%', like select * from attachments where textsearchable @@ 'zvt%'; I've been unable to find anything in documentation. If there's anything in tsearch2 that can do this and is faster than LIKE clauses on full-text, that would be fantastic. Cheers, Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] strangest thing happened
On Wed, Jul 07, 2010 at 04:25:13PM -0500, Justin Graf wrote:
> Are you using PG's sequence/auto increment???
>
> If so.
> Once PG fires off the nextval() for the sequence that number is
> considered used and gone even if the transaction that called nextval()
> is rolled back
>
> Depending on how the app is written nextval() might be called, but allow
> the User to cancel the invoice creation before the insert into table is
> completed eating up Invoice numbers
>
> To reset Sequences number call
> Select setval('Sequence_Name', VAlue_To_Set_To);
>
> Most people ignore this kind of annoyance when sequence numbers jump.
> Now if it happens all the time where every X hours eating up Z number of
> sequence numbers then one needs to dig into the logs and figure out what
> is calling nextval()
>
> Search the logs to see what is calling nextval('My_Sequence')
>
> You may need to turn up logging to find it.
Justin, you're missing that John reported that the sequences are
_behind_ the table. This only happens for me if I've been doing
bulk data loads. Then I use:
select setval(sequence_name,max(serial_id_column)) from table_with_serial_id;
You do need to trackdown how this might have happened, though. Any
clever code doing it's own 'serial' incrementing?
Ross
--
Ross Reedstrom, Ph.D. reeds...@rice.edu
Systems Engineer & Admin, Research Scientistphone: 713-348-6166
The Connexions Project http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
>
>
> On 7/7/2010 2:59 PM, John wrote:
> > I am the only developer, DBA etc.. for a small project. Today (yesterday
> > was
> > everything was perfect) many of the sequence numbers fell behind what is the
> > actual PK value. For example the invoice PK sequence current value = 1056
> > but the table PK was 1071. Nobody (other than myself) knows how to
> > edit/access the postgres server. So
> >
> > 1. Does anyone know how this could have happened?? Other than human
> > interaction.
> >
> > 2. Does anyone have a script to reset the sequences to match the tables?
> >
> > Thanks in advance,
> >
> > Johnf
> >
> >
>
>
>
> All legitimate Magwerks Corporation quotations are sent in a .PDF file
> attachment with a unique ID number generated by our proprietary quotation
> system. Quotations received via any other form of communication will not be
> honored.
>
> CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain
> legally privileged, confidential or other information proprietary to Magwerks
> Corporation and is intended solely for the use of the individual to whom it
> addresses. If the reader of this e-mail is not the intended recipient or
> authorized agent, the reader is hereby notified that any unauthorized
> viewing, dissemination, distribution or copying of this e-mail is strictly
> prohibited. If you have received this e-mail in error, please notify the
> sender by replying to this message and destroy all occurrences of this e-mail
> immediately.
> Thank you.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] strangest thing happened
On 07/07/2010 12:59 PM, John wrote:
> I am the only developer, DBA etc.. for a small project. Today (yesterday was
> everything was perfect) many of the sequence numbers fell behind what is the
> actual PK value. For example the invoice PK sequence current value = 1056
> but the table PK was 1071. Nobody (other than myself) knows how to
> edit/access the postgres server. So
>
> 1. Does anyone know how this could have happened?? Other than human
> interaction.
I've never heard of this happening. Are you certain nothing bypassed the
sequence and directly inserted a PK value?
> 2. Does anyone have a script to reset the sequences to match the tables?
Not heavily tested, but something like this might do the trick:
8<--
CREATE OR REPLACE FUNCTION adjust_seqs(namespace text)
RETURNS text AS $$
DECLARE
rec record;
startvalbigint;
sql text;
seqname text;
BEGIN
FOR rec in EXECUTE 'select table_name, column_name, column_default
from information_schema.columns
where table_schema = ''' || namespace || '''
and column_default like ''nextval%''' LOOP
seqname := pg_get_serial_sequence(rec.table_name, rec.column_name);
sql := 'select max(' || rec.column_name || ') + 1 from ' ||
rec.table_name;
EXECUTE sql INTO startval;
IF startval IS NOT NULL THEN
sql := 'ALTER SEQUENCE ' || seqname || ' RESTART WITH ' ||
startval;
EXECUTE sql;
RAISE NOTICE '%', sql;
END IF;
END LOOP;
RETURN 'OK';
END;
$$ LANGUAGE plpgsql STRICT;
select adjust_seqs('public');
8<--
HTH,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & Support
signature.asc
Description: OpenPGP digital signature
Re: [SQL] strangest thing happened
On 7/7/2010 3:42 PM, Ross J. Reedstrom wrote: > > Justin, you're missing that John reported that the sequences are > _behind_ the table. This only happens for me if I've been doing > bulk data loads. Then I use: > > select setval(sequence_name,max(serial_id_column)) from table_with_serial_id; > > You do need to trackdown how this might have happened, though. Any > clever code doing it's own 'serial' incrementing? > > Ross > Yes i did miss read his statement, oops =-O The highest PK value in the table is 1071 but the next sequence is 1056. That's interesting and could be a big problem Quoteing JonF I'm thinking/guessing it had something to do with vacumn or the backup. The backup is a windows product "exec" and I'm using a special plug-in from exec for the Linux backup. But I still can't see this actually happening. -- BakupExec HMMM. Are you doing a file level backup, meaning backing up PGDATA folder or are you doing pg_dump?? I don't think its a backup issue, unless you have done a restore. Which this would say there are more problems else where Are there invoices that use up numbers 1056 to 1071 in that table??? Does the app allow for resetting Sequence in a admin interface??? Many apps have such features and someone could have accidentally rest the value??? I would be looking at the log files for the Inserts into that table as a means to track down what is the cause. If there are no log files or don't have enough detail, crank up the logging level and wait for it to happen again??? All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. <> -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How to find events within a timespan to each other?
Hi, is there a way to find events in a log that happen within a certain timespan to each other? Log is like this event_idinteger not null default 0 primary key event_type_idinteger not null default user_idinteger not null default 0 event_ts timestamp(0) I need every event of a type that happened more often than one time within 5 minutes of another one of the same user. 173 1 ... 12:00 182 193 1 ... 13:03 203 2 ... 13:03 213 1 ... 13:04 222. 233 1 ... 13:05 242 1 ... 13:06 E.g. the checked event_typ_id may be 3 then the result should be line 19, 21, 23 regards Andreas :) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to find events within a timespan to each other?
(anonymous) wrote: > is there a way to find events in a log that happen within a > certain timespan to each other? > Log is like this > event_idinteger not null default 0 primary key > event_type_idinteger not null default > user_idinteger not null default 0 > event_ts timestamp(0) > I need every event of a type that happened more often than > one time within 5 minutes of another one of the same user. > 173 1 ... 12:00 > 182 > 193 1 ... 13:03 > 203 2 ... 13:03 > 213 1 ... 13:04 > 222. > 233 1 ... 13:05 > 242 1 ... 13:06 > E.g. the checked event_typ_id may be 3 then the result > should be line 19, 21, 23 You can use window functions and check whether the preceding or following timestamp is within range: | tim=# SELECT event_id, event_type_id, user_id, event_ts | tim-# FROM (SELECT event_id, | tim(#event_type_id, | tim(#user_id, | tim(#event_ts, | tim(#LAG(event_ts) OVER (PARTITION BY user_id, event_type_id ORDER BY event_ts ASC) AS PrecedingTimestamp, | tim(#LAG(event_ts) OVER (PARTITION BY user_id, event_type_id ORDER BY event_ts DESC) AS FollowingTimestamp | tim(# FROM TestEvents) AS SubQuery | tim-# WHERE (PrecedingTimestamp IS NOT NULL AND event_ts - PrecedingTimestamp <= '5 minutes') OR | tim-# (FollowingTimestamp IS NOT NULL AND FollowingTimestamp - event_ts <= '5 minutes'); | event_id | event_type_id | user_id | event_ts | --+---+-+- |23 | 3 | 1 | 2010-01-01 13:05:00 |21 | 3 | 1 | 2010-01-01 13:04:00 |19 | 3 | 1 | 2010-01-01 13:03:00 | (3 Zeilen) | tim=# Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] strangest thing happened
On Wednesday 07 July 2010 03:14:40 pm Justin Graf wrote: > I would be looking at the log files for the Inserts into that table as a > means to track down what is the cause. If there are no log files or > don't have enough detail, crank up the logging level and wait for it to > happen again??? That is scary - let it happen again I'm not keeping enough info in the log. I actually turned off most of the info the log files are gathering because the system has been running for 6-7 months without an issue. I just got a call around noon telling me something was going wrong. That's when I discovered the sequences were the wrong values. I'm sure there has to be some sort of real explanation - but I don't know what it is. Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] tsearch2 and wildcards/pattern matching?
it's called prefix search: =# select 'zvt123456'::tsvector @@ 'zvt:*'; ?column? -- t On Wed, 7 Jul 2010, Peter Koczan wrote: Hi all, Is there any sort of pattern matching or partial matching capability in tsearch2? I'd like to be able search for a common prefix (like for order numbers). For instance, tsearch2 will index 'zvt123456' or 'zvt55', but I'd like to be able to find both of them in one query using something akin to 'zvt%', like select * from attachments where textsearchable @@ 'zvt%'; I've been unable to find anything in documentation. If there's anything in tsearch2 that can do this and is faster than LIKE clauses on full-text, that would be fantastic. Cheers, Peter Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] tsearch2 and wildcards/pattern matching?
On Wed, Jul 7, 2010 at 5:44 PM, Oleg Bartunov wrote: > it's called prefix search: > > =# select 'zvt123456'::tsvector @@ 'zvt:*'; > ?column? -- > t Ah, that works. Excellent. Thanks, Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] strangest thing happened
On Wed, Jul 7, 2010 at 9:59 PM, John wrote: > I am the only developer, DBA etc.. for a small project. Today (yesterday > was > everything was perfect) many of the sequence numbers fell behind what is > the > actual PK value. For example the invoice PK sequence current value = 1056 > but the table PK was 1071. Nobody (other than myself) knows how to > edit/access the postgres server. So > > 1. Does anyone know how this could have happened?? Other than human > interaction. > > 2. Does anyone have a script to reset the sequences to match the tables? > > Thanks in advance, > > Johnf > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > John, If the insert is performed to triggered table, and that trigger returns null (doesn't insert) then the sequence will increment, but no data will be inserted. If needed I will send you some code examples where it happened to me many times. Sincerely -- --- Viktor Bojović --- Wherever I go, Murphy goes with me
