[SQL] Re: DB porting questions...
>>>>> "JD" == Jeffrey Diehl <[EMAIL PROTECTED]> writes:
JD> I'm in the final stages of migrating from mysql to postgres and have a few
JD> more questions...
I'm just starting, but I've got two questions. I've found some
scripts out there that claim to do the conversion of the SQL create
commands, but none does the right thing it seems.
I've now found out how to handle the timestamp for insert times and
how to do auto-increment fields.
My unsderstanding of MySQL's enum type is to use something like this
in postgres:
owner_status varchar(9) check
(owner_status in ('pending','active','suspended'))
NOT NULL default 'pending',
But how does one handle the "set" dataype? The archive for the
mailing lists is not helping me find out how to deal with that.
Basically, I have a field with a bunch of flags defining the
attributes of a user, and storing that in a bit-field makes sense.
Currently in MySQL I have this:
owner_features set('premium','haveccinfo') default NULL,
for example. Some other fiels may have about 20 such values, and
MySQL lets me keep these in 3 bytes as a bit-field behind the scenes.
>From what I see, my choice in Postgres is to store this as a
comma-separated string and let my application work as before.
Does anyone have a script that actually handles properly doing auto
increments with the SERIAL type, and does the set/enum conversions?
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.Khera Communications, Inc.
Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: MySql 'REPLACE'
>>>>> "JD" == Jeffrey Diehl <[EMAIL PROTECTED]> writes: JD> Replace into does an insert by default. If however, there is JD> already a record which would cause a unique index collision, then JD> mysql does an update on that record. I want to prevent my JD> application from having to do all of that bookkeeping. No, MySQL does not do an update in that case. It deletes the existing row, then does the insert. This really screws you over when you have auto incrementing (sequence) columns, and screws you when you are not specifying every field in the row (those columns take their defaults, not the original values of the old row). It is really a useless function, as far as I can tell. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: Dateadd
>>>>> "LP" == Ligia Pimentel <[EMAIL PROTECTED]> writes: LP> I need to know if there is a sql function implemented in postgres LP> that gives me a date plus any number of days, months or years (the LP> traditional dateadd function) or how to do it in sql? Here's what I use: select CURRENT_DATE + '4 DAYS'::interval; Just cast your "interval" from a string to an interval type, then add it to your date value. I doubt this is portable. What "tradition" provides for a dateadd() function? -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: START for SERIAL type?
>>>>> "j" == jferry <[EMAIL PROTECTED]> writes: j> When you create a SEQUENCE, you are allowed to specify a START. Is j> there a way to specify a START if you use a serial type in a CREATE j> TABLE statement? No. You do it after you create the table. The SERIAL type creates a sequence, so just use the sequence command setval() to set the starting value for the named sequence. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: How to store a linked list in a RDBMS
>>>>> "SR" == Srikanth Rao <[EMAIL PROTECTED]> writes: SR> I have a linked list representing a tree. How do I SR> store it in the database? Does PGSQL give us any SR> special procedures to deal with such structures? Pick up a data structures book and read up on how to implement a linked list inside an array. This maps directly to an SQL table with a sequential primary key. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: Timestamp without time zone
>>>>> "PS" == Pasi Salminen <[EMAIL PROTECTED]> writes: PS> I am trying to create table with a field which type is timestamp PS> and I don't want it to be timestamp with time zone. This question has been asked at least 4 times since I've been following this list, including once by me. Appaarently, nobody seems to have an answer. The docs imply that type timstamp is without timezone, since there is a type "timestamp with timezone". If you do a "\d tablename" in psql, it shows "timestamp with timezone" on all your fields you declare as timestamp. This leads me to believe that they are silently "upgraded" in their type. I have a subroutine in my apps that strips the timezone before passing it along to the users. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(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
[SQL] Re: Adding an INTERVAL to a variable
>>>>> "GC" == Graham Coates <[EMAIL PROTECTED]> writes: GC> SELECT Invoices.InvoiceDate + INTERVAL '41 Days' GC> works fine GC> but when trying to substitute the number of days with a value form a field GC> e.g. GC> SELECT Invoices.InvoiceDate + INTERVAL Acct.AverageDaysToPay 'Days' try SELECT Invoices.InvoiceDate + Acct.AverageDaysToPay::interval -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Re: Interval FAQ - please review
> "JB" == Josh Berkus <[EMAIL PROTECTED]> writes: JB> Q. What about TIMESTAMP WITH TIME ZONE? JB> A. An important topic, and datatype, that I don't want to get into here. JB> See the PostgreSQL docs. Those docs are lacking an explanation that there is no such thing in PostgreSQL as a timestamp *without* time zone. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Re: Interval FAQ - please review
> "JB" == Josh Berkus <[EMAIL PROTECTED]> writes: >> Those docs are lacking an explanation that there is no such thing in >> PostgreSQL as a timestamp *without* time zone. JB> Hmmm OK, I'll revise the A: but I *don't* want to go into Time Zone JB> issues in this intro. Any docs I can link to? The only good explanation came from the mailing list, from Tom Lane, naturally ;-). Basically, nobody wants to change it, and using a view or a client-side function to strip the timezone info is the way to do it. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: DBD::Pg install error (freebsd)
>>>>> "jj" == jake johnson <[EMAIL PROTECTED]> writes: jj> I'm trying to install (from source) DBD::Pg v1.01 after having jj> installed (from source) DBI v1.19 on FreeBSD 4.3 (Release) and I jj> encounter this error in the 'make test' step: jj> dell_box# make test jj> PERL_DL_NONLAZY=1 /usr/bin/perl -Iblib/arch -Iblib/lib jj> -I/usr/libdata/perl/5.00503/mach -I/usr/libdata/perl/5.00503 test.pl jj> OS: freebsd jj> install_driver(Pg) failed: Can't load 'blib/arch/auto/DBD/Pg/Pg.so' jj> for module DBD::Pg: Shared object "libpq.so.2" not found at jj> /usr/libdata/perl/5.00503/DynaLoader.pm line 169. After you installed postgres, did you run ldconfig -m /usr/local/pgsql/lib to add that shared lib to the system? -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] VARCHAR vs TEXT
> "BM" == Bruce Momjian <[EMAIL PROTECTED]> writes: >> Could you add the length limitation for TEXT to the reference manual? >> I searched high and low for that limit, but never found it. Also, >> what's the max VARCHAR() or CHAR() I can create? Is that also 1Gb? BM> TEXT limit is 1GB, as shown on the 'limits' FAQ item. Is it worth BM> mentioning here? CHAR()/VARCHAR() also 1GB limit. My personal belief is that most FAQ entries could go away if the reference documentation had the necessary information... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?
>>>>> "BM" == Bruce Momjian <[EMAIL PROTECTED]> writes: BM> OK, no one has commented on this, so I guess I am going to have to guess BM> the group's preference. BM> My guess, seeing as very few probably use LIMIT and FOR UPDATE together, BM> is to swap them and document it in the release notes. Was I correct in BM> my guess? My preference is to allow both orders for one release, then only allow the "correct" order in the next. be sure to absolutely make this a big red notice in the changelog. I just scanned my main app and found two instances where I use FOR UPDATE LIMIT 1. These are trivial to change, but difficult to do at the same moment I update the db server. One of these I probably don't even need the LIMIT... -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] adding column with not null constraint
I'm looking to add a column to my database with not null and a default value: vk=> alter table msg_owner add column user_optional_fields varchar(255) NOT NULL default ''; ERROR: Adding columns with defaults is not implemented. Add the column, then use ALTER TABLE SET DEFAULT. vk=> alter table msg_owner add column user_optional_fields varchar(255) NOT NULL ; ERROR: Adding NOT NULL columns is not implemented. Add the column, then use ALTER TABLE ADD CONSTRAINT. Ok, so we can succeed with this: ALTER TABLE msg_owner ADD COLUMN user_optional_fields VARCHAR(255); ALTER TABLE msg_owner ALTER user_optional_fields SET DEFAULT ''; UPDATE msg_owner SET user_optional_fields = ''; Now my problem is I cannot find any syntax for ALTER TABLE ADD CONSTRAINT to put a NOT NULL constraint on a column. Can someone help me here? I'm using Postgres 7.2.1 on FreeBSD 4.6. Thanks. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Question on triggers and plpgsql
On Apr 8, 2005, at 10:59 AM, Andrew Sullivan wrote: wakes up the mail-sender client with the NOTIFY; the NOTIFY and the commit to the mail-it table only happen in that case if the transaction commits. And since mail is async anyway, the extra few seconds shouldn't make any difference, right? I have a lot of processing that could benefit from this type of synchronization, except the fact that there's no Pg command to "wait until I get a notify message". You have to constantly poll to see if you got one, which negates a lot of the benefit of async notification to rarely run processes. Vivek Khera, Ph.D. +1-301-869-4449 x806 smime.p7s Description: S/MIME cryptographic signature
Re: [SQL] getting count for a specific querry
On Apr 8, 2005, at 3:23 PM, Joel Fradkin wrote: I set up the data on 4 10k scsi drives in a powervault and my wal on 2 15k drives. I am using links to those from the install directory. It starts and stops ok this way, but maybe it should be different. Your problem might just be the choice of using a Dell RAID controller. I have a 1 year old box connected to a 14 disk powervault (PowerEdge 2650) and it is dog slow compared to a dual opteron with 8 disks that is replacing it. It is all I/O for me, and the dell's just are not known for speedy I/O. Vivek Khera, Ph.D. +1-301-869-4449 x806 smime.p7s Description: S/MIME cryptographic signature
Re: [SQL] getting count for a specific querry
On Apr 8, 2005, at 4:31 PM, Scott Marlowe wrote: Note that there are several different RAID controllers you can get with a DELL. I had good luck with the PERC 4C (AMI MegaRAID based) at my I've had bad luck regarding speed with *all* of them, AMI MegaRAID and Adaptec based ones, under high load. Under moderate to low load they're acceptable. Vivek Khera, Ph.D. +1-301-869-4449 x806 smime.p7s Description: S/MIME cryptographic signature
Re: [SQL] getting count for a specific querry
On Apr 8, 2005, at 4:35 PM, Bob Henkel wrote: desktop SATA drive with no RAID? I'm by any means as knowledgeable about I/O setup as many of you are but my 2 cents wonders if the Dell RAID is really that much slower than a competitively priced/speced alternative? Would Joel's problems just fade away if he wasn't using a Dell RAID? "Dell RAID" is not one thing. They sell "altered" RAID cards from Adaptec and LSI. Whatever alteration they do to them tends to make them run not so fast. I have a Dell SATA RAID (adaptec based) on the office server and it is OK, though not something I'd buy again. I have various PERC 3 and PERC 4 RAID controllers on my servers (SCSI based) and they suck under heavy I/O load. I wonder why the name-brand LSI cards work so much faster... perhaps it is the motherboard? I don't know, and I don't care... :-) For me, high performance DB and Dell servers are mutually exclusive. Vivek Khera, Ph.D. +1-301-869-4449 x806 smime.p7s Description: S/MIME cryptographic signature
Re: [SQL] getting count for a specific querry
On Apr 8, 2005, at 4:50 PM, Scott Marlowe wrote: Do you run your 2650s with hyperthreading on? I found that slowed mine down under load, but we never had more than a couple dozen users hitting the db at once, so we may well have had a different load profile than what you're seeing. Yep. Turned off as per various recommendations on this list. The RAID card on this box is a PERC 3/DC. It is a very big disappointment. The Opteron based generic system totally outperforms this Dell box. Vivek Khera, Ph.D. +1-301-869-4449 x806 smime.p7s Description: S/MIME cryptographic signature
Re: [SQL] Query history file
On Apr 5, 2005, at 11:29 AM, Mauro Bertoli wrote: From the server side, if you enable 'log_statement' all queries will go into the server logs. Thank you, I enabled log_statement = all log_duration = true You may also want log_min_error_statement = error else any statement that causes an error (such as a typo) will not be logged -- just the error gets logged. Makes debugging of very complex systems much easier to be able to see the full query that caused the problem. Vivek Khera, Ph.D. +1-301-869-4449 x806 smime.p7s Description: S/MIME cryptographic signature
Re: [SQL] getting count for a specific querry
On Apr 12, 2005, at 4:23 PM, Scott Marlowe wrote: How much memory is in the box? I've heard horror stories about performance with >2 gigs of ram, which is why I made them order mine with 2 gigs. Does the 3/DC have battery backed cache set to write back? 4GB RAM and battery backed cache set to write-back mode. FreeBSD 4.11. Vivek Khera, Ph.D. +1-301-869-4449 x806 smime.p7s Description: S/MIME cryptographic signature
Re: [SQL] getting back autonumber just inserted
On Jul 7, 2005, at 4:14 PM, Theodore Petrosky wrote:
you have to use currval inside a transaction...
begin;
insert something that increments the counter;
select currval('sequence_name');
end;
using currval inside a transaction guarantees that the
value is correct for your insert statement and has not
changed by another insert statement.
your understanding of currval() is completely incorrect. no
transaction is required.
smime.p7s
Description: S/MIME cryptographic signature
Re: [SQL] Can EXCEPT Be Used for To Solve This Problem?
On Aug 24, 2005, at 4:47 PM, Lane Van Ingen wrote: I want to select 2nd oldest transaction from foo (transaction 3). The solution below works, but I think there may be a better way. Does anyone else have a better idea? why not just select order by update_time desc limit 2 then discard the first row you fetch? Vivek Khera, Ph.D. +1-301-869-4449 x806 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] question
On Aug 24, 2005, at 1:05 AM, Matt A. wrote:
We used nullif('$value','') on inserts in mssql. We
moved to postgres and love it but the nullif() doesn't
match empty strings to each other to return null other
than a text type, causing an error. This is a major
part of our application.
I *certainly* hope you're not passing $value in straight from your
web form directly into the SQL. You're opening yourself up for SQL
injection attacks.
Why not just have your app that reads the form generate the proper
value to insert? That is the safe route.
Vivek Khera, Ph.D.
+1-301-869-4449 x806
---(end of broadcast)---
TIP 1: 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: [SQL] sysid
On Nov 23, 2005, at 10:45 AM, Alvaro Herrera wrote: A. R. Van Hook wrote: It seems that in 8.1.0 we can no longer set the SYSID when adding users and groups. i.e template1=# create role hooker sysid 1345; NOTICE: SYSID can no longer be specified I have lots of code that depends on the actual group and user number. Is there a way to set the user and group number? No -- fix the code. Just curious, how can user code depend on the SYSIDs? I don't see a way. If 8.1 doesn't allow you to remove a user who still has rights granted, then this should be no problem. however, in older versions you can delete users and leave dangling rights with no way to revoke them unless you create a user with that specific ID and then revoke the rights, and re-delete the user. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Finding context for error log
On Sep 26, 2006, at 10:56 AM, Tom Lane wrote: "log_min_error_statement = error" is what you're looking for. my personal belief is that this should be the default, as the current default (essentially "never") is mostly useless. smime.p7s Description: S/MIME cryptographic signature
Re: [SQL] Partitioning and Foreign Keys
On Nov 4, 2007, at 12:57 PM, Volkan YAZICI wrote: major bottleneck. I planned to partition this table, but I learnt that PostgreSQL doesn't allow referencing views. Does anybody have any suggestions? I can envision writing your own custom trigger instead of using the stock FK trigger that knows which sub-table to check for the FK reference. Then add that as an insert/update trigger on your referencing table. I will be investigating this approach in the next few months; right now I don't have need for this particular case. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Create on insert a unique random number
On Mar 18, 2008, at 1:03 PM, Campbell, Lance wrote: The field n is not random but is sequential. Is there something I should do to make the serial number random? Depending on your "randomness" need, you can alter the increment of the sequence so it changes by a different amount than "1" on every increment, though it will still be constant increment. You need to specify what the purpose of it being random is, then you might get more useful responses. Does it need to be random for some security purpose? If so, describe the level of security, specifically, against what threat are you defending? -- 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] Create on insert a unique random number
On Mar 18, 2008, at 2:40 PM, Campbell, Lance wrote: Why use a random number as a primary key? Security via obscurity. I build web applications for a living. In most of my applications it is preferable to use a random primary key. Why? Don't expose the actual ID to the end user; only expose a reversible encrypted form of it. We use a relatively simple hash + check character. If you have several examples of it, you can reverse engineer it, but the casual "hacker" is easily thwarted. You can use stronger encryption on the number when exposed to end users if you need. You're making your DB overly complex. -- 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 double entries
On Apr 15, 2008, at 11:23 PM, Tom Lane wrote: What's really a duplicate sounds like a judgment call here, so you probably shouldn't even think of automating it completely. I did a consulting gig about 10 years ago for a company that made software to normalize street addresses and names. Literally dozens of people worked there, and that was their primary software product. It is definitely not a trivial task, as the rules can be extremely complex. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
