Temp tables and copy - PostgreSQL Database

Konstantin Goudkov

I posted it to the pgsql-hackers list, but I did not seem to get through.

Not even sure if this is a bug or a feature :)

When I create a temp table and then try to copy some data into the
table, if the data is corrupt and the synchronization is lost - the
table also seems to get lost.

For example
create temp table words(aa varchar(254));
copy words from stdin;
some stuff
more stuff
corrupt line\tline\tlin e
more\tstuff
and yet more stuff
\.

(replace ''\t'' with the real tabs to break the synchronization )

after that,

select * from words;
ERROR: Relation "words" does not exist

Running:

Welcome to psql 7.3.4, the PostgreSQL interactive terminal. on FreeBSD dev1.idftech.co m 4.6-RELEASE FreeBSD 4.6-RELEASE #2: Sun Jun 8

04:57:54 EDT 2003
Konstantin Goudkov
IDF Technologies, LLC.
bc*@idftech.com

Nov 11 '05 #1

Subscribe Reply

4692

Richard Huxton

On Wednesday 10 September 2003 02:22, Konstantin Goudkov wrote:

I posted it to the pgsql-hackers list, but I did not seem to get through.

Not even sure if this is a bug or a feature :)

When I create a temp table and then try to copy some data into the
table, if the data is corrupt and the synchronization is lost - the
table also seems to get lost.

A message like this?
lost synchronization with server, resetting connection

Temporary tables get removed when the connection is broken. In any case, all
or nothing of the COPY ... FROM should work, so you can't check what was the
first item that failed.

If you use the \i from psql's command line you can get line numbers for the
file, or "psql -f filename" will do the same.

PS - I've found that writing a small Perl script to process import files and
fix bugs saves me time. It takes longer the first time, but if I need to
re-import (and it seems I always do) then I can just run the perl over my
source data.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #2

Konstantin Goudkov

> A message like this?

lost synchronization with server, resetting connection

Yeah, like that.
I get pissed way too often when people miss simplest things and bug me with
stupid questions, and now it's me.
Sorry guys.

Nov 11 '05 #3

Konstantin Goudkov

> A message like this?

lost synchronization with server, resetting connection

Yeah, like that.
I get pissed way too often when people miss simplest things and bug me with
stupid questions, and now it's me.
Sorry guys.

Nov 11 '05 #4

Similar topics

6581

mysql: how create a temp table as a copy of a existing table?

by: dev | last post by:

how create a temp table as a copy of a existing table and then update1 field and insert the hole temp table back in the existing table? please any help? if i have 10 fields in 1 record and about 100 records and a field.status=1 in a existing_table and i want to create a temp_table with all the recordse and values of the existing_table and then update the field.status to 2 and insert in 1 query the temp_table in the existing_table

PHP

13277

Copying deleted into temp table in trigger

by: Rebecca Lovelace | last post by:

For some reason in Enterprise Manager for SQL Server 2000, I cannot put the following line into a trigger: select * into #deleted from deleted When I hit the Apply button I get the following error: Cannot use text, ntext, or image columns in the 'inserted' or 'deleted' tables This seems like a weird error, since I am not actually doing anything to the inserted or deleted tables, I am just trying to make a temp copy.

Microsoft SQL Server

12529

Temp Table on Linked Server

by: Ootyguy | last post by:

Trying to do this all day and googling for answers but found none, hope someone can help. Thanks in advance. select * into OPENROWSET('SQLOLEDB','SERVER';'uid';'pwd',##test) from LocalTable Reason: I am joining local tables with linked server tables using the format "LinkedServer.database.owner.object" to execute a query, it takes forever to execute since the tables joined on the remote servers

Microsoft SQL Server

597

Temp tables and copy

by: Konstantin Goudkov | last post by:

PostgreSQL Database

3228

Temp rows - is it possible?

by: Boris Popov | last post by:

Hello pgsql-general, I'm trying to implement a table with rows that are automatically deleted when the session that inserted them disconnects, sort of like our own alternative to pg_stat_activity. Is it possible and what approach should I be trying to achieve such a thing? Thanks! --

PostgreSQL Database

4091

Can't access temp-table in backend-db

by: Jon Ole Hedne | last post by:

I have worked on this problem some hours now (read many-many...), and I can't solve it: In vba-code I create a table with Connection.Execute, and add some data to it. This table is saved in the backend-database. After generating this table, I want to open a report based on a query with data from this temporary table. In the report's OnOpen event I set the report's record source to the query pointing to the temporary table. If I run...

Microsoft Access / VBA

10961

How to copy existing table structure to create empty temp table?

by: deko | last post by:

DoCmd.CopyObject copies data, but I only need structure. I'm trying to clone several tables in my Access 2003 mdb. The goal is to link to a series of Excel spreadsheets and then run various action queries against the data in the linked tables (to massage and import it). Since I can't run action queries against linked Excel tables, I need to create temp tables that are clones of the required existing tables in the database then append...

Microsoft Access / VBA

2293

Locking a temp table

by: BillCo | last post by:

I have a function which clears out a temporary table and then fills it with values from a recordset. One of the table fields is indexed with no duplicates. Very rarely - almost enough to ignore it - two users will call the function at once causing one of the functions to attempt to update the table with duplicate values. Is there a way to lock the table to prevent this happening? Function() 'wait until tmpTable is free

Microsoft Access / VBA

4464

Temp tables vs table variables

by: serge | last post by:

I am running SQL Server Best Practices on a SQL 2000 database and it is recommending me to change the temp tables inside SPs to table variables. I had read already in other places to use table variables over temp tables. I also know I can't create indexes as I can on temp tables. Instead I'll have to create either a primary key and/or a unique index on a table variable. One question I have is let's say I will be putting thousands

Microsoft SQL Server

2012

Temp database wouldn't open from jump drive

by: robert d via AccessMonster.com | last post by:

When my app starts up, it creates a temporary database. This temp database is created from a 'model' database that is in the same folder as the application. Because there is a model, the creation is essentially to just copy the 'model' database and give it the name of the application with the extension ".tmp". Then code opens the temp database and links to the tables. This has always worked flawlessly until today. Today, I needed to...

Microsoft Access / VBA

9575

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9407

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

9960

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

7384

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6656

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5280

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

5425

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3931

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2808

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General