CODEWITHSUNDEEP
sqlpostgresqlpgadmin
sk786
sk786

Reputation: 414

Importing a pgsql file via pgadmin

I work on MySQL, but one of my client gave me a project that uses postgreSql. He gives me the project files and db file with extension '.pgsql'.

I installed pgadmin and created a test db, but don't know how to import pgsql file. I tries copy-paste of queries in script editor, till tables everything is executing fine, but at the time of data queries, its throwing error.

And data format is also strange, don't know whether its the correct query format or not.

This is the glimpse of pgsql file:

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: kjh; Type: TABLE; Schema: public; Owner: design; Tablespace: 
--

CREATE TABLE kjh (
mknh integer NOT NULL,
jkh integer NOT NULL
);


ALTER TABLE public.kjh OWNER TO design;

--
-- Name: TABLE kjh; Type: COMMENT; Schema: public; Owner: design
--

//..And so on

These lines in pgsql files are throwing error:

--
-- Data for Name: kjh; Type: TABLE DATA; Schema: public; Owner: design
--

COPY kjh (mknh, jkh) FROM stdin;
1   1
\.


--
-- Data for Name: w_ads; Type: TABLE DATA; Schema: public; Owner: design
--

COPY w_ads (id, link, city, type, name, enabled, "time", hits, isimg) FROM stdin;
44  #   -1  0   1   t   1   20  1
\.

Any suggestions?

Upvotes: 0

Views: 579

Answers (1)

Evgeniy Chekan
Evgeniy Chekan

Reputation: 2655

You are likely running into problems with delimiters, try something like this:

COPY kjh (mknh, jkh) FROM stdin WITH DELIMITER ' ';
1 1
\.

This should execute just fine.

Also, note, that your pgsql file have inconsistent spaces count between values, so you may have to prepare this file replacing multiple spaces in values with single delimiter (two spaces with single space delimiter will result in error, since postgres will assume you providing empty column value).

And I would suggest you to choose another delimiter instead of space, something like ; or ^.

Not sure if COPY ... FROM stdin will work from pgadmin (well, it should), but if it fails, try to run this query from CLI using psql command.

For more info on delimiters and copy syntax, refer to docs.

Upvotes: 1

Related Questions