Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

postgresql problem - script to create tables does not run

Posted on 2016-10-07
8
Medium Priority
?
126 Views
Last Modified: 2016-10-08
In postgresql 9.6 (running under Windows 7 64),  I attempted to run a script to create tables but it showed this error:

ERROR:  table "street_locality_point" does not exist
********** Error **********

ERROR: table "street_locality_point" does not exist
SQL state: 42P01

The problem is that "street_locality_point" does exist in lines 14, and 333 and 334.

What is causing this error?
create_tables_ansi.sql
0
Comment
Question by:gregfthompson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 30

Accepted Solution

by:
Pawan Kumar earned 1000 total points
ID: 41834657
Please Try..

--

CREATE TABLE ADDRESS_ALIAS (
 address_alias_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 principal_pid varchar(15) NOT NULL,
 alias_pid varchar(15) NOT NULL,
 alias_type_code varchar(10) NOT NULL,
 alias_comment varchar(200)
);

CREATE TABLE ADDRESS_ALIAS_TYPE_AUT (
 code varchar(10) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(30)
);

CREATE TABLE ADDRESS_DEFAULT_GEOCODE (
 address_default_geocode_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 address_detail_pid varchar(15) NOT NULL,
 geocode_type_code varchar(4) NOT NULL,
 longitude numeric(11,8),
 latitude numeric(10,8)
);

CREATE TABLE ADDRESS_DETAIL (
 address_detail_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_last_modified date,
 date_retired date,
 building_name varchar(45),
 lot_number_prefix varchar(2),
 lot_number varchar(5),
 lot_number_suffix varchar(2),
 flat_type_code varchar(7),
 flat_number_prefix varchar(2),
 flat_number numeric(5),
 flat_number_suffix varchar(2),
 level_type_code varchar(4),
 level_number_prefix varchar(2),
 level_number numeric(3),
 level_number_suffix varchar(2),
 number_first_prefix varchar(3),
 number_first numeric(6),
 number_first_suffix varchar(2),
 number_last_prefix varchar(3),
 number_last numeric(6),
 number_last_suffix varchar(2),
 street_locality_pid varchar(15),
 location_description varchar(45),
 locality_pid varchar(15) NOT NULL,
 alias_principal char(1),
 postcode varchar(4),
 private_street varchar(75),
 legal_parcel_id varchar(20),
 confidence numeric(1),
 address_site_pid varchar(15) NOT NULL,
 level_geocoded_code numeric(2) NOT NULL,
 property_pid varchar(15),
 gnaf_property_pid varchar(15),
 primary_secondary varchar(1)
);

CREATE TABLE ADDRESS_MESH_BLOCK_2011 (
 address_mesh_block_2011_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 address_detail_pid varchar(15) NOT NULL,
 mb_match_code varchar(15) NOT NULL,
 mb_2011_pid varchar(15) NOT NULL
);

CREATE TABLE ADDRESS_MESH_BLOCK_2016 (
 address_mesh_block_2016_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 address_detail_pid varchar(15) NOT NULL,
 mb_match_code varchar(15) NOT NULL,
 mb_2016_pid varchar(15) NOT NULL
);

CREATE TABLE ADDRESS_SITE (
 address_site_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 address_type varchar(8),
 address_site_name varchar(45)
);

CREATE TABLE ADDRESS_SITE_GEOCODE (
 address_site_geocode_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 address_site_pid varchar(15),
 geocode_site_name varchar(46),
 geocode_site_description varchar(45),
 geocode_type_code varchar(4),
 reliability_code numeric(1) NOT NULL,
 boundary_extent numeric(7),
 planimetric_accuracy numeric(12),
 elevation numeric(7),
 longitude numeric(11,8),
 latitude numeric(10,8)
);

CREATE TABLE ADDRESS_TYPE_AUT (
 code varchar(8) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(30)
);

CREATE TABLE FLAT_TYPE_AUT (
 code varchar(7) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(30)
);

CREATE TABLE GEOCODED_LEVEL_TYPE_AUT (
 code numeric(2) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(70)
);

CREATE TABLE GEOCODE_RELIABILITY_AUT (
 code numeric(1) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(100)
);

CREATE TABLE GEOCODE_TYPE_AUT (
 code varchar(4) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(250)
);

CREATE TABLE LEVEL_TYPE_AUT (
 code varchar(4) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(30)
);

CREATE TABLE LOCALITY (
 locality_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 locality_name varchar(100) NOT NULL,
 primary_postcode varchar(4),
 locality_class_code char(1) NOT NULL,
 state_pid varchar(15) NOT NULL,
 gnaf_locality_pid varchar(15),
 gnaf_reliability_code numeric(1) NOT NULL
);

CREATE TABLE LOCALITY_ALIAS (
 locality_alias_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 locality_pid varchar(15) NOT NULL,
 name varchar(100) NOT NULL,
 postcode varchar(4),
 alias_type_code varchar(10) NOT NULL,
 state_pid varchar(15) NOT NULL
);

CREATE TABLE LOCALITY_ALIAS_TYPE_AUT (
 code varchar(10) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(100)
);

CREATE TABLE LOCALITY_CLASS_AUT (
 code char(1) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(200)
);

CREATE TABLE LOCALITY_NEIGHBOUR (
 locality_neighbour_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 locality_pid varchar(15) NOT NULL,
 neighbour_locality_pid varchar(15) NOT NULL
);

CREATE TABLE LOCALITY_POINT (
 locality_point_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 locality_pid varchar(15) NOT NULL,
 planimetric_accuracy numeric(12),
 longitude numeric(11,8),
 latitude numeric(10,8)
);

CREATE TABLE MB_2011 (
 mb_2011_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 mb_2011_code varchar(15) NOT NULL
);

CREATE TABLE MB_2016 (
 mb_2016_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 mb_2016_code varchar(15) NOT NULL
);

CREATE TABLE MB_MATCH_CODE_AUT (
 code varchar(15) NOT NULL,
 name varchar(100) NOT NULL,
 description varchar(250)
);

CREATE TABLE PRIMARY_SECONDARY (
 primary_secondary_pid varchar(15) NOT NULL,
 primary_pid varchar(15) NOT NULL,
 secondary_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 ps_join_type_code numeric(2) NOT NULL,
 ps_join_comment varchar(500)
);

CREATE TABLE PS_JOIN_TYPE_AUT (
 code numeric(2) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(500)
);

CREATE TABLE STATE (
 state_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 state_name varchar(50) NOT NULL,
 state_abbreviation varchar(3) NOT NULL
);

CREATE TABLE STREET_CLASS_AUT (
 code char(1) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(200)
);

CREATE TABLE STREET_LOCALITY (
 street_locality_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 street_class_code char(1) NOT NULL,
 street_name varchar(100) NOT NULL,
 street_type_code varchar(15),
 street_suffix_code varchar(15),
 locality_pid varchar(15) NOT NULL,
 gnaf_street_pid varchar(15),
 gnaf_street_confidence numeric(1),
 gnaf_reliability_code numeric(1) NOT NULL
);

CREATE TABLE STREET_LOCALITY_ALIAS (
 street_locality_alias_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 street_locality_pid varchar(15) NOT NULL,
 street_name varchar(100) NOT NULL,
 street_type_code varchar(15),
 street_suffix_code varchar(15),
 alias_type_code varchar(10) NOT NULL
);

CREATE TABLE STREET_LOCALITY_ALIAS_TYPE_AUT (
 code varchar(10) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(15)
);

CREATE TABLE STREET_LOCALITY_POINT (
 street_locality_point_pid varchar(15) NOT NULL,
 date_created date NOT NULL,
 date_retired date,
 street_locality_pid varchar(15) NOT NULL,
 boundary_extent numeric(7),
 planimetric_accuracy numeric(12),
 longitude numeric(11,8),
 latitude numeric(10,8)
);

CREATE TABLE STREET_SUFFIX_AUT (
 code varchar(15) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(30)
);

CREATE TABLE STREET_TYPE_AUT (
 code varchar(15) NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(15)
);

--

Open in new window


Enjoy!!
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 41834669
The DROP TABLE on line 14 will cause an error if the table does not exist.  Try...

DROP TABLE IF EXISTS STREET_LOCALITY_POINT;

http://www.postgresqltutorial.com/postgresql-drop-table/
0
 

Author Comment

by:gregfthompson
ID: 41834684
Thanks Pawan and Dave,

I ran Pawan's solution and received this result:

"Query returned successfully with no result in 512 msec."

I am unable to see what is wrong with this script?

Greg
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 1000 total points
ID: 41834702
Did it create the tables?  CREATE TABLE does not return a 'result'.
0
 

Author Comment

by:gregfthompson
ID: 41834713
No tables created.
0
 

Author Closing Comment

by:gregfthompson
ID: 41834714
Thanks heaps.
0
 

Author Comment

by:gregfthompson
ID: 41834721
Oops. It appears one of my messages was not posted. The tables were created after I clicked on the tables name.  Thanks so much for your help.
0
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 41834725
Glad you got it working.  In my copy of PostGreSQL, I had to replace 'REM' with '--' to get the DROP TABLE statements to work.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question