#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    2
    Rep Power
    0

    Unhappy Large SQL file with up to 200mb


    Hello folks,

    I'm Brazilian and a friend of mine recommended this forum.

    When I try to import that SQL file to my PostgreSQL database I get stuck.

    It is a list of Postal Codes from Brazil, it has special characters like "//". It's very large.

    I can't see all of the contents, cuz it gets an error when I try to open it on excel.

    I just can see some lines:

    Code:
    -- SQL Manager for PostgreSQL 5.1.0.3 
    -- --------------------------------------- 
    #NOME? 
    #NOME? 
    -- Version : PostgreSQL 8.4.10 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit 
    
    
    
    SET check_function_bodies = false; 
    -- 
    -- Definition for function sem_acentos (OID = 25419) : 
    -- 
    SET search_path = public, pg_catalog; 
    CREATE FUNCTION public.sem_acentos ( 
    string character varying 
    ) 
    RETURNS varchar 
    AS 
    $body$ 
    SELECT TRANSLATE($1, , aeiouaeiouaoaeiooaeioucAEIOUAEIOUAOAEIOOAEIOUC) 
    $body$ 
    LANGUAGE sql; 
    -- 
    -- Structure for table tend_cidade (OID = 16940) : 
    -- 
    CREATE TABLE public.tend_cidade ( 
    id_cidade integer NOT NULL, 
    cidade varchar(100) NOT NULL, 
    uf varchar(2) NOT NULL 
    ) WITHOUT OIDS; 
    -- 
    -- Structure for table tend_bairro (OID = 16943) : 
    -- 
    CREATE TABLE public.tend_bairro ( 
    id_bairro integer NOT NULL, 
    bairro varchar(50) NOT NULL, 
    id_cidade integer NOT NULL 
    ) WITHOUT OIDS; 
    -- 
    -- Structure for table tend_endereco (OID = 16946) : 
    -- 
    CREATE TABLE public.tend_endereco ( 
    cep varchar(10) NOT NULL, 
    endereco varchar(200) NOT NULL, 
    id_cidade integer NOT NULL, 
    id_bairro integer NOT NULL 
    ) WITHOUT OIDS; 
    -- 
    -- Data for table public.tend_cidade (OID = 16940) (LIMIT 0,500) 
    -- 
    BEGIN; 
    
    INSERT INTO tend_cidade (id_cidade, cidade, uf) 
    VALUES (1, Acrelndia, AC); 
    
    INSERT INTO tend_cidade (id_cidade, cidade, uf) 
    VALUES (2, Assis Brasil, AC); 
    
    INSERT INTO tend_cidade (id_cidade, cidade, uf) 
    VALUES (3, Brasilia, AC);
    And it goes in many more lines, that I just don't know how many.

    How can I import it to PostgreSQL?
    I've tried it by the "CTRL+E" and OPEN, but it get stuck. Then I need to restart Postgresql.

    If I manually insert this code into CTRL+E, it just doesn't work.
    Is it because of this Script?

    Anyone can please help me...I'm frustrated.

    Thank you all,

    Best,

    Yuri Cavazin
    --
    GIS ANALIST
    @ TECSA servios LTDA
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    This:
    Code:
    INSERT INTO tend_cidade (id_cidade, cidade, uf) 
    VALUES (1, Acrelndia, AC);
    is invalid SQL.

    Character values must be enclosed in single quotes like this:
    Code:
    INSERT INTO tend_cidade (id_cidade, cidade, uf) 
    VALUES (1, 'Acrelndia', 'AC');
    Apart from that obvious error I can't see anything wrong with that script.

    But I have to admit that I have no idea what "SQL Manager for Postgres" is and what you mean with "I've tried it by the "CTRL+E" and OPEN".

    Did you try running the file using psql? (using \i or the -f option?)
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    2
    Rep Power
    0

    Question PgAdmin III


    Sorry Shammat, I've forgot to tell you that I'm using PgAdmin III.

    CTRL+E is a SQL Query Window, that I use to do query on my database.

    On that window, I have the option to OPEN a file.
    SQL files can be imported by there.

    When you say:

    Character values must be enclosed in single quotes like this:
    Code:
    INSERT INTO tend_cidade (id_cidade, cidade, uf)
    VALUES (1, 'Acrelndia', 'AC');
    I'm afraid that I cannot do such thing, because I can't see all of my table (it's too big to see in any software that I know, like notepad, excel, etc...)

    When I tried to import it to MySQL, it gives me an error (ERROR SQL SYNTAXE "Without OIDS" on table structure).

    I don't know what to do...I'm afraind that it's useless this SQL file.

    Thanks for the help!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    Originally Posted by ycavazin
    When I tried to import it to MySQL, it gives me an error (ERROR SQL SYNTAXE "Without OIDS" on table structure).
    Well of course. MySQL is a totally different beast.


    I don't know what to do...I'm afraind that it's useless this SQL file
    Did you try this using the command line tool psql? (See the link from my previous answer)

    How did you create this SQL script in the first place?
    Last edited by shammat; June 18th, 2012 at 09:44 AM.

IMN logo majestic logo threadwatch logo seochat tools logo